Implementing PHP and MySQL search on four tables

Question

I have four tables j_music, j_topics ,J_users, j_video, with MySQL db structure:

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

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:

(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

Something like this in the result set

If found in music
$artist $title <a href=’music.php?id’>click here></a>”;

If found in video
$artist $title <a href=’video.php?id’>click here></a>”;

If found in topics
$title $subject <a href=’topic.php?topicid’>click here></a>”;

If found in users
$username $firstname <a href=’users.php?userid’>click here></a>”;

Here's the code I have for a single table search and output:

echo"<form action='#' method='POST'><ul><li><input type='text' name='q' /><input type='submit' name='submit' value='Search' class='button'></center></li></ul>";
$q=$_REQUEST["q"];
if(isset($q) && !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"]) && 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>$totalpages)
{
$currentpage=$totalpages;
}
if($currentpage<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"<a href='profile.php?uid=$id'>$username</a>";
}
if($currentpage>1)
{
echo"<a href='$self?currentpage=1&q=$q'>First </a>";
$prevpage=$currentpage-1;
echo"<a href='$self?currentpage=$prevpage&q=$q'>Prev</a>";
}
for($x=($currentpage-$range); $x<(($currentpage+$range)+1); $x++)
{
if(($x>0) &&($x<=$totalpages))
{
if($x==$currentpage)
{
echo"[<font color='red'>$x</font>]";
}
else
{
echo"<a href='$self?currentpage=$x&q=$q'>[<b>$x</b>]</a>";
}
}
}
if($currentpage!=$totalpages)
{
$nextpage=$currentpage+1;
echo"<a href='$self?currentpage=$nextpage&q=$q'>Next</a>";
echo"<a href='$self?currentpage=$totalpages&q=$q'>Last</a>";
}}
}

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Dec 16 '15 at 20:23
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 16 '15 at 20:23
Please I'm still stalked and confuse. I read normalize and join of tables but still don't get it. can some one please point in the right direction maybe with codes as example. — johnwelch, Dec 18 '15 at 00:08

score 0 · Answer 1 · answered Dec 21 '15 at 15:39

Man, your code is really not good. I suggest you to choose a PHP Framework and start studying it, this will help you a lot. Laravel is my suggestion for you. It's easy to learn and very powerful.

And as the other user suggested, prefer to use PDO instead of mysql_* functions.

As you want to do some SELECT's, this is an example using PDO for MySQL database:

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'myuser', 'mypassword');

$artist = "Justin";

$query = $pdo->query("SELECT id, artist, title FROM j_music WHERE artist LIKE '%".$artist."%'");
$row   = $query->fetch(PDO::FETCH_ASSOC);

echo($row['artist']." ".$row['title']." <a href='music.php?'".$row['id'].">click here></a>");

Implementing PHP and MySQL search on four tables

1 Answers1