CODEWITHSUNDEEP
phpmysql
duke
duke

Reputation: 13

PHP/MYSql PDO Select with variable

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>";
?>

Upvotes: 1

Views: 168

Answers (2)

Barmar
Barmar

Reputation: 780688

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));

Upvotes: 4

Difster
Difster

Reputation: 3270

Try this instead:

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

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

Upvotes: 1

Related Questions