Page 1 of 1

Need Help with a Search Query

Posted: Tue May 26, 2009 1:19 am
by noise
Hi

First of all I'm very new with php and mysql queries and would appreciate all the help i can get.

I have managed to take the following search script sample from the internet and changed a few fields to do what i want.

http://www.webreference.com/programm...rch/index.html

By changing the following script I achieved to query the the first name and lastname and display all the other added tables I added as well. Please see my changed code below.

Code: Select all

You may search either by first or last name
<form method="post" action="search.php?go" id="searchform">
  <p>
  <input type="text" name="name">
</p>
  <p>
    <input type="submit" name="submit" value="Search">
    </p>
</form>
<?php
 
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){
$name=$_POST['name'];
 
//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 
 
//-select the database to use
$mydb=mysql_select_db("fresheye_staff");
 
//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'";
 
//-run the query against the mysql query function
$result=mysql_query($sql);
 
//-count results
 
$numrows=mysql_num_rows($result);
 
echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>"; 
 
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
 
    $FirstName =$row['FirstName'];
    $LastName=$row['LastName'];
    $ID=$row['ID'];
        
//-display the result of the array
 
echo "<ul>\n"; 
echo "<li>" . "<a href=\"search.php?id=$ID\">"  .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}
}
 
if(isset($_GET['by'])){
$letter=$_GET['by'];
 
//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 
 
//-select the database to use
$mydb=mysql_select_db("fresheye_staff");
 
//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $letter . "%' OR LastName LIKE '%" . $letter ."%'";
 
 
//-run the query against the mysql query function
$result=mysql_query($sql); 
 
//-count results
$numrows=mysql_num_rows($result);
 
echo "<p>" .$numrows . " results found for " . $letter . "</p>"; 
 
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
 
$FirstName =$row['FirstName'];
    $LastName=$row['LastName'];
    $ID=$row['ID'];
    
//-display the result of the array
 
echo "<ul>\n"; 
echo "<li>" . "<a href=\"search.php?id=$ID\">"  .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}
 
if(isset($_GET['id'])){
$contactid=$_GET['id'];
 
//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 
 
//-select the database to use
$mydb=mysql_select_db("fresheye_staff");
 
//-query the database table
$sql="SELECT * FROM staff WHERE ID=" . $contactid;
 
 
//-run the query against the mysql query function
$result=mysql_query($sql); 
 
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
 
  $FirstName =$row['FirstName'];
  $LastName=$row['LastName'];
  $idnumber=$row['idnumber'];
  $position=$row['position'];
  $Recommended=$row['Recommended'];
 
//-display the result of the array
 
echo "<ul>\n"; 
echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n";
echo "<li><h4>ID Number   : " . $idnumber . "</h4></li>\n";
echo "<li><h4>Position    : " . $position . "</h4></li>\n";
echo "<li><h4>Recommended&nbsp; : " . $Recommended . "</h4></li>\n";
echo "</ul>";
}
}
 
?>
All I need to do now is change the code a little bit to quary(search) the idnumber table in the database and NOT the first name and lastname as it is currently doing.

Can anybody please help me figure this out. I will greatly appreciate it.

Re: Need Help with a Search Query

Posted: Thu May 28, 2009 1:03 pm
by anand
Line 24 needs to be changed.

Code: Select all

 
//-query the database table
$sql="SELECT ID FROM staff WHERE idnumber LIKE '%" . $idnumber ."%'";
See if this works or not.

Re: Need Help with a Search Query

Posted: Thu May 28, 2009 1:13 pm
by califdon
anand wrote:Line 24 needs to be changed.

Code: Select all

 
//-query the database table
$sql="SELECT ID FROM staff WHERE idnumber LIKE '%" . $idnumber ."%'";
See if this works or not.
That would work IF the field idnumber is a character field, but it's more likely to be an integer field, in which case that won't work. When you're trying to match an integer value, you can't use the LIKE operator and you must not use quotation marks around the value. So it would look like this:

Code: Select all

$sql="SELECT ID FROM staff WHERE idnumber = $idnumber";

Re: Need Help with a Search Query

Posted: Thu May 28, 2009 1:37 pm
by anand
califdon wrote: That would work IF the field idnumber is a character field, but it's more likely to be an integer field, in which case that won't work. When you're trying to match an integer value, you can't use the LIKE operator and you must not use quotation marks around the value. So it would look like this:

Code: Select all

$sql="SELECT ID FROM staff WHERE idnumber = $idnumber";
i was thinking the same. honest :D

Re: Need Help with a Search Query

Posted: Fri May 29, 2009 1:01 am
by noise
thanks for the reply guys.

Actually I found a way that it works the way I wanted to. Was basically me just being new and still learning as its an easy query this..

Code: Select all

 
<form method="post" action="search.php?go" id="searchform">
  <p>
  <input type="text" name="idnumber">
</p>
  <p>
    <input type="submit" name="submit" value="Search">
    </p>
</form>
<?php
 
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$id=$_POST['idnumber'];
 
//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "xxxxxx") or die ('I cannot connect to the database because: ' . mysql_error()); 
 
//-select the database to use
$mydb=mysql_select_db("fresheye_staff");
 
//-query the database table
$sql="SELECT * FROM staff WHERE idnumber LIKE '%" . $id . "%'";
 
//-run the query against the mysql query function
$result=mysql_query($sql);
 
//-count results
 
$numrows=mysql_num_rows($result);
 
echo "<p>" .$numrows . " results found for " . stripslashes($id) . "</p>"; 
 
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
 
  $FirstName =$row['FirstName'];
  $LastName=$row['LastName'];
  $idnumber=$row['idnumber'];
  $position=$row['position'];
  $Recommended=$row['Recommended'];
        
//-display the result of the array
 
echo "<ul>\n"; 
echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n";
echo "<li><h4>ID Number   : " . $idnumber . "</h4></li>\n";
echo "<li><h4>Position    : " . $position . "</h4></li>\n";
echo "<li><h4>Recommended : " . $Recommended . "</h4></li>\n";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}
 
?>