Databases
StackOverflow2013
Postsdbo
POPHP and MySQL filter multiple tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP and MySQL filter multiple tables
    text
    copied!<p>I have 3 tables:</p> <p><strong>users</strong></p> <ul> <li>id</li> <li>name</li> </ul> <p><strong>categories</strong></p> <ul> <li>id</li> <li>name</li> </ul> <p><strong>posts</strong></p> <ul> <li>id</li> <li>title</li> <li>content</li> <li>user_id</li> <li>category_id</li> </ul> <p>I'm making a filter (like live search) with jquery. A form with multiple fields represents diferente columns to list posts.</p> <pre><code>&lt;form onkeyup="filter()"&gt; &lt;input name="post_id" /&gt; &lt;input name="post_title" /&gt; &lt;input name="post_content" /&gt; &lt;input name="user_name" /&gt; &lt;input name="category_name" /&gt; //order by column &lt;select name="col"&gt; &lt;option value="post_id"&gt;post_id&lt;/option&gt; &lt;option value="post_title"&gt;post_title&lt;/option&gt; &lt;option value="post_content"&gt;post_content&lt;/option&gt; &lt;option value="user_name"&gt;user_name&lt;/option&gt; &lt;option value="category_name"&gt;category_name&lt;/option&gt; &lt;/select&gt; //direction &lt;select name="dir"&gt; &lt;option value="DESC"&gt;DESC&lt;/option&gt; &lt;option value="ASC"&gt;ASC&lt;/option&gt; &lt;/select&gt; //limit number of results &lt;input name="lim"/&gt; &lt;/form&gt; </code></pre> <p>Than I POST this values through jQuery to a .php file that will query the database.</p> <p>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:</p> <pre><code>$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()); </code></pre> <p>Than I return rows like this:</p> <pre><code>while($post = mysql_fetch_assoc($query)) { echo "&lt;tr&gt;"; echo "&lt;td&gt;" . $post['id'] . "&lt;/td&gt;"; echo "&lt;td&gt;" . $post['title'] . "&lt;/td&gt;"; echo "&lt;td&gt;" . $post['content'] . "&lt;/td&gt;"; echo "&lt;td&gt;" . $post['category_name'] . "&lt;/td&gt;"; echo "&lt;td&gt;" . $post['user_name'] . "&lt;/td&gt;"; echo "&lt;/tr&gt;"; } </code></pre> <p>How do I make a query that searches inside other tables so that I don't have to duplicate columns in the posts table?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload