Displaying by using alternate method avoiding loopings

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Displaying by using alternate method avoiding loopings

Post by shivam0101 »

I have a table from which i have to display like,


result_details
res_det_id (pk, auto_inc)
question_id
numbers
marks
character_matching




Numbers matching 5 characters are:
matching character numbers
.....


and below is the code i am using to display. It is working fine, but is it possible to make the whole thing in one query. Instead of writing a query to get max number and then looping every time, as it will take more resource and time.

Code: Select all

$query_numbers=mysql_query("SELECT MAX(character_matching) FROM results WHERE question_id=27");
	$fetch_max=mysql_fetch_array($query_numbers); 
	$max_number=$fetch_max['MAX(character_matching)'];
	
	$result_details="<form method='post' action='final_result_edit.php'><table border=1 width='400'>";
	
	if($max_number >0)
	{
		while($max_number >=1)
		{
		   $query_results=mysql_query("SELECT * FROM result_details WHERE character_matching=$max_number");
		   if(mysql_num_rows($query_results)>0)
		   {
			$result_details.="<tr><td colspan='2' align='center'>Numbers matching $max_number characters are: </td></tr>";	   
			while($fetch_result=mysql_fetch_array($query_results))
			{
			  $numbers=$fetch_result['numbers'];
			  $marks=$fetch_result['marks'];
			 
			  $result_details.="<tr><td>$numbers</td> <td><input type='text' name='mark_text[$marks]' value='$marks'></td></tr>";
			}
		   }	
		   $max_number--;
		}
		$result_details.="<tr><td align='center' colspan='2'><input type='submit' name='submit' value='submit'></td></tr></table></form>";
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

In one query.. no.

But it is possible to use less queries.. and avoid those nasty queries inside the loop. What you do is do a query to get the information you need. Loop through the result set and store it in an array. Then you reference this array inside the main loop.

example:

Code: Select all

$results = mysql_query("SELECT `id` FROM `users` WHERE `username` LIKE '%a'") or die(mysql_error());

$ids = array();
while($array = mysql_fetch_assoc($results))
{
    $ids[] = $array['id'];
}

$ids = implode(',', $ids);

$results = mysql_query("SELECT `somethingelse` FROM `table` WHERE `id` IN($ids)") or die(mysql_error());
In that example (which isn't relevant to your problem, but the coding is), we grabbed all of the `id`s in one query. That way, instead of querying for a single id each time you go through the loop, we can query for all of the `id`s in one query, and not do it inside of the loop.

Things can get much more complicated than this. But arrays are good usage in this problem.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply