Can't ORDER BY when using a variable in MySQL query!

[ipfreely]

Hello,

 

Having one heck of a time trying to sort some results from an array. If I run my query with static info, ORDER BY works fine. When I attempt to use a variable in the query, ORDER BY is ignored. I'm including a code sample. Could use a little help!

 

Thanks,

 

Ipfreely

 

Code:

*****************************************************************************************************

 

while ($row = $result1->fetchRow(DB_FETCHMODE_ASSOC)){

$factoryid = $row['factoryid'];

 

// Get factory list

$query2="SELECT * FROM factoryinfo WHERE factoryinfo.factoryid = '$factoryid' ORDER BY rating DESC";

$result2=$connection->query($query2);

 

if (DB::isError($connection)){

die("Could not connect to the database: <br />".DB::errorMessage($connection));

}

if (DB::isError($result2)){

die("Could not query the database: <br />". $query2." ".DB::errorMessage($result2));

}

 

while ($result_row = $result2->fetchRow(DB_FETCHMODE_ASSOC)){

 

    echo '<tr align="center" valign="middle"><td>';

    echo $result_row['rating'] . '</td><td>';

    echo $result_row['factoryname'] . '</td><td>';

    echo $result_row['factorycountry'] . '</td><td>';

    echo $result_row['factoryowner'] . '</td><td>';

    echo '<a href="' .$result_row['lcfactinfo'] . '">Info Page</td><td>';

    echo '<a href="http://' .$result_row['factoryweb'] . '">'.$result_row['factoryweb'].'</td></tr>';

 

}

}

 

 

[Greaser9780]

$query2="SELECT * FROM factoryinfo WHERE factoryid = '$factoryid' ORDER BY rating DESC";

 

[ipfreely]

Thanks for the fast reply. I had originally had the query as suggested:

 

$query2="SELECT * FROM factoryinfo WHERE factoryid = '$factoryid' ORDER BY rating DESC";

 

This had the same result of ignoring the ORDER BY. I can see what's going on but I can't figure out the syntax. Since $query2 is in a while loop, it encounters several records meeting the criteria for $factoryid. Since it's evaluating the records one at a time, the ORDER BY rating DESC is true regardless of the rating field value because it's one record at a time. What I believe I need to do is get all the records into an array and THEN do an ORDER BY rating DESC. I just can't seem to work out the syntax. Does this seem to be the case?

 

Thanks!

 

Ipfreely

[Greaser9780]

The way you had it meant you were trying to call the field from the table during the where section.