Select when form processing

[LinusIT]

I've got a form with two drop down menus which parse numerical values, I can see in the email being sent that the values are being parsed but the select query isn't working.

Here's the "broken" code I've got:

//Vehicle Details
$getmakesql = "SELECT manufacturer_id, manufacturer_name FROM manufacturer WHERE manufacturer_id = '".$make."'";
$getmakeqry = mysql_query($getmakesql) or die(mysql_error());
$getmakerow = mysql_fetch_row($getmakeqry);
$getmakeresult = $getmakerow[2];

$getmodelsql = "SELECT model_id, model_name FROM model WHERE model_id = '".$model."'";
$getmodelqry = mysql_query($getmodelsql);
$getmodelrow = mysql_fetch_row($getmodelqry);
while($getmodelresult = mysql_fetch_row($getmodelrow))
{
$getmodel = $getmodelresult[1];
}

You'll see I've tried different methods but still no joy.

Here's what is sent in the email:

Name: Test
Make: 1
Make:
Model: 3
Model:

The empty Make & Model are where $getmodel & $getmakeresult

The Make & Model that are populated are displayed using $make & $model

Any help greatly appreciated.

[jspeed]

First off, I always make it a point to backtick my field names and table names to ensure none of them are reserved for mysql. e.g:

$getmakesql = "SELECT `manufacturer_id`, `manufacturer_name` FROM `manufacturer` WHERE `manufacturer_id` = '".$make."'

(which you can do so by holding alt and pressing 96 FYI)

Second add the mysql_error(); syntax to your $getmodelquery to see if there are any errors.

Run the code and echo the results to specify if there are errors in your php, or if the data is getting lost in the email process.

[rocknbil]

While jspeed is correct, there's nothing conflicting in your field names, the query **should** be fine.

It's the indexing that is wrong. Look:

$getmakesql = "SELECT manufacturer_id, manufacturer_name . . .

The indices for those are 0 and 1

$getmakeresult = $getmakerow[2];

There is no 2.
However, I will add, when you query numeric fields, quoting is **not** required:

... manufacturer_id = '".$make."'";
... model_id = '".$model."'";

Why is this relevant? If those values are empty/null (which they aren't, in this case) it will still select when quoted, without error. It just won't return any results. If left unquoted, it will error - so if you add an error trap on your selects, it will alert you to the problem sooner.

Second, look at your second query - I think you need to execute mysql_fetch_row on getmodelqry, not $getmodelrow. $getmodelrow is already an array of fields (in your initial implementation.)

Revised, note nesting, no need for second query if first doesn't succeed. :-)

$getmakesql = "SELECT manufacturer_id, manufacturer_name FROM manufacturer WHERE manufacturer_id =$make"; 
// **ONLY add mysql_error() for debugging - it reveals info about your database 
// which could be of use to a potential hacker 
$getmakeqry = mysql_query($getmakesql) or die("Cannot select make at line 1234"); 
if ($getmakerow = mysql_fetch_row($getmakeqry)) { 
 $getmakeresult = $getmakerow[1]; 
 $getmodelsql = "SELECT model_id, model_name FROM model WHERE model_id=$model"; 
 $getmodelqry = mysql_query($getmodelsql) or die("cannot get model at line 1238"); 
 // Only if is required, should be only one result, right? 
 // if multiples, return to while 
 if($getmodelrow = mysql_fetch_row($getmodelqry)) { 
 $getmodel = $getmodelrow[1]; 
 } 
 else { echo "<p>No results found for model.</p>"; } 
} 
else { echo "<p>No results found for make.</p>"; } 

Error trapping is your friend. :-)

[LinusIT]

Thank you ever so much for this, it has worked perfectly.

I never did clearly understand how you used $name[0] but know I know exactly how to use it.

The only problem I've got now is if I select a make but not a model and the form is submitted I get "No results found for model."

I am using "LiveValidation" to validate the forms but as the page I'm using uses Ajax to populate the contents of Model based on Make, once I select a make the validation is ignored.

Thanks again :)

[rocknbil]

The only problem I've got now is if I select a make but not a model and the form is submitted I get "No results found for model."

Which is exactly what you want, isn't it, select both a make and model? If not, just alter the logic . . . overall I'd do this at the top level with a join so you don't have to query multiple times. Try something like this - note how the select is build conditionally.

$where = $jn = $result = null; // Squelches concatenation errors/warnings 
$fields = array('mfg_id','name','model_id','model_name'); 
// 
// When you do joins, you should (sometimes, and in this case must) specify the table 
$select = "SELECT manufacturer.manufacturer_id as mfg_id, manufacturer.manufacturer_name as name"; 
if (is_numeric($make) and ($make > 0)) { 
 $where = " manufacturer.manufacturer_id=$make"; // NOTE SPACE 
 $from = " from manufacturer"; 
} 
if (is_numeric($model) and ($model > 0)) { 
 // Only need AND if $where has been set 
 if ($where) { $where .= ' and'; } 
 $where .= " model.manufacturer_id=$model"; 
 // The if's are for future potential expansion, see comment belo about YEAR 
 if (! $from) { $from .= ",model"; } 
 $select .= ", model.model_id as model_id, model.model_name as model_name"; 
 if (! $jn) { $jn = " left join model on manufacturer.manufacturer_id=model.manufacturer_id"; } 
} 
// Put them all together 
$getsql = "$select $from"; 
if ($where) { $getsql .= " where $where"; } 
if ($jn) { $getsql .= $jn; } 
// 
// You'll need this - my code is typed on the fly and may have errors 
// echo $getsql; 
// exit; 
// 
$getqry = mysql_query($getsql) or die("Cannot select make and model");  
while ($getmakerow = mysql_fetch_row($getmakeqry)) {  
 $result .= '<tr>'; 
 foreach ($fields as $fld) { 
 // Left joins will return null results if no matching left value 
 // is found - which is the beauty of it 
 $val = (isset($getmakerow[$fld]))?$getmakerow[$fld]:'-'; 
 // Might need this instead 
 // $val = (! empty($getmakerow[$fld]))?$getmakerow[$fld]:'-'; 
 $result .= "<td>$val</td>"; 
 }  
 $result .= '</tr>'; 
}  
if ($result) {echo "<table> $result </table>"; } 
else { echo "<p>No results found for make $make.</p>"; }  

Which could potentially give you

<table>
<tr><td>1234</td><td>Toyota</td><td>5673</td><td>Camry</td></tr>
<tr><td>1236</td><td>Toyota</td><td>657</td><td>Celica</td></tr>
<tr><td>896798</td><td>Toyota</td><td>45436</td><td>Pickup</td></tr>
<tr><td>1238</td><td>Ford</td><td>-</td><td>-</td></tr> <!-- no make found for this record -->
<tr><td>1256</td><td>Mini</td><td>678</td><td>Cooper</td></tr>
</table>

I pulled a fast one on you - in this instance we're referencing the returned data by field name, not by array index.

($getmakerow[$fld]) = )
$getmakerow['model_id']

as opposed to $getmakerow[0], $getmakerow[1], etc. Fetch row and fetch array return both indexed arrays and associative arrays. When dealing with left joins, it's a little easier to manage them by name.

A reminder, this is all typed on the fly, probably contains errors so don't copy and paste but play around with it, it will afford you much more flexibility. For example, what if you want to add the make year? Most people do in vehicle searches . . .

if (is_numeric($year) and preg_match('/^\d{4}$/',$year)) { 
 if ($where) { $where .= ' and'; } 
 $where .= " model.year=$year"; 
 if (! $from) { $from .= ",model"; } 
 $select .= ", model.year as year"; 
 if (! $jn) { $jn = " left join model on manufacturer.manufacturer_id=model.manufacturer_id"; } 
} 

And of cours add year to your fields array.