Modifying MySQL Column Text Output For Reports

If you're doing quick queries to build Excel reports for others you may need to add in extra text around particular data fields/columns.


I find myself needing to generate reports on Drupal and WordPress sites almost every other day now so this quick string technique will probably save you time as well.

SELECT CONCAT('before', n.nid, 'after') AS data FROM node n

If you’re familiar with Drupal you’ll notice the node table call.

A real world example with this might be to generate JavaScript code for a signature in which users could display on their own external site or forums. The query demonstrated below represents the Drupal database structure.

SELECT CONCAT('<script type="text/javascript" src="http://www.domain.com/signature.php?userid=',u.uid,'"></script>') AS sig FROM users u ORDER BY u.uid ASC

For the sake of it, here is a WordPress version:

SELECT CONCAT('<script type="text/javascript" src="http://www.domain.com/signature.php?userid=',u.ID,'"></script>') AS sig FROM wp_users u ORDER BY u.uid ASC

You can read up on the MySQL CONCAT function in the official MySQL.com manual.

Now you can export your query results into an excel CSV file without any additional alterations. Straight away its ready to be imported into outlook, mailchimp/aweber/constant contact, or any other service that takes a CSV format in order to automate the import of your data.

In case you’re curious about the above example, the signature.php script would pass on the userid to the script in which you could use to validate the ID and pull additional fields (Add some JOIN’s to the query above) the user might have filled out in the profile to create a final signature. Of course you’d need to output everything with document.write.

Respond: Leave A Comment | Trackback URL

Entrupeners, Subscribe for the lastest tools, tips, and tutorials.

Leave a Reply

Custom Theme by Rob Malon | Content & Design © 2010 - RobMalon.Com - Chicago, Illinois