multiple search field search engine

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

multiple search field search engine

Post by stanleycwb »

Code: Select all

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1-traditional.dtd">
 
<?php
 
$Stud_Name = $_POST['Stud_Name'];
$Stud_ID = $_POST['Stud_ID'];
$Gender = $_POST['Gender'];
$Course_Code = $_POST['Course_Code'];
$Occupation = $_POST['Occupation'];
$Year_of_Admission = $_POST['Year_of_Admission'];
$Year_of_Grad = $_POST['Year_of_Grad'];
 
 
$conn = mysqli_connect("localhost", "xxx", "xxxx", "xxxxx");
 
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}
 
 
 
if ($Stud_Name) {
$query1 = mysqli_query($conn, "Select * FROM Student WHERE Stud_Name LIKE '%$Stud_Name%'");
 
  print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of     Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result1 = mysqli_fetch_array($query1))
  {
   
   $Stud_ID=$result1["Stud_ID"];
   $Stud_Name=$result1["Stud_Name"];
   $DOB=$result1["DOB"];
   $Gender=$result1["Gender"];
   $Address=$result1["Address"];
   $Stud_OContact=$result1["Stud_OContact"];
   $Stud_HPContact=$result1["Stud_HPContact"];
   $Stud_HContact=$result1["Stud_HContact"];
   $Email=$result1["Email"];
   $Occupation=$result1["Occupation"];
   $Course_Code=$result1["Course_Code"];
   $Year_of_Admission=$result1["Year_of_Admission"];
   $Year_of_Grad=$result1["Year_of_Grad"];
 
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
 
$num_results = @mysqli_num_rows($query1);
    printf("%d student(s) found.\n", $num_results);
 
} 
 
else if ($Gender) {
 
$query2 = mysqli_query($conn, "Select * FROM Student WHERE Gender = '$Gender'");
 
 
  print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of     Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result2 = mysqli_fetch_array($query2))
  {
   
   $Stud_ID=$result2["Stud_ID"];
   $Stud_Name=$result2["Stud_Name"];
   $DOB=$result2["DOB"];
   $Gender=$result2["Gender"];
   $Address=$result2["Address"];
   $Stud_OContact=$result2["Stud_OContact"];
   $Stud_HPContact=$result2["Stud_HPContact"];
   $Stud_HContact=$result2["Stud_HContact"];
   $Email=$result2["Email"];
   $Occupation=$result2["Occupation"];
   $Course_Code=$result2["Course_Code"];
   $Year_of_Admission=$result2["Year_of_Admission"];
   $Year_of_Grad=$result2["Year_of_Grad"];
 
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
$num_results = @mysqli_num_rows($query2);
    printf("%d student(s) found.\n", $num_results);
 
}
 
.
.
.
.
.
.
 
The coding will still continue for other search fields. I have a search HTML form. Currently, the coding can only execute if only one field is been selected (e.g. name or gender). But i wan to have coding that could seach multiple fields (e.g. name and gender and other search fields.

Anyone could help me with the coding???
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

So you want to have an OR search for values of any of the fields that have data supplied? You need to do something like this:

Code: Select all

 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' OR ";
    }
}
$sql = substr($sql,0,strripos($sql," OR ")-1);
$query1 = mysqli_query($conn, $sql);
The idea is to build your query string by going through the superglobal array $_POST and for each field found, add an OR to the WHERE clause, then remove the last one.
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Hey, thanks for the reply.
It looks like a solution to me. But i dun really understand what is the code trying to do and what they all means. Especially from line 4 to line 9. Im quite lost and how to insert my insert statement criteria(WHERE Stud_Name LIKE '%$Stud_Name%') in. I know line 5 is about asking if the field is empty anot. Then if it is not, it will execute line 6 codes. But wat does the $key and $values means. And after the coding structure u given me, is that means that i can insert my codes where they will print out the results in the table?

The printing results codes

Code: Select all

print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of     Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result1 = mysqli_fetch_array($query1))
  {
   
   $Stud_ID=$result1["Stud_ID"];
   $Stud_Name=$result1["Stud_Name"];
   $DOB=$result1["DOB"];
   $Gender=$result1["Gender"];
   $Address=$result1["Address"];
   $Stud_OContact=$result1["Stud_OContact"];
   $Stud_HPContact=$result1["Stud_HPContact"];
   $Stud_HContact=$result1["Stud_HContact"];
   $Email=$result1["Email"];
   $Occupation=$result1["Occupation"];
   $Course_Code=$result1["Course_Code"];
   $Year_of_Admission=$result1["Year_of_Admission"];
   $Year_of_Grad=$result1["Year_of_Grad"];
 
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
 
$num_results = @mysqli_num_rows($query1);
    printf("%d student(s) found.\n", $num_results);
 
} 
 
Please help me! Thank you in advance.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

OK, the key=>value notation is used in PHP associative arrays. An associative array is essentially a 2-dimensional array, like:

Code: Select all

(key)         (value)
'firstname'   'John'
'lastname'    'Doe'
'phone'       '123-4567'
PHP has two "superglobal" associative arrays, $_POST and $_GET that are always defined, and when you define a Form, you specify which of these methods you want to use to pass values to the next script. If you use $_GET, the names and values appear in the URL.

So, even when you don't know what the names of the fields are that are being passed, you can do a "foreach" loop that goes through all key/value pairs, which is what lines 4 to 7 of my sample code does. For each pair, it temporarily assigns the first one to $key and the second one to $value so you can test and use these values. Assuming that the "keys" (which are really the names of the Input elements in the Form), are actually the field names (you'll have to check to make sure this is true), $value is whatever the user entered in that Input element.

So what you're after is to build a SQL string that might look something like this:

Code: Select all

SELECT * FROM Student WHERE `Gender`='M' OR `Course_Code`='BUS101' OR `Year_of_Grad`='2006'
So you examine each of the non-blank value pairs in the $_POST array and add onto the SQL string you're building. Finally, you need to remove the last " OR " at the end of the string.

As I look at your requirement again, I'm not sure that using "OR" is what you need. If you want the user to be able to narrow down the search by including more than one field, you would have to use "AND". The way I showed you will work if the user enters only one field, and it will also work if you want it to include ALL records that meet ANY of the criteria, but if the user enters a student's name and their year of graduation, for example, an "OR" search will return records for every student that graduated that year.
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Hey thanks thanks thanks.
Then how do i go about replacing the column names inside?
I mean like change the $key to my first field name.
And by the way, for my requirements, i need to have those query to search all the fields criteria.
Example like if the user enter the student id(primary key) and the year of graduation, it will display the only student that graduate at the year.

Code: Select all

$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $Stud_Name => $value) {
    if(isset($value)) {
      $sql .= "`$Stud_Name` LIKE '%$value%' AND ";
    }
}
$sql = substr($sql,0,strripos($sql," AND ")-1);
$query1 = mysqli_query($conn, $sql);
 
.......
//printing results codes
 
 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $Stud_ID => $value) {
    if(isset($value)) {
      $sql .= "`$Stud_ID` LIKE '%$value%' AND ";
    }
}
$sql = substr($sql,0,strripos($sql," AND ")-1);
$query2 = mysqli_query($conn, $sql);
 
.......
//printing results codes
For example like for my 1st search field is Stud_Name and 2nd search field is Stud_ID
Am i in the right track?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

stanleycwb wrote:Hey thanks thanks thanks.
Then how do i go about replacing the column names inside?
I mean like change the $key to my first field name.
You won't need to, because $key will BE the field name (assuming that your field names are the same as the names you give to the Input element, which is what you would normally do anyway).
And by the way, for my requirements, i need to have those query to search all the fields criteria.
Example like if the user enter the student id(primary key) and the year of graduation, it will display the only student that graduate at the year. For example like for my 1st search field is Stud_Name and 2nd search field is Stud_ID
Am i in the right track?
Not quite. You have to think very carefully what you are asking for. It does not make logical sense to search for both the id (primary key) and anything else, because there is only ONE record with that id, that's the whole purpose of a primary key. So forget about the primary key unless you are looking for ONE record only, then search for ONLY the primary key.

If you want to search for multiple records that meet several criteria, you have to decide if you want to find all records that meet ANY of the criteria, or ONLY those records that meet ALL of the criteria. Here's an example:

Code: Select all

[u]ID[/u]  [u]color[/u]    [u]size[/u]     [u]year[/u]
 1  Blue     Large    1985
 2  Red      Medium   1985
 3  Green    Large    1988
 4  Blue     Small    1982
So if you want all the records for year=1985, you will get ID's 1 and 2.
If you want all the records for size=Large, you will get ID's 1 and 3.
If you want all the records for year=1985 OR size=Large, you will get ID's 1, 2 and 3.
If you want all the records for year=1985 AND size=Large, you will only ID 1, because it is the only record that meets both conditions.

It's exactly the same with SQL. In the WHERE clause, if you use several matches separated by "OR", you will get the records that meet ANY of the conditions. If you use several matches separated by "AND", you will get only the records that meet ALL of the conditions. You have to determine which results you want.
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Okay. I get your point. The results that i wanted is to get only the records that meet all of the conditions. So i should use AND instead of OR.

For the coding part, are you saying that i just need to write one code and a printing results code and that's it?
Where the $key and the $value will automatically be the field name.
Like this?

Code: Select all

$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' AND ";
    }
}
$sql = substr($sql,0,strripos($sql," AND ")-1);
$query1 = mysqli_query($conn, $sql);
 
print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of     Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result1 = mysqli_fetch_array($query1))
  {
   
   $Stud_ID=$result1["Stud_ID"];
   $Stud_Name=$result1["Stud_Name"];
   $DOB=$result1["DOB"];
   $Gender=$result1["Gender"];
   $Address=$result1["Address"];
   $Stud_OContact=$result1["Stud_OContact"];
   $Stud_HPContact=$result1["Stud_HPContact"];
   $Stud_HContact=$result1["Stud_HContact"];
   $Email=$result1["Email"];
   $Occupation=$result1["Occupation"];
   $Course_Code=$result1["Course_Code"];
   $Year_of_Admission=$result1["Year_of_Admission"];
   $Year_of_Grad=$result1["Year_of_Grad"];
 
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
 
$num_results = @mysqli_num_rows($query1);
    printf("%d student(s) found.\n", $num_results);
 
} 
 
Is that right?

And by the way, this is my search form html.

Code: Select all

<html> 
<head><title>Student Search Form</title></head> 
<body bgcolor="white"> 
 
<h1>Student Search Form</h1>
<h4>You can use the options on this page to create a very specific search. Just fill in the fields you needed for your current search. </h4>
 
<form action="xxx.php" method="post">
 
<table border="0">
<col span="1" align="left">
 
<tr>
    <td>Student Name:</td>
    <td><input type="text" name="Stud_Name" size="80" /></tr>
<tr><td>  </td><td><h6>(e.g. Celestine) </h6></td></tr>
 
<tr>
<td><br />Admission Number:<br /><br /></td>
<td><br /><input type="text" name="Stud_ID" size="50" /></td></tr>
 
<tr>
<td><br />Gender:<br /><br /></td>
<td><br />Male<input type="radio" value="Male" name="Gender"> Female<input type="radio" value="Female" name="Gender"><br /><br /></td></tr>
 
<tr>
<td>Course of Study:</td>                           
<td><br /><select name="Course_Code">
<option value="">- - -</option>
<option value="C0B">Diploma in a</option>
<option value="C0H">Diploma in b</option>
<option value="C0J">Diploma in c</option>
<option value="C0E">Diploma in d</option>
<option value="C0H">Diploma in e</option>
<option value="C0D">Diploma in f</option></select><br /><br /></td>
</tr>
 
<tr>
<td>Occupation:</td>
<td><br /><select name="Occupation">
<option value="">- - -</option>
<option value="Student">Student</option>
<option value="Housewife">Housewife</option>
<option value="Employed">Employed</option>
<option value="Self-Employed">Self-Employed</option>
<option value="Unemployed">Unemployed</option>
<option value="Retired">Retired</option>
<option value="Others">Others</option></select><br /><br /></td>
</tr>
 
<tr>
<td>Year Of Admission:  </td>
<td><br /><select name="Year_of_Admission">
<option value="">- - -</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option></select><br /><br /></td>
 
</tr>
 
<tr>
<td>Year of Graduate:</td>
<td><br /><select name="Year_of_Grad">
<option value="">- - -</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option></select><br /><br /></td>
</tr>
 
<tr>
<td><br /><br /><input type="submit" value="Search">
<input type="reset" value="Clear"></td>
</tr>
 
</table>
</form> 
</body> 
</html>
 
The field name and the column name in mySQL is the same. So it wont pose any problem or error right?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

Yes, that should do it. Did you try it? Don't be afraid to try a solution and see what happens. If it works the way you want it to, you're all done. If it doesn't, you will gain experience from trying to see where it went wrong.

I didn't go through all your code thoroughly, but I didn't see any obvious problems. You could actually make it a little simpler by skipping the assignment of the variables in lines 17 to 29 (in the part that you showed in your last post) and using the PHP function extract(). It works like this:

Code: Select all

  while ($result1 = mysqli_fetch_array($query1))  {
    extract($result1);   // assigns $ variables for all the elements in the array
    print "<td>$Stud_ID</td>";
    print "<td>$Stud_Name</td>";
    ... and so forth
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Code: Select all

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1-traditional.dtd">
 
<?php
 
$Stud_Name = $_POST['Stud_Name'];
$Stud_ID = $_POST['Stud_ID'];
$Gender = $_POST['Gender'];
$Course_Code = $_POST['Course_Code'];
$Occupation = $_POST['Occupation'];
$Year_of_Admission = $_POST['Year_of_Admission'];
$Year_of_Grad = $_POST['Year_of_Grad'];
 
 
$conn = mysqli_connect("localhost", "xx", "xxx", "xxxx");
 
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}
 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' AND ";
    }
}
 
$sql = substr($sql,0,strripos($sql," AND ")-1);
$query1 = mysqli_query($conn, $sql);
 
print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result1 = mysqli_fetch_array($query1)) 
  {
  extract($result1);   // assigns $ variables for all the elements in the array
 
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
 
$num_results = @mysqli_num_rows($query1);
    printf("%d student(s) found.\n", $num_results);
 
mysqli_close($conn);
?>
 
Alright, i tried the following codes. No errors found. Except for this.

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xx\xxx\xxxx\xxxxx\xxxxxx.php on line 38

And another potential problem is they didn't display any data no matter which fields i used.
It only display the column tables. Example like the Student id, student name, dob, gender... without the real data inside.

What is wrong with the coding?
Could it be I didnt post $key and $value or define them properly?
Help me!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

OK, sorry, my line to cut off the last " AND " was wrong. Change the 1 to 4 and I think it will work:

Code: Select all

$sql = substr($sql,0,strripos($sql," AND ")-[color=#FF0000]4[/color]);
But to make this a learning experience for you, I will mention a couple of things:

1. The warning message, "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result" is telling you that you didn't have a valid result from the query, so it couldn't fetch the row. In fact, it undoubtedly returned the boolean value False. So you know that the problem is in either the connection or the query itself.

2. To make such problems easier to find, you should use "die" functions after queries, like this:

Code: Select all

$conn = mysqli_connect("localhost", "xx", "xxx", "xxxx") or die("Unable to connect!");
  ...
$query1 = mysqli_query($conn, $sql) or die("Query failed! " . mysql_error() . "<br>". $sql);
What "die" does is absolutely nothing unless the operation fails, then it will echo whatever you tell it to, then halt the script, rather than going on to execute the rest of it.

So if the connection fails, it will display "Unable to connect!" If the query fails, it will display "Query failed!" plus the error from MySQL, plus the exact SQL statement that it tried to execute. You can even try that, and I'm pretty sure you will find that the problem was caused because I didn't give you quite the correct code to chop off that last " AND " from the $sql, so it was invalid syntax and MySQL couldn't execute the query, so it returned a boolean False instead of a valid MySQL result (data).
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Thanks, I will go try it out.
But for the meantime, i got a problem. The following codes could print out table that is in horizontal format. But that is not what i wan. This is because it might not be user-friendly and user have to scroll to the right and down which is quite tedious for them. So i intend to make it vertically so that they could see them easily.

Code: Select all

 
print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
 
.
.
.
.
.
 
   print "</TR>";
  };
print "</table>";
 

NOT THIS

stud_id stud_name Date of birth Gender ... ...
12345 john 01-01-1911 Male


BUT THIS

stud id 12345
stud_name john
Date of birth 01-01-1911
Gender Male
.
.
.
.
.

Can help me with this?
Thanks!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search field search engine

Post by califdon »

I would just not use an HTML Table. Just echo each item with a "<br />" at the end.
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

hey! I just tried the code but it still doesnt work.

It display the following error.

Query failed!
SELECT * FROM Student WHERE `Stud_Name` LIKE '%%' AND `Stud_ID` LIKE '%%' AND `Gender` LIKE '%Male%' AND `Course_Code` LIKE '%%' AND `Occupation` LIKE '%%' AND `Year_of_Admission` LIKE '%%' AND `Year_of_Grad` LIKE

And stop right there after LIKE.
What's wrong with my codes?

Code: Select all

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1-traditional.dtd">
 
<?php
 
$Stud_Name = $_POST['Stud_Name'];
$Stud_ID = $_POST['Stud_ID'];
$Gender = $_POST['Gender'];
$Course_Code = $_POST['Course_Code'];
$Occupation = $_POST['Occupation'];
$Year_of_Admission = $_POST['Year_of_Admission'];
$Year_of_Grad = $_POST['Year_of_Grad'];
 
 
$conn = mysqli_connect("localhost", "xx", "xxx", "xxxx") or die("Unable to connect!");
 
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}
 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' AND ";
    }
}
 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' AND ";
    }
}
 
$sql = substr($sql,0,strripos($sql," AND ")-4);
$query1 = mysqli_query($conn, $sql) or die("Query failed! " . mysql_error() . "<br>". $sql);
 
print "<table border=2>";
  print "<TR><TH>Student ID</TH><TH>Student Name</TH><TH>Date Of Birth</TH><TH>Gender</TH><TH>Address</TH><TH>Office Contact</TH><TH>Mobile Contact</TH><TH>Home Contact</TH><TH>Email</TH><TH>Occupation</TH><TH>Course Of Study</TH><TH>Year of Admission</TH><TH>Year of Graduate</TH></TR>";
  print "<TR>";
  while ($result1 = mysqli_fetch_array($query1))
  {
  extract($result1);   // assigns $ variables for all the elements in the array
   print "<td>$Stud_ID</td>";
   print "<td>$Stud_Name</td>";
   print "<td>$DOB</td>";
   print "<td>$Gender</td>";
   print "<td>$Address</td>";
   print "<td>$Stud_OContact</td>";
   print "<td>$Stud_HPContact</td>";
   print "<td>$Stud_HCOntact</td>";
   print "<td>$Email</td>";
   print "<td>$Occupation</td>";
   print "<td>$Course_Code</td>";
   print "<td>$Year_of_Admission</td>";
   print "<td>$Year_of_Grad</td>";
   print "</TR>";
  };
print "</table>";
 
$num_results = @mysqli_num_rows($query1);
    printf("%d student(s) found.\n", $num_results);
 
mysqli_close($conn);
?>
 

And if i change this code
$sql = substr($sql,0,strripos($sql," AND ")-4);
to this code
$sql = substr($sql,0,strripos($sql," AND "));

It works. What's the difference?
Can anyone tell me?
If i use this code, what kind of errors or functionality i will not achieve?
stanleycwb
Forum Newbie
Posts: 23
Joined: Fri Aug 15, 2008 11:33 am

Re: multiple search field search engine

Post by stanleycwb »

Hi, I met another problem. This time round, it's the displaying the information part.
Example when i search male, in my database there is 1 male and it will display all his information from that table. And then i wanted to print his information from another table as well. But when i used the following codes, it displays all the information of that table. This is not what i wanted. Instead, i want only his information and print out.

Code: Select all

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1-traditional.dtd">
 
<?php
 
$Stud_Name = $_POST['Stud_Name'];
$Stud_ID = $_POST['Stud_ID'];
$Gender = $_POST['Gender'];
$Course_Code = $_POST['Course_Code'];
$Occupation = $_POST['Occupation'];
$Year_of_Admission = $_POST['Year_of_Admission'];
$Year_of_Grad = $_POST['Year_of_Grad'];
$Gender = $_POST['Gender'];
$Course_Code = $_POST['Course_Code'];
$Occupation = $_POST['Occupation'];
$Year_of_Admission = $_POST['Year_of_Admission'];
$Year_of_Grad = $_POST['Year_of_Grad'];
$Stud_OContact = $_POST['Stud_OContact'];
$Stud_HContact = $_POST['Stud_HContact'];
$Stud_HPContact = $_POST['Stud_HPContact'];
 
$Job_Industry = $_POST['Job_Industry'];
$Job_Position = $_POST['Job_Position'];
$Job_Company = $_POST['Job_Company'];
$Job_Department = $_POST['Job_Department'];
$Job_Description = $_POST['Job_Description'];
$Job_Start_Date = $_POST['Job_Start_Date'];
$Job_End_Date = $_POST['Job_End_Date'];
 
$back_button = '<a href="#" onclick="history.go(-1);return false;">Next Query</a>';
 
$conn = mysqli_connect("localhost", "xx", "xxx", "xxxx") or die("Unable to connect!");
 
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}
 
 
$sql = "SELECT * FROM Student WHERE ";
 
foreach($_POST as $key => $value) {
    if(isset($value)) {
      $sql .= "`$key` LIKE '%$value%' AND ";
    }
}
 
$sql = substr($sql,0,strripos($sql," AND "));
$query1 = mysqli_query($conn, $sql) or die("Query failed! " . mysql_error() . "<br>". $sql);
 
 
 
 
$sql2 = "SELECT * FROM jobs";
$query2 = mysqli_query($conn, $sql2) or die("Query2 failed! " . mysql_error() . "<br>". $sql);
 
 
$num_results = @mysqli_num_rows($query1);
    printf("<h1>%d student(s) found.\n</h1>", $num_results);
 
 
print "<table border=2>";
  while ($result1 = mysqli_fetch_array($query1))
  {
  extract($result1);   // assigns $ variables for all the elements in the array
   print"<tr>";
   print "<td><b> Student ID: </b></td> <td>$Stud_ID</td>";
   print"<tr></tr>";
   print "<td><b> Student Name: </b></td ><td>$Stud_Name</td>";
   print "<tr></tr>";
   print "<td><b>Date Of Birth</b> </td> <td>$DOB</td>";
   print "<tr></tr>";
   print "<td><b> Gender: </b></td> <td>$Gender</td>";
   print"<tr></tr>";
   print "<td><b> Address: </b></td ><td>$Address</td>";
   print "<tr></tr>";
   print "<td><b>Office Contact</b> </td> <td>$Stud_OContact</td>";
   print "<tr></tr>";
   print "<td><b> Handphone Contact: </b></td> <td>$Stud_HPContact</td>";
   print"<tr></tr>";
   print "<td><b> Home Contact: </b></td ><td>$Stud_HContact</td>";
   print "<tr></tr>";
   print "<td><b>Email Address</b> </td> <td>$Email</td>";
   print "<tr></tr>";
   print "<td><b>Occupation</b> </td> <td>$Occupation</td>";
   print "<tr></tr>";
   print "<td><b> Course Code: </b></td> <td>$Course_Code</td>";
   print"<tr></tr>";
   print "<td><b> Admission Year: </b></td ><td>$Year_of_Admission</td>";
   print "<tr></tr>";
   print "<td><b>Graduation Year</b> </td> <td>$Year_of_Grad</td>";
   print "<tr></tr>";
   };
print "</table>";
 
  print "<table border=2>";
  while ($result2 = mysqli_fetch_array($query2))
  {
  extract($result2);
   print "<td><b>Job Industry:</b> </td> <td>$Job_Industry</td>";
   print "<tr></tr>";
   print "<td><b>Job Position:</b> </td> <td>$Job_Position</td>";
   print "<tr></tr>";
   print "<td><b> Job Company: </b></td> <td>$Job_Company</td>";
   print"<tr></tr>";
   print "<td><b> Job Department: </b></td ><td>$Job_Department</td>";
   print "<tr></tr>";
   print "<td><b>Job Description:</b> </td> <td>$Job_Description</td>";
   print "<tr></tr>";
   print "<td><b> Job Start Date: </b></td> <td>$Job_Start_Date</td>";
   print"<tr></tr>";
   print "<td><b> Job End Date: </b></td ><td>$Job_End_Date</td>";
   print "<tr></tr>";
   print "</tr>";
  };
print "</table>";
 
print "$back_button";
 
mysqli_close($conn);
?>
 
 
Another problem is when i search female, there is 2 female in my database. But then when it display, it only display the information of the 1st female from the student table. Like the name, number, gender, address, phone number,... ... but not the 2nd female. And the same thing again, all the information of the jobs tables are displayed.
I cant view the detail of the 2nd female.

Can anyone help me?
Thanks!
Post Reply