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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Kevin CrossChief 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.