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.

Increasing value in records

Discussion in 'PHP' started by piropeator, Jan 9, 2017.

  1. #1
    I want to insert a group of records from temporal table, increasing the value of field CONTA from the last inserted value at table work. I have this:
    $sql = "SELECT max(CONTA) conta FROM table_work";  // I get the last value of CONTA
    $sth = $BD->prepare($sql);
    $sth->execute();     
    
    $newconta = $row['id'] + 1;    // Increasing +1 
    
    $sql = "INSERT INTO table_work (conta, cod_t2, nom_t2)
    SELECT ".$newconta.", cod_t1, nom_t1
    FROM table_temp
    WHERE  periodo = 201611";
    
    $sth = $BD->prepare($sql);
    $sth->execute();
    
    PHP:
    How do I insert the increment in each record?
    The field conta is no incremental because this is reset every year.
     
    piropeator, Jan 9, 2017 IP
  2. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #2
    I was testing and I've change my requirement to go at the same way.
    I want to update every record of table1 but does not work.
    $sql = "SELECT cod, nom from table1";
    $sth = $BD->prepare($sql);
    $sth->execute();
    
    $valor = 0;
    
    while ($fila = $sth->fetch(PDO::FETCH_ASSOC)) {
        $valor++;
        echo $valor." ";
        echo $fila['cod_stud'];     
        echo "</br>";
    
        $sql= "UPDATE table1 SET orden = ".$valor;    <-- This parte does not work.
        $sth = $BD->prepare($sql);
        $sth->execute();
    }
    PHP:
    Can some body helpme?
     
    piropeator, Jan 10, 2017 IP
  3. kirsl

    kirsl Member

    Messages:
    42
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    26
    #3
    I'm not sure, but that sql command give you last inserted ID and you can use it in further: mysql_insert_id(). Tell me helps it or no.
     
    kirsl, Jan 10, 2017 IP
  4. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #4
    I changed by this:
    $sql = "SELECT cod, nom from table1";
    $sth = $BD->prepare($sql);
    $sth->execute();
    $valor = 0;
    
    while ($fila = $sth->fetch(PDO::FETCH_ASSOC)) {     <---- ERROR: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in
    
        $valor++;
        echo $valor." ";
        echo $fila['cod'];    <-- This part work, show values from $valor and $fila['cod']
        echo "</br>";
    
        $sql= "UPDATE table1 SET orden = ".$valor." WHERE cod = ".$fila['cod'];  <-- Does not work.
        $sth = $BD->prepare($sql);
        $sth->execute();
    }
    PHP:
    The error show is Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error'
     
    piropeator, Jan 11, 2017 IP
  5. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #5
    That's because you're closing the query before you add the $file['cod']-variable to the query. Change the update-query to this:
     
    $sql= "UPDATE table1 SET orden =? WHERE cod =?";
    $sth = $BD->prepare($sql);
    $sth->execute([$valor, $fila['cod']]);
    
    PHP:
    This uses a prepared query, and closes the update query properly.
     
    PoPSiCLe, Jan 11, 2017 IP
  6. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #6
    I get the same error.
     
    piropeator, Jan 11, 2017 IP