I put an AND after the WHERE, pls delete that - otherwise it should
work.
On Nov 4, 6:17 pm, Hamish Campbell <[EMAIL PROTECTED]> wrote:
> K, just join up your two queries. I've replaced the some values so I
> can read it easier:
>
> (1) SELECT productid,title FROM product WHERE categoryid='myCategory'
> AND display='1' ORDER BY title ASC LIMIT $st,$pp
>
> (2) SELECT count(*) AS xyz FROM picture WHERE productid='$productid'
> AND pic !=''"
>
> add them together with a left join so that null values from (2) are
> included:
>
> SELECT FIRST(product.title) as title, FIRST(product.productid) as
> productid, count(picture.*) AS hasPictures FROM product LEFT JOIN
> picture ON product.productid = picture.productid WHERE AND
> picture.pic != ''" AND product.display = '1' AND
> product.categoryid='myCategory' ORDER BY title ASC LIMIT $st, $pp
>
> I haven't tested, but you should be able to take it from there. The
> result should be an array of the form [productid, title, hasPicture],
> where hasPicture will be a number or null. Since PHP will cast to
> false if it is null or 0, and to true otherwise, you can treat it as a
> boolean operator.
>
> On Nov 4, 1:15 pm, Michael <[EMAIL PROTECTED]> wrote:
>
> > > Uh, what's the question (I didn't read the other posts).
>
> > > If you want to know how to get the product id for the second query, it
> > > will just be the value of the result array with the key
> > > 'productid' (assuming you got an associative array from the first
> > > query).
>
> > > ie, "SELECT count(*) FROM picture WHERE productid='".
> > > $row['productid']."' AND (field 'pic' blob size) > 0 LIMIT 1"
>
> > > Obviously, if the result is 1, you've got something, otherwise 0.
>
> > I am currently achieving what i want with the following code-
>
> > $result1 = mysqli_query($link,"SELECT productid,title FROM product WHERE
> > categoryid='".addslashes($_GET[cid])."' AND display='1' ORDER BY title ASC
> > LIMIT $st,$pp") or die ('Query sql1 failed');
>
> > while ($aryG1 = mysqli_fetch_assoc($result1)) {
> > while (list($keyG1,$valG1) = each($aryG1)) {$$keyG1 = $valG1;}
>
> > $res=mysqli_query($link,"SELECT count(*) AS xyz FROM picture WHERE
> > productid='$productid' AND pic !=''") or die ('Query sql1a failed');
> > while ($row3=mysqli_fetch_assoc($res)) {
> > $xyz=$row3["xyz"];};
>
> > What I want to do is have one SQL query and obtain value $xyz in one swoop
> > with the other values (productid and title).
>
> > $productid is the same on both tables.
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
