Passing ORDER BY using variable PHP

Question

I have the next code but I am not getting the desired result by using ORDER BY:

// OrderBy WHERE Conditions
if ($sort_by == "0")    $sortby_condition = "ORDER BY mv_user_ranking.ranking_date DESC ";
if ($sort_by == "1")    $sortby_condition = "ORDER BY mv_user_info.age DESC ";
if ($sort_by == "2")    $sortby_condition = "ORDER BY mv_user_info.sex DESC ";

$query ="SELECT * FROM mv_user_info 
         LEFT JOIN mv_user_lang_interested ON mv_user_lang_interested.uid = mv_user_info.uid        
         LEFT JOIN mv_user_disponibility   ON mv_user_disponibility.uid   = mv_user_info.uid    
         LEFT JOIN mv_user_ranking         ON mv_user_ranking.uid         = mv_user_info.uid   

         WHERE country ='$country' AND city = '$city' AND
               mv_user_lang_interested.english = '1' AND
               mv_user_lang_interested.english_level = '2' AND 
               mv_user_info.uid != '$uid'"

         .$sortby_condition.

         "LIMIT 0, 50";

echo $query;

$result = mysql_query($query) or die(mysql_error());

What I am doing wrong?

it seems ok... Have you tried to see what the resulting (that you echo) query returns in phpmyadmin or similar tool? — kasitan
– kasitan, Commented Sep 29, 2013 at 14:17
"I am not getting the desired result" <-- then what do you get? And what do you desire? — Sumurai8
– Sumurai8, Commented Sep 29, 2013 at 14:21
the variable $sort_by doesn't have the value you expect. Inspect its value. — goat
– goat, Commented Sep 29, 2013 at 14:23
echo $sort_by to see what the value is. You could change it to an if/elseif/else, to give a default sort in case the value is not 0,1,2 — Sean
– Sean, Commented Sep 29, 2013 at 14:23

Sabbir · Accepted Answer · 2013-09-29 14:35:58Z

This looks interesting, ok lets try it

// OrderBy WHERE Conditions
//i don't know what was passed as $sort_by value
//perhaps you may try 
$sort_by = (integer)$sort_by;  //casting 

if ($sort_by == 0){$sortby_condition = "ORDER BY mv_user_ranking.ranking_date DESC ";}//if () if() three times not an optimize way, try if, elseif
elseif ($sort_by == 1){    $sortby_condition = "ORDER BY mv_user_info.age DESC ";}//write your code in block "{}" this may prevent your block of statement mix
elseif ($sort_by == "2"){    $sortby_condition = "ORDER BY mv_user_info.sex DESC ";}

$query ="SELECT * FROM mv_user_info 
         LEFT JOIN mv_user_lang_interested ON mv_user_lang_interested.uid = mv_user_info.uid        
         LEFT JOIN mv_user_disponibility   ON mv_user_disponibility.uid   = mv_user_info.uid    
         LEFT JOIN mv_user_ranking         ON mv_user_ranking.uid         = mv_user_info.uid   

         WHERE country ='$country' AND city = '$city' AND
               mv_user_lang_interested.english = '1' AND
               mv_user_lang_interested.english_level = '2' AND 
               mv_user_info.uid != '$uid'"

    .$sortby_condition.

    "LIMIT 0, 50";

echo $query;

$result = mysql_query($query) or die(mysql_error());

if any problem feel free to ask better you give whole lines of text you right, then We can answer you better :)

asim-ishaq · Accepted Answer · 2013-09-29 14:35:55Z

Technically, i don't see any issue with the query but you can do one thing: Create another query over this query, so that this query becomes a subquery and then sort from the above query. Your code will look like this:

// OrderBy WHERE Conditions
if ($sort_by == "0")    $sortby_condition = "ORDER BY ranking_date DESC ";
if ($sort_by == "1")    $sortby_condition = "ORDER BY age DESC ";
if ($sort_by == "2")    $sortby_condition = "ORDER BY sex DESC ";

//Note I have removed the table name from order by clause because in the super-query we will only have column name.

$query ="
Select * From (
 SELECT * FROM mv_user_info 
     LEFT JOIN mv_user_lang_interested ON mv_user_lang_interested.uid = mv_user_info.uid        
     LEFT JOIN mv_user_disponibility   ON mv_user_disponibility.uid   = mv_user_info.uid    
     LEFT JOIN mv_user_ranking         ON mv_user_ranking.uid         = mv_user_info.uid   

     WHERE country ='$country' AND city = '$city' AND
           mv_user_lang_interested.english = '1' AND
           mv_user_lang_interested.english_level = '2' AND 
           mv_user_info.uid != '$uid'
     LIMIT 0, 50) as a ".$sortby_condition;

echo $query;

$result = mysql_query($query) or die(mysql_error());

I hope this helps

Collectives™ on Stack Overflow

Passing ORDER BY using variable PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related