Jump to content

using array to store values from db


svgmx5

Recommended Posts

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.

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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'];
}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.