Solved

Combine distinct columns in mysql database

Posted on 2011-02-23
18
292 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]
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, you can use ...... as size, execute query and then use
$row['size']
0
 
LVL 1

Expert Comment

by:DoCBReeD
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
... with sizes up to 9
0
 

Author Comment

by:djfenom
Comment Utility
I get an SQL error with that Roads_Roads?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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:ingwa
Comment Utility
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:ingwa
Comment Utility
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:
ingwa earned 500 total points
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
"...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:ingwa
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now