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]
-~----------~----~----~----~------~----~------~--~---
