Complex MySQL query advice needed

Posted on 2011-02-11
Last Modified: 2013-12-13
I have a non-normalized table that I need to extract data from. I am using PHP and MySQL and am adept at displaying the records once I submit the query

A simplified example is this:
size_sort      wt_ft             bill_lgth         analysis_desc
9/32"             0.2113          12'                 silver
9/32"             0.2113          12'                 gold
11/32"           0.3156          12'                 silver
11/32"           0.3156          20'                 gold
11/32"           0.3156          12'                 copper

(Desired results)
Size          Weight          silver      gold        copper
9/32"         0.2113          12'          12'          -
11/32"       0.3156          12'          20'         12'

(my results now)
Size          Weight       silver     gold     copper
9/32"         0.2113       12'         -           -
9/32"         0.2113        -           12'        -
11/32"       0.3156       12'         -           -
11/32"       0.3156        -           20'        -
11/32"       0.3156        -           -           12'

So basically I need a query that will select distinct wt_ft and add size_sort along with the distinct analysis_desc's as result fields populated with the bill_length.
This is one of 100s of "sub-tables" I need to extract from this one main table. I thought of creating views "on the fly" using php and am trying to create a query that gives me the results (see code) but I get an error (#1242 - Subquery returns more than 1 row). Is there a better way to acheive the results?

Unfortunately there is no relationship between grade_sort and analysis_desc
) AS wt_ft, size_sort, (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "gold"
) AS `gold` , (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "silver"
) AS `silver` , (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "copper"
) AS `copper` 

FROM mf_stock
grade_sort = "34120"
OR (
grade_sort = "34090"
ORDER BY wt_ft

Open in new window

Question by:jabberwok_e
  • 3
  • 3

Expert Comment

ID: 34875380
the code below does the job in PHP.  It can be further generalized so that it can do the 100 other queries you have to do.  Haven't thought of an SQL way of doing it yet.

replace *** with your host user and pass

$database_link1 = mysql_connect($HOST, $USER, $PASS) OR die ("$filename: Connecting problem: ".mysql_errno()." .". mysql_error() .". tis all.");
mysql_select_db("test", $database_link1);

// wrapper function for queries so that query format and error checking happen in one place
function transaction($query="", $database_link) {
      global $filename;
      if (strlen(trim($query)) == 0) {
            return false;
      $result = mysql_query($query, $database_link) OR die ("$filename: Query problem: ". mysql_error() ." [$query].");
      return $result;

function showRow($size, $wt, $temp_array) {
      print "<TR><TD>$size</TD>\n<TD>$wt</TD>\n";
      for ($i=0; $i< count($temp_array); $i++) {
            print "<TD>".$temp_array[$i] . "</TD>\n";
      print "</TR>\n\n";

// create place for unique analysis_desc  (instead of hardcoding 'gold' 'silver' 'copper' we could discover this for ourselves)
$analysis_desc = array("silver" => 0,"gold" => 1, "copper" => 2);//var_dump($analysis_desc);
// alternatively you could write the code below to discover the unique values
/*$analysis_desc = array();
$query = "SELECT DISTINCT analysis_desc FROM ecoin_stock";
$statement = transaction($query, $database_link1);
$column = 0;
while ($value = mysql_fetch_row($statement)) {
      $analysis_desc{$value[0]} = $column++;      

// table header
print "<TABLE border=1><TR><TD>size_sort</TD><TD>wt_ft</TD>";
foreach ($analysis_desc as $key=>$value) {
      echo "<TD>".$key ."</TD>";
print "</TR>\n";

// now we have the extra columns needed for each row of our table of distinct information
// ie:   9/32"            0.2113            [gold            silver            copper]
$temp_array = array();
$query = "SELECT * FROM ecoin_stock ORDER BY size_sort, wt_ft";
$statement = transaction($query, $database_link1);
$prev_size = $prev_wt_ft = "";
while ($value = mysql_fetch_assoc($statement)) {
      if (!(strcmp($prev_size,$value{'size_sort'})==0 && strcmp($prev_wt_ft, $value{'wt_ft'})==0) && strlen($prev_size) != 0) { // not same record  then print and reset temp_array
            showRow($prev_size, $prev_wt_ft, $temp_array);
      $temp_array[$analysis_desc{$value{'analysis_desc'}}] = $value{'bill_lgth'};
      $prev_size = $value{'size_sort'};      $prev_wt_ft = $value{'wt_ft'};
showRow($prev_size, $prev_wt_ft, $temp_array);

print "</TABLE>";

Accepted Solution

ropenner earned 500 total points
ID: 34876336
this does it.  It isn't pretty and it has only been tested on your sample.

select DISTINCT final.size_sort, final.wt_ft, group_concat(, group_concat(final.silver), group_concat(final.copper) from (
select t1.size_sort, t1.wt_ft, t2.bill_lgth as gold, t3.bill_lgth as silver, t4.bill_lgth as copper FROM ecoin_stock  t1
LEFT JOIN ecoin_stock t2 ON t1.size_sort=t2.size_sort AND t1.wt_ft=t2.wt_ft AND t1.analysis_desc=t2.analysis_desc and t1.analysis_desc="gold"
LEFT JOIN ecoin_stock t3 ON t1.size_sort=t3.size_sort AND t1.wt_ft=t3.wt_ft AND t1.analysis_desc=t3.analysis_desc and t1.analysis_desc="silver"
LEFT JOIN ecoin_stock t4 ON t1.size_sort=t4.size_sort AND t1.wt_ft=t4.wt_ft AND t1.analysis_desc=t4.analysis_desc and t1.analysis_desc="copper") as final
group by final.size_sort, final.wt_ft

Author Comment

ID: 34898387
Thank you for the information. This query locks up the server, but is giving me some ideas on how I might be able to get it done by using views. I will post the results of my attempts.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Assisted Solution

ropenner earned 500 total points
ID: 34899951
if you do it in stages with views, I was going to add that the inner query gives the same result you have and the outer one shrinks it to one line per.

outer ==

select DISTINCT final.size_sort, final.wt_ft, group_concat(, group_concat(final.silver), group_concat(final.copper) from (

) as final
group by final.size_sort, final.wt_ft

So if you already have the view you call "my results now" and put those in a table... then the insides of this query would be "SELECT * FROM my_view"

Author Comment

ID: 34942702
I've been tied up with other projects, but I think you helped put me on the right track.

I'm going to close this.

Author Closing Comment

ID: 34942723
The proposed solution pointed me in the right direction of using views or temporary tables to joint the data with itself.


Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Echo Multiple values from multiple records 19 40
Wordpress Query 5 25
Ajax and PHP 4 30
$_SERVER Variable question 31 27
These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

856 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