Jamal ALharthy Jamal ALharthy - 5 months ago 17
SQL Question

Where and Order BY ID does not work

I have an issue and i have looked and found many solution on Stack overflow site,
however when I try it with my code it does not seem to work. I was hoping that someone may help find out what is wrong with my code.

some of the solution on stack overflow

mysql query with where and order by take long time

I am trying to select a record from the database where status = $status, however the response is always as follows:


Warning: Invalid argument supplied for foreach() in /Users/khloudamer/Documents/Websites/BusinessDoorFinal2/index.php on line 98


The method is as follows:

public static function readAlllisted($status){
try{

// = pending ORDER BY id DESC'
$db = Database::getInstance();
$dbh = $db->getConnection();
$results = $dbh->query('SELECT * FROM application where status = {$status} order by id desc limit 0,30');//select * from data where cat_id=12 order by id desc limit 0,30
return $results;
}catch(Exception $e){
return $e->getMessage();
}


I have tried

(SELECT * FROM application where status = {$status} order by id desc limit 0,30')
(SELECT * FROM application where status = '$status' order by id desc limit 0,30')

AND
SELECT * FROM application where status = $status order by id desc limit 0,30


and still nothing works

my table structure is as follows

1 id Primary int(11)
2 _customer_id char(8)
3 personal_id int(11)
4 emp_id int(11)
5 fin_id int(11)
6 status varchar(100)


The way i am calling the method is as follows >>

$app = Application::readAlllisted("pending");
foreach( $app as $r){
echo $r['status'];
echo '<br/>';
echo $r['personal_id'];
echo '<br/>';
echo $r['emp_id'];
echo '<br/>';

}
?>

Answer

since #statut is a varchar so you should put it between '".$statut."' in your query try it again using this :

$results = $dbh->query("SELECT * FROM application where status = '".$status."' order by id desc limit 0,30");

after this request try to debug it and see the result content by typing

$app = Application::readAlllisted("pending");
print_r($app);//to see if the query is really getting result or not if every thing is okay so you can print the data you want by acceding the associative array