Sorting and displaying mysql results without multiple queries??

[young_nickodemus]

Hi all,

 

I am really new to PHP and am a little stuck.

 

I am trying to populate a dropdown menu with items from a database so that a user can select which button they see on their personalised homepage.

 

The database has two fields 'bid' (which is the button id) and 'type' (which is the category the button is in).

 

I want to display the results in the dropdown menu so that they appear sorted by type, with the type name as a heading (disabled) for each list of buttons underneath it, then a space before the next type heading and list of buttons. (hope that makes sense).

Eg. the dropdown would look kinda like this:

 

TYPE 1

button1

button2

button3

 

TYPE 2

button4

button5

button6

 

....and so on. (there are a lot of buttons to choose from).

 

Ideally, i guess, i want to query the database once, then just sort through the results, but i've got a mental block on how i can do this!

 

Currently i am query the database for every different type and it seems to be a really slow way of doing it....my code looks kinda like this...

 

function buttonNames($buttonNumber) {
        // get which user is logged in
$un = $_SESSION['username'];

        // get which button the user currently has set for their homepage
$q = mysql_query("SELECT $buttonNumber FROM users WHERE username='$un'")
or die(mysql_error());

$SBarray = mysql_fetch_array($q);
$selectedButton = $SBarray[0];

        // get all the button id's for the category type1
$resultTYPE1 = mysql_query("SELECT * FROM buttons WHERE type='type1'")
or die(mysql_error());
echo "<option disabled=\"disabled\" style=\"font-weight:bold; color:#000\">Type 1</option>";
while($row = mysql_fetch_array($resultTYPE1)){
                // if the button id is the same as the users current button make this the selected one in the form, else just add it normally
	if($selectedButton == $row['bid']){
		echo "<option selected=\"selected\">";
	} else {
		echo "<option>";
	}
	echo $row['bid'];
	echo "</option>";

}

// repeat as above but for type2
$resultTYPE2 = mysql_query("SELECT * FROM buttons WHERE type='type2'")
or die(mysql_error());
echo "<option disabled=\"disabled\"></option><option disabled=\"disabled\" style=\"font-weight:bold; color:#000\">TYPE2</option>";
while($row = mysql_fetch_array($resultTYPE2)){
	if($selectedButton == $row['bid']){
		echo "<option selected=\"selected\">";
	} else {
		echo "<option>";
	}
	echo $row['bid'];
	echo "</option>";

}

// repeat it again for type3
$resultTYPE3 = mysql_query("SELECT * FROM buttons WHERE type='type3'")
or die(mysql_error());
echo "<option disabled=\"disabled\"></option><option disabled=\"disabled\" style=\"font-weight:bold; color:#000\">TYPE3</option>";
while($row = mysql_fetch_array($resultTYPE3)){
	if($selectedButton == $row['bid']){
		echo "<option selected=\"selected\">";
	} else {
		echo "<option>";
	}
	echo $row['bid'];
	echo "</option>";		
}				 
}

 

 

I then call this function from inside the form using, for example:

buttonNames('BUT1');

 

The user can personalise multiple buttons on their homepage.

 

 

I really hope that all makes sense!!

 

Any help to speed up the page load time would be greatly appreciated!

 

Thanks,

 

Nick

 

Don't forget CODE tags next time (The # button)

 

[gevensen]

try downloading TOAD free

it will help you make complex mysql statements in php

[young_nickodemus]

helpful.

[young_nickodemus]

try downloading TOAD free

it will help you make complex mysql statements in php

 

It's not the mysql statement i'm struggling with; i know how to call the data from the database that i want/need, it's the PHP that i'm struggling with.

 

Once i've got the data from the database what's the best way to filter through it extracting it in the right order and putting subheadings in the right place?

 

I'd really appreciate some help on this.

 

cheers.