PHP/MYSql PDO Select with variable

Question

Edit: Ok, so while this does work great for $ID when I set it equal to 28, it is not working for instance below if I was trying to get the $name = $TableRows['name']; variable. I know I could select it, but other variables require this.

$ID = 28; 
$name = $TableRows['name']; 

try { ini_set('display_errors', true); 
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$stmt = $conn->prepare(" SELECT ? AS name FROM TABLE1 e WHERE id = ?"); 
$stmt->execute(array($name, $ID));
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
}
}

I'm in the process of transitioning from MySQLi to PDO. My problem is I'd like to modify one of the values in my SELECT query, similar to how I set my WHERE value of ID to $ID = 28. Does anyone know how instead of just outputting millisecondstime from the SELECT query, I could change it to output $minutes $secondsrounded $millisecondsround instead? This is the idea that I am going for:

$stmt = $conn->prepare("SELECT name, $minutes, $secondsrounded, $millisecondsround FROM TABLE1 e

This is my entire query:

<?php
echo "<center><div style='max-width: 1100px'><table id ='myTable' class='display' cellspacing='0' width='100%'>
    <thead>
    ";
echo "<tr><th>Name</th><th>Time</th></tr></thead>";

class TableRows extends RecursiveIteratorIterator { 
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }

    function current() {
        return "<td style='width: 150px; border: 1px solid black;'>" . parent::current(). "</td>";
    }

    function beginChildren() { 
        echo "<tr>"; 
    } 

    function endChildren() { 
        echo "</tr>" . "\n";
    } 
} 

$servername = "localhost";
$username = "";
$password = "";
$dbname = "";

$name = $TableRows['name'];

$milliseconds = $TableRows['millisecondstime'];

$time = $milliseconds / 1000;
$days = floor($time / (24*60*60));
$hours = floor(($time - ($days*24*60*60)) / (60*60));
$minutes = floor(($time - ($days*24*60*60)-($hours*60*60)) / 60);
$seconds = ($time - ($days*24*60*60) - ($hours*60*60) - ($minutes*60)) % 60;

$str_length = 3;
$millisecondsround = substr("000{$milliseconds}", -$str_length);
$secondsrounded = str_pad($seconds, 2, '0', STR_PAD_LEFT);

$ID = 28;   

try {
    ini_set('display_errors', true);
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("SELECT name, millisecondstime FROM TABLE1 e 
        WHERE ID = ?
        ORDER BY millisecondstime ASC
        "); 

    $stmt->execute(array($ID));
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

That isn't a very good db design having minutes, seconds and miliiseconds as column names. Why would you want to do that? — Funk Forty Niner
– Funk Forty Niner, Commented Nov 15, 2018 at 23:03
They're not the column names. They are the variables I'm setting and manipulating from the millisecondstime value. — duke
– duke, Commented Nov 15, 2018 at 23:09
Ok. Well Barmar seems to have a solution for you; give that a whirl. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 15, 2018 at 23:10

Barmar · Accepted Answer · 2018-11-15 23:04:57Z

4

You can use placeholders in the SELECT list, and they'll be replaced with the values from the parameter array.

$stmt = $conn->prepare("
    SELECT name, ? AS minutes, ? AS secondsrounded, ? AS millisecondsround 
    FROM TABLE1 e 
    WHERE id = ?");
$stmt->execute(array($minutes, $secondsrounded, $millisecondsround, $ID));

answered Nov 15, 2018 at 23:04

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Funk Forty Niner Over a year ago

Can you really do that?

Barmar Over a year ago

Yes. Placeholders can be used wherever expressions are allowed. You can write SELECT 1 AS minutes FROM ...

Funk Forty Niner Over a year ago

heh, interesting.

Barmar Over a year ago

I actually have code that does something like this.

Barmar Over a year ago

And while I teach 99% of the time, every now and then I learn something new.

|

Difster · Accepted Answer · 2018-11-15 22:48:12Z

1

Try this instead:

$stmt = $conn->prepare("SELECT name, millisecondstime FROM TABLE1 e
WHERE ID = :ID
ORDER BY millisecondstime ASC
"); 

   $stmt->execute(array('ID' => $ID));

answered Nov 15, 2018 at 22:48

Difster

3,2802 gold badges24 silver badges32 bronze badges

2 Comments

Barmar Over a year ago

It doesn't come from the table, it's a variable he set earlier in the script.

duke Over a year ago

Thanks, but if I add the other variables to the array, I'll get: Invalid parameter number: number of bound variables does not match number of tokens. I still need to set millisecondstime equal to those three variables. $stmt->execute(array('ID' => $ID, 'millisecondstime' => $minutes $secondsrounded $millisecondsround));

Collectives™ on Stack Overflow

PHP/MYSql PDO Select with variable

2 Answers 2

16 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related