Searching MySQL database

el_nino · January 27, 2010

I have implemented a search function on to my website which works as expected when the user enters a value in the search engine. I am now wanting to add an advanced search feature which will allow the user to narrow down their search by selecting values using radio buttons, check boxes and a drop down list.

I have created the form, this can be seen below:

<html>
<head>
	<title>Advanced Search</title>
</head>

<div align="center">

<?php
	include ('includes/header.html')
?>

<body>
<p> &nbsp </p>
<h1>Advanced Search</h1></br>
<p>Narrow your search using the following options</p>
<p> &nbsp </p>

<form action="asearch.php" method="post">
<input type="text" name="asearch" />
<table table border='1' table width = 50%>	
<tr><th COLSPAN=4>Is this player still playing?</th></tr>
<tr><td>
	<p> &nbsp </p>
</td><td>
	<input type="radio" name="playing" value="Retired" /> Retired
</td><td>
	<input type="radio" name="playing" value="Still Playing" /> Still Playing
</td><td>
	<p> &nbsp </p>	
</td></tr>
<tr><th COLSPAN=4>What Nationality is the player?</th></tr>
<tr><td>
	<p> &nbsp </p>
</td><td>	
	<select name="nationality" align="center">
	<option value="all">Any</option>
	<option value="african">African</option>
	<option value="aisian">Asian</option>
	<option value="european">European</option>
	<option value="namerican">North American</option>
	<option value="samerican">South American</option>	
	<option value="oceanian">Oceanian</option>
	<option value="uncapped">*Uncapped</option>			
	</select>
</td><td>
	(select "any" if unsure)
</td><td>
	<p> &nbsp </p>
</td></tr>
<tr><th COLSPAN=4>Position?</th></tr>
<tr><td>
	Goalkeeper
	<input type="checkbox" name="position" value="goalkeeper" />
</td><td>
	Defener
	<input type="checkbox" name="position" value="defender" />
</td><td>
	Midfielder
	<input type="checkbox" name="position" value="midfielder" />
</td><td>
	Striker
	<input type="checkbox" name="position" value="striker" />
</td></tr>
<tr><th COLSPAN=4>Club Shirt Number</th></tr>
<tr><td>
	<input type="radio" name="clubno" value="0111" /> #1 - #11
</td><td>
	<input type="radio" name="clubno" value="1230" /> #12 - #30
</td><td>
	<input type="radio" name="clubno" value="3159" /> #31- #59
</td><td>
	<input type="radio" name="clubno" value="6099" /> #60 - #99
</td></tr>
</table>

<p> &nbsp </p>
<input type="submit" name="submit" value="Search" />
<input type="hidden" name="submitted" value="TRUE" />
<p> &nbsp </p>
<p>* select Uncapped in the player has not yet been called up by the national team</p>
<p> &nbsp </p>

</form>
	<p> &nbsp </p>
</body>

	<?php
	include ('includes/footer.html')
?>

</div>

</html>

would anyone be able to get me started on the query i would use in the file asearch.php?

thank you

el nino

manwhoeatsrats · January 27, 2010

well, your next page is going to have a number of post variables.... so something like this for each of the fields...

<?php
$the_query = "SELECT * table_name WHERE ";
if (!empty($_POST['asearch'])
{
   $the_query = $the_query . "active_player = " . $_POST['asearch'];
  } 
if (!empty($_POST['clubno'])
{
    $the_query = $the_query . " clubno = " . $_POST['clubno'];
}
?>

I am sure there is a much better way to do it, but I know not what that is. hope this is helpful.

el_nino · January 27, 2010

cheers...

ok so i've got all the variable that could be sent and i think i know what comes next but before i attempt the next part could you explain the following line:

$the_query = $the_query . "active_player = " . $_POST['asearch'];

} [code=php:0]

thanks

el_nino · January 27, 2010

that line was supposed to be...

{
   $the_query = $the_query . "active_player = " . $_POST['asearch'];
  }

manwhoeatsrats · January 27, 2010

pretty much it was a quick code I wrote that would preform the function you need, based off of the post variables

it takes $the_query and adds to it dependent upon if the post variables are empty. This will work, I have acually done it before; however, I do have a recommendation. Do not try doing the query right away. instead when you get directed to this page, just echo $the_query and check the syntax. I am sure you will need some minor tweaking, like placing a coma here, a single quote there...

I have found that echoing values to be a very helpful trouble shooting tool.

el_nino · January 27, 2010

ok, this is what i have so far


<html>

<div align="center">

<body>

<p>&nbsp</p>

<?php
include ('includes\header.html');
?>	

<?php
  // Get the search variable from URL

  $var = @$_POST['asearch'] ;
?>

<?php
//connect to your database
mysql_connect("*****","*****","*****"); //(host, username, password)

//specify database
mysql_select_db("*****") or die("Unable to select database"); //select which database we're using



$the_query = "SELECT * FROM players WHERE Number like /%clubno%/ 

if (!empty($_POST['asearch'])
{
   $the_query = $the_query . "active_player = " . $_POST['asearch'];
  } 
if (!empty($_POST['clubno'])
{
    $the_query = $the_query . " clubno = " . $_POST['clubno'];
}
if (!empty($_POST['playing'])
{
    $the_query = $the_query . " playing = " . $_POST['playing'];
}
if (!empty($_POST['nationality'])
{
    $the_query = $the_query . " nationality = " . $_POST['nationality'];
}
  if (!empty($_POST['position'])
{
    $the_query = $the_query . " position = " . $_POST['position'];
}

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");


// begin to show results set
echo "Results";
$count = 1 + $s ;


echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>";
   // keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['Name'];
echo "</td><td>"; 
echo $row['DOB'];
echo "</td><td>"; 
echo $row['Club'];
echo "</td><td>"; 
echo $row['Number'];
echo "</td><td>"; 
echo $row['Cost'];
echo "</td><td>"; 
echo $row['Position'];
echo "</td><td>"; 
echo $row['NationalTeam'];	
echo "</td></tr>"; 
} 

echo "</table>";
?>

</body>

<div align="center">

?>

<?php
include ('includes\footer.html');
?>	

</html>

but i'm kinda lost now :-\ with the actual query

manwhoeatsrats · January 27, 2010

Here is an example of what I am talking about. this is currently a work in progress, and is made for updating a mysql table.

$user_update_query = "UPDATE users SET ";
if (isset($_POST[update_user]))
{
if ($_POST['access_level'] != $row_get_user_info['access'])
{
	$access_level = $_POST[access_level];
	$user_update_query = $user_update_query . " access = '" . $_POST['access_level'] . "', ";

} else {
	$user_update_query = $user_update_query . " access = '" . $row_get_user_info['access'] . "', ";
}

if ($_POST['reset_account'] != $row_get_user_info['account_status'])
{
	if ("Locked" == $_POST['reset_account'])
	{
		$user_update_query = $user_update_query . " account_status = 'Locked' " . ", ";
	} else {
		$user_update_query = $user_update_query . " account_status = '" . $row_get_user_info[account_status] . "', ";
	}
		if ("Yes" == $_POST['reset_account'])
		{
			$new_password = createRandomPassword();
		}
	}
}

if ("Yes" == $_POST['reset_password'])
{
	$reset_password = "Yes";
}

manwhoeatsrats · January 27, 2010

I just miss read what you posted. to do the query is simple once you have it built. it would be something like.

$your_connection = mysql_connect("*****","*****","*****");
$foo = mysql_query($the_query, $your_connection) or die(mysql_error());
$get_your_info_into_an_array = mysql_fetch_assoc($foo);

el_nino · January 29, 2010

ok, so the code i have come up with looks like:

<?php
ini_set('display_errors', '1');
error_reporting(E_ALL);
?>

<html>

    <head>
        <title>Advanced Search</title>
    </head>

<div align="center">

<body>

<p>  </p>

<?php
    include ('includes\header.html');
?>    

<?php
      // Get the search variable from URL

  $var = @$_POST['asearch'];
?>

<?php
//connect to your database
mysql_connect("****","****","****"); //(host, username, password)

//specify database
mysql_select_db("****") or die("Unable to select database"); //select which database we're using

$the_query = "SELECT * FROM players WHERE Number like '".$_POST['clubno']."'";
if (!empty($_POST['asearch']))
{
   $the_query = $the_query . "active_player = " . $_POST['asearch'];
  } 
if (!empty($_POST['clubno']))
{
    $the_query = $the_query . " clubno = " . $_POST['clubno'];
}
if (!empty($_POST['playing']))
{
    $the_query = $the_query . " playing = " . $_POST['playing'];
}
if (!empty($_POST['nationality']))
{
    $the_query = $the_query . " nationality = " . $_POST['nationality'];
}
  if (!empty($_POST['position']))
{
    $the_query = $the_query . " position = " . $_POST['position'];
}

$numresults=mysql_query($the_query);
//$numrows=mysql_num_rows($numresults);

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  //$the_query .= " limit $s,$limit";
  echo "<p>  </p>";
  $result = mysql_query($the_query) or die("Couldn't execute query");

// begin to show results set
echo "Results";
$count = 1 + $s;


    echo "<table border='1'>";
    echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>";
   // keeps getting the next row until there are no more to get
    while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['Name'];
    echo "</td><td>"; 
    echo $row['DOB'];
    echo "</td><td>"; 
    echo $row['Club'];
    echo "</td><td>"; 
    echo $row['Number'];
    echo "</td><td>"; 
    echo $row['Cost'];
    echo "</td><td>"; 
    echo $row['Position'];
    echo "</td><td>"; 
    echo $row['NationalTeam'];    
    echo "</td></tr>"; 
} 

echo "</table>";
?>


</body>

<div align="center">

<?php
    include ('includes\footer.html');
?>    

</html>

but i now get the error "Couldn't execute query" :shrug:

mattal999 · January 29, 2010

$the_query = "SELECT * FROM players WHERE Number like '".$_POST['clubno']."' AND ";

And you need to add AND to all of your other post values aswell, otherwise you'll get a string like active_player = 1 clubno = 1 when it needs an AND in the middle.

el_nino · January 29, 2010

the reason i didnt add the other variables is because i just wanted to ensure that the query worked for that one radio button, is that possible or do i need to include all the other posted variables in the query even if the user was user was only to select one of the options?

el_nino · February 1, 2010

help?

Sign In

Searching MySQL database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information