Searching MySQL db

[jamescover]

Hi:

I'm using a MySQL db, PHPMyAdmin, and PHP:

Please, what is wrong with the following query:


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

Here is my form:

<form action="http://www.ekigroup.com/dbdemo/getModels.php" method="post" name="mForm2">
<select name="getModels" onchange="this.form.submit();">
<option selected value="**">Select a Make</option>
<option value="Chevy">Chevy</option>
</select>
</form>

Here is the error msg:

Unknown column 'Chevy' in 'where clause'

Here are the columns in my table:


year | make | model | link

A typical row looks like this:

2000 | Chevy | Cavalier | http://www.myDomain/blahblah...

Why is it looking for a Column named "Chevy" instead of a row containing "Chevy" in the make column?

I want to return all/only "Chevy" models in my table--not the year, make, or link.

For example, I have a dropdown selection list that is populated with all makes. Once someone chooses a single make, I want to populate another dropdown list with all of the models for that particular make.

So, if they choose "Chevy" from the first selection list, the second selection list will be populated with, say:


Cavalier
Impala
Malibu
S-10 Pickup
Silverado Pickup
etc.

???


-james

[sad69]

Try this for humour's sake:

$query = "SELECT * FROM myTableName WHERE make = $getModels ORDER BY model ASC";
echo 'query: '.$query.'<br>';
$models = mysql_query($query) or die(mysql_error());


This way you can see what query is being tried on your database and can help you with debugging.

Also, you don't pass a reference to your database, like $db, in mysql_query(). I suppose it isn't needed, but it's best to be as explicit as possible.

If you don't see anything wrong with the query that's printed to the screen, try the same query through phpMyAdmin and see what happens. If you're still stuck, post back.

Sadiq.

[dumpfi]

You must use quotes. Otherwise it searches for entries where column 'make' has the same value as column 'Chevy' (in your example).
$models = mysql_query('SELECT * FROM myTableName WHERE make = "'.$getModels.'" ORDER BY model')
or die (mysql_error());dumpfi

Edit: if you only want all the models use 'SELECT model FROM ...' instead of 'SELECT * FROM ...'

[jamescover]

Thanks to trib4lmaniac and dumpfi for getting me headed in the right direction.

Sadiq, below are the results of the query using the code you gave me:


query: SELECT * FROM showme WHERE make = Chevy ORDER BY model ASC

Unknown column 'Chevy' in 'where clause'


I posted the script below, and commented it out, according to what I think is happening. I'd appreciate it, if someone could go line by line and correct any errors. Again, I have very little db experience (about a weeks worth). Anyway, I have used a variation of this to populate the first (make) selection list successfully, but I don't know enough to see why this one won't work in a similar manner.





<?php

//connect to db server, if fails, print error msg

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

//select which db, if fails, print error msg

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

//create variable = db query, and query db, if fails, print error msg

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

//if any rows contain results (return true) from query, print form elements

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

//start printing form elements

print "<select name='model'>";
print "<option value='**' selected>Select a Model</option>";

//cycle thru (while) rows and create array of db query results

while ($row = mysql_fetch_array ($models)) {

//create another variable = results returned for each row

$r_model = $row[model];

//keep adding (while) options as long as there are rows returned in query

print "<option value='$r_model'>$r_model</option>";
}

//print this, when no more rows are returned in query

print "</select>";

} else {

//if query is successful, but no matching results are returned, print this:

print "No models in database!";
}

//close connection to db server

mysql_close($db);

?>


You must use quotes. Otherwise it searches for entries where column 'make' has the same value as column 'Chevy' (in your example).

if you only want all the models use 'SELECT model FROM ...' instead of 'SELECT * FROM ...'



I had already tried both of these, without success. They produce an
altogether different error, so I have posted the entire code above. No doubt, I have made some error, or don't understand the logic, or both....


Here is the error I get:

Fatal error: Call to undefined function: mysql_num_row() in /home/ekigroup/www/www/formTest/getModels.php on line 10


when I use:


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



Thanks, for your assistance...


-james

[jamescover]

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


Dumpfi:


When I use the above(with my actual table name, of course), I get a blank page--no errors, no results. The same goes for SELECT model.


-james

[dumpfi]

Here is the error I get:

Fatal error: Call to undefined function: mysql_num_row() in /home/ekigroup/www/www/formTest/getModels.php on line 10
The function is named mysql_num_rows(). That's why the error.


<form action='file_name.php'>
<?php
$db = mysql_connect ('localhost', 'login', 'password') or die (mysql_error());
mysql_select_db ('dbName', $db) or die(mysql_error());
$models = mysql_query ('SELECT model FROM showme WHERE make = "'.$_GET['getModels'].'" ORDER BY model') or die (mysql_error());
if(mysql_num_rows($models)) {
echo '<select name="model"><option value="**" selected>Select a Model</option>';
while($row = mysql_fetch_row($models)) {
echo '<option value="'.$row[0].'">'.$row[0].'</option>';
}
echo '</select><input type="submit" value="Search">';
}
else echo 'No models in database!';
mysql_close($db);
?></form>dumpfi

[jamescover]

The function is named mysql_num_rows(). That's why the error.


YAHOO!!! Man, it's always something stupid with me, a missing quotation mark, typo...something :D

I must have gone over that script a hundred times, and missed it. I thought, it makes sense to me...why doesn't it work.

Anyway, thanks, dumpfi, you're the best! It works like a charm :thumbsup:

I appreciate your help. I'm still trying to decipher the other code you posted in the other thread.

Thanks to you too Sadiq.


-james