jsteidl
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.subscr iberid,
ss_subscribers_data.subscr iberid,
ss_subscribers_data.fieldi d,
ss_customfields.fieldid,
ss_customfields.name,
ss_subscribers_data.`data`
FROM
ss_list_subscribers
INNER JOIN ss_subscribers_data ON (ss_list_subscribers.subsc riberid = ss_subscribers_data.subscr iberid)
INNER JOIN ss_customfields ON (ss_subscribers_data.field id = ss_customfields.fieldid)
WHERE
ss_list_subscribers.subscr iberid = 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
I have the following query built but need the data shown to be converted to column headers:
SELECT
ss_list_subscribers.subscr
ss_subscribers_data.subscr
ss_subscribers_data.fieldi
ss_customfields.fieldid,
ss_customfields.name,
ss_subscribers_data.`data`
FROM
ss_list_subscribers
INNER JOIN ss_subscribers_data ON (ss_list_subscribers.subsc
INNER JOIN ss_customfields ON (ss_subscribers_data.field
WHERE
ss_list_subscribers.subscr
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`
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
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";
}
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
MAX(IF(e='Company',f,NULL)
FROM jake_stats
GROUP BY a;
Thanks for the guidance!
Jake
You are most welcome.
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
ASKER