Use query results to perform multiple new queries

[peterbarone]

Hello I have a question. I'm trying to perform multiple queries based off the results from a query.  Is this possible ?

 

$result = mysql_query("SELECT id FROM sometable");

 

for each id returned

 

$result1 = mysql_query("SELECT * FROM someothertable WHERE id=result from $result query");

 

Any help would be GREAT

 

[BlueSkyIS]

Unless you need to perform 2 queries, you could just use one something like...

 

SELECT * FROM someothertable WHERE id = (SELECT id FROM sometable)

[Psycho]

Unless you need to perform 2 queries, you could just use one something like...

 

SELECT * FROM someothertable WHERE id = (SELECT id FROM sometable)

 

One correction, you would need to use 'IN' instead of an '='

SELECT * FROM someothertable WHERE id IN (SELECT id FROM sometable)

 

Or you could also do a JOIN if you need data from both tables

SELECT *
FROM sometable
JOIN someothertable ON sometable.id = someothertable.id
WHERE sometable.id = $somevalue --optional, if needed

 

Or, if the column names are exactly the same as in your example

SELECT *
FROM sometable
JOIN someothertable USING id
WHERE id = $somevalue --optional, if needed

[peterbarone]

Sorry I guess I was not clear.

 

I need to do one query to get all id's from a table

 

There maybe 3,5, 15, 60  ect...

 

Then I want to take each ID returned from the first query and use it in  NEW query's

 

select id from sometable

returns

id = 3

id =10

id =23

 

select * from someothertable where id = 3

select * from someothertable where id = 10

select * from someothertable where id = 23

 

That is what i'm looking to do.

 

Thanks for the time

 

 

 

[Psycho]

Sorry I guess I was not clear.

 

No, you were clear. We have given you the correct solution. What you are asking to do would require running quries in loops which is very inneficient.

[peterbarone]

my lack of experience is starting to shine. really sorry

 

Let me ask the question a different way.  How can I break results for a query into separate tables ?

 

table1  displays all records with id of 1

table2  displays all records with id of 4

table3  displays all records with id of 55

 

ect...

 

And I mean display tables not MySql tables

 

Sorry if I make you feel like

[Psycho]

You would put the logic in the PHP code - never do queries in loops.

 

Here is an example using some mock code

function createTable($results)
{
    $output = "<table>\n";
    foreach()
    {
        $output .= "<tr>\n";
        $output .= "<td>{$record['name']}</td>\n";
        $output .= "<td>{$record['birthdate']}</td>\n";
        $output .= "<td>{$record['phone']}</td>\n";
        $output .= "</tr>\n";
    }
    $output .= "</table>\n";
    return $output;
}

$query = "SELECT id, name, birthdate, phone FROM table ORDER BY id";
$result = mysql_query($query);

$current_id = false;
$recordsByID = array();
while($record = mysql_fetch_assoc($result))
{
    if($current_id != $record['id'])
    {
        $current_id = $record['id'];
        if(count($recordsByID)>0)
        {
            echo createTable($recordsByID);
            $recordsByID = array();
        }
    }
    $recordsByID[] = $record;
}
echo createTable($recordsByID);