I am trying to get rows from a table when a condition is satisfied (status = 'no transit') but nothing shows up even when rows are supposed to show up (count is 1 and more).
if($query['num'] == 0){
echo "<p>No shopping orders on transit</p>";
}else{
$sql = "SELECT *, FORMAT(total, 0) AS total, FORMAT(grand_total, 0) AS grand_total FROM shipping_details WHERE status = 'no transit' ORDER BY order_id DESC";
foreach ($db->query($sql) AS $query){
echo" Show some results ";
$select = "SELECT * FROM shipping_order WHERE order_id = :order_id";
foreach ($db->query($select, array('order_id' => $query['order_id'])) AS $items){
echo"
Some results
";
//Foreach ends
}
}
}
It is not working, because you forgot to use prepare and execute methods from pdoStatemnt class.
See below:
$stmt = $db->prepare("SELECT * FROM shipping_order WHERE order_id = :order_id");
$stmt->execute(array('order_id' => $query['order_id']));
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
echo"
Some results
";
//Foreach ends
}
You don't show enough that we can tell which codebase you use to connect to your DB (MySQLi, mysql_, or PDO), so the code below may need some tweaking.
The problem is basically that you never retrieve your database results. Instead you try to loop through the query execution itself.
Change
$sql = "SELECT *...";
foreach ($db->query($sql) AS $query)...
To
$sql = "SELECT *...";
$result = $db->query($sql); //execute the query
if(!$result) die($db->error); //exit and show error if query failed
//now we can fetch the results one at a time and loop through them
//this line may need to be adjusted if you're not using MySQLi
while($row = $result->fetch_assoc())...
Within the while loop, $row will contain the values from the DB record. Use print_r($row) to learn its shape.