searching an SQL database with a form

DJMK4 · 19 Mar 2006 at 15:26

Im looking for any decent tutorials on the net to implement a form echo'd in PHP so that when the user enters some sort of word/string and click submit, it will query the database for that string and return any results.

Iv been looking on the net for tutorials/examples but cant find any.

If you know of any can you post the link for me to read up on it..

thanks

Craig321 · 19 Mar 2006 at 18:55

The query is:
SELECT * FROM Videos WHERE MATCH (TableName) AGAINST ('user_input');

Here's some code:
search.php:

Code:

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);

$sql = "SELECT * FROM Table WHERE MATCH (FieldName) AGAINST ('$input');";
$result = mysql_query($sql);

while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}

The form - search.html or whatever:

Code:

<form name="form1" method="post" action="search.php">
  Query: 
  <input type="text" name="userQuery">
  <input type="submit" name="Submit" value="Submit">
</form>

I'm not really sure on the security of this as I have never had to use this properly, I have only ever messed around with it. Maybe someone else can fill in any security I have missed?

Untested, but it should work

Hope this helps
Craig.

DJMK4 · 19 Mar 2006 at 19:48

Thanks mate, instead of using a seperate php file for this cant i place it in my .php file where i have the catalogue front-end? place it inside <? ?> tags.

what would i point the form to then?

<form name="form1" method="post" action="???.php">

atm iv pasted the code in the relevant places so now it looks like this

Code:

<div id="catnav">
<a href="index4.php?show=DVD"> DVD</a><br />
<a href="index4.php?show=video"> Video's</a>

<br />
<br />

<form name="form1" method="post" action="search.php">
  Search: 
  <input type="text" name="userQuery">
  <input type="submit" name="Submit" value="Submit">
</form>

<?

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);

$sql = "SELECT * FROM Table WHERE MATCH (FieldName) AGAINST ('$input');";
$result = mysql_query($sql);

while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}



?>

</div>



<div id="catcontent">

<?
$username="";
$password="";
$database="caldico_mediacat";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


//If the $_get function == DVD then get execute the following statement

if($_GET['show'] == "DVD") {

//If $_get == dvd then execute the following SQL query and output the results
$query="SELECT * FROM DVD ORDER BY catagory ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo  "<b><center>Database Output</center></b><br><br>";


$i=0;
while ($i < $num) {
$catagory=mysql_result($result,$i,"catagory");
$director=mysql_result($result,$i,"director");
$title=mysql_result($result,$i,"title");

$price=mysql_result($result,$i,"price");
$image=mysql_result($result,$i,"image");

//Output display
echo '<div align="left" class="product">';
echo "<b>Catagory: </b> $catagory <br></br>
<b> Director: </b> $director<br> </br>
<b>Title: </b>$title <br></br>
<b> Certificate: </b> $certificate<br> </br>
<b>Price: </b> $price<br></br><br>";
echo '<img src='.$image.' />';
echo "</div>";


$i++;
}




//else if $_get function == video then get execute the following statement
}elseif($_GET['show'] == "video") {

//If $_get == video then execute the following SQL query and output the results
$query="SELECT * FROM Video";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo  "<b><center>Database Output</center></b><br><br>";


$i=0;
while ($i < $num) {
$catagory=mysql_result($result,$i,"catagory");
$director=mysql_result($result,$i,"director");
$title=mysql_result($result,$i,"title");
$certificate=mysql_result($result,$i,"certificate");
$price=mysql_result($result,$i,"price");
$image=mysql_result($result,$i,"image");

//Output display
echo '<div align="left" class="product">';
echo "<b>Catagory: </b> $catagory <br></br>
<b> Director: </b> $director<br> </br>
<b>Title: </b>$title <br></br>
<b> Certificate: </b> $certificate<br> </br>
<b>Price: </b> $price<br></br><br>";
echo '<img src='.$image.' />';
echo "</div>";

echo "</div>";


$i++;
}

}

?>



</div>

The problem is when i click submit button it wants to open search.php

DJMK4 · 20 Mar 2006 at 13:00

Ok im getting this error

http://www.caldicot-pc-helpdesk.co.uk/fcv/database/search.php

with this line

while ($field = mysql_fetch_array($result))

search.php

Code:

<?

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);

$sql = "SELECT * FROM Table WHERE MATCH (FieldName) AGAINST ('$input');";
$result = mysql_query($sql);

while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}



?>

Craig321 · 20 Mar 2006 at 14:19

You need to change the field/table names etc. in the query.
Also in the form you change post="" to whatever file has the search code.

Craig.

DJMK4 · 20 Mar 2006 at 19:36

Yeah but im going to search the whole database for what ever $input is rather than searching tables and fields?

Craig321 · 20 Mar 2006 at 20:05

WHERE MATCH (name, name, name, name)

DJMK4 · 20 Mar 2006 at 20:30

im sorry mate but what is name reffering to?

Craig321 · 20 Mar 2006 at 20:40

Code:

<?

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);

$sql = "SELECT * FROM Table WHERE MATCH (FieldName) AGAINST ('$input');";
$result = mysql_query($sql);

while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}

Code:

$sql = "SELECT * FROM Table WHERE MATCH (FieldName) AGAINST ('$input');";

The above is the query that is run when someone submits something from the search form. It matches up "FieldName" against $input - $input is whatever was put in the form.

For example, lets take it you have the table named Links and in there are the fields ID, Title, URL, Date & you wanted the user to be able to search within Title & URL so you'd do this for the query:

Code:

$sql = "SELECT * FROM Links WHERE MATCH (Title, URL) AGAINST ('$input');";

Also, make sure you change input in the $_POST['input'] bit according to what the text field is named in your form.

Craig.

?>

DJMK4 · 20 Mar 2006 at 20:53

But as stated before i want to be able to search the whole database not just a specified table.

E.G

I go on catalogue and i have the search fields, i want to be able to type in wedding crashers which will return results back from both DVD and Video tables with all the relevant fields.

Your example searches a specified table & field for results.

oneilldo · 20 Mar 2006 at 21:20

You'll have to search each table individually or do a union of the results (will only work if sleected fields are of the same schema.

You can't really 'search' a whole database, you can search specific tables within that database.

DJMK4 · 20 Mar 2006 at 21:29

ok so maybe an implementation of a 2nd form item on the page, namely a dropdown box which lets you choose the table you want to search? but then your still going to have a problem of which field your actually searching for?

Would this call for another drop down box with the field name?

e.g.

Search field

Drop down (Tables)

Drop down (Fields)

so say i could search for:

Wedding crashers

DVD (Table)

Title (Field)

Craig321 · 21 Mar 2006 at 02:04

If you use a dropdown box then you could do something like this:

The dropdown box (within the form that will later post to get the result(s)):

Code:

  <select name="select">
	<option>item 1</option>
	<option>item 2</option>
	<option>item 3</option>
  </select>

Then in the query you would do this:

Code:

$searchTable = $_POST['select'];

$sql = "SELECT * FROM $searchTable WHERE MATCH (Field, Fielda, Fieldb, Fieldetc) AGAINST ('$input');";

If you use the way above then it'd be best to name your tables well (capitals etc.) as you'll have to have the menu item the same name as the table name.

Craig.

DJMK4 · 21 Mar 2006 at 10:52

Nice one, thanks mate

Will give that a bash!

Presumable i would just amend the search.php file to accompany this query replacing the one you previously gave me

Code:

<?

$username="";
$password="";
$database="caldico_mediacat";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);
$searchTable = $_POST['tables'];


$sql = "SELECT * FROM $searchTable WHERE MATCH (Fields) AGAINST ('$userQuery');"

$result = mysql_query($sql) or die(mysql_error());

while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}



?>

$searchTable = $_POST['select'];

$sql = "SELECT * FROM $searchTable WHERE MATCH (fields) AGAINST ('$userQuery');"

DJMK4 · 21 Mar 2006 at 18:55

Anyone?

Trigger · 21 Mar 2006 at 19:01

Craig321 said:

That wouldn't work because you haven't assigned the options values

It needs to be

Code:

  <select name="select">
	<option value="item1">item 1</option>
	<option value="item2">item 2</option>
	<option value="item3">item 3</option>
  </select>

EDIT: And if you're going to put the search form in the same file as the searching code, you need:

Code:

<?php

if (!isset($_POST['submit'])) {

?> 

// form code etc

<?php

} else 
{

// searching db code

}

?>

and

Code:

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">

DJMK4 · 21 Mar 2006 at 19:07

Im still getting the

Parse error: parse error, unexpected T_VARIABLE in /home/caldico/public_html/fcv/database/search.php on line 32

error message on that line which has something to do with..

Code:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

function remove_magic_quotes($array) {
	foreach ($array as $k => $v) {
		if (is_array($v)) {
			$array[$k] = remove_magic_quotes($v);
		} else {
			$array[$k] = stripslashes($v);
		}
	}
	return $array;
}
if (get_magic_quotes_gpc()) {
	$_GET	= remove_magic_quotes($_GET);
	$_POST   = remove_magic_quotes($_POST);
	$_COOKIE = remove_magic_quotes($_COOKIE);
}

$input = strip_tags($_POST['userQuery']);
$searchTable = $_POST['select'];


$sql = "SELECT * FROM $searchTable WHERE MATCH (Fields) AGAINST ('$userQuery');"




//THIS LINE
[b]$result = mysql_query($sql) or die(mysql_error());[/b]
//


while ($field = mysql_fetch_array($result))
{
	$searchResults = $field['FieldName'];
	
	echo '$searchResults';
}



?>

the line identified by bold

Trigger · 21 Mar 2006 at 19:09

Try it without the 'or die' section:

Code:

$result = mysql_query($sql);

DJMK4 · 21 Mar 2006 at 19:11

no change with the following

Code:

$sql = "SELECT * FROM $searchTable WHERE MATCH (catagory) AGAINST ('$userQuery');"

$result = mysql_query($sql);

Trigger · 21 Mar 2006 at 19:17

I don't think your problem lies with that as when I copy and paste your code, line 32 is blank :confused:

Where you have

Code:

echo '$searchresults';

remove the quotes:

Code:

echo $searchresults;