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

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';
}



?>

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?

DJMK4 · 20 Mar 2006 at 20:30

im sorry mate but what is name reffering to?

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.

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)

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?

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

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);

DJMK4 · 21 Mar 2006 at 19:19

Nope, its still not liking anything on

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

$result = mysql_query($sql);

that line, cant think what else it could be, iv tried many different combinations but nothing seems to be changing

DJMK4 · 22 Mar 2006 at 11:04

sorry to be a pain but there is one last problem i cant work out with the last while statement

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')";

$result = mysql_query($sql);

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




?>

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/caldico/public_html/fcv/database/search.php on line 34

DJMK4 · 22 Mar 2006 at 13:13

Anyone? i cant seem to get rid of this error

DJMK4 · 22 Mar 2006 at 19:25

Will try this

$userQuery

is the search field in the form on the menu, along with $select and submit button

DJMK4 · 22 Mar 2006 at 19:35

Grrr, bloody host is down

they usually really good with downtime but today they have had some problems

DJMK4 · 23 Mar 2006 at 13:24

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 (catagory, title) AGAINST ('$userQuery')";

$result = mysql_query($sql);

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



?>

Its not returning any results as i have tried a number of different combo's. it dont like the while loop at the end, iv tried the $check alternative code that someone posted, it timed out.

Code:

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

does the $field have to be replaced by a field name? changing $field in the loop and also change ['fieldName'] to an actual field name?

DJMK4 · 23 Mar 2006 at 15:52

getting there

type on comedy, romance, horror into the search field, select dvd table then submit..