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

Question

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!)

Professor Abronsius · Accepted Answer · 2015-02-20 16:23:08Z

2

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!

answered Feb 20, 2015 at 16:23

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jonmrich Over a year ago

Had to change it to this: $stmt = $dbh->prepare("select group_concat( category ) as 'cats' FROM {$table}"); and it works. However, each category has multiple rows in my table and the array that is created has as many entries as there are rows for a given column (e.g.,"category1","category1","category1","category1", ,"category2","category2"). Any way to ensure I only get one entry per category?

Professor Abronsius Over a year ago

Probably use array_unique on final array before passing it to the logic test ( in_array( $whatCategory, $categories ) )

jonmrich Over a year ago

Tried adding to array_unique to this which builds the array, but it only includes one category (first one alphabetically): $categories = explode(",",$row["cats"]);. I guess I'm not sure how to integrate array_unique into my specific code.

jonmrich Over a year ago

nevermind...I got it. I had made a change to my sql statement that broke everything and didn't change it back. Adding the array_unique to this worked perfectly: $categories = explode(",",$row["cats"]); Thanks.

Professor Abronsius Over a year ago

or... $categories = array_unique( explode(",",$row["cats"]) ); ??

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related