CODEWITHSUNDEEP
phpjquerymysql
Joao Belchior
Joao Belchior

Reputation: 25

PHP and MySQL filter multiple tables

I have 3 tables:

users

categories

posts

I'm making a filter (like live search) with jquery. A form with multiple fields represents diferente columns to list posts.

<form onkeyup="filter()">

<input name="post_id" />
<input name="post_title" />
<input name="post_content" />
<input name="user_name" />
<input name="category_name" />

//order by column
<select name="col">
    <option value="post_id">post_id</option>
    <option value="post_title">post_title</option>
    <option value="post_content">post_content</option>
    <option value="user_name">user_name</option>
    <option value="category_name">category_name</option>
</select>

//direction
<select name="dir">
    <option value="DESC">DESC</option>
    <option value="ASC">ASC</option>
</select>

//limit number of results
<input name="lim"/>

</form>

Than I POST this values through jQuery to a .php file that will query the database.

But in order to filter "posts" with the category name or user name I have or any other value outside the posts table I have duplicated these columns in the posts table. Currently I have this query:

$query = mysql_query("
SELECT *
FROM posts
WHERE id LIKE '%$id%'
AND title LIKE '%$title%'
AND content LIKE '%$content%'
AND user_id LIKE '%$user_id%'
AND user_name LIKE '%$user_name%'
AND category_id LIKE '%$category_id%'
AND category_name LIKE '%$category_name%'
ORDER BY $col $dir
$_lim
") or die (mysql_error());

Than I return rows like this:

while($post = mysql_fetch_assoc($query))
{
echo "<tr>";
echo    "<td>" . $post['id'] . "</td>";
echo    "<td>" . $post['title'] . "</td>";
echo    "<td>" . $post['content'] . "</td>";
echo    "<td>" . $post['category_name'] . "</td>";
echo    "<td>" . $post['user_name'] . "</td>";
echo "</tr>";
}

How do I make a query that searches inside other tables so that I don't have to duplicate columns in the posts table?

Upvotes: 2

Views: 3028

Answers (3)

Joao Belchior
Joao Belchior

Reputation: 25

I've figured it out using a query that's something like this:

SELECT posts.id, posts.title, posts.content, users.name, categories.name
FROM posts
LEFT JOIN users
ON posts.users_id = users.id
LEFT JOIN categories
ON posts.category_id = categories.id
WHERE posts.id LIKE '%$id%' OR
WHERE posts.title LIKE '%$title%' OR
WHERE posts.content LIKE '%$content%' OR
WHERE users.name LIKE '%$user_name%' OR
WHERE categories.name LIKE '%$category_name%'

Upvotes: 0

Gundars Mēness
Gundars Mēness

Reputation: 498

Use SQL JOIN for that, it will join the tables in memory without duplicating them. W3Schools has a good example - Inner Join

Upvotes: 3

Mostafa Berg
Mostafa Berg

Reputation: 3239

Also, I'm not sure if I understand clear, but you're currently just searching against the posts table, users and categories should be joined.

Upvotes: 0

Related Questions