MySQL/PHP Selecting only unique values from multiple columns and put them into seperate arrays

Question

Using PHP and MySQL I'm trying to select unique values from multiple columns in a table (but not all the columns in the table) and put all the unique values from each column selected in its own array. The uniqueness of each columns values should be compared only to other values in the same column.

The code below puts all the unique values from all the columns in the $make_array then puts empty values in all the rest of the arrays.

How can I select only unique values from these columns and put each columns values in its own array?

$sql = "
    (SELECT make FROM items)
    UNION
    (SELECT model FROM items)
    UNION
    (SELECT year FROM items)
    UNION
    (SELECT month FROM items)
    UNION
    (SELECT day FROM items)
    UNION
    (SELECT hour FROM items)";

$result = @mysql_query($sql, $con) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

    $make = $row[make];
    $model = $row[model];
    $year = $row[year];
    $month = $row[month];
    $day = $row[day];
    $hour = $row[hour];

    $make_array[make][] = $make;
    $model_array[model][] = $model;
    $year_array[year][] = $year;
    $month_array[month][] = $month;
    $day_array[day][] = $day;
    $hour_array[hour][] = $hour;
}

@Col Shrapnel Not really relevant to the question. They are just sample values for this example. — Marcus
– Marcus, Commented Oct 14, 2010 at 18:16
you know, your whole question is not much relevant to common sense. I am just trying to figure out why do you need such unusual thing. — Your Common Sense
– Your Common Sense, Commented Oct 14, 2010 at 18:20
union appends rows, not columns, so your query is building one big table with lots of records and only one column. When you think about it, how could it give you multiple columns when each query could have a different number of records? — Brad Mace
– Brad Mace, Commented Oct 14, 2010 at 18:24
@Col Shrapnel I understand your curious why I would use unique days and hours. In my actual script I am not. I simply picked arbitrary values for this example rather then publicizing my actual data. — Marcus
– Marcus, Commented Oct 14, 2010 at 18:31

Rudu · Accepted Answer · 2010-10-14 18:20:00Z

2

If you want to keep it as one SQL statement, then you could:

$sql = "
    SELECT DISTINCT 'make' as descr,make as val FROM items
    UNION
    SELECT DISTINCT 'model' as descr,model as val FROM items
    UNION
    SELECT DISTINCT 'year' as descr,year as val FROM items
    UNION
    SELECT DISTINCT 'month' as descr,month as val FROM items
    UNION
    SELECT DISTINCT 'day' as descr,day as val FROM items
    UNION
    SELECT DISTINCT 'hour' as descr,hour as val FROM items";

$result = @mysql_query($sql, $con) or die(mysql_error());

while($row = mysql_fetch_array($result)) {
    $make_array[$row['descr']][]=$row['val'];
}

answered Oct 14, 2010 at 18:20

Rudu

15.9k4 gold badges51 silver badges66 bronze badges

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

2 Comments

Brad Mace Over a year ago

@Mark this will do what you were trying to do, though I'm not sure what you were trying to do makes much sense. You're probably just as well off to execute them as separate queries.

Rudu Over a year ago

Certainly don't disagree with that @bemace although from Mark's comments perhaps this is just a pseudo-example - perhaps this approach makes more sense for his application.

Collectives™ on Stack Overflow

MySQL/PHP Selecting only unique values from multiple columns and put them into seperate arrays

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related