justlukeyou Posted April 12, 2011 Share Posted April 12, 2011 Hi, I have a piece of code which searches and displays a table called 'questions'. However, I am trying to get it to search both 'questions' and 'answers'. Can anyone advise me how I can do this please. I have put 'AND answers' but I think it needs a lot more code than this to search two tables. // Set error reporting. ini_set('display_errors', 1); error_reporting(-1); // Check if there are is a search term. if(isset($_GET['term'])) { $term = mysql_real_escape_string(trim($_GET['term'])); $query = "SELECT * FROM questions AND answers WHERE "; $query .= "question LIKE '%" . $term . "%' OR notes LIKE '%" . $term . "%'"; $query .= " LIMIT 0, 20"; $result = mysql_query($query) or die("Could not run the search query."); $results = "Search results for \"".htmlspecialchars($term)."\":<br /><br />"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_assoc($result)) { $question = $row['question']; $notes = $row['notes']; $results .= "$question $notes<br />"; } } else { $results = 'Sorry, there are no questions or answers relating to your search. Please <a href="http://www.domain.co.uk/test/easy/phpaskquestion.php">ask a question</a>.'; } } ?> <html> <head> </head> <body> <?php // If results have been set, show them instead of the form. if(isset($results)) { echo $results; } else { ?> <form action="" method="get"> Search for: <input type="text" name="term" size="20" /><br /><br /> <input type="submit" name="submit" value="Search!" /> </form> <?php } ?> Quote Link to comment Share on other sites More sharing options...
Stooney Posted April 12, 2011 Share Posted April 12, 2011 Read up on SQL Table Joins. This example isn't really a join, but may help you out for using multiple tables in a query. Note that the code isn't very meaningful, it's just to show the syntax. $result=mysql_query("SELECT table1.field_a, table2.field_c FROM sometable as table1, anothertable as table2 WHERE table1.field_b='$search_keyword' OR table2.field_c='$search_keyword'"); Quote Link to comment Share on other sites More sharing options...
Maq Posted April 12, 2011 Share Posted April 12, 2011 This question has to do with SQL, not PHP. As chris mentioned, learn to use mysql JOIN. Quote Link to comment Share on other sites More sharing options...
justlukeyou Posted April 12, 2011 Author Share Posted April 12, 2011 Hi, Im not sure I understand, isn't it a PHP script reading MySQL My connection code opens 'questions'. do I need to link to both questions and answers or dies join do this for me? Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 12, 2011 Share Posted April 12, 2011 Hi A join is used to get matching rows from several tables, returning a rows made up from both tables. So if you had a table of classes with each row having the id of the teacher taking the class and a table of teachers you could join them together to get the classes and all the teacher details for those classes. A basic join might be SELECT Questions.QuestionText, Answers.AnswerText FROM Questions INNER JOIN Answers ON Questions.QuestionId = Answers.AnswerId However I would presume that a question has several answers (presume one of the answers is correct). If there were (say) 5 answers per question then you would get 5 rows returned per question. In each of these rows the columns from the question tables would be the same while each would have one of the matching answers. All the best Keith Quote Link to comment Share on other sites More sharing options...
justlukeyou Posted April 13, 2011 Author Share Posted April 13, 2011 Thanks, This seems to be one of the hardest things I have come across with PHP. I did have someone doing the coding for me who was very reliable at a decent rate but he hasn't replied in the last few days. Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 13, 2011 Share Posted April 13, 2011 Hi How do want to return the details? A JOIN will link the records from 2 tables. However what you might want is a UNION, which will append the records from one select onto the records from another. I can try and help you more, but I need more details of what you want, the table layouts and how they relate to each other. All the best Keith Quote Link to comment Share on other sites More sharing options...
justlukeyou Posted April 13, 2011 Author Share Posted April 13, 2011 Great thanks, I am looking to search the 'answers' table along with 'questions'. So let's say some searches "blue" and this is in questions it displays each question in which "blue" is featured in. If someone searches "red" which is in both answers and questions then it displays each full question and answer which "red" is in. So they display independently of one another. The other table is called 'easyanswers' and the field is 'answers'. Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 13, 2011 Share Posted April 13, 2011 Hi Think that will be a JOIN. SELECT * FROM questions a LEFT OUTER JOIN easyanswers b ON a.question_id = b.question_id AND b.answer LIKE '%red%' WHERE a.question LIKE '%red%' OR a.notes LIKE '%red%' OR b.answer LIKE '%red%' Assuming that there is a field called question_id on the questions table and a matching field on the answers table. As I mentioned a JOIN produces a row with records from 2 tables. A LEFT OUTER JOIN does this, but will also bring back a row where there is no matching row. Using the example of a class with a teacher id, if you used a LEFT OUTER JOIN you could return a row for the class and the teacher for that class, but should there be no matching teacher you would still get a row back with the columns from the teachers table being empty (ie, NULL). In the SQL above it is doing a LEFT OUTER JOIN between the questions table and the answers table, so a row will have a question and a matching answer. I have checked that the answer contains the search term as well. Doing it in the ON clause means that it will only use answers which contain the search term. If a question doesn't have any answers which contain the search term then the question will be brought back with the answer fields being NULL. The WHERE clause is done after the JOIN. The WHERE clause will find a record where either the question or the question notes contains the search term, or the answer contains the search term. All the best Keith Quote Link to comment Share on other sites More sharing options...
justlukeyou Posted April 14, 2011 Author Share Posted April 14, 2011 Thanks, Im surprised this is so complicated, just to search another table makes it far more complex code. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 14, 2011 Share Posted April 14, 2011 Thats actualy not a complicated query... I'm not trying to be insulting, I know it seems like a big step up from simply SELECT FROM WHERE. But the truth is, there are a few core principles that, although daunting at first, once you have a working understanding of you will be pulling out these kind of queries without much problem at all. Try and take some time and read up on JOINS in all their forms and, if you are in a position to, have a play with them in a sandbox. It's not to say that you will ever have to use them again, but it takes a lot of the generated stress out of life when you hit things like this. Also, don't think twice about asking "how does that do what it does". The guys here like Kickstart can clear things up in minutes that would take hours (sometimes days) researching online. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.