using array to store values from db

[svgmx5]

i'm trying to get all the results from a query search into an array and them have another query search the results from that array againts a different table to get the right results.

 

 

Here is my code:

 

$name = $_GET['name']; //lets say $name = 'california'

$get_state = mysql_query("SELECT * FROM locations WHERE state LIKE '$name'") or die(mysql_error());
$num_states = mysql_num_rows($get_state);

$location_array = array();
$i = 0;
while($state = mysql_fetch_assoc($get_state)){
	$location_array[$i][] = $state['id']; 
	$i++;							
}

      $get_buildings = mysql_query("SELECT * FROM points WHERE location_id='".implode(",", $location_array)."' ORDER BY name ASC") or die(mysql_error());

 

 

However i can't get any results. I tried outputing the array by imploding it and all i got was "array, array, array, array,"

 

I hope someone here can help me out.

 

[svgmx5]

Also the value i'm trying to get is just the ID based on how many values have that name.

[bh]

while($state = mysql_fetch_assoc($get_state))
{
      $location_array[] = $state['id']; 
      $i++;							
}

$get_buildings = mysql_query("SELECT * FROM points WHERE location_id IN (".implode(",", $location_array).") ORDER BY name ASC") or die(mysql_error());

 

Hows that?

 

Update:

Anyway its much better than you do your stuff in one query (with JOIN)

[samoht]

Well, you should only SELECT the value that you need to use instead of SELECT *

Also, I wonder if you could accomplish what you want from a JOIN and do this in one query.

 

SELECT loc.id  FROM Locations AS loc INNER JOIN points as p ON ( fk IN ('yourarray') ) WHERE loc.state LIKE '$name'

 

if you give us the table structure and a sample out put of each of the tables - we could help better.

[svgmx5]

Welll BH script worked, i guess all i need to do was to remove teh [$i] from the there....

 

 

The table script goes as follow....

 

locations table :

id

name

state

country

 

Now the points table where all the info regarding each actual result goes as follow:

id

location_id

name

 

 

The id in the locations table and location_id in the points table are the unique ID's that connect the two tables.

 

[samoht]

Then you can do this with one query

 

try...

<?php
$name = $_GET['name']; //lets say $name = 'california'

$get_state_loc = mysql_query("SELECT loc.id AS lid, loc.name AS lname, COUNT(*) AS cnt FROM locations AS loc INNER JOIN points AS p ON (loc.id = p.location_id) WHERE loc.state LIKE '$name' GROUP BY loc.id") or die (mysqul_error());

while($row = myql_fetch_assoc($get_state)){

	echo $row['lid'] . '|' . $row['lname'] . '|'. $row['cnt'];
}

[svgmx5]

yup that works also.

 

Thanks for you guys help!