View Full Version : How to search MySQL database from PHP?

12-01-2011, 01:21 PM

I am trying to create a page which lists all the entries in the database if the user does not perform a search or only selected entries in the database if the user chooses an entertainer type or county but I keep getting error messages.

Can anyone tell me what's going wrong?

Lots of errors along the lines of:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/entsuk/public_html/admin/index.php on line 13


$category = $_GET['category'];
$county = $_GET['county'];

$query = "SELECT * from entertainers WHERE category='.$category.' and county='.$county.'";

$result = mysql_query($query) or die("$query FAILED because".mysql_error());
$num = mysql_num_rows ($result);

$your_title = mysql_result($result,$i,"your_title");
$first_name = mysql_result($result,$i,"first_name");
$last_name = mysql_result($result,$i,"last_name");
$company_name = mysql_result($result,$i,"company_name");
$company_logo = mysql_result($result,$i,"company_logo");
$address_line_1 = mysql_result($result,$i,"address_line_1");
$address_line_2 = mysql_result($result,$i,"address_line_2");
$town_or_city = mysql_result($result,$i,"town_or_city");
$county = mysql_result($result,$i,"county");
$postcode = mysql_result($result,$i,"postcode");
$telephone = mysql_result($result,$i,"telephone");
$email_address = mysql_result($result,$i,"email_address");
$choose_a_password = mysql_result($result,$i,"choose_a_password");
$bullet_point_1 = mysql_result($result,$i,"bullet_point_1");
$bullet_point_2 = mysql_result($result,$i,"bullet_point_2");
$bullet_point_3 = mysql_result($result,$i,"bullet_point_3");
$bullet_point_4 = mysql_result($result,$i,"bullet_point_4");
$bullet_point_5 = mysql_result($result,$i,"bullet_point_5");
$bullet_point_6 = mysql_result($result,$i,"bullet_point_6");
$website_url = mysql_result($result,$i,"website_url");
$video = mysql_result($result,$i,"video");
$photo_1 = mysql_result($result,$i,"photo_1");
$photo_2 = mysql_result($result,$i,"photo_2");
$photo_3 = mysql_result($result,$i,"photo_3");
$category = mysql_result($result,$i,"category");
$sort_order = mysql_result($result,$i,"sort_order");
$expiry_date = mysql_result($result,$i,"expiry_date");
$agree_to_terms = mysql_result($result,$i,"agree_to_terms");
$id = mysql_result($result,$i,"id");


<div class="search">

<form name="search" method="post" action="index.php">

<select name="category" id="category">
<option value="*" selected>All Entertainers</option>
<option value="Bands and Singers">Bands and Singers</option>
<option value="Bouncy Castles">Bouncy Castles</option>
<option value="Childrens Entertainers">Children's Entertainers</option>
<option value="Chocolate Fountains">Chocolate Fountains</option>
<option value="Dancefloor Hire">Dancefloor Hire</option>
<option value="Discos and DJs">Discos and DJs</option>
<option value="Karaoke and KJs">Karaoke and KJs</option>
<option value="Magicians">Magicians</option>
<option value="Mobile Bars">Mobile Bars</option>
<option value="Musicians">Musicians</option>
<option value="Photographers">Photographers</option>
<option value="Venue Decoration">Venue Decoration</option>
<option value="Wedding and Event Planners">Wedding and Event Planners</option>
<option value="Wedding Fayres">Wedding Fayres</option>

<select name="county" id="county">
<option value="*" selected>All Counties</option>
<option value="Bristol">Bristol</option>
<option value="Cornwall">Cornwall</option>
<option value="Devon">Devon</option>
<option value="Dorset">Dorset</option>
<option value="Gloucestershire">Gloucestershire</option>
<option value="Somerset">Somerset</option>
<option value="Wiltshire">Wiltshire</option>

<input type="submit" name="search" id="search" value="Search Database">


if ($num > 0 ) {
while ($i < $num) {

<div class="entry">
<div class="fl">
<div class="company_name"><? echo $company_name; ?> | <a href="update.php?id=<? echo $id ?>">Update</a> | <a href="delete.php?id=<? echo $id ?>">Delete</a></div>
<div class="company_location"><? echo $town_or_city; ?>, <? echo $county; ?></div>
<div class="bulletpoints">
<li><? echo $bullet_point_1; ?></li>
<li><? echo $bullet_point_2; ?></li>
<li><? echo $bullet_point_3; ?></li>
<li><? echo $bullet_point_4; ?></li>
<li><? echo $bullet_point_5; ?></li>
<li><? echo $bullet_point_6; ?></li>
<div class="website"><a href="http://<? echo $website_url; ?>"><? echo $website_url; ?></a></div>
<div class="fr"><? echo $company_logo; ?></div>

<? ++$i; } } else { echo "The database is empty"; }?>

12-01-2011, 05:58 PM
First thing I see is this line:

$query = "SELECT * from entertainers WHERE category='.$category.' and county='.$county.'";


$query = "SELECT * from entertainers WHERE category= '$category' and county= '$county'";

To check querys You should insert this after a $query is coded to see if it's what you want.

echo $query;die;Then remove it.

This line:

$your_title = mysql_result($result,$i,"your_title");and all that come after. The $i is not defined. looks like you need a for loop here.