sql from mysql in php

[asaidi]

Hi
how i can use this sql in php..

SET @a = 0;  
UPDATE van SET position = @a:=@a+1 where day='2';===>can be variable it goes from 2 to 30

thanks

[cereal]

By using PDO you can execute two prepared statements, for example:

<?php

    try {
        $pdo = new PDO("mysql:dbname=db", "user", "pass");
    }

    catch (PDOException $e) {
        die('Connection failed: ' . $e->getMessage());
    }

    $value1 = 10;
    $value2 = 2;

    $stmt = $pdo->prepare("SET @a = ?");
    $stmt->execute(array($value1));

    $stmt = $pdo->prepare("UPDATE van SET position = @a:= @a + 1 where day = ?");
    $stmt->execute(array($value2));

But if you do not need @a somewhere else, then you can execute one single query:

    $stmt = $pdo->prepare("UPDATE van SET position = ? + 1 where day = ?");
    $stmt->execute(array($value1, $value2));

[asaidi]

Hi
thank you very much
that i sthe solution

[asaidi]

Hi
sorry about the last message that i said solved..
the problem when i pass to the second record the position dows not update..

<?php
$hostname = "localhost";
    $dbname = "route";
    $username = "root";
    $pw = "root"; 

    try {
        $pdo = new PDO("mysql:host=$hostname;dbname=$dbname", "$username", "$pw");
    }
    catch (PDOException $e) {
        die('Connection failed: ' . $e->getMessage());
    }
    $value1 = 0;
    $value2 =array(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26);

    foreach($value2 as $id){ 
     $stmt = $pdo->prepare("SET @a = ?");

    $stmt->execute(array($value1));


          $stmt = $pdo->prepare("UPDATE van SET `position` = @a:= @a + 1 WHERE day='$id'");


      $stmt->execute($value2);
    }     
    header('Location:index.php ');   


?>

value2 is for the day...when the day is 1
1 1==>position
1 2
1 3
1 1
but when the day is 2
2 1
2 4 ===>position did not updated
2 3
2 2

[cereal]

You're inserting $id directly in the prepared statement, use the array in the execute method:

$stmt = $pdo->prepare("UPDATE van SET `position` = @a:= @a + 1 WHERE day = ?");
$stmt->execute(array($id));

This should fix the issue.

[asaidi]

Thank you very much
it did