Using a Form to Search Database. Why Doesn't My Form Work?

[Tehuti]

I've been having many problems with this one. Any help will be very much appreciated.

All I want is for people to be able to search for particular advertisers on my site, all of which are held in a MySQL table called Advertiser_Table, in a row called advertiser_name.

Here's the form:

<form action="search-results.php" method="post">
<input type="text" name="search" /><br />
<input type="submit" value="Search Advertisers" />
</form>

Here's the first script that I tried on search-results.php:

<?php

$search = $_POST["search"];

$con = mysql_connect("localhost","root");

if (!$con)

{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ctyi", $con);

$result = mysql_query("SELECT * from Advertiser_Table WHERE advertiser_name LIKE '%$search%'");

echo $result;

else echo "Sorry! Couldn't find advertiser.";

?>

It gave this error:

"Parse error: syntax error, unexpected T_ELSE in C:\xampp\htdocs\xampp\search-results.php on line 19"

Here's the second script that I tried:

<?php

$search = $_POST["search"];

$con = mysql_connect("localhost","root","") or die('cant connect: '.mysql_error());
mysql_select_db("ctyi", $con);

$sql = "SELECT * from Advertiser_Table WHERE advertiser_name LIKE '%$search%'";
$result = mysql_query($sql) or die('cant select: '.mysql_error());

while($row = mysql_fetch_assoc($result) )
{
print_r($row);
}

?>

This just returned the contents of an entire row, depending on which advertiser I searched for:

"Array ( [advertiser_id] => 1 [advertiser_name] => AbeBooks.com [advertiser_logo] => abebooks.png [advertiser_url] => abebooks-coupons.php )"

Then I tried this script:

<?php

$search = $_POST["search"];

$con = mysql_connect("localhost","root");

if (!$con)
{
die('Could not connect: ' . mysql_error());
}
else
{
mysql_select_db("ctyi", $con);
$result = mysql_query("SELECT * from Advertiser_Table WHERE advertiser_name LIKE '%$search%'");
}

if(!$result)
{
echo $result;
}
else
{
echo "Sorry! Couldn't find advertiser.";
}

?>

This one nearly worked: it kept echoing, "Sorry! Couldn't find store." no matter what I searched for.

Anyone know how I can fix this?

[cameraman]

The first one is complaining because you don't have an if to match your else.
I don't understand why you're unhappy with the second one - it's giving you exactly what you're asking it for - what do want from it instead?
The third one is also doing what you want. With this:
if(!$result)
{
echo $result;
}
else
{
echo "Sorry! Couldn't find advertiser.";
}

You're telling it: If there's an error in the query (!$result means something's wrong with the query) then show me nothing, and if the query is ok, tell me you couldn't find anything.

There's nothing wrong with the second one - what do you want from the row it returns?

[PHP_Chimp]

Firstly please have a look at mysql_real_escape_string as you dont want to be using $search = $_POST["search"];. As you are not checking or validation this data in any way.

You especially dont want to be allowing this if you are accessing the database as root, as you seem to be doing from $con = mysql_connect("localhost","root");. As allowing users root access to your database is asking for trouble.

Back to your problem:
A slight modification to your first set of code.

<?php
$search = [url=http://www.php.net/manual/en/function.mysql-real-escape-string.php]mysql_real_escape_string[/url]($_POST["search"]);
$con = mysql_connect("localhost","root");
if (!$con) { // checking of connection errors
die('Could not connect: ' . mysql_error());
}
else {
mysql_select_db("ctyi", $con);
$result = mysql_query("SELECT * from Advertiser_Table WHERE advertiser_name LIKE '%$search%'");
if ([url=http://www.php.net/manual/en/function.mysql-num-rows.php]mysql_num_rows[/url]($result) > 0) { // checking that there is a result
echo $result;
}
else {
echo "Sorry! Couldn't find advertiser.";
}
}
?>

Give that a try and see how that goes. As this has a couple of levels of error checking.

[Tehuti]

PHP_Chimp, thank you for the response! However, I tried your code and received these errors:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\xampp\search-results.php on line 3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\htdocs\xampp\search-results.php on line 3
Resource id #3

Even so, I thank you for the effort, and especially for the security advice.

Cameraman, I thank you, too. After considering what you said, I came out with this:

<?php
$search = $_POST["search"];

$con = mysql_connect("localhost","root","") or die('cant connect: '.mysql_error());

mysql_select_db("ctyi", $con);

$sql = "SELECT advertiser_name from Advertiser_Table WHERE advertiser_name LIKE '%$search%'";

$result = mysql_query($sql) or die('cant select: '.mysql_error());

while($row = mysql_fetch_assoc($result) )

{
echo $row['advertiser_name'];
}

?>

It manages to retrieve the data I want (the advertiser_name) but includes no else statement. If there are no matches between search query and advertiser_name, I want it to echo, "Sorry! No matches found." or something like that. How do I incorporate the else statement into this code?

Any help will be appreciated.

[cameraman]

You can have an else, as long as you have an if.

<?php
$search = $_POST["search"];

$con = mysql_connect("localhost","root","") or die('cant connect: '.mysql_error());

mysql_select_db("ctyi", $con);

$sql = "SELECT advertiser_name from Advertiser_Table WHERE advertiser_name LIKE '%$search%'";

$result = mysql_query($sql) or die('cant select: '.mysql_error());
if(mysql_num_rows($result)) {
while($row = mysql_fetch_assoc($result) )

{
echo $row['advertiser_name'];
}
} // EndIf show match(es)
else {
echo "Sorry! No matches found";
} // EndElse no matches
?>

[MattAU]

You need to connect to the database before you use mysql_real_escape_string(), otherwise it tries to connect with default values.

mysql_num_rows() tells you the number of matches, so you can use that in an if statement:

$result = mysql_query($sql) or die('cant select: '.mysql_error());
if(mysql_num_rows($result))
{
while($row = mysql_fetch_assoc($result) )
{
echo $row['advertiser_name'];
}
}
else
{
echo "Sorry! No Matches Found.";
}

[Tehuti]

Cameraman, thank you very much! It worked! I appreciate it, dude.

And thank you, MattAU, for the explanation!