Solved

Complex MySQL query advice needed

Posted on 2011-02-11
6
850 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
[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
  • 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
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.

 
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

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

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.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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 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…

739 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