SQL report help
8 posts by 3 authors in: Forums > CMS Builder
Last Post: February 15, 2012 (RSS)
I have written the following SQL command to pull off the listings of a specific user:
SELECT `num`,`createdDate`,`category`,`product`,`model`,`manufacturer`,`manufacturer_url`,`sale_price`,`warranty`,`age`,`condition`
FROM cms_listings
WHERE createdByUserNum = 155;This works but the "category" is displayed as a number, rather than the name of the category.
The names of my categories are stored in another table called "cms_categories" and the field is called "name".
Could someone please help me join this data together?
Thank you so much.
Regards,
Greg
Re: [gversion] SQL report help
Try the pseudofield designation name:label where you are displaying the field values IE:
<?php echo $record['name:label'] ?>
That might help,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] SQL report help
Sorry I didn't explain this but I am using the MySQL Console plug-in, so I don't think I can use your PHP suggestion.
Any other ideas?
Thanks,
Greg
Re: [gversion] SQL report help
By Jason - February 14, 2012 - edited: February 15, 2012
What you can do is pull in information form two different tables using table aliases like this:
SELECT `listings.num` as num,`listings.createdDate` as createdDate,`category.name` as category,
`listings.product` as product,`listings.model` as model,`listings.manufacturer` as manufacturer,
`listings.manufacturer_url` as manufacturer_url,`listings.sale_price` as sale_price,
`listings.warranty` as warranty,`listings.age` as age,`listings.condition` as condition
FROM cms_listings listings, cms_categories category
WHERE listings.createdByUserNum = 155 AND listings.category = category.num;Copy and paste this entire query into the console to test it out.
Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] SQL report help
Thanks for your help. I've inserted the code you sent and just corrected the spelling "listings" where there was a double "t". Unfortunately I get the following error:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition FROM cms_listings listings, cms_categories category WHERE li' at line 4
Does that make sense to you? :)
I'd be delighted if you could help me crack this!
Thanks again,
Greg
Re: [gversion] SQL report help
By Jason - February 15, 2012
It turns out that the word "condition" is a reserved word in mysql. We can get around this by adding ` around the word.
Change this:
`listings.condition` as conditionto this:
`listings.condition` as `condition`Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] SQL report help
Thanks for figuring that out! I never would have done.
The code is now running correct but there are no results being displayed, which is strange because there should be about 50 listings from ID 155..
Regards,
Greg
Re: [gversion] SQL report help
By Jason - February 15, 2012
If you could fill out a [url http://www.interactivetools.com/support]2nd Level Support Request[/url] I can take a quick look and see what I can find.
Thanks,
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/