PHP Versions Question posted in
You can find a list of all versions here.

Php versions – mySQL query in a PHP script

I need to specify "value/key" pairs within my URL to add data in a mySQL DB. For example : adding values of ‘x’ and ‘y’ with mysite.com/myphpscript.php?x=1&y=2. This works, but now, if a "value/key" pair is missing, like so : mysite.com/myphpscript.php?x=1, i want to add the value of ‘x’ (new row in my DB) and keep the previous value of ‘y’ in the new row.

I have this query in SQL that is working fine :

SET @x= '1';
SET @y = '2'; 

INSERT INTO myDB (x, y)
  SELECT
    COALESCE(@x, x) AS x,
    COALESCE(@y, y) AS y
FROM myDB
ORDER BY id DESC
LIMIT 1;

This is the version that i have in my PHP script :

$x= $_GET['x']; // these values come from a pressed button in a webpage with the "value/key pairs" in the URL
$y= $_GET['y']; 

$sql = "INSERT INTO myDB (x, y) 
        SELECT 
            COALESCE('" . $x. "', x) AS x, 
            COALESCE('" . $y. "', y) AS y 
        FROM myDB
        ORDER BY id DESC 
        LIMIT 1";

(Of course any other query is OK as long as the result is the same)

i tried multiple variations like "COALESCE(‘$x’, x) AS x"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for your answers. I managed to make it work with following code :

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
        $sql = "INSERT INTO myDB (x, y)
             SELECT 
                    COALESCE(:x, x) AS x, 
                    COALESCE(:y, y) AS y
                    FROM myDB
                    ORDER BY id DESC 
                    LIMIT 1";
                
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':x', $x);
        $stmt->bindParam(':y', $y);
        $stmt->execute();

  2. 0

    When you merge NULL with string, null is converted to empty string. So, when you you coalesce it in your query, it’s already not null.

    What you could do would be something like this:
    I’m asuming you are using PDO, but if you are usiny mysqli or anything else, you will have to modify your code according.

    if ($x===null) $x='NULL'; 
else $x= $pdoConnection->quote($x);

$y=$_GET['y']??null;
if ($y===null) $y='NULL'; 
else $y= $pdoConnection->quote($y);

$sql="INSERT INTO myDB (x, y) 
        SELECT 
            COALESCE(" . $x. ", x) AS x, 
            COALESCE(" . $y. ", y) AS y, 
        FROM Pins 
        ORDER BY id DESC 
        LIMIT 1";

    But even better way would be to use prepared statements, for example like this:

    $sql="INSERT INTO myDB (x, y) 
        SELECT 
            COALESCE(:x, x) AS x, 
            COALESCE(:y, y) AS y, 
        FROM Pins 
        ORDER BY id DESC 
        LIMIT 1";
$statement = $pdoConnection->prepare($sql);
$statement->execute(['x'=>$_GET['x']??null,'y'=>$_GET['y']??null];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search