Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Complex MySQL query advice needed

Posted on 2011-02-11
6
Medium Priority
?
858 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 1500 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 8

Assisted Solution

by:ropenner
ropenner earned 1500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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