Solved

Combine distinct columns in mysql database

Posted on 2011-02-23
18
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
18 Comments
 
LVL 143

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 110

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 110

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

733 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