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

By gversion - February 15, 2012

Hi Jason,

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

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/