473,327 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. <html>
  3. <Head>
  4.       <title>Contact form</title>
  5.       <style type="text/css">
  6.                   table{
  7.                   border:1;
  8.                   border-collapse:collapse;
  9.                   font: normal 12px 'Lucida Grande',Verdana,sans-serif;
  10.                   }
  11.                   td{
  12.                   color:#663333;font-family:verdana;
  13.                   border-bottom: 1px solid #666;
  14.                   padding-left:10px;
  15.                   background-color:#F0F8FF;
  16.                   }
  17.                   #sub{ text-align:center;}
  18.       </style>
  19. </Head>
  20. <body>
  21. James' New Site
  22. <br>
  23.  <h2> FeedBack/Contact Form</h2>
  24.       <form action="contact_insert.php" method="POST" id="insert">
  25.                   <table>
  26.                               <tr>
  27.                                           <td >Name*</td>
  28.                                           <td  ><input type="text" size=40 name="name"></td>
  29.                               </tr>
  30.                               <tr>
  31.                                           <td >location</td>
  32.                                           <td  ><input type="text" size=40 name="location"></td>
  33.                               </tr>
  34.                               <tr>
  35.                                           <td >Comments/Suggestions*</td>
  36.                                           <td  ><textarea  name="msg" cols=40 rows=7></textarea> </td>
  37.                               </tr>
  38.                               <tr>
  39.                                           <td colspan=2 id="sub"><input type="submit" name="submit" value="submit" ></td>
  40.                               </tr>
  41.                   </Table>
  42.       </form>
  43. <br>
  44. <br>
  45. <form action="seaside.php" method="post">
  46. <input type="text" name="search">
  47. <input type="text" name="search2">
  48. <input type="submit">
  49. </form>
  50. <hr width="100%"></hr>
  51. </body>
  52. </html>
  53. <?php
  54. if(strlen(trim($_POST['search'])) > 0) {
  55. //all of your php code for the search
  56.  
  57.   $search = "%" . $_POST["search"] . "%";
  58.  
  59.   mysql_connect ("", "", "");
  60.   mysql_select_db ("");
  61.  if (!empty($_POST["search_string"])) 
  62.    { 
  63.       // then perform your queries and stuff here. 
  64.    }  
  65.   $query = "SELECT name,msg FROM contact WHERE name LIKE '$search'";
  66.   $result = mysql_query ($query);
  67.   if ($result) {
  68.     while ($row = mysql_fetch_array ($result)) {
  69.       echo "<br>$row[0]</br>";
  70.       echo $row[1];
  71.     }
  72.   }
  73. }
  74. ?>
  75.  
  76.  
Aug 17 '11 #1
25 8165
Rabbit
12,516 Expert Mod 8TB
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.
Aug 17 '11 #2
Hi there,

Yes so say for instance someone searches:

"Receptionist" 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 "receptionist" and "kent" in its table row

Thanks!

James
Aug 17 '11 #3
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?
Aug 17 '11 #4
Rabbit
12,516 Expert Mod 8TB
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.
Aug 17 '11 #5
Thanks,

So will it literally look like this?


Expand|Select|Wrap|Line Numbers
  1.  $query = "SELECT name AND location FROM contact WHERE name LIKE '$search'";
  2.  
how can I also include my field msg to show its data in results?

Cheers!
Aug 18 '11 #6
Rabbit
12,516 Expert Mod 8TB
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.
Aug 18 '11 #7
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
Aug 18 '11 #8
Rabbit
12,516 Expert Mod 8TB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Customers
  3. WHERE LastName = 'Smith'
  4.   AND FirstName = 'John'
That should be enough to fix the SQL syntax.
Aug 18 '11 #9
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?
Aug 18 '11 #10
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2.  $query = "SELECT name,location,msg FROM contact WHERE name = 'trainee' AND location = 'kent' LIKE '$search'";
  3.   $result = mysql_query ($query);
  4.   if ($result) {
  5.     while ($row = mysql_fetch_array ($result)) {
  6.       echo "<br>$row[0]</br>";
  7.       echo $row[1];
  8.       echo "<br>$row[2]</br>";
  9.  
  10.  
Aug 18 '11 #11
Rabbit
12,516 Expert Mod 8TB
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.
Aug 18 '11 #12
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.uk/public_html/seaside.php on line 67

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

Expand|Select|Wrap|Line Numbers
  1.   $query = "SELECT name,location,msg FROM contact WHERE name ='shannon' AND location = 'bexleyheath';
  2.   $result = mysql_query ($query);
  3.   if ($result) {
  4.     while ($row = mysql_fetch_array ($result)) {
  5.       echo "<br>$row[0]</br>";
  6.       echo $row[1];
  7.       echo "<br>$row[2]</br>";
  8.     }
  9.   }
  10. }
  11. ?>
  12. </body>
  13. </html>
  14.  
Aug 18 '11 #13
Rabbit
12,516 Expert Mod 8TB
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 />.
Aug 18 '11 #14
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

Expand|Select|Wrap|Line Numbers
  1.  $query = "SELECT name,location,msg FROM contact WHERE name = 'shannon' AND location = 'bexleyheath' ";
  2.   $result = mysql_query ($query);
  3.   if ($result) {
  4.     while ($row = mysql_fetch_array ($result)) {
  5.       echo "<br>$row[0]<br/>";
  6.       echo $row[1];
  7.       echo "<br>$row[2]<br/>";
  8.     }
  9.   }
  10. }
  11. ?>
  12. </body>
  13. </html>
  14.  
Aug 18 '11 #15
One tutorial said to do this:

Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT name,location,msg FROM contact WHERE name = '$searchname' AND location = '$searchlocation' ";
  2.  
AND:

One tutorial said to do this:

Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT name,location,msg FROM contact WHERE name = '$name' AND location = '$location' ";
  2.  
But that also hasn't worked...
Aug 18 '11 #16
Rabbit
12,516 Expert Mod 8TB
Those are just variables, you can call them whatever you want. The important part is to populate that variable with the correct POST data.

You have an example of that in line 57 of your original post. But I suspect you didn't use the correct POST variable name. I don't know what you named your inputs so I can't tell you what that's supposed to be.
Aug 18 '11 #17
I have submitted examples into my database like the one above:

name: shannon
location: bexleyheath

So I am typing the correct text in. What kind of coding am I needing/missing instead of

Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT name,location,msg FROM contact WHERE name = '$name' AND location = '$location' ";
  2.  
The post parts of my script are parts another web developer told me to put in to stop blank searches retreiving all data in my search results and to stop search results appearing when space bar is put in the search box with nothing else.
Aug 18 '11 #18
[deleted post deleted post deleted post]
Aug 18 '11 #19
Referring back to POST
this is the script I use for posting information to my database:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. // contact to database
  4. $connect = mysql_connect("", "", "") or die ("Error , check your server connection.");
  5. mysql_select_db("");
  6.  
  7. //Get data in local variable
  8. $v_name=$_POST['name'];
  9. $v_location=$_POST['location'];
  10. $v_msg=$_POST['msg'];
  11.  
  12. // check for null values
  13. if ($v_name==""  or $v_msg=="")
  14. echo "All fields must be entered, hit back button and re-enter information";
  15. else{
  16. $query="insert into contact(name,location,msg) values('$v_name','$v_location','$v_msg')";
  17. mysql_query($query)  or die(mysql_error());
  18. echo "Your message has been received";
  19. }
  20.  
  21.  
  22. ?>
  23. <html>
  24. <body>
  25. <a href="seaside.php">Back</a>
  26. </body
  27. </html>
  28.  
Aug 18 '11 #20
Rabbit
12,516 Expert Mod 8TB
Lines 8 and 9 are how you get information out of POST. Line 16 is how you use the information you got out of POST. Do the same thing for the search.
Aug 18 '11 #21
I have tried to include this but I am so confused as where to put it

<?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,location,msg FROM contact WHERE name='$v_name'' AND location='$v_location'";
$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/><br><br/>";

}
}
}
?>

This failed to work so what do I put instead of that?
Aug 18 '11 #22
Rabbit
12,516 Expert Mod 8TB
From post #21, you only did the second part, you still have to do the first part.
Aug 18 '11 #23
I have tried to put it all the places i think it could be but It still returns no results...
Is it a case of just copying lines 8 and 9 and placing them? Or does it involve including more code?
Sorry to be a pain! I have only started using php since Sunday!

Thanks again!




Expand|Select|Wrap|Line Numbers
  1. <?php
  2. if(strlen(trim($_POST['search'])) > 0) {
  3. //all of your php code for the search
  4. $v_name=$_POST['name'];
  5. $v_location=$_POST['location'];
  6. $v_msg=$_POST['msg'];
  7.    $search = "%" . $_POST["search"] . "%";
  8.  
  9.   mysql_connect ("cust-mysql-123-03", "ufou_576458_0001", "trotman1");
  10.   mysql_select_db ("fourwaysdpcouk_576458_db1");
  11.  if (!empty($_POST["search_string"])) 
  12.    { 
  13.       // then perform your queries and stuff here. 
  14.    }  
  15.   $query = "SELECT name,location,msg FROM contact WHERE name='$v_name'' AND location='$v_location'";
  16.   $result = mysql_query ($query);
  17.   if ($result) {
  18.     while ($row = mysql_fetch_array ($result)) {
  19.       echo "<br>$row[0]<br/>";
  20.       echo $row[1];
  21.       echo "<br>$row[2]<br/><br><br/>";
  22.  
  23.     }
  24.   }
  25. }
  26. ?>
  27.  
Aug 18 '11 #24
http://fourwaysdp.co.uk/seaside.php

This is the mock up input and output forms I am using here to make it clearer to you.
Aug 18 '11 #25
Rabbit
12,516 Expert Mod 8TB
That depends on if you actually named your inputs with those names on the form. You also have two single quotes in your select query after the name variable. And you're checking for a search_string variable in your post which I doubt is in the input form.
Aug 21 '11 #26

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default...
1
by: Richard | last post by:
Hi I have a PHP script which parces a mySQL table and shows which sections meet on which day by using an array containing (mon,...Fri) and another lisitng sections. I then have the PHP do to...
0
by: Google Mike | last post by:
This has been discussed before back in 2002, and then in Feb of 2003 when I did a search on Google Groups for MySQL Table Sizes. Back then, people said go here: ...
2
by: jaks | last post by:
How can I export a MySQL table with a different name using phpmyadmin? I want to export nuke_bbsearch_wordmatch and all it contents to phpbb_search_wordmatch This table in a different db. ...
15
by: l3vi | last post by:
I have a new system Im building that stores entries of what people are searching for on my sites. I want to be able to keep records of how many times a keyword was searched for daily, and from...
4
by: whitemoss | last post by:
Hi, I've made some changes to my coding..but unfortunately, there were errors when compiling it..dunno how to solve it..hope anyone can help me...the errors: client.c: In function senddata:...
2
by: djdarpan | last post by:
Hi, Can anyone help me! I have multiple auto generated input fields(e.g. values are 1 to 10) in a form and I want to insert all input fields data in one column of MySQL table. Is there any way...
12
by: mantrid | last post by:
Hello Can anyone point me in the right direction for the way to read a text file a line at a time and separate the fields on that line and use them as data in an INSERT to add a record to a mysql...
4
by: mramsay | last post by:
Hi, I'm having a real problem creating a dynamic hyperlink for my website. I want to pull the field name from mysql table. Field name is description. I would like this to be a hyperlink on my...
1
by: cardeal | last post by:
Hi! I have a long field on a mysql table and I would like to create a new table (from the old one) with the (new) content distributed into several fields. Example: oldField: 1. pace paz...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.