Where and Order BY ID does not work

Question

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/>';

            }
            ?>

`'SELECT * FROM application where status = {$status} order by id desc limit 0,30'` is eclosed witf `'` (single quotes). Enclose it with `"` double quotes — roullie, Jun 20 '16 at 01:34
You are getting an exception (I believe) but you don't check the returned string. You need something like `if(is_array($app)){ foreach(...)} else { echo 'oops, error' . $app; }` — Ken Y-N, Jun 20 '16 at 01:40

score 1 · Answer 1 · answered Jun 20 '16 at 01:44

your query is correct, but you've enclosed it with ', just change it to " like following:

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

And it should work fine. Because strings with single quotes are returned as it is (a string) including variables. In your case query being run on MySQL is looking for rows with status value of '$status' literally.

score 0 · Accepted Answer · answered Jun 20 '16 at 01:37

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

Thanks a lot mate. i really appreciate your quick support. – Jamal ALharthy Jun 20 '16 at 01:57 — Jamal ALharthy, Jun 20 '16 at 01:57

Where and Order BY ID does not work

2 Answers2