Using a query's result in subsequent queries - HTML Forums

cmetz1977 · 10-29-2009, 02:46 PM

I am running a script similar to the following:

PHP Code:


			
$customerquery = mysql_query( "SELECT `customerid` FROM `customers` WHERE ...", $connection );

if( $customerquery && mysql_num_rows( $customerquery ) )

{

    $customeridarray = array( );

    while( $customerresult = mysql_fetch_assoc( $customerquery ) )

        $customeridarray[ ] = $customerresult[ 'customerid' ];

    $customeridstring = implode( "', '", $customeridarray );

}

This will create a string like : 1', '2', '3. So in a future WHERE statement I can say "WHERE `customerid` IN( '$customeridstring' )" and it will translate to "WHERE `customerid` IN( '1', '2', '3' )".

Unfortuneately this string can contain hundreds or thousands of values. Is there a way to that the initial result of the initial query and just use that in the future queries? Such as "WHERE `customerid` IN( $customerquery )"?

Vege · 10-29-2009, 03:13 PM

would it be wrong to do

Quote:

select * from second table where customeric in (select customerid from customers where...)

So not to store the results but to repeat it as a subquery.
Should be faster.

cmetz1977 · 10-29-2009, 03:28 PM

I have considered that, but i use result set about a dozen times. So I would go from the initial query, which I need to do anyways, plus 12 other queries (13 total), to the initial query plus 12 queries each containing a subquery (25 total).

prasanthmj · 10-30-2009, 12:43 AM

The database itself should be able to handle it using query cache

cmetz1977 · 10-30-2009, 10:17 AM

Thank you both for your responses...

I've run into several statements where PHP is painfully faster than MySQL when doing calculations, which is why I was hesitant of that option. Would query cache be more efficient than my $customeridstring?

Horus_Kol · 11-01-2009, 05:28 PM

are these twelve other queries the same? You could just recycle the result from one of them...

alternatively - you could maybe create an SQL function/procedure that does what you need?

perhaps if you showed all of the queries you're running, we might be able to suggest a solution for efficientizing :p your code

cmetz1977 · 11-02-2009, 09:17 AM

These queries are for demographics. Each query is for an individual demographic that considers all the POSTed selections that aren't itself.

For example, if there were only 3 qualities, and the user had already chosen that "gender" could be "m", and "age" could be "25" to "49", I would do the following:

PHP Code:


			
//Primary query

$customeridquery = mysql_query( "SELECT `customerid` FROM `invoices` WHERE `invoicedate` BETWEEN '...' AND '...'" );

//this is the set of 'customerids' to which all future queries are restricted



//3 demographic queries

$agequery = mysql_query( "SELECT `age`, count( `age` ) FROM `customers` WHERE `customerid` IN ( '$customeridstring' ) AND `gender` = 'M' GROUP BY `age`" );

$genderquery = mysql_query( "SELECT `gender`, count( `gender` ) FROM `customers` WHERE `customerid` IN ( '$customeridstring' ) AND `age` BETWEEN '25' AND '40' GROUP BY `gender`" );

$locationquery = mysql_query( "SELECT `location`, count( `location` ) FROM `customers` WHERE `customerid` IN ( '$customeridstring' ) AND `gender` = 'M' AND `age` BETWEEN '25' AND '40' GROUP BY `location`" );

//each query is restricted by everything but itself

//"age" is restricted by the initial $customeridquery and by the selected gender

//this way if the user wants to change their "age" values, they can see how many

//records are available for other existing "age" values within this same set of

//POSTed selections

I have considered what I think you're implying, which is select all the demographics from one big query

PHP Code:


			
SELECT `age`, count( `age` ), `gender`, count( `gender` ), ... 

FROM `invoices` LEFT JOIN `customers` USING ( `customerid` ) 

WHERE `invoicedate` ... AND `age` ... AND `gender` ...

However I could not figure out how to properly group this since the demographics are independant of each other. Also, the result set would be restricted to already selected values. I want the user to see what the selection would look like before they added 'F' to their 'gender' selection without having to add it to see what changed. With this query they would only get a count of 'M'.