1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Increment field using set/mysql in php

Discussion in 'PHP' started by piropeator, Feb 27, 2017.

  1. #1
    Hello everyone. I using Xampp (php/mysql).
    I want to insert a record by incrementing by one the value of the ID field, this field is not autoincremental.
    But it does not show or do anything.
    $sql = "SET @id = (SELECT max(id) from prueba_insert);
    INSERT INTO prueba_insert (id, asiento, correlativo, glosa)
    values (@id:=@id+1 as id, 8, 10, 'GLOSA PARA ASIENTO')";
    $sth = $BD->prepare($sql);
    $sth->execute();
    
    PHP:
    What is my mistake?
     
    Solved! View solution.
    piropeator, Feb 27, 2017 IP
  2. osmasters

    osmasters Well-Known Member

    Messages:
    453
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    100
    #2
    What error are you getting?
     
    osmasters, Mar 4, 2017 IP
    ThePHPMaster likes this.
  3. cesarpa

    cesarpa Greenhorn

    Messages:
    10
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    13
    #3
    Try this (maybe):

    
    $sql = "SET @id = (SELECT max(id) from prueba_insert);
    INSERT INTO prueba_insert (id, asiento, correlativo, glosa)
    values (@id+1, 8, 10, 'GLOSA PARA ASIENTO')";
    $sth = $BD->prepare($sql);
    $sth->execute();
    [code]
    
    But whatever what happens, if you execute this query twice at the exactly same time, you will have two rows with the same ID. You shoud use an autoincrement
    Code (markup):
     
    cesarpa, Mar 8, 2017 IP
  4. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #4
    Are you using PDO or mysqli?

    PDO doesn't allow for multiple queries per querystring.... so you can't SET and INSERT in the same string.

    Also not sure why you're prepare/executing when you are passing/binding no variables to it.

    Though really, if it's an id, WHY isn't it set up as the master key with auto increment? Is there some other field already set to that?!? This looks like brute forcing something you should be letting SQL do for you.
     
    deathshadow, Mar 8, 2017 IP
  5. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #5
    I use PDO. Other way to solve my issue is use:
    $sql = "SELECT max(id) as valorId FROM table";
    $sth = $BD->prepare($sql);
    $sth->execute();
    
    $valor   = $sth->fetch(PDO::FETCH_ASSOC);
    $valorId = $valor['valorId'];   // is not a number (why?)
    
    if (is_null($valorId)){  // Do this if there is not records
        $valorId = 0;
    }
    
    if (is_string($valorId)){  // Do this if there is records.
        $valorId = intval($valorId) + 1;
    }
    
    Code (markup):
    There are any short way to do this?
     
    piropeator, Apr 21, 2017 IP
  6. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #6
    1) lose the $sql variable for NOTHING

    2) If you aren't passing any variables, you don't need prepare/execute, just used PDO->query

    3) PHP is loosely cast.. you shouldn't have to typecast for integer.

    4) fetch returns null when there are no more records, or even no records...

    5) you shouldn't be pulling MAX since race conditions could make that not me the most recently added. Again, let SQL do it's bloody job with an auto-increment field on creating a new one. IF you need the number AFTER doing an INSERT, use lastInsertId

    http://php.net/manual/en/pdo.lastinsertid.php

    Take your original query:

    
    $stmt = $db->query('
    	INSERT INTO prueba_insert (
    		asiento, correlativo, glosa
    	) values (
    		9, 10, 'GLOSA PARA ASIENTO'
    	)
    ');
    echo $db->lastInsertId();
    
    Code (markup):
    If 'id' is set to "auto-increment primary key", PDO->lastInsertId will pull the newly created one for you. You should NOT be screwing around trying to calculate that in the PHP in the first place.
     
    deathshadow, Apr 23, 2017 IP
  7. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #7
    I don't know why you are not letting the database do this for you, but here's another way of doing this:
    This I think will be more reliable than your max(id) query

    $sql = "SELECT id FROM table order by id desc limit 1 ";
    $sth = $BD->prepare($sql);
    $sth->execute();
    $valor = $sth->fetch(PDO::FETCH_ASSOC);
    if(!is_numeric( $valor )){ $valor=1; }else{ $valor= $valor+1; }

    And then insert using the $valor value as id
     
    JEET, May 8, 2017 IP
  8. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #8
    ok. I got it.
    One question about query(), is not necesary to use execute()??
    
    $stmt = $db->query( "statements");
    $stmt->execute();
    
    Code (markup):
     
    piropeator, May 10, 2017 IP
  9. #9
    PDO->query -- should be used when NO values from variables are being plugged in, but results are expected. Automatically executes so you do NOT PDOStatement->execute

    PDO->exec -- should be used when NO values from variables are being plugged in, and NO results are returned by the query. Automatically executes so you do NOT PDOStatement->execute

    PDO->prepare -- the one you use when plugging in values from variables, this is the only one you do a separate PDOStatement->execute on. Also be aware that you can PDOStatement->execute the same prepared statement more than once with different values, either passed by array, or using PDOStatement->bindParam.

    ... and do NOT confuse PDO->exec with PDOStatement->execute -- they are two entirely separate things.
     
    deathshadow, May 11, 2017 IP