CODEWITHSUNDEEP
phpsql
Noah Smith
Noah Smith

Reputation: 303

How can I add multiple tables to this query

I have a table called news and with these two snippets of code, I want to create a search engine that scans through the keywords table. With one table connected and running very nicely, it would be cleaner to add an extra table to the query.

My task is to create a search engine that returns rows from the tables. The search engine is based on keywords and is great for specific terming, such as 'New York Times' but if I want to type in news, that's where all the news sites are ordered by id. But sometimes completely different terms that have the keyword 'news' will pop up quite high in the table unlike CNN because of the id. With a new table, it would be a lot easier to organize the tables. That way if a term entered is 'news', sites will be ordered by id and even if they clash on other tables, they are still ordered by id.

Though my query is a bit different than the traditional query, I don't know how to

  1. add a table via a UNION or

  2. with a LEFT JOIN tag of some sort.

My query is below and I would love for someone to explain: a) what's wrong simply b) tell me or paste the code below:

<?php
if( isset($_GET['k']) ){
    $k = $_GET['k'];
}else{
    $k = '';
}
$k = ( isset($_GET['k']) )? trim($_GET['k']) : '';
$terms = (strlen($k) > 0)? explode(' ', $k) : Array();


/* The code below is from a different part of the script */

$query = " SELECT * FROM scan WHERE "; 

$terms = array_map('mysql_real_escape_string', $terms);
$i = 0; 
foreach ($terms as $each) {
    if ($i++ !== 0){
        $query .= " AND ";
    }
    $query .= "keywords LIKE '%{$each}%'";
}

Upvotes: 1

Views: 100

Answers (2)

user1401072
user1401072

Reputation:

I don't know exactly what you want to do, but this might help :

$query = " SELECT * FROM scan, news WHERE scan.news_id = news.id AND ";

$terms = array_map('mysql_real_escape_string', $terms);

foreach ($terms as $each) {
  $query .= "AND scan.keywords LIKE '%{$each}%'";
}

You make an union between two table by adding a condition in the query and selecting from the two tables. The condition is to ensure that the common column in the two tables are equals.

Upvotes: 2

user1477388
user1477388

Reputation: 21430

For a left join, read this http://www.w3schools.com/sql/sql_join_left.asp

I don't really know what you're asking. If you can clarify your question, I will provide an example for you. Thanks.

Upvotes: 0

Related Questions