Match two fields in MYSQL table via php search

Collapse
X
Collapse
 
  • Page of 2
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
  • jamesmoore
    New Member
    • Jan 2011
    • 47
    #1

    Match two fields in MYSQL table via php search

    Hi,

    I am trying to make a job board.

    I have made two search boxes:

    1/ Job Title
    2/ Location

    I have made a mockup form for the recruiters to input their job data into my database.

    How do I link up two of the fields from the table so the job title when searched is matched up with the location?

    The table consists of

    msg_id | name | location | msg

    Name is the table field for 'Job Title' by the way

    The code is below:


    Thanks!

    James

    Code:
    <html>
<Head>
      <title>Contact form</title>
      <style type="text/css">
                  table{
                  border:1;
                  border-collapse:collapse;
                  font: normal 12px 'Lucida Grande',Verdana,sans-serif;
                  }
                  td{
                  color:#663333;font-family:verdana;
                  border-bottom: 1px solid #666;
                  padding-left:10px;
                  background-color:#F0F8FF;
                  }
                  #sub{ text-align:center;}
      </style>
</Head>
<body>
James' New Site
<br>
 <h2> FeedBack/Contact Form</h2>
      <form action="contact_insert.php" method="POST" id="insert">
                  <table>
                              <tr>
                                          <td >Name*</td>
                                          <td  ><input type="text" size=40 name="name"></td>
                              </tr>
                              <tr>
                                          <td >location</td>
                                          <td  ><input type="text" size=40 name="location"></td>
                              </tr>
                              <tr>
                                          <td >Comments/Suggestions*</td>
                                          <td  ><textarea  name="msg" cols=40 rows=7></textarea> </td>
                              </tr>
                              <tr>
                                          <td colspan=2 id="sub"><input type="submit" name="submit" value="submit" ></td>
                              </tr>
                  </Table>
      </form>
<br>
<br>
<form action="seaside.php" method="post">
<input type="text" name="search">
<input type="text" name="search2">
<input type="submit">
</form>
<hr width="100%"></hr>
</body>
</html>
<?php
if(strlen(trim($_POST['search'])) > 0) {
//all of your php code for the search

  $search = "%" . $_POST["search"] . "%";
 
  mysql_connect ("", "", "");
  mysql_select_db ("");
 if (!empty($_POST["search_string"])) 
   { 
      // then perform your queries and stuff here. 
   }  
  $query = "SELECT name,msg FROM contact WHERE name LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo "<br>$row[0]</br>";
      echo $row[1];
    }
  }
}
?>
    Tags: None
    • Rabbit
      Recognized Expert MVP
      • Jan 2007
      • 12517
      #2
      Do you mean match Job Title in the name field and Location in the location field? You can use AND in the SQL's where clause to use two criteria.

        Comment

        • jamesmoore
          New Member
          • Jan 2011
          • 47
          #3
          Hi there,

          Yes so say for instance someone searches:

          "Receptioni st" in the Job title search box and "Kent" in the location search box and click submit, the only results that show is data that involves "receptioni st" and "kent" in its table row

          Thanks!

          James

            Comment

            • jamesmoore
              New Member
              • Jan 2011
              • 47
              #4
              Also do I have to so anything to the search boxes so each one refers to its certain properties so one only answers to name and one to location?

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517
                  #5
                  You don't have to do anything to the search boxes. You just need to modify the query to account for both criteria using the key word AND.

                    Comment

                    • jamesmoore
                      New Member
                      • Jan 2011
                      • 47
                      #6
                      Thanks,

                      So will it literally look like this?


                      Code:
                       $query = "SELECT name AND location FROM contact WHERE name LIKE '$search'";
                      how can I also include my field msg to show its data in results?

                      Cheers!

                        Comment

                        • Rabbit
                          Recognized Expert MVP
                          • Jan 2007
                          • 12517
                          #7
                          You keep the same SELECT clause as before. You just need to modify your WHERE clause. The WHERE clause is the place where you put criteria.

                          If you're having trouble with SQL, you should look into a SQL tutorial. It will save you countless hours.

                          Also, I noticed that in your PHP, you're referring to a POST field named 'search'. I can only assume on the page before the search results, your name textbox is named 'search'. If this is not the case, then that is wrong as well.

                          Also, there's nothing in your SQL connection. That's just to protect confidential information correct? Otherwise, you're not actually connecting to anything.

                            Comment

                            • jamesmoore
                              New Member
                              • Jan 2011
                              • 47
                              #8
                              Yeah that's just to protect confidential information. I searched for SQL Where tutorials and all the posts seem to be like this example:

                              SELECT *
                              FROM Customers
                              WHERE LastName = 'Smith'

                              How do I do it so people are entering what they want to search in that field not going by what I have written into the script like the example above?

                              Thanks for the help so far!

                              James

                                Comment

                                • Rabbit
                                  Recognized Expert MVP
                                  • Jan 2007
                                  • 12517
                                  #9
                                  I'm not sure what you mean by your third paragraph.

                                  But bouncing off your SQL example, if I wanted to find someone a LastName of Smith and a FirstName of John, I would do this
                                  Code:
                                  SELECT *
FROM Customers
WHERE LastName = 'Smith'
  AND FirstName = 'John'
                                  That should be enough to fix the SQL syntax.

                                    Comment

                                    • jamesmoore
                                      New Member
                                      • Jan 2011
                                      • 47
                                      #10
                                      I mean as you are stating in the script, referring to the example above, to only show John Smith so the users freedom to search whatever last name cannot happen?

                                        Comment

                                        • jamesmoore
                                          New Member
                                          • Jan 2011
                                          • 47
                                          #11
                                          Ok from looking at the tutorials I did the same to my script but it returns all posts with the word trainee disregarding if kent is in its row's data. How do I do it so the only data that shows in my results are entries where BOTH trainee and kent are present in the same row in my table?

                                          Code:
                                           $query = "SELECT name,location,msg FROM contact WHERE name = 'trainee' AND location = 'kent' LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo "<br>$row[0]</br>";
      echo $row[1];
      echo "<br>$row[2]</br>";

                                            Comment

                                            • Rabbit
                                              Recognized Expert MVP
                                              • Jan 2007
                                              • 12517
                                              #12
                                              That's because you have the LIKE predicate in there. Take that out and it'll work.

                                              The user is free to search whatever they want. The example was only to show how to combine two criteria, which is what you were asking about.

                                                Comment

                                                • jamesmoore
                                                  New Member
                                                  • Jan 2011
                                                  • 47
                                                  #13
                                                  Ok I got rid of the LIKE and when i entered in my search boxes nothing appears now..
                                                  and then I got this message:

                                                  Parse error: syntax error, unexpected '>' in /websites/123reg/LinuxPackage21/fo/ur/wa/fourwaysdp.co.u k/public_html/seaside.php on line 67

                                                  it refers to the <br> part below, why is it not working still?

                                                  Code:
                                                    $query = "SELECT name,location,msg FROM contact WHERE name ='shannon' AND location = 'bexleyheath';
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo "<br>$row[0]</br>";
      echo $row[1];
      echo "<br>$row[2]</br>";
    }
  }
}
?>
</body>
</html>

                                                    Comment

                                                    • Rabbit
                                                      Recognized Expert MVP
                                                      • Jan 2007
                                                      • 12517
                                                      #14
                                                      You didn't close off your sql string; you have an open double quote. Also, there is no such thing as </br>. Either <br> or <br />.

                                                        Comment

                                                        • jamesmoore
                                                          New Member
                                                          • Jan 2011
                                                          • 47
                                                          #15
                                                          Ah right my mistake sorry!

                                                          Ok now I have updated the same section BUT now only 'shannon' and 'bexleyheath' are returned from the search box. No matter what I type in the search box the only results are shannon and bexleyheath.

                                                          How is this part fixed?

                                                          Thank you so much for the help! Nearly there!

                                                          James

                                                          Code:
                                                           $query = "SELECT name,location,msg FROM contact WHERE name = 'shannon' AND location = 'bexleyheath' ";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo "<br>$row[0]<br/>";
      echo $row[1];
      echo "<br>$row[2]<br/>";
    }
  }
}
?>
</body>
</html>

                                                            Comment

                                                            Previous 1 2 template Next
                                                            Working...