• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Combine distinct columns in mysql database

I'm trying to combine 9 size columns in my database into 1, I currently have size1, size2, size9.

I have an autosuggest field which the user can input a size and the results are populated ala Google.

Obviously I want this field to cover all 9 fields and to only display distinct sizes, not repeating duplicated sizes which there will be in the db.

I've got the distinct part of it working, but can't seem to work out how to combine the columns, I've tried CONCAT, but realised that wasn't really what I was looking for.

This is about as far as I've got:

SELECT DISTINCT (size1, size2, size3, size4, size5, size6, size7, size8, size9) FROM tiles

Thanks!
0
djfenom
Asked:
djfenom
  • 5
  • 4
  • 3
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you need to do this:
select size1 from tiles where size1 is not null
union
select size2 from tiles where size2 is not null
union
select size3 from tiles where size3 is not null

Open in new window

etc for the other fields
0
 
djfenomAuthor Commented:
But how do I go about displaying this on my page, could I use an "AS size" or something like that, so that I can then display the size as one variable?
0
 
Lukasz ChmielewskiCommented:
Yes, you can use ...... as size, execute query and then use
$row['size']
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DoCBReeDCommented:
Are you looking for something like this?

<?php

$test = array('size1', 'size2', 'size3', 'size4', 'size5', 'size6', 'size7', 'size8', 'size9');
var_dump($test);

$put = '';
$i='0';
foreach($test as $tta){
    if($i >= '1'){$put .= ', ';}
    $put .= $tta;
    $i++;
}

echo $put;
?

Open in new window

0
 
djfenomAuthor Commented:
Where do I put the AS bit?

My autosuggest query is currently like this:

SELECT DISTINCT size1 FROM tiles WHERE size1 LIKE '$queryString%' LIMIT 10

Obviously I don't just want to be searching size1, how does this fit in with the UNION bit above?
0
 
Lukasz ChmielewskiCommented:
Guess like this ?

$query = "select (size1 from tiles where size1 is not null union select size2 from tiles where size2 is not null union select size3 from tiles where size3 is not null ) AS size FROM tiles WHERE size LIKE '$queryString%' LIMIT 10";
$result = mysql_query($query) or die('error');

while($row = mysql_fetch_row($result))
echo"$row[size]";

Open in new window


0
 
Lukasz ChmielewskiCommented:
... with sizes up to 9
0
 
djfenomAuthor Commented:
I get an SQL error with that Roads_Roads?
0
 
Ray PaseurCommented:
I think a normalized data base table might have one column for 'size.'  If the client asked for size 8, here is an algorithm that might make sense.

1. SELECT exact matches for size = 8
2. If no results, SELECT matches WHERE size BETWEEN 7 AND 9
0
 
djfenomAuthor Commented:
Hi Ray, the columns are already setup and there are over 2000 records in the database, each of which could have something in each of the 9 size columns, so there is no option to change to one column.

I've tried a different tactic now:

select DISTINCT(size1, size2, size3, size4, size5, size6, size7, size8, size9) AS size FROM tiles WHERE size1 LIKE '$queryString%' OR size2 LIKE '$queryString%' OR size3 LIKE '$queryString%' OR size4 LIKE '$queryString%' OR size5 LIKE '$queryString%' OR size6 LIKE '$queryString%' OR size7 LIKE '$queryString%' OR size8 LIKE '$queryString%' OR size9 LIKE '$queryString%' LIMIT 10

This is working and I'm getting distinct values for each column, but as a collective, I'm still getting repeated values. I need it so that I get if I had a size 100x100 in size1, size2 and size3, it would only display once in the autosuggest list.
0
 
Mark GilbertSenior Performance EngineerCommented:
This is a long shot but here goes, once you've selected the records from the database, iterate through the array, creating a single level array with each of the values and then power the auto-suggest list will only show distinct items from the entire array of what's in the database. An example of what your array might look like with 4 fields:

If my database structure was id, fielda, fieldb, fieldc, fieldd, with values as:

                  3      a      a      b      a
                  1      a      b      c      d
                  4      a      c      a      b
                  5      d      e      b      a

The array should look like:

$dbresults = array(a,a,b,a,a,b,c,d,a,c,a,b,d,e,b,a);

And then running the array_unique function on that:

$listvars = array_unique(sort($dbresults)) should give you an array containing:

a,b,c,d,e (sorted results, as the original array may have contained a strange order.

I'm still trying to figure out the correct sql syntax that you could use that will work to get a unique array from the database source as it's always best to let the data horse do the work.
0
 
Mark GilbertSenior Performance EngineerCommented:
With the sql statement, you were both on the right track, however because of the nature of your data I believe as it stands it's doubtful that you would reduce the data in any way other than running it like my original example. It's not ultimately optimum but it will work.

The example I used to determine this was based on the above table structure:

SELECT DISTINCT  `fielda` ,  `fieldb` ,  `fieldc` ,  `fieldd`
FROM  `test`.`test`
WHERE (
 `id` LIKE  '%a%'
OR  `fielda` LIKE  '%a%'
OR  `fieldb` LIKE  '%a%'
OR  `fieldc` LIKE  '%a%'
OR  `fieldd` LIKE  '%a%'
)

which has given me an identical result to the select all (minus the ID's as I didn't request those):

                  a      b      c      d
                  a      a      b      a
                  a      c      a      b
                  d      e      b      a

By selecting distinct like this, we're covering all the fields.

Hope this helps.
0
 
Mark GilbertSenior Performance EngineerCommented:
I've continued to read and other examples say that a group by is better than distinct, and I agree, however not across multiple columns.

The first option by running the result through php is an option to take, and to further on that point, there may also be a better way to process the array:


It's often faster to use a foreache and array_keys than array_unique:
    <?php 

    $max = 1000000; 
    $arr = range(1,$max,3); 
    $arr2 = range(1,$max,2); 
    $arr = array_merge($arr,$arr2); 

    $time = -microtime(true); 
    $res1 = array_unique($arr); 
    $time += microtime(true); 
    echo "deduped to ".count($res1)." in ".$time; 
    // deduped to 666667 in 32.300781965256 

    $time = -microtime(true); 
    $res2 = array(); 
    foreach($arr as $key=>$val) {    
        $res2[$val] = true; 
    } 
    $res2 = array_keys($res2); 
    $time += microtime(true); 
    echo "<br />deduped to ".count($res2)." in ".$time; 
    // deduped to 666667 in 0.84372591972351 

    ?>

Open in new window

http://au2.php.net/manual/en/function.array-unique.php
0
 
djfenomAuthor Commented:
Thanks ingwa, this is a little over my head, this was my original query:

select DISTINCT(size1, size2, size3, size4, size5, size6, size7, size8, size9) AS size FROM tiles WHERE size1 LIKE '$queryString%' OR size2 LIKE '$queryString%' OR size3 LIKE '$queryString%' OR size4 LIKE '$queryString%' OR size5 LIKE '$queryString%' OR size6 LIKE '$queryString%' OR size7 LIKE '$queryString%' OR size8 LIKE '$queryString%' OR size9 LIKE '$queryString%' LIMIT 10

But I changed it like yours to:

SELECT DISTINCT  `size1` ,  `size2` ,  `size3` ,  `size4`
FROM  `tiles`
WHERE (`size1` LIKE  '%a%'
OR  `size2` LIKE  '%a%'
OR  `size3` LIKE  '%a%'
OR  `size4` LIKE  '%a%'
)

But now I'm getting nothing?
0
 
Ray PaseurCommented:
"...there is no option to change to one column" - that is factually inaccurate.  You can use ALTER TABLE to add new columns to existing tables.  I do it all the time when I find that an original table design was not suitable for the application's SELECT needs.

ORDER BY and GROUP BY will probably be more useful than SELECT DISTINCT (and will probably perform better, although with only 2000 rows performance won't matter much).  Have you tried using EXPLAIN SELECT to see what the SQL engine is doing with your query?  
0
 
Mark GilbertSenior Performance EngineerCommented:
I am pleased I was able to assist you with your question and thank you for the points.

Lookin at your code cincerns me a little as far as code injection is concerned. You are directly calling a URL parameter as a value into the SQL. I recommend first declaring a php variable ($varname) then assign the get to the value and then sanitize the values. Otherwise someone could clear your database maliciously.

Hope this helps.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now