multiple queries, one script

[jamescover]

Hi:

Thanks for bearing with me, as I learn PHP and db queries. I did a search but didn't find anything directly addressing my question...

I want to make two queries with a single script, because I want to be able to output different results, in the event that there is no match for the first query, which searches for a variable passed from a select menu. But there is one option in the select menu that has no match in the db, so I want to return different results, which in this case, is all rows (All Models) for the given column (Models).

I have gotten this to work, but it doesn't seem intuitive. I tried using the if and else clauses with the actual queries, but without success. Would someone be willing to show me the proper way of going about this--how to script it--but produce the same results.


<?php

$db = mysql_connect ("localhost", "username", "password") or die (mysql_error());

mysql_select_db ("mydbname", $db) or die(mysql_error());

//$getModels is the name of my select, and all options but one match rows in the db

$models = mysql_query ("SELECT model FROM mytablename WHERE make = '$getModels' ORDER BY model ASC") or die (mysql_error());

//This query is used to return all models, when the "VIEW ALL MODELS" option is selected, since it doesn't match any row in the db

$allModels = mysql_query ("SELECT model FROM mytablename ORDER BY model ASC") or die (mysql_error());

if (mysql_num_rows ($models) != 0) {

echo "<form name=\"oForm2\" method=\"post\" action=\"getResults.php\">\n";
echo "<select name=\"getModels\">\n";
echo "<option value=\"**\" selected>Select a Model</option>\n";

while ($row = mysql_fetch_array ($models)) {
$r_model = $row[model];

echo "<option value=\"$r_model\">$r_model</option>\n";
}
echo "</select></form>";

} else if (mysql_num_rows ($allModels) != 0) {

echo "<form name=\"oForm2\" method=\"post\" action=\"getResults.php\">\n";
echo "<select name=\"getModels\">\n";
echo "<option value=\"**\" selected>Select a Model</option>\n";

while ($row = mysql_fetch_array ($allModels)) {
$r_model = $row[model];

echo "<option value=\"$r_model\">$r_model</option>\n";
}
echo "</select></form>";

} else {
echo "<form name=\"oForm2\">\n";
echo "<select name=\"getModels\">\n";
echo "<option value=\"**\" selected>Select a Model</option>\n";
echo "<option value=\"null\">No models in database</option>\n";
echo "</select></form>";
}

mysql_close($db);

?>

I've commented the above--hope it helps.

Below is a link to the working scripts (again, this works as desired):

http://www.ekigroup.com/dbdemo/testPage.php


Any help is appreciated. Thanks!


-james

[raf]

<?php
/* these should be inside a seperate firl that you then include like require('myconnectionfile_zfsdfsdf4442.php')
$db = mysql_connect ("localhost", "username", "password") or die (mysql_error());

mysql_select_db ("mydbname", $db) or die(mysql_error());
*/
$error='';
$models = mysql_query ("SELECT model FROM mytablename WHERE make = '" . $getModels . "' ORDER BY model ASC") or die (mysql_error());
if (mysql_num_rows ($models) < 1) { // no records for the chosen value
mysql_free_result($models);
$models = mysql_query ("SELECT model FROM mytablename ORDER BY model ASC") or die (mysql_error());
if (mysql_num_rows ($models) < 1){
$error='No records in that table !';
}
}
if (strlen($error) >= 1){
echo $error;
}else{
echo '<form name="oForm2" method="post" action="getResults.php">' . "\n" .
'<select name="getModels" id="getModels" size="1">' . "\n" .
'<option value="**" selected="selected">Select a Model</option>' . "\n";

while ($row = mysql_fetch_array ($models)) {
echo '<option value="' . $row['model'] . '">' . $row['model'] . '</option>' . "\n";
}
echo '</select></form>';

}
mysql_free_result($models)
?>

[jamescover]

Hi raf:


Thanks for responding!!! I appreciate the help.

I tried your code, but it doesn't quite work, although, I like the formatting and flow better, so I'll just modify it to suit. Thanks!!!

For your info, the only problem arose when I added an unknown value to the Makes select, then submitted it...the script returns All makes in the db, instead of the error message, as expected, i.e., it doesn't return and error for a non-matching query.

Granted, there should never be a non-matching query, thus, never a need for an error message, because the first select is populated from the db too, but just the same, I would rather remove the error message altogether.

Actually, I just realized that this is the case if Select a Make is selected as well, so I may have to code for that, in any event, even thought that would only occur if someone used the browser back button...


Thanks, again, for your help. Your script gave me lots of insight into how things are scripted in PHP, when querying db's.



-james