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: [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/