Search Mysql table using a variable

[artnow]

Hello

From a form, I want to use a variable $city to search through a table called stores and output all those with matching criteria or return a message saying nothing meeting the search criteria was found.

I have used these forums to get to where I am at (they have been extremely helpful) but now I am at a loss. In testing, I only get the Invalid Query message and for the life of me, I can't seem to see what is wrong with the query.

<?
$connection = mysql_connect('localhost', 'user', 'password');
$db = mysql_select_db('database');

//query

if ( "city" == $city ) {
$sql = mysql_query("SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC");
} else {
die("Invalid Query");
}

$result=mysql_query($sql, $connection);

//initialize arrays
blah, blah, blah

Any help would really be appreciated.

Bob

[zhabala]

What do you mean with this condition ???

if ( "city" == $city )

It means that $city have to contain only 'city' nothing else. Why ??? It is the problem I think.

Zdenek

[artnow]

Hi

Thanks for such a quick response!

The field in the search form is named 'city'
<input type="text" name="city"> so when the form is submitted, I need to pass the variable 'city' from the form to the php script searchresult.php3. So far so good. I tried changing the lines:

if ($city) {
$sql = mysql_query("SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC");
} else {
die("No records found");
}

$result=mysql_query($sql, $connection);
if (! $result)
{
die("Invalid Query");
}

using your suggestion I changed the code as such:

if ($city) {
$sql = mysql_query("SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC");
} else {
die("No records found");
}

$result=mysql_query($sql, $connection);
if (! $result)
{
die("Invalid Query");
}

Now I get an 'Invalid Query' error.

Thanks again,

Bob

[artnow]

Sorry, the original code was:

if ( "city" == $city ) {
$sql = mysql_query("SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC");
} else {
die("No records found");
}

$result=mysql_query($sql, $connection);
if (! $result)
{
die("Invalid Query");
}

and the new code is using your suggestion:

if ($city) {
$sql = mysql_query("SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC");
} else {
die("No records found");
}

$result=mysql_query($sql, $connection);
if (! $result)
{
die("Invalid Query");
}

[zhabala]

You are trying to call mysql_query twice. it should look like:

if ($city) {
$sql = "SELECT * FROM stores WHERE city = '$city' ORDER BY 'ranking' DESC";
} else {
die("No records found");
}

$result=mysql_query($sql, $connection);
if (! $result)
{
die("Invalid Query");
}

[artnow]

Yes, now I get it. Thank you. I am now onto displaying the results in a table. This is what I have so far:

//start while loop to store results of a query into arrays
while($res=mysql_fetch_array($result))
{
$company[]=$res["company"];
$address[]=$res["address"];
}
//start for loop to retrieve data from arrays
for($i=0; $i {
echo "$company[$i]
";
}

So far, all I get is an empty page. Thanks for your help. For a non-programmer like myself this is challenging but fun.

Bob

[Anon]

I do not understand what are you trying to do, but when you want to show content of array you can do:

foreach ( $company AS $value ) {
echo "$value";
}

or you can display the values in the while loop.

for($i=0; $i {
echo "$company[$i";
}
this is incorrect it can look like
for ($i=0; $i < count($company); $i++ ) {
echo $company[$i];
}

Zdenek

[artnow]

Hi again.

I was actually just showing you where I was starting. I combed the forums and managed to learn how to put the results into an array and then display them in a table. It works great! Appending to what we started with I added:

$number = mysql_num_rows($result);
if($number == 0){ echo "Sorry, no listings found.";
}

//start while loop to store results of a query into arrays

while($res=mysql_fetch_array($result))
{
$company=$res["company"];
$address=$res["address"];
?>

Results are here using the expression <?php print "$variable name" ?> plus regular HTML (with quote marks removed) for the table itself.

<?
; }

?>

Thanks so much for all your help!

Bob