Posted by Rik on 05/16/06 23:05

TristaSD wrote:
> Very effifient. Mine is a monster - I did a query inside a query:
>
> $locationquery = mysql_query ("SELECT * FROM locations");
> $comparequery = mysql_query ("SELECT location FROM reservations WHERE
> id=" . $_GET['id']);
> while ($compareresult = mysql_fetch_array ($comparequery)) {
> while ($locationrow = mysql_fetch_array ($locationquery)) {
> echo ("<option");
> if ($locationrow['name'] == $compareresult['location']) {
> //the magic happens here
> echo (" selected");
> }
> echo (">$locationrow[name]</option>");
> }
> }
>
> Because $comparequery fetches only one value, any way to retrieve it
> BEFORE going into the forst loop?


First of all, you don't need the first while loop,. I assume it's only on
record that has that certain id, so you can just:
$compareresult = mysql_fetch_array ($comparequery)
while ($locationrow = mysql_fetch_array ($locationquery)) {
if ($locationrow['name'] == $compareresult['location']) {
echo (" selected");
}
}

It's possible in one query:

if(isset($_GET['id'])&&$_GET['id']!=''){
$id = mysql_rel_escape_string(GET['id']);
$query = "SELECT x.name, IF(x.name=y.location, ' selected=\"selected\"',
'') as `selected` FROM locations x, reservations y WHERE y.id=$id";
} else {
$query = "SELECT name, '' as `selected` FROM locations";
}

$locationquery = mysql_query($query);
while ($locationrow = mysql_fetch_array ($locationquery)){
printf('<option%s>%s</option>', $locationrow['selected'],
$locationrow['name']);
}

I'm not enterily sure it's quicker though... My MySQL is still not as it
should be.

Grtz,
--
Rik Wasmus

[Back to original message]