Solved

Combine distinct columns in mysql database

Posted on 2011-02-23
18
298 Views
Last Modified: 2013-12-13
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
Comment
Question by:djfenom
  • 5
  • 4
  • 3
  • +3
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34960095
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
 

Author Comment

by:djfenom
ID: 34960143
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34960189
Yes, you can use ...... as size, execute query and then use
$row['size']
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Expert Comment

by:DoCBReeD
ID: 34960195
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
 

Author Comment

by:djfenom
ID: 34960229
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34960323
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34960331
... with sizes up to 9
0
 

Author Comment

by:djfenom
ID: 34960382
I get an SQL error with that Roads_Roads?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35001809
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
 

Author Comment

by:djfenom
ID: 35005381
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
 
LVL 18

Expert Comment

by:Mark Gilbert
ID: 35005594
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
 
LVL 18

Expert Comment

by:Mark Gilbert
ID: 35005682
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
 
LVL 18

Accepted Solution

by:
Mark Gilbert earned 500 total points
ID: 35005920
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
 

Author Comment

by:djfenom
ID: 35006344
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35009363
"...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
 
LVL 18

Expert Comment

by:Mark Gilbert
ID: 35058282
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question