RRPANDEY RRPANDEY - 2 months ago 16
MySQL Question

Linking fetched data using "LIKE %columname%" for each parameter PHP

I have actors field in my movie database which is having many actors in one field separated by comma and fetching them using below code. My requirement is to link all fetched actors. on click on each actor will take to the list of their movie.
Since i am having all actors in one field and separated by commas, struggling to link each of them with separate url

<?php
require('connect');

$filmActor=$_GET['filmActor'];
$sql ="SELECT * FROM films WHERE filmActor LIKE '%$filmActor%' LIMIT 0 , 5;";

$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{
$filmActor=$row['filmActor'];
$filmName=$row['filmName'];
$url=$row['url'];
echo "
<a href='$url.html'>$filmName</a>: $filmActor<br>
";
}
mysqli_free_result($result);
mysqli_close($conn);
?>


Output i am getting like:
enter image description here

Expected:
enter image description here

Want to pass this parameter:
/actor.php?filmActor=Tom_Hanks, /actor.php?filmActor=Emma_Thompson etc will displace each actors film they have worked on.

Answer

This script should work. It takes the $row['filmActor'] and split all the actors into an array by ',', and then we print them out one after one.

Just keep in mind that this can be done a better way, but this should work. Also, I've added a "mysqli_real_escape_string" to the GET input "$_GET['filmActor']" to prevent SQL injections.

<?php

require('connect');

// Escape the input from the user, preventing SQL injections
$filmActor = mysqli_real_escape_string($conn,$_GET['filmActor']); 
$sql ="SELECT * FROM films WHERE filmActor LIKE  '%$filmActor%' LIMIT 0 , 5;";

$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{
    $filmActor=$row['filmActor'];
    $filmName=$row['filmName'];
    $url=$row['url'];
    echo "<a href='$url.html'>$filmName</a>:";

    // Make an array of the actors by splitting them by ','
    $actorsArray = explode(',',$filmActor);

    // Loop the array
    foreach ($actorsArray as $key => $actor)
    {

        // Just trim the space in front of name in case there is any
        $actor = trim($actor);

        // Check if the current key is == to the last key in the array
        // so it wont make an ',' in the end of the actors.
        if ($key == (count($actorsArray)-1))
            echo "<a href='/actor.php?filmActor=$actor'>$actor</a>";
        else
            echo "<a href='/actor.php?filmActor=$actor'>$actor</a>, ";
    }
}
mysqli_free_result($result);
mysqli_close($conn);

Let me know how it works out. And as tadman said in the comments above "NEVER put $_POST or $_GET data directly into a query, it can be very harmful if someone seeks to exploit your mistake."

Hope it helps!