title: Implementing PHP and MySQL search on four tables
body: <p>I have four tables j_music, j_topics ,J_users, j_video, with MySQL db structure:</p>
  
  <pre><code>J_music
  id  artist  title   Views   Album art   Link        uploader
  1   niyi    Love me 121 Love.jpg    Niyi-love.mp3    Admin
  2   tolu    I am an ode 111 Toludope.jpg    Tolu-doe.mp3    admin
  3   justin  Kind love   122 Justin-kind-love.jpg    Justin-kind-lovw.mp3    Admin
  
  J_video
  Id  artist  title   Downloads   video art   Link    uploader
  1   vandeross   Sweet love  121 Love.jpg    Niyi-love.mp3   Admin
  2   richie  Locked down 111 Toludope.jpg    Tolu-doe.mp3    Admin2
  3   justin  A wonderful word    122 Justin-kind-love.jpg    Justin-kind-lovw.mp3    Admin
  
  J_user
  id  username    First name  Last name   userdp  password    country
  1   niyi    faleye  niyi    Niyi-dp.jpg *****   Wales
  2   tolu    abolarin    tolu    Tolu-dp.jpg ******* Nigeria
  3   justin  gayle   justin  Justin-dp.jpg   ********    England
  
  J_topics
  id  title   subject Views   Topic image Date    creator
  1   Justin beiber goes nude **************  121 Love.jpg    21-02-2013  mark
  2   High crisis in the  ********    111 Toludope.jpg    21-02-2013  peter
  3   Justin gatlin wins  ********    122 Justin-kind-love.jpg    21-02-2013  Steven
  </code></pre>
  
  <p>I want to implement a php and mysql search to search for a specific query “justin” in the four tables and output the results like this if found. It should match the search query with the following columns;  artist and title in j_music, artist and artist and title in j_video, username in j_users, title and subject in j_topics:</p>
  
  <p>(a) If the result is found in the music table it provides a special link
  (b) If the result is found in the topics table it provides a special link
  (c) If the result is found in the videos table it provides a special link
  (d) If the result is found in the users table it provides a special link</p>
  
  <p>Something like this in the result set</p>
  
  <pre><code>If found in music
  $artist $title &lt;a href=’music.php?id’&gt;click here&gt;&lt;/a&gt;”;
  
  If found in video
  $artist $title &lt;a href=’video.php?id’&gt;click here&gt;&lt;/a&gt;”;
  
  If found in topics
  $title $subject &lt;a href=’topic.php?topicid’&gt;click here&gt;&lt;/a&gt;”;
  
  If found in users
  $username $firstname &lt;a href=’users.php?userid’&gt;click here&gt;&lt;/a&gt;”;
  </code></pre>
  
  <p>Here's the code I have for a single table search and output:</p>
  
  <pre><code>echo"&lt;form action='#' method='POST'&gt;&lt;ul&gt;&lt;li&gt;&lt;input type='text' name='q' /&gt;&lt;input type='submit' name='submit' value='Search' class='button'&gt;&lt;/center&gt;&lt;/li&gt;&lt;/ul&gt;";
  $q=$_REQUEST["q"];
  if(isset($q) &amp;&amp; !empty($q))
  {
  $q=trim($q);
  $q=cleanvalues($q);
  $q=strtoupper($q);
  //echo"you searched for $q";
  //$u="is";
  $self=$_SERVER["PHP_SELF"];
  $rowsperpage=2;
  $range=2;
  if(isset($_GET["currentpage"]) &amp;&amp; is_numeric($_GET["currentpage"]))
  {
  $currentpage=(int)$_GET["currentpage"];
  }
  else
  {
  $currentpage=1;
  }
  $offset=($currentpage-1)*$rowsperpage;
  $numrows=mysql_num_rows(mysql_query("SELECT * from j_users WHERE UPPER(username) LIKE '%$q%' OR UPPER(username) LIKE '%$q' OR UPPER(username) LIKE '$q%'"));
  $totalpages=ceil($numrows/$rowsperpage);
  if($currentpage&gt;$totalpages)
  {
  $currentpage=$totalpages;
  }
  if($currentpage&lt;1)
  {
  $currentpage=1;
  }
  $query=mysql_query("SELECT * from j_users WHERE UPPER(username) LIKE '%$q%' OR UPPER(username) LIKE '%$q' OR UPPER(username) LIKE '$q%' LIMIT $offset, $rowsperpage");
  $num=mysql_num_rows($query);
  if($num==0)
  {
  echo"No result found";
  }
  else
  {
  echo"Search Results For $q";
  while($info=mysql_fetch_assoc($query))
  {
  $username=cleanvalues2($info["username"]);
  $id=$info["userID"];
  echo"&lt;a href='profile.php?uid=$id'&gt;$username&lt;/a&gt;";
  }
  if($currentpage&gt;1)
  {
  echo"&lt;a href='$self?currentpage=1&amp;q=$q'&gt;First &lt;/a&gt;";
  $prevpage=$currentpage-1;
  echo"&lt;a href='$self?currentpage=$prevpage&amp;q=$q'&gt;Prev&lt;/a&gt;";
  }
  for($x=($currentpage-$range); $x&lt;(($currentpage+$range)+1); $x++)
  {
  if(($x&gt;0) &amp;&amp;($x&lt;=$totalpages))
  {
  if($x==$currentpage)
  {
  echo"[&lt;font color='red'&gt;$x&lt;/font&gt;]";
  }
  else
  {
  echo"&lt;a href='$self?currentpage=$x&amp;q=$q'&gt;[&lt;b&gt;$x&lt;/b&gt;]&lt;/a&gt;";
  }
  }
  }
  if($currentpage!=$totalpages)
  {
  $nextpage=$currentpage+1;
  echo"&lt;a href='$self?currentpage=$nextpage&amp;q=$q'&gt;Next&lt;/a&gt;";
  echo"&lt;a href='$self?currentpage=$totalpages&amp;q=$q'&gt;Last&lt;/a&gt;";
  }}
  }
  </code></pre>