The result from querying a database is no being displayed

jamdason

I have spent couple of weeks trying to solve the following problem to no avail.

When I enter and execute a search through my form, no result is displayed in the table and the following symbols appears above the table: " }?>

The script for the form is as follows:

<form method="post" action="searchform_results1.php">
<div align="center">
<table border="0" cellpadding="1" cellspacing="1" align="centre">
<tr>
<td>
<select name="searchcategory" size="1">
<option value="firstname">First Name</option>
<option value="surname">Surname</option>
<option value="telephone_no">Telephone</option>
<option value="emailaddress">Email Address</option>
</select> 
<input type="text" name="search" size="30"><br></p>
Search database:<input type="submit" Name="submit" value="Sumbmit" size="20">
 </td>
</tr>
</table>
</div>
</form>

I use the following script to process the query result:

<?php
MYSQL_CONNECT("localhost","twilson","marquis") OR DIE("Database connection unavailable");
mysql_select_db("general") or die("Unable to connect to the database");
//query the table
$query = mysql_query("SELECT * FROM contact_details WHERE $searchcategory LIKE '%$search%' LIMIT 0, 10"));
$num=mysql_num_rows($result);
if (mysql_num_rows($query) ==0)
{
echo "no record match your criteria";}
?>

<h1 align="center">Search Report</H1>
<TABLE align=center cellpaddig=2 border=1>
<TR>
<TH>First Name</TH>
<TH>Surname</TH>
<TH>Telephone No.</TH>
<TH>Email Address</TH>
</tr>

<?php
while ($row = mysql_fetch_array($query))
{
$firstname=$row["firstname"];
$surname=$row["surname"];
$telephone_no=$row["telephone_no"];
$emailaddress=$row["emailaddress"];
//Display results
echo 
"<TR>
<TD>$row[firstname]</TD>
<TD>$row[surname]</TD>
<TD>$row[telephone_no]</TD>
<TD>$row[emailaddress]</TD>
</TR>"
}
?>
</table>
</BODY>
</HTML>

I would be grateful if anyone can advice/assist me to resolve the problem.

Thanks

bradgrafelman

Where do you ever define the variables $searchcategory and $search before you attempt to use them in a SQL query string?

Weedpacket

There are quite a few problems with that code beyond the one you mention (for a start, the code you won't even parse, what with the extra [font=monospace])[/font] on line 5 and a missing [font=monospace];[/font] on line 36).

As for the code you mention - if the quotes at the end of

echo "no record match your criteria";}

don't match those at the start, you'd have that problem - but you haven't got that in the code you posted.

jamdason

Hello Bradgrafelman,

You absolutely correct.

As a complete beginner to the subject who have read through several php & mysql ebooks and related literature from the internet that gave contradictory and misleading information on the subject, I apologised if I appeared to be confused and uninformed.

Thanks for highlighting the bad practice.

Most appreciated.

jamdason

Hello Bradgrafelman,

You absolutely correct.

Thanks for highlighting the bad practice.

Most appreciated.

jamdason

Hello Weedpacket,

Thanks for guiding me in the right direction for solution.

Most appreciate.

Weedpacket

jamdason wrote:
several php & mysql ebooks and related literature from the internet that gave contradictory and misleading information

I recommend checking anything you read against the [man]manual[/man]. It's more likely to be up to date and less likely to be written by someone who doesn't know what they're talking about.

For example, how to get data from forms, the danger of using them in database queries, and the fact that the original MySQL extension is outdated and should no longer be used for anything new.

jamdason

Hello Weedpacket

Thanks for posting sources of relevant information which I can consult.

I have modified the script as follows but still the problem persists.

<?php
$searchcategory=$_POST[‘searchcategory’]; 
$searchterm=trim($_POST[‘searchterm’]); 

if(!$searchterm)){
echo “Search details has not been entered . Please try again.”;
exit;}

mtsql_connect("localhost","xxxxxx","xxxxxx") OR DIE("Database connection unavailable");
mysql_select_db("general") or die("Unable to connect to the database");
$query = "SELECT * FROM contact_details WHERE ".$searchcategory." LIKE '%".$searchterm."%";

$result = mysql_query($query);
$num_result = mysql_num_rows(result);
echo "<h4>".$num_results." results found for ".$searchterm."</h4>";

if($num_result == 0)
{echo "<h3>no record match your criteria. <br>Please try again using difference category and serach term</h3><br>";}

while ($row = mysql_fetch_array($query))
{Same as posted above}
?>

Thanks for all the assistance.

Regards.

NogDog

In the first couple of lines you have directional (left/right) quotes around your $_POST array keys, and again in the echo statement in the ensuing if() block. They need to be straight, non-direction quotes. (Make sure you're using a code/plain-text editor and not a word processor to edit your PHP scripts.)

jamdason

Hello NogDog,

Thanks for spotting the errors in the use of quote symbols which I’ve now corrected. I’ve also spotted and corrected an omitted single quote after the $searchterm between the % symbol and the double quote symbol.

Regards

jamdason

Update

I’ve spotted this error in the script: mtsql_connect("localhost" etc). It should be mysql_connect("localhost" etc);

The scrip is now displaying the data but there is a new problem which I’m trying to resolve.

The problem is: the results for the First_Name column is being displayed along the top of the table rather than inside the First_Name column.

The results for the Surname column are being displayed in the First_Name column.
The results for the Telephone No. column are being displayed in the Surname column.
The results for the Email_Address column are being displayed in the Telephone No. column.
No results are displayed in the Email_Address column.

While I continue my endeavours towards solution I would welcome any suggestions, from more experience individuals on the subject, for overcoming the problem.

Thanks for all the help I’ve received thus far.

Most appreciated.

Jamdason

jamdason

Problem resolved.

Thanks for all the help I’ve received.

Jamdason