CODEWITHSUNDEEP
phpmysql
user1839207
user1839207

Reputation: 87

PHP / MYSQL generalise SELECT clause

I am trying to generalise a Select query using checkboxes, the varible choice you will see is posted from a front end file which uses checkboxes to create an array in the back end. This array is then used to for the SELECT clause of a select statement. Currently the table head outputs the correct header however the data also outputs the same data. Can anyone help me find my error.

<?php
session_start(); //Begins a session
?>

<html> <head> <title> Genre selection back end </title>
</head>
<body>
<?php
$genre_value =  $_POST['genrelist'];

$choice = $_POST['choice'];

$numvalues = count($choice);
echo '<h2> Table shows some data </h2>';
echo "<table border = '1'>";
echo '<tr>';

for($i= 0; $i < $numvalues; $i ++)
{
echo "<th>" .$choice[$i]. "</th>";
$choicearray = $choicearray . ", " .  $choice[$i];
}
echo '</tr>';

$select = substr($choicearray, 1);
include '../functions/connect.php';
$query = "SELECT '$select' FROM film WHERE Genre = '".$genre_value."'";
$result = mysql_query($query) or die ("Invalid query");

while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
        for($i = 0; $i < $numvalues; $i ++)
        {
        echo "<td>" .$row[$i]. "</td>";
        }
    echo "</tr>";
    }
echo "</table>";
mysql_close($con);
?>
</body>
</html>

Upvotes: 0

Views: 94

Answers (1)

Hibiscus
Hibiscus

Reputation: 592

You're using single quotes in your select statement. That makes MySQL select those exact string values.

Remove the single quotes. Change your query to 

$query = "SELECT $select FROM film WHERE Genre = '".$genre_value."'";

Also, please don't use this code for anything more than proof on concept. You absolutely must never trust POST or GET data directly in a query. Make sure you escape each of those values first.

I'd suggest you change your for loop to something like this:

$choicearray = array();
for($i= 0; $i < $numvalues; $i ++)
{
    echo "<th>" .$choice[$i]. "</th>";
    $choicearray[] = mysql_escape_string(trim($choice[$i]));
}
echo '</tr>';
$choicearray = implode(',', $choicearray);

Which will give you the same result but escape every item along the way.

Upvotes: 1

Related Questions