Hi Michael,
You'd just need to add that into the where clause. You might also
consider aliasing your tables, easier to read imo.
I hope you're escaping the data you're passing into the query (
http://nz.php.net/manual/en/function.mysql-real-escape-string.php ).
###
SELECT
pr.productid,
pr.title,
pr.added,
pi.w AS `width`,
pi.h AS `height`,
pi.m AS `mime`,
IF( ISNULL( pi.pic ) || pi.pic = '' , 0 , 1 ) AS `pcx`
FROM
`product` pr
LEFT JOIN `picture` pi ON pr.productid=pi.productid
WHERE
pr.productid = '$productid'
AND pr.categoryid='$categoryid'
AND pr.`categoryid` IN $catlist
AND `display`='1'
ORDER BY
pr.`title` ASC
LIMIT $start,$products
###
Michael wrote:
> On Sat, 03 Oct 2009 10:39:48 Rob Jensen wrote:
>
>> Hi Michael,
>>
>> If you're just trying to establish if you have a picture or not try this:
>>
>> ###
>> SELECT
>> product.productid,product.title,product.added,picture.w AS `width`,
>> picture.h AS `height`,picture.m AS `mime`,
>> IF( ISNULL( picture.pic ) || picture.pic = '' , 0 , 1 ) AS `pcx`
>> FROM
>> `product`
>> LEFT JOIN `picture` ON product.productid=picture.productid
>> WHERE
>> categoryid='$categoryid'
>> AND `categoryid` IN $catlist
>> AND `display`='1'
>> ORDER BY
>> `title` ASC
>> LIMIT $start,$products
>> ###
>>
>> That should save you a sub query and establish if you have pictures or not.
>>
>
> Thanks -
>
> Though in this query where does it do the equivalent of WHERE
> productid='$productid' on the table 'picture'.
>
> Remember this query is pulling info from 2 tables-
> 'product' - productid, title
> 'picture' - width, height, mime (fields w, h, m)
> and pic (the blob field) only need to know if exists.
>
> >
>
>
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
