SQL report help

8 posts by 3 authors in: Forums > CMS Builder
Last Post: February 15, 2012   (RSS)

By gversion - February 13, 2012

Hi,

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

By gkornbluth - February 13, 2012

Hi Greg,

Try the pseudofield designation name:label where you are displaying the field values IE:

<?php echo $record['name:label'] ?>

That might help,

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







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

By gversion - February 13, 2012

Hi,

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

Hi Greg,

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

By gversion - February 14, 2012

Hi Jason,

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

Hi Greg,

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 condition

to 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: [gversion] SQL report help

By Jason - February 15, 2012

Hi Greg,

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/