Search query returning all records in database when they are no records

PHP
1

Hey very new to PHP

SEARCH PAGE:
<form id=“searcMennuu” name=“searcMennuu” method=“post” action=“VehicleListing.php”>
<select name=“vehicles” id=“vehicles”>
<option value=“allCars” selected=“selected”>ALL</option>
<option value=“Sedan”>Sedan</option>
<option value=“Jeep”>Jeep</option>
<option value=“Motorbike”>Motorbike</option>

</select>

<input name=“radioVehicle” type=“radio” id=“searchSale” value=“searchSale” />
Vehicles for Sale</label>
<label>
<input type=“radio” name=“radioVehicle” id=“searchRent” value=“searchRent” />
Vehicles for Rent</label>

<input type=“submit” name=“searchButton” id=“searchButton” value=“Search” />
</form>

RESULTS PAGE:
mysql_select_db($database_ddd, $ddd);

$query_rsTest = “SELECT year, model FROM vehicles”;
if (isset ($_POST [‘vehicles’])) {
$parSearch = mysql_real_escape_string ($_POST[‘vehicles’]);

if($_POST[‘vehicles’] == “Sedan”){
$query_rsTest.= " WHERE vehicle_type LIKE ‘%$parSearch%’";
}
}

$rsTest = mysql_query ($query_rsTest, $ddd) or die(mysql_error());
$row_rsTest = mysql_fetch_assoc ($rsTest);
$totalRows_rsTest = mysql_num_rows ($rsTest);

<div class=“showPage”><?php echo $totalRows_rsTest ?></div>
<?php do { ?>

<?php if ($totalRows_rsTest > 0) { // Show if recordset not empty ?>

<div class=“output” id=“Results”>
<p><?php echo $row_rsTest[‘year’]; ?></p>
<p><?php echo $row_rsTest[‘model’]; ?></p>
</div>
<?php } // Show if recordset empty ?>
<?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?>

  1. I think i found out why the hide region is not working, whenever i search for something that is not in my database or does not satisfy my search criteria the results page is displaying all records in the database. However i don’t know how to fix that. Can someone please help me?

  2. Also i’m trying to find out how i would display ALL vehicles in my database either for rent or sale, i dont have a field in my database for “ALL” so i’m not sure where to start. Thanks in advance

2

Based on your code search only works for vehicles == “Sedan”.
Make sure that is passed to the post.

if($_POST[‘vehicles’] == “Sedan”){
$query_rsTest.= " WHERE vehicle_type LIKE ‘%$parSearch%’";
}
}

3 
<form id="searcMennuu" name="searcMennuu" method="post" action="VehicleListing.php">
<select name="vehicles" id="vehicles">
<option value="allCars" selected="selected">ALL</option>
<option value="Sedan">Sedan</option>
<option value="Jeep">Jeep</option>
<option value="Motorbike">Motorbike</option>

</select>

<input name="radioVehicle" type="radio" id="searchSale" value="searchSale" />
Vehicles for Sale</label>
<label>
<input type="radio" name="radioVehicle" id="searchRent" value="searchRent" />
Vehicles for Rent</label>

<input type="submit" name="searchButton" id="searchButton" value="Search" />
</form>

RESULTS PAGE:

&lt;?php
$maxRows_rsTest = 10;
$pageNum_rsTest = 0;
if (isset($_GET['pageNum_rsTest'])) {
  $pageNum_rsTest = $_GET['pageNum_rsTest'];
}
$startRow_rsTest = $pageNum_rsTest * $maxRows_rsTest;
mysql_select_db($database_ddd, $ddd);

$query_rsTest = "SELECT `year`, `model` FROM vehicles"; 
if (isset ($_POST ['vehicles']) && ($_POST ['radioVehicles'])) { 

$parSearch = mysql_real_escape_string ($_POST['vehicles']); 
$parSearch1 = mysql_real_escape_string ($_POST['radioVehicles']); 

if($_POST['vehicles'] == "Sedan"){
$query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
}else if f($_POST['vehicles'] == "Jeep"){
$query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
}
}

if (isset($_GET['totalRows_rsTest'])) {
  $totalRows_rsTest = $_GET['totalRows_rsTest'];
} else {
  $all_rsTest = mysql_query($query_rsTest);
  $totalRows_rsTest = mysql_num_rows($all_rsTest);
}

$query_limit_rsTest = sprintf("%s LIMIT %d, %d", $query_rsTest, $startRow_rsTest, $maxRows_rsTest);
$rsTest = mysql_query($query_limit_rsTest, $ddd) or die(mysql_error());
$row_rsTest = mysql_fetch_assoc ($rsTest);
$totalRows_rsTest = mysql_num_rows ($rsTest);

$totalPages_rsProperties = ceil($totalRows_rsProperties/$maxRows_rsProperties)-1;

?&gt;

&lt;div class="showPage"&gt;&lt;?php echo $totalRows_rsTest ?&gt;&lt;/div&gt;
&lt;?php do { ?&gt;

&lt;?php if ($totalRows_rsTest &gt; 0) { // Show if recordset not empty ?&gt;

&lt;div class="output" id="Results"&gt;
&lt;p&gt;&lt;?php echo $row_rsTest['year']; ?&gt;&lt;/p&gt;
&lt;p&gt;&lt;?php echo $row_rsTest['model']; ?&gt;&lt;/p&gt;
&lt;/div&gt;
&lt;?php } // Show if recordset empty ?&gt;
&lt;?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?&gt;

My problem is when i try to search for sedan and there are no sedans in my database i display all records, how do i get the query to display nothing if there is nothing in my database.

4

Did you test this as you where building it? “else if f($_POST[‘vehicles’] == “Jeep”){” But besides that you need to add a condition to your script to check if you results set is empty or not. Then you can display accordingly.

5

That “f” is a typo from putting the code on this site. Everything works once i have info in the database that matches my search criteria, but when there is no matching information everything is displayed. You mentioned a condition to check if the results is empty, can you help me the relevant code?

Thanks in advanced

6 
<div class="showPage"><?php echo $totalRows_rsTest ?></div>


<?php if($totalRows_rsTest > 0): // Show if recordset not empty ?>
	<div class="output" id="Results">
		<p><?php echo $row_rsTest['year']; ?></p>
		<p><?php echo $row_rsTest['model']; ?></p>
	</div>
<?php endif; // Show if recordset empty ?>
<?php else{
	$row_rsTest = mysql_fetch_assoc($rsTest) 
} ?>
7

I am getting an error code in syntax where the endif statement is

8

$row_rsTest = mysql_fetch_assoc($rsTest) add the closing tag “;”

9

I have that, still not working

10

Does nobody know how to help me, I believe the problem is in the way my database query is set up. Help please

11

Leave PHP out of the equation for a moment.

Use whatever you use to manage your database to create a valid sql statement which returns the number of rows you expect it to.

Paste that here and lets have a look at it - tell us how many rows it brings back, post just one of those rows here too.

Also, then issue the following instruction to Mysql DESCRIBE vehicles and post that here too, then we can see if you have set up your table correctly.

12

cups, i prefer SHOW CREATE TABLE rather than DESCRIBE because the former includes indexes, which are sometimes important in debugging a query

13

I’ll remember that, thanks.

14

This is the structure

SELECT * FROM vehicles WHERE 1

This how it looks in the database

#	Column	Type	Collation	Attributes	Null	Default	Extra	Action
 1	vehicle_id	int(11)			No	None	AUTO_INCREMENT	  Change	  Drop	 More
 2	vehicle_type	varchar(255)	latin1_swedish_ci		No	None		  Change	  Drop	 More
 3	model	varchar(255)	latin1_swedish_ci		No	None		  Change	  Drop	 More
 4	year	varchar(255)	latin1_swedish_ci		No	None		  Change	  Drop	 More
 5	sale	varchar(255)	latin1_swedish_ci		No	None		  Change	  Drop	 More
 6	rent	varchar(255)	latin1_swedish_ci		No	None		  Change	  Drop	 More
15 

mysql_select_db($database_ddd, $ddd);

$query_rsTest = "SELECT `year`, `model` FROM vehicles"; 
if (isset ($_POST ['vehicles'])) { 
$parSearch = mysql_real_escape_string ($_POST['vehicles']); 

if($_POST['vehicles'] == "Sedan"){
$query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
}
}

So your Q is about queries like the above, I want to know if you have any matching data in the database. The use of LIKE on a column such as vehicle_type looks way off.

Surely vehicle type is limited to a small set of possibilities:

van
sedan
pickup

and so on?

If so, you should have the equivalent of “select year, model, from vehicles where vehicle_type = ‘sedan’”

What you need to prove is that

a) you have some matching data in your database.
b) you successfully connected to mysql
c) PHP ACTUALLY does construct that desired sql string correctly

BTW, varchar(255) is not the best way to hold a YEAR value, use a datestamp instead.

16

Thanks for the response, and thanks for the correction with datestamp.

My problem is, when i have no jeeps or sedans for sale and someone selects one of them i am getting all the vehicles for sale rather than them message no vehicles for sale. I have added the vehicle count from Dreamweaver server behaviour and tht is counting correctly everytime. So I believe my problem is the records i am returning.

eg. If i select Sedan since i have 2 Sedans in the database, the count for Sedans = 2 and the two Sedans are displayed.
However i have no Jeeps in the database and when i select Jeeps all the vehicles in the database are displayed. How do i go about fixing that problem?

17

i don’t do php, but my guess is, the problem is here –

if($_POST['vehicles'] == "Sedan"){
$query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";

where is $parSearch being initialized? why aren’t you using $_POST[‘vehicles’] in the LIKE string?

if $parSearch is left empty, the query becomes WHERE vehicle_type LIKE ‘%%’ which of course would explain why it’s returning all rows

18

Ok so i should just swap the $_POST[‘vehicles’] in place of $parSearch and use AND if i need to add the other string for the radio button?

19

To look at c) in my previous post you would simply echo the assembled sql query onto the page:


echo $query_rsTest;

… and take a close look at it, paste it into your database admin screen and see what it returns.

My guess is that you are not adding the second part, the WHERE clause because you are searching on “jeep” instead of “Jeep” or perhaps you have a rogue space eg " Jeep"

20

It returns everything in the database. Do you know how to code it to work?