Link to home
Start Free TrialLog in
Avatar of jabberwok_e
jabberwok_e

asked on

Complex MySQL query advice needed

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

Avatar of ropenner
ropenner
Flag of Canada image

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>";
?>
ASKER CERTIFIED SOLUTION
Avatar of ropenner
ropenner
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jabberwok_e
jabberwok_e

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
The proposed solution pointed me in the right direction of using views or temporary tables to joint the data with itself.

Thanks!