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

Question

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:

Expected:

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.

You didn't make `$filmActor` a link.. Do what you did for `$filmName`. You also are open to SQL injections. — chris85, Sep 10 '16 at 22:35
Okay, per your update you should normalize youer DB. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 You could explode on `,`s or use one of the CSV functions as well. http://php.net/manual/en/function.str-getcsv.php — chris85, Sep 10 '16 at 22:37
@chris85 i can do like this but it will not give seperate link to each actor since actors are in one field$filmActor — RRPANDEY, Sep 10 '16 at 22:40
See comment #2, http://stackoverflow.com/questions/39431181/linking-fetched-data-using-like-columname-for-each-parameter-php#comment66185598_39431181. — chris85, Sep 10 '16 at 22:40
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Sep 10 '16 at 22:40

score 2 · Accepted Answer · answered Sep 10 '16 at 23:11

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!

Simeon, This works like a charm, thanks a lot, one more question regarding "NEVER put $_POST or $_GET data directly into a query, it can be very harmful if someone seeks to exploit your mistake." how i can get rid of this as i am not an expert in PHP — RRPANDEY, Sep 10 '16 at 23:25
What it means is that when you get raw user input like username, password, email (and in this case filmActor) then you need to make sure that the input is safe. You do that by using the mysqli_real_escape_string or prepared statements (http://www.w3schools.com/php/php_mysql_prepared_statements.asp) — SimmeD, Sep 10 '16 at 23:28
Thank you, i have taken care about this at most of the places, also will check if i have missed prepared statements somewhere in project. — RRPANDEY, Sep 10 '16 at 23:32

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

1 Answers1