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
jsteidlAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
What you are looking for is a cross-tab or pivot query within MySQL.  As stated, you may be best served doing this on application/presentation layer side of your application or through a reporting tool like Crystal for example.
But here is an article on dev.mysql that discusses what you are looking for:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
Regards,
Kevin
0
 
jsteidlAuthor Commented:
Anyone know how to do what I need?
0
 
Steve BinkCommented:
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

0
 
jsteidlAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome.
Best regards and happy coding,
Kevin
0
All Courses

From novice to tech pro — start learning today.