...

View Full Version : Searching a MySQL database



el_nino
02-01-2010, 10:58 AM
Hi...

I have created a form which consists of check boxes, radio buttons etc and these can be used to make a selection and search for an item in the database.

the form i have created looks as follows:

<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">
<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="01" /> #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>



and the file asearch.php looks like:

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

<html>

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

<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 '".$_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>&nbsp </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>

the error i am getting is: "Couldn't execute query"

currently i am just testing the query by only looking at one of the values selected on the form.

Help?

el nino

Jeremy Ross
02-01-2010, 11:09 AM
Change this line


$result = mysql_query($the_query) or die("Couldn't execute query");


to this:



$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());


then let us know the output.

el_nino
02-01-2010, 11:54 AM
ok, i now get the following error


Couldn't execute query. MySQL Said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clubno = 1 nationality = all' at line 1

I am not looking at the nationality just yet as i have not yet defined the values for the different options in my form

SKDevelopment
02-01-2010, 12:52 PM
1) You have several conditions in the WHERE clause not separated with AND or OR. This gives the error.

Just in case: while you are debugging locally (not at the Production system of course because it would be not safe), it could be often useful to echo the query before using it in mysql_query(). Often it helps to see an error at once.

2) You are using POST variables right in your query. This makes your query vulnerable for SQL injection attack. Never do it like this. Always escape any potential user input. Please see the mysql_real_escape_string() (http://php.net/mysql_real_escape_string) function for reference. If you find this brief description not clear or not sufficient, please ask questions. Security is a very important aspect in web-programming.

el_nino
02-01-2010, 01:20 PM
thanks for the reply

i understand the second point you made about security, as i am doing for a project i wanted to first get the query working, then show the effects of sql injection and then provide a solution for making it more secure.

however i do not quite understand the first point you made:

You have several conditions in the WHERE clause not separated with AND or OR. This gives the error

could you please elaborate on that? i was under the assumption that if the user clicked on the box that stated #1-#11 on the form, the value would always be set to 1 as is defined in the form.

SKDevelopment
02-01-2010, 01:38 PM
Yes, of course. Initially the query is formed by the line:


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

Then in if-conditions you are adding some parts to this query like


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

If you did it like this:


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

you would not get the SQL syntax error. But since AND (or maybe OR) is missing, you are getting the error.

el_nino
02-01-2010, 04:07 PM
i'm not quite understanding the function of:

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

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


thanks

SKDevelopment
02-01-2010, 04:26 PM
If you try to echo the query, the query without and should give you something like


SELECT * FROM players WHERE Number like 'something here' active_player = 1

It is necessary to have something like


SELECT * FROM players WHERE Number like 'something here' AND active_player = 1

The error you have shown says that the conditions are joined like "clubno = 1 nationality = all". This is wrong. Conditions must be joined via AND or OR.

You could try to echo the query


echo $the_query;

right before using it in mysql_query(). It would allow you to see the problems with the query syntax too.

Also please notice that all string values must be surrounded with single quotes. So "nationality = all" must be "nationality = 'all'".

el_nino
02-01-2010, 04:58 PM
hi, sorry about all the questions but i really don't seem to be with it today, i now see why the nationality was appearing from apparently nowhere, i took that out as i didnt want it to feature in this "test" query.

when i echo out the query it looks like:

SELECT * FROM players WHERE Number like '1' AND active_player =

and the accompanying error messages look like:


Couldn't execute query. MySQL Said: Unknown column 'active_player' in 'where clause'

and


Notice: Undefined index: asearch in asearch.php on line 41

line 41 being


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

SKDevelopment
02-01-2010, 05:18 PM
1) The notice


Notice: Undefined index: asearch in asearch.php on line 41

means the index "asearch" is not present in the array $_POST.

The only element with the name "asearch" which I see in the HTML code you have posted is:


<name="asearch" />

It is not an HTML element. And it would not be submitted by POST. Maybe you meant some hidden field instead ? Then the HTML code for it would be like this


<input type="hidden" name="asearch" value="some_value_here" />


2) The error


Couldn't execute query. MySQL Said: Unknown column 'active_player' in 'where clause'

means that the field `active_player` is absent in the table `players`. I mean the table `players` contains no field with the name `active_player`. Maybe this field is called a little bit differently in the database table `players` ?

thekooliest
02-02-2010, 02:46 AM
Like SKDevelopment said,


<name="asearch" />

vvv Change To vvv


<input type="hidden" name="asearch" value="some_value_here" />

Does have to happen because right now you are asking mySQL to find something
where active player = nothing. Impossible, right? Also I believe you will need to change your mySQL line from:

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

$the_query = $the_query . " AND active_player = ' " . $_POST['asearch']." ' "; (I added spacing around the apostrophes to make it easier to read...these aren't necessary)...

Sorry if that didn't make sense, feel free to ask more questions.

el_nino
02-02-2010, 01:11 PM
thanks for the help :)

i've removed the active_players as i feel its not needed. i have the file a search.php which looks like:


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

<html>

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

<div align="center">

<body>

<p>&nbsp </p>

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

<?php
// POST the search variable from URL

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

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

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

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

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

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

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

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

echo "<p>&nbsp </p>";
echo $the_query;


if (!empty($_POST['asearch']))
{
$the_query = $_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;
}

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

$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
echo "<p>&nbsp </p>";
echo $the_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 POSTting the next row until there are no more to POST
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>

and the query which i have echoed looks like:


SELECT * FROM players WHERE Number like '1'

this is the query i was hoping to get out for this particular radio button, i have used 'like' because i will be adding more value to that particular radio button but for now it only has one value, that being '1'

i would have expected this to now work but still get:


Couldn't execute query. MySQL Said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clubno = 1' at line 1

SKDevelopment - you mentioned SQL Injection earlier, i wanted to ask, in this form the user is unable enter their own specific value into a text field, but instead asked to select options using radio buttons/ check boxes. am i correct in assuming that i would not need to worry about sql injection here, but would need to consider it in the form which allowed the user to enter their own search term?

thanks
el nino

SKDevelopment
02-02-2010, 01:23 PM
You have echoed your query when it was only partially formed. Please echo it right before the line:


$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());

Then you would see the problem at once. It is the same problem as before. AND or OR is missing between conditions in the WHERE clause.

Edit: Sorry for the error in the word "missing" (corrected).

el_nino
02-04-2010, 01:40 PM
when i attempt to echo the query where you have suggested it doesn't seem to be printed :S

SKDevelopment
02-04-2010, 06:17 PM
Do you mean when you echo the query exactly here:


echo $the_query . '<br />';
$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());

the query is not printed ?

el_nino
02-05-2010, 01:26 PM
ok i now see it. it was not being printed when i did it like:


$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
echo $the_query;

but does now print when i do it like:



echo $the_query . '<br />';
$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());

thanks

the query being printed looks like

SELECT * FROM players WHERE Number like '1' clubno = 1

i don't understand why the clubno = 1 is being included, i assumed it would only include the value ('1' in this case) so i would have expected the query to look like


SELECT * FROM players WHERE Number like '1'

because the clubno is only a field in the form but not in the database

SKDevelopment
02-05-2010, 01:58 PM
clubno = 1 is added by the condition:


if (!empty($_POST['clubno']))
{
$the_query = $the_query . " clubno = " . $_POST['clubno'];
}

If you replace it with


if (!empty($_POST['clubno']))
{
$the_query .= ' AND clubno = ' . $_POST['clubno'];
}

you will stop getting the MySQL parsing error for clubno. Of course all conditions must be corrected in this way (by adding ' AND').

el_nino
02-05-2010, 03:33 PM
thanks for your help, i have the query working :)

my next question is, can


<input type="radio" name="clubno" value="1" /> #1 - #11

have multiple values. so what i mean is, can the field above have all the values 1 - 11? so currently the value of that field is 1, can i have it so the value is 1 or 2 or 3... or 11?

cheers

SKDevelopment
02-05-2010, 04:06 PM
For a radio button the value could be any string. You could set it to "1" or to "1-11". But the value is always a string - any string you would like to use for it. And for a radio button it would be a scalar value (I mean only 1 radio button with the same name could be checked at a time).

I am sorry if I have not understood the question correctly ... In this case, could you could you explain it please a little bit more in detail ?

el_nino
02-05-2010, 04:25 PM
what i mean iss, as i am using this field to find out what the shirt number of a footballer is, i have radio button which narrow down the particular bands, e.g. 1-12, 13-25 etc. so i want the value of the radio button for 1-11 to be all the values from 1 to 11 so that when the query is run it will pick up all rows which have a number between 1 and 11. does that make more sense?

SKDevelopment
02-05-2010, 04:41 PM
Personally I would do it like this. I would set values at the form to


<tr><td>
<input type="radio" name="clubno" value="1" /> #1 - #11
</td><td>
<input type="radio" name="clubno" value="12" /> #12 - #30
</td><td>
<input type="radio" name="clubno" value="31" /> #31- #59
</td><td>
<input type="radio" name="clubno" value="60" /> #60 - #99
</td></tr>

and in asearch.php I would replace the condition:


if (!empty($_POST['clubno']))
{
$the_query .= ' AND clubno = ' . $_POST['clubno'];
}

with:


if (!empty($_POST['clubno']))
{
switch($_POST['clubno'])
{
case '1':
$the_query .= ' AND (clubno BETWEEN 1 AND 11)';
break;
case '12':
$the_query .= ' AND (clubno BETWEEN 12 AND 30)';
break;
case '31':
$the_query .= ' AND (clubno BETWEEN 31 AND 59)';
break;
case '60':
$the_query .= ' AND clubno>60';
break;
}
}

Of course it would work if clubno is of numeric data type in the DB table (e.g. SMALLINT, INT etc.), not is of string data type.

el_nino
02-05-2010, 06:05 PM
in order to get the query to work correctly in the first place i changed the file asearch to:



if (!empty($_POST['clubno']))

if (!empty($_POST['playing']))

if (!empty($_POST['nationality']))

if (!empty($_POST['position']))


would it be possible to get the script you posted to work without having to amend the query again?

SKDevelopment
02-05-2010, 06:59 PM
I am sorry, I have not understood the question. Could you show the changed version of asearch.php please ?

el_nino
02-08-2010, 01:15 PM
the new version of asearch.php


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

<html>

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

<div align="center">

<body>

<p>&nbsp </p>

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

<?php
// POST the search variable from URL

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

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

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

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

if (!empty($_POST['asearch']))
{
$the_query = $_POST['asearch'];
}

if (!empty($_POST['clubno']))

if (!empty($_POST['playing']))

if (!empty($_POST['nationality']))

if (!empty($_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;
}

echo "<p>&nbsp </p>";

echo $the_query . '<br />';
$result = mysql_query($the_query) or die("Couldn't execute query. MySQL Said: ".mysql_error());

// 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 POSTting the next row until there are no more to POST
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>";
echo "<p>&nbsp </p>";
?>


</body>

<div align="center">

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

</html>

SKDevelopment
02-08-2010, 01:31 PM
Just in case: To show how your code would be executed, I'll set curly brackets in the piece of code:


if (!empty($_POST['clubno']))

if (!empty($_POST['playing']))

if (!empty($_POST['nationality']))

if (!empty($_POST['position']))


$numresults=mysql_query($the_query);


This block of code is exactly the same as if you have written it like this:


<?php
if (!empty($_POST['clubno']))
{
if (!empty($_POST['playing']))
{
if (!empty($_POST['nationality']))
{
if (!empty($_POST['position']))
{
$numresults=mysql_query($the_query);
}
}
}
}

Please notice that these 2 blocks of code do exactly the same thing. I just thought that maybe you did not mean such a logic so I decided to mention this...

Anyway which is the problem with the new asearch.php ? Please ask any questions you could have. I would be very glad to answer.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum