Is there a way to query a result set? My query is too slow 3

[fatfishmatt]

Is there a way with PHP to query a result set rather than query the database, similar to the <cfquery dbtype="queryName"> tag in ColdFusion?

I have a database with around 10,000 records, each record having a country, state, city field etc.

The user selects the country from a drop down list. The code then querys all 10,000 records in the database and returns a result set of records in that country giving the option for the user to now select a state.

The user then selects the state and the code querys 10,000 records again and returns records with the matching country and matching state.

What I'd like to do in the second instance is instead of query the full 10,000 records again on Country & State, is query result set of the first query (which could be as little as 15 or 20 rather than 10,000) on just State.

And again in the third instance I'd like to query the second result set (which could be as little as 5 or 6 records) on City rather than the full 10,000 again on Country, State & City.

Thanks for the help in advance...

 

[kenrbnsn]

Can we see the code you're now using?

Here's one way using arrays stored in the $_SESSION super array.

First time get the country array:

<?
session_start();

$q = "select * from your_db_table where country = '$user_selection'";
$rs = @mysql_query($q) or die('something is wrong with ' . $q . '<br>' . mysql_error());
$country_array = array();
while ($rw = mysql_fetch_array($rs)) {
    $country_array[] = $rw;
//
// do any other processing on the row here
//
}
$_SESSION['country_array'] = $country_array;
?>

Second time, get state array:

<?
session_start();

if (isset($_SESSION['country_array']))
   $country_array = $_SESSION['country_array'];
else die('No country_array');
$state_array = array();
for ($i=0;$i<count($country_array);$i++) {
    if ($country_array[$i]['state'] == $user_state_selection)
        $state_array[] = $country_array[$i];
//
// do any other processing on the for this state here
//
}
$_SESSION['state_array'] = $state_array;
?>

Third time, get city array:

<?
session_start();

if (isset($_SESSION['state_array']))
   $state_array = $_SESSION['state_array'];
else die('No state_array');
$city_array = array();
for ($i=0;$i<count($state_array);$i++) {
    if ($state_array[$i]['city'] == $user_city_selection)
        $city_array[] = $state_array[$i];
//
// do any other processing on the for this city here
//
}
$_SESSION['city_array'] = $city_array;
?>

See if this helps.

Please note: I just typed this code in off the top of my head and it hasn't been checked for syntax or logic errors.

Ken

 

[fatfishmatt]

Thanks Ken...

Exactly what I was after....

U DA MAN!!!

 

[sleipnir214]

fatfishmatt:
I'll take your posting of "U DA MAN!!!" to mean that kenrbnsn]b] did a good job. Since in your excitement you forgot to vote him a star, I'll do it for you.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[fatfishmatt]

Just before I do leave however...

Is there a way with kenrbnsn's second piece of code that I can say:

SELECT * FROM $country_array[] WHERE State = $_Get['State']

or

SELECT * FROM $rs WHERE State = $_Get['State']

As you can with the ColdFusion <cfquery dbtype="queryName">?

 

[MattNeeley]

$query = "SELECT * FROM '".$country_array[ID HERE]."' WHERE `State`='".$_GET['State']."'";
$query_results = mysql_query($query);

OR

$country = $country_array[ID HERE];
$state = $_GET['State'];
$query = "SELECT * FROM '$country' WHERE `State`='$state'");
$query_results = mysql_query($query);

Whichever you see as easier

 

[fatfishmatt]

MattNeeley - This is the code I was after initially. The PHP version of its ColdFusion equivalent.

Both kenrbnsn and MattNeeley solutions achieve my desired results.

I've cut my execution time down from 1min 25sec to 4secs....

Amazing what a little bit of efficient code practice can do...

 

[MattNeeley]

No problem fatfishmatt, glad I could help.