Link to home
Start Free TrialLog in
Avatar of jsteidl
jsteidlFlag for United States of America

asked on

Need SQL Query Help: Showing data properly

Hello,

I have the following query built but need the data shown to be converted to column headers:

SELECT
  ss_list_subscribers.subscriberid,
  ss_subscribers_data.subscriberid,
  ss_subscribers_data.fieldid,
  ss_customfields.fieldid,
  ss_customfields.name,
  ss_subscribers_data.`data`
FROM
  ss_list_subscribers
  INNER JOIN ss_subscribers_data ON (ss_list_subscribers.subscriberid = ss_subscribers_data.subscriberid)
  INNER JOIN ss_customfields ON (ss_subscribers_data.fieldid = ss_customfields.fieldid)
WHERE
  ss_list_subscribers.subscriberid = 59525

In the result, I get back what I should, but need to have the values of ss_customfields.name be the column headers with the appropriate ss_subscribers_data.`data` values. How do I get what I need so I have a normalised table with the persons data, etc???

I am using this in a PHP script to show the data and will need to be able to export out said data as well.

Any help much appreciated!


query.png
Avatar of jsteidl
jsteidl
Flag of United States of America image

ASKER

Anyone know how to do what I need?
Avatar of Steve Bink
You have fallen into the easy trap of trying to use a database for reporting.  A database returns data, not reports.  You have to build the report.  It sounds like you have an idea of what you want it to look like.  Can you post a pic or HTML of the structure you would like to see?  In the meantime, here is some basic code that might point you in the right direction:
<?
//assumes $link is your database connection
$query = "SELECT...and so on...";
if (!($result=mysql_query($query,$link))) {
  // query failed.  report and ...
  die();
}
$data=array();
while ($row=mysql_fetch_assoc($result)) {
  $data[$row['name']]=$row['data'];
}
// now $data is an array(name=>data) of everything that came back
foreach ($data as $key=>$val) {
  echo "$key: $val<br />\n";
}
?>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America 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 jsteidl

ASKER

After reading the article from Kevin(mwvisa1), and numerous others I found, I was able to come up with a solution to my issue. By running my first query above and placing all data in a table, I can run the following sql against said table for my export:

SELECT a,
       MAX(IF(e='First Name',f,NULL)) AS First_Name,
       MAX(IF(e='Last Name',f,NULL)) AS Last_Name,
       MAX(IF(e='Industry',f,NULL)) AS Industry,
       MAX(IF(e='Company',f,NULL)) AS Company
       FROM jake_stats
       GROUP BY a;

Thanks for the guidance!

Jake
You are most welcome.
Best regards and happy coding,
Kevin