Search Data not outputting the correct results

Question

This form is a search form which allows the user to search for an event using the Venue and category fields which are scripted as dropdown boxes and the Price and event title as user input text boxes, as shown via the code if a keyword is entered which matches the fields on the database it should output all the related information for that event if any matches have been made on either search fields, the tickboxes allow the user to identify what criteria they would like to search with, if the tickbox field hasn't been checked then the SQL enquiry will not search for keywords with that corresponding field.

The issue is, it all seems to work fine except no results seem to show up for the Venue and Category fields if they was solely used to search for an event. But if I choose another field everything is outputting correctly including the venue and Category field.

DATABASE: https://i.sstatic.net/8k4V6.jpg

HTML FORM

<form name="searchform" action ="PHP/searchfunction.php" method = "post" >
<h2>Event Search:</h2>
Use the Check Boxes to indicate which fields you watch to search with
<br /><br />
<h2>Search by Venue:</h2>

<?php
echo "<select name = 'venueName'>";
$queryresult2 = mysql_query($sql2) or die (mysql_error());
while   ($row = mysql_fetch_assoc($queryresult2))  {
echo "\n";
$venueID = $row['venueID'];
$venueName = $row['venueName'];
echo "<option value ='$venueName'";
echo ">$venueName</option>";
}# when the option selected matches the queryresult it will echo this

echo "</select>";
echo" <input type='checkbox' name='S_venueName'>";
mysql_free_result($queryresult2);
mysql_close($conn);
?>

<br /><br />
<h2>Search by Category:</h2>
<?php
include 'PHP/database_conn.php';

$sql3 ="SELECT catID, catDesc
FROM te_category";

echo "<select name = 'catdesc'>";
$queryresult3 = mysql_query($sql3) or die (mysql_error());
while   ($row = mysql_fetch_assoc($queryresult3))  {
echo "\n";
$catID = $row['catID'];
$catDesc = $row['catDesc'];
echo "<option value = '$catDesc'";
echo ">$catDesc </option>";
}

echo "</select>";
mysql_free_result($queryresult3);
mysql_close($conn);
?>
<input type="checkbox" name="S_catDes">
<br /><br />

<h2>Search By Price</h2>
<input type="text" name="S_price" />
<input type="checkbox" name="S_CheckPrice">
<br /><br />

<h2>Search By Event title</h2>
<input type="text" name="S_EventT" />
<input type="checkbox" name="S_EventTitle">
<br /><br />
<input name="update" type="submit" id="update" value="Search">

</form>

PHP CODE THAT DEALS WITH PROCESSING THE FORM DATA

<?php
include 'database_conn.php';


$venuename = $_POST['venueName']; //this is an integer
$catdesc = $_POST['catdesc']; //this is a string
$Price = $_POST['S_price'];
$EventT = $_POST['S_EventT'];

#the IF statements state if the tickbox is checked then search with these enquires
if (isset($_POST['S_VenueName'])) {

$sql = "SELECT * FROM te_venue WHERE venueName= '$venuename'";

}


if (isset($_POST['S_catDes'])) {

$sql = "SELECT * FROM te_category WHERE catID=  '$catdesc'";

}

if (isset($_POST['S_CheckPrice'])) {

$sql = "SELECT * FROM te_events WHERE (eventPrice LIKE '%$Price%')";

}

if (isset($_POST['S_EventTitle'])) {

$sql = "SELECT * FROM te_events WHERE (eventTitle LIKE '%$EventT%')";

}


$queryresult = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_assoc($queryresult))
{
    echo "Event Title: "; echo $row['eventTitle'];
    echo "<br />";
    echo "Event Description: "; echo $row['eventDescription'];
    echo "<br />";
    echo "Event Venue "; echo "$venuename";
    echo "<br />";
    echo "Event Category "; echo "$catdesc";
    echo "<br />";
    echo "Event Start Date "; echo $row['eventStartDate'];
    echo "<br />";
    echo "Event End Date "; echo $row['eventEndDate'];
    echo "<br />";
    echo "Event Price "; echo $row['eventPrice'];
    echo "<br /><br />";
}

mysql_free_result($queryresult);
mysql_close($conn);

?>

Answer 1

Try using atleast MySQLi instead of deprecated MySQL. You can try this:

database_conn.php:

<?php

/* ESTABLISH YOUR CONNECTION. REPLACE THE NECESSARY DATA BELOW */

$con=mysqli_connect("YourHost","YourUsername","YourPassword","YourDatabase");

if(mysqli_connect_errno()){

echo "Error".mysqli_connect_error();
}

?>

HTML Form:

<html>
<body>

<?php

include 'PHP/database_conn.php';

$sql2="SELECT venueID, venueName FROM te_venue"; /* PLEASE REPLACE THE NECESSARY DATA */
echo "<select name = 'venueName'>";
$queryresult2 = mysqli_query($con,$sql2);
while($row = mysqli_fetch_array($queryresult2))  {
echo "\n";
$venueID = mysqli_real_escape_string($con,$row['venueID']);
$venueName = mysqli_real_escape_string($con,$row['venueName']);
echo "<option value ='$venueName'>";
echo $venueName."</option>";
} /* when the option selected matches the queryresult it will echo this ?? */
echo "</select>";

echo "<input type='checkbox' name='S_venueName'>";

?>

<br><br>
<h2>Search by Category:</h2>

<?php

$sql3 ="SELECT catID, catDesc FROM te_category";

echo "<select name = 'catdesc'>";
$queryresult3 = mysqli_query($con,$sql3);
while($row = mysqli_fetch_array($queryresult3))  {
echo "\n";
$catID = mysqli_real_escape_string($con,$row['catID']);
$catDesc = mysqli_real_escape_string($con,$row['catDesc']);
echo "<option value = '$catDesc'>";
echo $catDesc."</option>";
}

echo "</select>";

?>

<input type="checkbox" name="S_catDes">
<br><br>

<h2>Search By Price</h2>
<input type="text" name="S_price" />
<input type="checkbox" name="S_CheckPrice">
<br><br>

<h2>Search By Event title</h2>
<input type="text" name="S_EventT" />
<input type="checkbox" name="S_EventTitle">
<br><br>
<input name="update" type="submit" id="update" value="Search">

</form>

</body>
</html>

PHP:

<?php

include 'database_conn.php';

$venuename = mysqli_real_escape_string($con,$_POST['venueName']); /* this is an integer */
$catdesc = mysqli_real_escape_string($con,$_POST['catdesc']); /* this is a string */
$Price = mysqli_real_escape_string($con,$_POST['S_price']);
$EventT = mysqli_real_escape_string($con,$_POST['S_EventT']);

/* SHOULD PRACTICE USING ESCAPE_STRING TO PREVENT SOME OF SQL INJECTIONS */

/* the IF statements state if the tickbox is checked then search with these enquires */

if (isset($_POST['S_VenueName'])) {

$sql = "SELECT * FROM te_venue WHERE venueName= '$venuename'";

}    

if (isset($_POST['S_catDes'])) {

$sql = "SELECT * FROM te_category WHERE catID=  '$catdesc'";

}

if (isset($_POST['S_CheckPrice'])) {

$sql = "SELECT * FROM te_events WHERE (eventPrice LIKE '%$Price%')";

}

if (isset($_POST['S_EventTitle'])) {

$sql = "SELECT * FROM te_events WHERE (eventTitle LIKE '%$EventT%')";

}  

$queryresult = mysqli_query($con,$sql);
while ($row = mysqli_fetch_array($queryresult))
{
    echo "Event Title: "; echo $row['eventTitle'];
    echo "<br />";
    echo "Event Description: "; echo $row['eventDescription'];
    echo "<br />";
    echo "Event Venue "; echo "$venuename";
    echo "<br />";
    echo "Event Category "; echo "$catdesc";
    echo "<br />";
    echo "Event Start Date "; echo $row['eventStartDate'];
    echo "<br />";
    echo "Event End Date "; echo $row['eventEndDate'];
    echo "<br />";
    echo "Event Price "; echo $row['eventPrice'];
    echo "<br /><br />";
}

mysqli_close($conn);

?>

• What if user checks all the check box? What would happen is, the last condition will be used. The first three conditions will be overwritten by the last condition.

• If you use ELSE IF in those conditions, the first condition will be implemented.

• My advice is to use radio button instead of check box and hope you gets the idea along the way.

Answer 2

Have you tried printing out your $sql query for debugging?

Try <input type="checkbox" name="S_catDes" value="checked">.

From memory checkboxes need a value field but I could be wrong. Hope this helps.