Create an array using MySQL data?

[lpeek]

Hey!

Im setting up a 'Friends and Family' list type thing, and i know there will be certain people with the same surname...

What i want to do is get data from a mysql database and sort it by surname, the only thing is, that the table im getting the data from only contains the users id, and another table contains their surname.

What would be a good way to basically:

get the data from the first table and place each $result['id'] into a new array, lets just say $array then i would use that array when selecting from the next table to get the surnames...

I know 'how' to do it in theory, but im not sure on the coding to use.

something like this:


$result = mysql_query("SELECT * FROM table WHERE category=3");
$result_array = mysql_fetch_array($result);

// then here get the $result_array['id'] of every result and place it in a new variable called $array

$newresult = mysql_query("SELECT * FROM users WHERE id=$array ORDER BY surname ASC")



i think you get the idea...

any thoughts?

Many thanks.

[whizard]

$result = mysql_query("SELECT * FROM table WHERE category=3");

while($row = mysql_fetch_array($result))
{
$array[] = $row[NAME OF FIELD THAT HOLDS THE ID]
}

foreach($array as $id)
{
$newresult = mysql_query("SELECT * FROM users WHERE id=$id ORDER BY surname ASC")
}


And technically, if your only using the id field from the first table, you only need to select that field in your first query, not *.

HTH
Dan

[derzok]

Not sure if this is what you need, but are you looking for a join statement?

"SELECT * from table INNER JOIN users ON table.id=users.id WHERE table.category=3 ORDER BY users.surname ASC"

[lpeek]

Not sure if this is what you need, but are you looking for a join statement?

"SELECT * from table INNER JOIN users ON table.id=users.id WHERE table.category=3 ORDER BY users.surname ASC"

Inner Join seems to be what im looking for! Cheers! will give it a go now!

[lpeek]

$result = mysql_query("SELECT * FROM table WHERE category=3");

while($row = mysql_fetch_array($result))
{
$array[] = $row[NAME OF FIELD THAT HOLDS THE ID]
}

foreach($array as $id)
{
$newresult = mysql_query("SELECT * FROM users WHERE id=$id ORDER BY surname ASC")
}




Cheers Dan, although if thats a foreach wont that still just get the data without ordering it? because for each id in the array it will perform the query and it'll just return one restult... but it will do this multiple times...

or am i thinking wrong here?

[lpeek]

derzok, your INNER JOIN worked perfectly! Thanks a bunch!!

[whizard]

oh... you're right. I forgot about order.. lol. my bad. glad u got the correct solution.

I didn't even think of doing it in a query, but that is a much better way.

Dan

[derzok]

Awesome! Glad I could help :)