ORDER BY not working

[Dillenger]

I know the ORDER BY is not working, and I know, its because its in the for statement... how would i recode this to make it a while statement instead, so that the oder works? I can't figure it out.

/************************************* BEGIN PROJECT DETAIL DISPLAY **************************************/						

	/* takes the comma seperated values in catID and seperates them */
	$new_cats = "$catID";
	$new_cats = explode(",", $new_cats);

	/* for each seperated value, displays the category that is linked to it from the category table */

	for ($i=0; $i<sizeof($new_cats); $i++)
	{ 
	$sql = "SELECT * FROM category, project_master WHERE category.catID='$new_cats[$i]' ORDER BY category.importance ASC";

	$result = mysql_query($sql);

	$owners = mysql_fetch_array($result);

	$id=$owners['nprojectID'];
	$name=$owners['catSelect'];
	$detail=$owners['description'];
	$importance=$owners['importance'];

	echo "<span class='headers3' STYLE='width:150px'>$name</span><br>";
	}

	echo"</td>";
    echo"<td width=\"875\" bgcolor=\"#F9F9F9\">";


	/* takes the comma seperated values in detailID and seperates them */
	$new_details = "$detailID";
	$new_details = explode(",", $new_details);

	/* for each seperated value, displays the detail that is linked to it from the projectdata_details table */
	for ($p=0; $p<sizeof($new_details); $p++)
	{
	$sql2 = "SELECT * FROM category, project_master, projectdata_details WHERE projectdata_details.projID='$new_details[$p]' ORDER BY category.importance ASC";

	$result2 = mysql_query($sql2);

	$owners = mysql_fetch_array($result2);

	$id=$owners['nprojectID'];
	$name=$owners['catSelect'];
	$detail=$owners['description'];

	echo "<span class='bodytext'>:- $detail</span><br>";
	}

/************************************* END PROJECT DETAIL DISPLAY **************************************/	

[SpanKie]

Couldn't you use the IN clause in your select statement and reduce the numbers of queries in your page.

Would eliminate your FOR LOOP all together.

$sql = "SELECT * FROM category, project_master WHERE category.catID IN ($new_cats) ORDER BY category.importance ASC";

Running a query several times for everything within your array is inefficient...

I'm not sure what data is stored within your array, but its probably not ordering because its only selecting 1 record at a time ... WHERE category.catID = ""

Its appears that each time the loop is going and the query is ran, its pulling only 1 record, until the next loop, where its pulling 1 again. So, it will never order the results, cause there is only 1 result each time. (hope that makes sense)

Use the IN clause, it will ORDER BY then for you..

[paulnaj]

Spankie ... I seem to be following you around.

Seems to me that Dillenger is expecting a set of results for each element of $new_cats.

Dillenger, in order to see what was going on, I re-jigged (the first half of) your code to the following ...

$new_cats = explode(",", $catID); 
for($i = 0; $i < count($new_cats); $i++) {  

	$sql = "SELECT * FROM category, project_master WHERE category.catID='".$new_cats[$i]."' ORDER BY category.importance ASC"; 
	$result = mysql_query($sql); 
	while($owners = mysql_fetch_array($result)){ 
		$id = $owners['nprojectID']; 
		$name = $owners['catSelect']; 
		$detail = $owners['description']; 
		$importance = $owners['importance']; 
		echo '<span class="headers3" style="width:150px">'.$name.'</span><br>'; 
	}
} 

Now, you'll get a list of 'spans' per $new_cat.

Paul.

[SpanKie]

Spankie ... I seem to be following you around.

😃 😃

Nicely done paulnaj...

Unless he is expecting more than 1 result each loop ...

However, I still would still recommend on trying to eliminate the multiple queries, which can be done in 1.

If the array contains alot of values .. that can mean 1, 2, 3, 5, 10 or even more queries, all of which can be done with 1. Making the page alot faster and less load on the server.

If its not a busy site, then what the heck 😉

[paulnaj]

Dillenger,

SpanKie is right ... the 'IN ($new_cats)' method would always be more efficient!

But you might have to change things other things outside of the code you have shown us to make it work. :queasy:

Paul 🙂

[Dillenger]

WHOA! this is a head spinner... thanks for all your help guys. I tried that code you posted, and I still get the same result, because the Select statement is still in the FOR loop. So i'm going to try the IN statement and see if that works. I may have to do a re-order before even inserting the comma seperated values for this to work.

[paulnaj]

Dillenger,

Just noticed something else ... you have listed two tables in the FROM clause but have no join info connecting them ... surely that's not correct!!

Something like ...

SELECT *
FROM category, project_master
WHERE category.catID='".$new_cats[$i]."'

AND category.project_master_id = project_master.id

ORDER BY category.importance

Is that it?
Paulo.