I am doing a database search using php. Everything works rights except when I just press the enter button (not searching anything) or a match is not found. In those two situations, I want a message displaying no items were found. Currently, when I search for nothing by pressing submit, all the entries show up. When I search for something that isn't there, the table headers display. How can I fix this?

Also, this is a video game site and I would like links to display what is for sale for each system (Xbox, Wii, Playstation, etc). I know I could do seperate pages and have a select statement that selects everything with that system Id but would there be a more efficient way to create the page instead of having 24 different pages?

Below is the code for searching:

<link rel="stylesheet" type="text/css" href="styles.css" />
<?php # search_games.php
// This script searches the available selections for a given product name.

// Set the page title and include the HTML header.
$page_title = 'Search the Video Game Database';

$name = $_POST['select_name'];

$query = "SELECT productName, price, image_name, systemName, genre
          FROM products, systems, genre
          WHERE productName LIKE '%$name%'
          AND systems.systemid = products.systemid
          AND genre.genreid = products.genreid";

// Set up the database connection.
require_once ('mysqli_connect.php');

// Run the query and retrieve the result
$result = mysqli_query ($dbc, $query);

// If the result is not empty
if ($result <> '') {

	// Create the table head.
	echo '<center>Click on a selection below to add to your shopping cart.</center>';
	echo '<table border="1" width="90%" cellspacing="3" cellpadding="3" align="center">
	<tr>
	<td align="middle" width="20%"><b>Video Game Name</b></td>
	<td align="middle" width="30%"><b>Price</b></td>
	<td align="middle" width="30%"><b>System</b></td>
	<td align="middle" width="30%"><b>Genre</b></td>
	<td align="middle" width="30%"><b>Image</b></td>
	</tr>';

	while ($row = mysqli_fetch_array ($result, MYSQLI_ASSOC)) {

		// Display each record.
		echo 	'<tr>
			<td align="middle">' . $row['productName'] . '</td>
			<td align="middle">' . $row['price'] . '</td>
			<td align="middle">' . $row['systemName'] . '</td>
			<td align="middle">' . $row['genre'] . '</td>
			<td align="middle"><IMG SRC=images\/' . $row['image_name'] . '></td>

		</tr>';

	} // End of while loop.

	// Close the table.
	echo '</table>';

	// Free up the resources.
     	mysqli_free_result($result);

}
else {
	echo 'No result matching the search criterion.<br>';
}

echo '<p><center><a href="query_games.html">Search Again</a></center><p>';

mysqli_close($dbc); // Close the database connection.

?>

I am adding something to condition in line no 23

if ($result <> '' && trim($name) !='') {
Member Avatar for rajarajan2017

Instead of

if ($result <> '')

check with

if (mysql_num_rows($result) > 0 && $name!='')

I tried using the num_rows function but the page always said no search results found. The below code works except that when I search for something not in the database, it displays the table header, not the no search results message.

Also one odd problem, the "no search..." message is being displayed above the site header, instead of below. The table header is going below though.

<HTML>
<HEAD>
<link rel="stylesheet" type="text/css" href="styles.css" />
<TITLE>Search the Video Game Database</TITLE></HEAD>

<BODY><center>
<table cellspacing="5" cellpadding="5">
<tr>
<td><img src='http://img7.imageshack.us/img7/6237/newlogocp.jpg' border='0'/><br><br>
</td>
<FORM ACTION="./search_games.php" METHOD="post">
<td>
Enter a game name to search:<br>
<INPUT TYPE="text" NAME="select_name">
<BR>
<INPUT TYPE="reset" value="Reset"><INPUT TYPE="submit" VALUE="Search">
</FORM></td>
</tr>
</td></tr>
<?php
$name = $_POST['select_name'];

$query = "SELECT productName, price, image_name, systemName, genre
          FROM products, systems, genre
          WHERE productName LIKE '%$name%'
          AND systems.systemid = products.systemid
          AND genre.genreid = products.genreid";

// Set up the database connection.
require_once ('mysqli_connect.php');

// Run the query and retrieve the result
$result = mysqli_query ($dbc, $query);

// If the result is not empty
if ($result <> '' && trim($name) !='') {

	// Create the table head.
	echo '<table border="1" width="90%" cellspacing="3" cellpadding="3" align="center">
	<tr>
	<td align="middle" width="20%"><b>Video Game Name</b></td>
	<td align="middle" width="30%"><b>Price</b></td>
	<td align="middle" width="30%"><b>System</b></td>
	<td align="middle" width="30%"><b>Genre</b></td>
	<td align="middle" width="30%"><b>Image</b></td>
	</tr>';

	while ($row = mysqli_fetch_array ($result, MYSQLI_ASSOC)) {

		// Display each record.
		echo 	'<tr>
			<td align="middle">' . $row['productName'] . '</td>
			<td align="middle">' . $row['price'] . '</td>
			<td align="middle">' . $row['systemName'] . '</td>
			<td align="middle">' . $row['genre'] . '</td>
			<td align="middle"><IMG SRC=images/' . $row['image_name'] . '></td>

		</tr>';

	} // End of while loop.

	// Close the table.
	echo '</table>';

	// Free up the resources.
     	mysqli_free_result($result);

}
else {
	echo 'No result matching the search criterion.<br>';
}

mysqli_close($dbc); // Close the database connection.

?>
</td></tr>
</table>
</center>
</BODY>
</HTML>

You try with mysqli

if (mysqli_num_rows($result) > 0 && $name!='')

If you want header in any case then bring table head and footer out of if condition. Also wrap "no result matching...." text in tr td tags.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.