A better way to do this

Square1

Hi all:

I need to find the result of a query and then based on that result, get the results of a second query. It seems like there may be a more efficient way to do this than running to separate queries. if anyone can help it would be appreciated:

		$query = "
			SELECT
			listingRequired
			FROM
			cartItems
			WHERE
			listingRequired = 'Y' AND
			sessionID = '".session_id()."'
			";
		$result = mysqli_query($link, $query);
		$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
			if (mysqli_num_rows($result)>0) {
    			$queryRequired = "
				SELECT
				required
				FROM
				cartItems INNER JOIN
				cartProducts ON
				cartItems.pdID = cartProducts.pdID
				WHERE
				required = 'Y' AND
				sessionID = '".session_id()."'
				";
				$resultRequired = mysqli_query($link, $queryRequired);
				$rowRequired = mysqli_fetch_array($resultRequired, MYSQLI_ASSOC);

			if (mysqli_num_rows($resultRequired)===0) {
				echo "THIS WORKS";
			}
			mysqli_free_result($resultRequired);
		}
	mysqli_free_result($result);

awbrowni30

try the following....

$query = "
            SELECT
            ci.listingRequired, ci.required
            FROM
            cartItems as ci INNER JOIN cartProducts as cp on ci.pdID = cp.pdID
            WHERE
            ci.required = 'Y' AND
            ci.listingRequired = 'Y' AND
            ci.sessionID = '".session_id()."'
            ";

            $resultRequired = mysqli_query($link, $query);
            $rowRequired = mysqli_fetch_array($resultRequired, MYSQLI_ASSOC);

            if (mysqli_num_rows($resultRequired)===0) {
                echo "THIS WORKS";
            }
            mysqli_free_result($resultRequired);

    mysqli_free_result($result);

Square1

Thank you.

Here's "the rub" with this. If a listing is required (listingRequired = Y) and a required listing is in the records (required = Y) then if row count != 0 this would be fine. But what if a listing IS NOT required (listingRequired = N). Now row count == 0 causing a flag to come up, when actually if listingRequired = N then no flag should come up for a required listing.

But you got me thinking. Maybe the WHERE should be more like


WHERE
(ci.required = 'Y' AND
ci.listingRequired = 'Y') OR
ci.listingRequired = 'N'

johanafm

Square1;10987940 wrote:
If a listing is required (listingRequired = Y) and a required listing is in the records (required = Y)

The way you have formulated this sounds non-sensical to me, but if I guess correctly, what you mean is that listingRequired='Y' means there should be something in cartProducts for that same item. If so, you want something like

SELECT whatever, fields
FROM cartItems as ci
LEFT JOIN cartProducts as cp on ci.pdID = cp.pdID
WHERE
(ci.listingRequired = 'Y' AND cp.pdID IS NOT NULL) OR ci.listingRequired = 'N')