Cant get search script to work

[pha3dr0n]

I'm trying to get a search script running so that users can choose the field to search (from a list of 5), then type in the data to search for, and get the results displayed in a table. I've been trying for ages and cant get anything to work. Heres the script I'm using to enter data to the db, so you can see the fields, etc:

<?php
if ($submit):
$dbcnx = @mysql_connect( "xxxx", "xxxx", "xxxx");
mysql_select_db("contacts");

$sql = "INSERT INTO admin_contacts SET " .
"forename='$forename', " .
"surname='$surname', " .
"category='$category', " .
"address1='$address1', " .
"address2='$address2', " .
"address3='$address3', " .
"address4='$address4', " .
"postcode='$postcode', " .
"homephone='$homephone', " .
"mobile='$mobile', " .
"fax='$fax', " .
"email='$email', " .
"title='$title', " .
"shows='$shows', " .
"sex='$sex'";
if (mysql_query($sql)) { echo("<P>New Record Added</P>");
} else {
echo("<P>Error adding new record: " .
mysql_error() . "</P>"); }
?>

<P><A HREF="<?php echo($PHP_SELF); ?>">Add another record</A></P>
<?php else: // Enter another record?>

<FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>

<P>Please enter your information below<BR>
Name:
<INPUT TYPE=TEXT NAME="forename" SIZE=22MAXLENGTH=100>
<input type=TEXT name="surname" size=30MAXLENGTH=100>
<br>
Category:
<select name="category">
<option value="Committee">Committee
<option value="Business">Business
<option value="Mailshot">Mailshot
<option value="Vip">VIP
<option value="Cast">Cast
<option value="Fans">Fans
</select>
<br>
Address:
<input type=TEXT name="address1" size=44MAXLENGTH=100>
<br>
Address2:
<input type=TEXT name="address2" size=44MAXLENGTH=100>
<br>
Address3:
<input type=TEXT name="address3" size=44MAXLENGTH=100>
<br>
Address4:
<input type=TEXT name="address4" size=44MAXLENGTH=100>
<br>
Postcode:
<input type=TEXT name="postcode" size=44MAXLENGTH=100>
<br>
Home Phone:
<input type=TEXT name="homephone" size=44MAXLENGTH=100>
<br>
Mobile:
<input type=TEXT name="mobile" size=28MAXLENGTH=100>
<br>
Fax:
<input type=TEXT name="fax" size=44MAXLENGTH=100>
<br>
Email:
<input type=TEXT name="email" size=44MAXLENGTH=100>
<br>
Title:
<input type=TEXT name="title" size=44MAXLENGTH=100>
<br>
Shows:
<input type=TEXT name="shows" size=44MAXLENGTH=100>
<br>
Sex:
<select name="sex">
<option value="Male">Male
<option value="Female">Female
</select>
<br>
<INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT">
</P>
</FORM>
<?php endif; ?>

As an afterthought, is there any way to get the above script to refresh the table when the submit button is clicked, instead of confirming that the data has been entered??

I know its a lot to ask, but I am really banging my head off a wall now.

Pha3dr0n

[Celtboy]

this is interesting...i'm not sure about your "search" as I don't see any "search" code....

this is what I'd do with what you've got:

split into 2 files:

proc_form.php

<?php

/* Read the template for the form */
$form_file = "form.htm";

$template = file($form_file);
$page = "";
foreach ($template as $line) {
   $page .= $line;
}

/*** insert to db code ***/
if (isset($_POST["submit"])) {
   $dbcnx = @mysql_connect( "xxxx", "xxxx", "xxxx");
   mysql_select_db("contacts");

   $sql  = "INSERT INTO admin_contacts (forename,surname,category,address1,address2,address3,address4,postcode,homephone,mobile,fax,email,title,shows,sex) VALUES(";
   $sql .= "'" . $_POST["forename"] . "','" . $_POST["surname"] . "','" . $_POST["category"] . "','" . $POST["address1"] . "','" . $_POST["address2"] . "','";
   $sql .= $_POST["address3"] . "','" . $_POST["address4"] . "','" . $_POST["postcode"] . "','" . $_POST["homephone"] . "','" . $_POST["mobile"] . "','" ;
   $sql .= $_POST["fax"]  . "','" . $_POST["email"] . "','" . $_POST["title"] . "','" . $_POST["shows"] . "','" . $_POST["sex"] . "')";
   
   $result = mysql_query($sql);
   if ($result) {
      str_replace("<!--MESSAGE-->","<p>New Record Added. Feel free to add another.</p>",$page);
   } else {
      str_replace("<!--MESSAGE-->","<p>Error adding new record: " . mysql_error() . "</p><br/>",$page);
   }
} else {
  str_replace("<!--MESSAGE-->","<p>Add a record:</p>",$page);
}
   print $page;
?>

and

form.htm

<html><body>
<!--MESSAGE-->
<form action="proc_form.php" method="POST">

<p>Please enter your information below<BR>
Name:
<input type="text" name="forename" size="22" maxlength="100">
<input type="text" name="surname" size="30" maxlength="100">
<br>
Category:
<select name="category">
<option value="Committee">Committee
<option value="Business">Business
<option value="Mailshot">Mailshot
<option value="Vip">VIP
<option value="Cast">Cast
<option value="Fans">Fans
</select>
<br>
Address:
<input type="text" name="address1" size="44" maxlength="100">
<br>
Address2:
<input type="text" name="address2" size="44" maxlength="100">
<br>
Address3:
<input type="text" name="address3" size="44" maxlength="100">
<br>
Address4:
<input type="text" name="address4" size="44" maxlength="100">
<br>
Postcode:
<input type="text" name="postcode" size="44" maxlength="100">
<br>
Home Phone:
<input type="text" name="homephone" size="44" maxlength="100">
<br>
Mobile:
<input type="text" name="mobile" size="44" maxlength="100">
<br>
Fax:
<input type="text" name="fax" size="44" maxlength="100">
<br>
Email:
<input type="text" name="email" size="44" maxlength="100">
<br>
Title:
<input type="text" name="title" size="44" maxlength="100">
<br>
Shows:
<input type="text" name="shows" size="44" maxlength="100">
<br>
Sex:
<select name="sex">
<option value="Male">Male
<option value="Female">Female
</select>
<br>
<input type="submit" name="submit" value="SUBMIT">
</p>
</form>
</body></html>

as for searching...i'd need more complete code. PS, I haven't tested that code, but it should work...

-Celt

[pha3dr0n]

Thats the code for the data input to the db.

I havent got any search code to work, hence I havent posted any !!!

Theres an error in line 13 of the proc_form.php

[Celtboy]

line 13 problem fixed

What parameters are you wanting the user to be able to search by?

[pha3dr0n]

Originally posted by Celtboy
line 13 problem fixed

What parameters are you wanting the user to be able to search by?

Thanks m8. The parameters I want them to use are :

forename
surname
category
shows

Thanks for your help

[pha3dr0n]

Ok, I've tried the two following scripts. searchdb.htm presents the form where users can choose the field to search and enter the query; results.php should process this and display the results in a table. I know its a bit messy, but if I can get it to work, I can clean it up a bit.

searchdb.htm:

<html>
<head>
  <title>Database search</title>
</head>

<body>

<h1><font face="Arial, Helvetica, sans-serif" color="#333333">Database Search</font></h1>

<form action="results.php" method="post">
        <font face="Arial, Helvetica, sans-serif" color="#333333" size="2">Choose
        Search Type:</font><br>
        <select name="searchtype">
          <option value="forename">Forename
          <option value="surname">Surname
          <option value="category">Category
          <option value="title">Title
          <option value="shows">Shows
        <select>
        <br>
        <font face="Arial, Helvetica, sans-serif" size="2" color="#333333">Enter
        Search Term:</font><br>
        <input name="searchterm" type=text>
        <br>
        <input type=submit value="Search">
      </form>

</body>
</html> 

results.php:

<html>
<head>
 <title>Search results</title>
</head>

<body>
<h1>search results</h1>
<?
 trim($searchterm);
 if (!$searchtype || !$searchterm)
  {
    echo "You have not entered search details. Please go back and try again.";
        exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

@ $db = mysql_pconnect("xxxx","xxxx","xxxx");

if (!$db)
{
  echo "Error: Could not connect to database. Please try again later.";
  exit;
}

mysql_select_db("contacts");
$query = "select * from contacts where ".$searchtype." like
  '%".$searchterm."%'";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo "<p>Number of entries found: ".$num_results."</p>";

echo '<TABLE border="1">';
echo '<TR><TH>Forename</TH><TH>Surname</TH><TH>Address1</TH><TH>Address2</TH><TH>Address3</TH><TH>Address4</TH><TH>Postcode</TH><TH>Home Phone</TH>
<TH>Mobile</TH><TH>Fax</TH><TH>Email</TH><TH>DOB</TH><TH>Title</TH><TH>Category</TH><TH>Show</TH></TR>';

while ( $row = mysql_fetch_array( $result ) )
{
    echo '<TR>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['forename'] ) ) . '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['surname'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address1'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address2'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address3'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address4'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['postcode'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['homephone'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['mobile'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['fax'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['email'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['dateofbirth'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['title'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['category'] ) ) . '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['shows'] ) ) .   '</TD>';
    echo '</TR>';
}

echo '</TABLE>';



?>

</body>
</html>

[Celtboy]

what kind of results are you getting?

it's really late, so I'm kinda out of it.

here's results.php with a few changes...

<html>
<head>
 <title>Search results</title>
</head>

<body>
<h1>search results</h1>
<?php
 trim($_POST["searchterm"]);
 if (!($_POST["searchtype"]) || !($_POST["searchterm"]))
 {
    echo "You have not entered search details. Please go back and try again.";
    exit;
 }

$searchtype = addslashes($_POST["searchtype"]);
$searchterm = addslashes($_POST["searchterm"]);

@ $db = mysql_pconnect("xxxx","xxxx","xxxx");

if (!$db)
{
  echo "Error: Could not connect to database. Please try again later.";
  exit;
}

mysql_select_db("contacts");
$query = "select * from contacts where ".$searchtype." like
  '%".$searchterm."%'";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo "<p>Number of entries found: ".$num_results."</p>";

echo '<TABLE border="1">';
echo '<TR><TH>Forename</TH><TH>Surname</TH><TH>Address1</TH><TH>Address2</TH><TH>Address3</TH><TH>Address4</TH><TH>Postcode</TH><TH>Home Phone</TH>
<TH>Mobile</TH><TH>Fax</TH><TH>Email</TH><TH>DOB</TH><TH>Title</TH><TH>Category</TH><TH>Show</TH></TR>';

while ( $row = mysql_fetch_assoc( $result ) )
{
    echo '<TR>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['forename'] ) ) . '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['surname'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address1'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address2'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address3'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['address4'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['postcode'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['homephone'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['mobile'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['fax'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['email'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['dateofbirth'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['title'] ) ) .   '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['category'] ) ) . '</TD>';
    echo '<TD>' . htmlspecialchars( stripslashes( $row['shows'] ) ) .   '</TD>';
    echo '</TR>';
}

echo '</TABLE>';



?>

</body>
</html>

hmmm...curiously, why did you addslashes to the searchtype?

-Celt

[pha3dr0n]

I'm not getting any results returned at all, but I'm not getting any error messages either

I didnt write the script, just found it online and tried to amend it - the addslashes were already there.