Solved

Complex MySQL query advice needed

Posted on 2011-02-11
6
839 Views
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:
(table)
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
SELECT DISTINCT (
`wt_ft`
) 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
WHERE (
grade_sort = "34120"
)
OR (
grade_sort = "34090"
)
ORDER BY wt_ft

Open in new window

0
Comment
Question by:jabberwok_e
  • 3
  • 3
6 Comments
 
LVL 8

Expert Comment

by:ropenner
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
<?PHP
$filename=basename($_SERVER['PHP_SELF']);

#open
$HOST='***';$USER='***';$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++;      
}//var_dump($analysis_desc);*/

// 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);
            unset($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>";
?>
0
 
LVL 8

Accepted Solution

by:
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(final.gold), 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
0
 

Author Comment

by:jabberwok_e
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 8

Assisted Solution

by:ropenner
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(final.gold), 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"
0
 

Author Comment

by:jabberwok_e
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.
0
 

Author Closing Comment

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

Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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…
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.

762 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

23 Experts available now in Live!

Get 1:1 Help Now