CODEWITHSUNDEEP
phpmysqlarrays
jonmrich
jonmrich

Reputation: 4323

Issue getting distinct values from column into array from mySQL via PHP

I'm trying to get the unique values in a specific column into an array, so I can validate what's being sent to my PHP to avoid mySQL injection. Here's what I have:

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$sessiont = 'SET SESSION group_concat_max_len=6000';
$fixed = $dbh->query($sessiont);
$stmt = $dbh->prepare("select distinct `category` as cats FROM {$table}");
$stmt->execute(); 
$row = $stmt->fetch();
$categories = explode(",",$row["cats"]);

$whatCategory = isset($_GET['category']) ? "{$_GET['category']}" : '';

if (in_array($whatCategory, $categories))
{
    $sql = "select distinct value from {$table} where category = '{$whatCategory}';";

    $result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    header('Content-type: application/json');
    echo json_encode($result);
}
else
{
    echo ("unauthorized!");
}

Right now, this is only giving me the first value in the column category and isn't creating a valid array, which means I'm getting "unauthorized!"

If I make a replacement to the above for $categories like this:

$categories = array ('category1','category2','category3');

Then it works (i.e., I don't get unauthorized!)

Upvotes: 3

Views: 1167

Answers (1)

Professor Abronsius
Professor Abronsius

Reputation: 33813

Totally untested but looking at the sql you use initially you will get several rows probably containing 'cats' rather than one field with comma separated values of cats. Perhaps you would wish to use group_concat( category ) as 'cats' to return a single row?

You then go on to test whether '$whereCategory' is in the array of categories. I see no previous mention of $whereCategory!

Upvotes: 2

Related Questions