[RESOLVED] Select Query Problem - Multiple Tables

obrienkev

Hi all,

I have 4 database tables. I want to perform a query to loop through each table and display the results under the relevand heading as below.

At the moment only results from mergers table appears.

Why is this?

// Perform Query
$query = "SELECT mergers.id, mergers.name,
                                   rights.id, rights.name,
                                 spin_offs.id, spin_offs.name,
                                 splits.id, splits.name
                    FROM mergers, rights, spin_offs, splits";

echo $query.'<br /></br >';

$result = mysql_query($query) or die (mysql_error());

echo $result;


while(list($id, $name) = mysql_fetch_array($result))
    {
?>
        <p>Mergers Table:<br />
    <a href="corporate_actions.php?id=<?=$id;?>"><?=$name;?></a> <br />

    <p>Rights Table:<br />
<a href="corporate_actions.php?id=<?=$id;?>"><?=$name;?></a> <br />

    <p>Spin-Offs Table:<br />
<a href="corporate_actions.php?id=<?=$id;?>"><?=$name;?></a> <br />
<?       

    }
?>

Thanks.

devinemke

are these tables related in any way? if so how?

obrienkev

No they're not related. Just want to display the data from each table under relevant headings.

Tried using this...

$tables = array(
            "splits" => "splits",
            "mergers" => "mergers",
            "spin_offs" => "spin_offs",
            "rights" => "rights");

$table = $tables[$key];

// Perform Query
$query = "SELECT id, name FROM $table";

But it gives an SQL syntax error - doesn't read $table.

Any ideas?

toplay

Read up on what list() does. You are specifying only two variables in the list() while your SQL query has 8. Sine you're only got two variables listed, PHP puts mergers.id value into the $id variable, and mergers.name into the $name variable while the rest are ignored.

list() manual page:
http://us2.php.net/manual/en/function.list.php

Since all the tables have an id and name columns, then maybe you can use this query instead (and leave the list() the way it is):

$query =
"SELECT id, name FROM mergers
 UNION
 SELECT id, name FROM rights
 UNION
 SELECT id, name FROM spin_offs
 UNION
 SELECT id, name FROM splits";

The UNION will automatically exclude any duplicate rows from the data returned by the SELECT. If you want it to return all rows, including duplicates, use the UNION ALL clause instead of the just the UNION keyword.

hth

devinemke

$tables = array(
'splits',
'mergers',
'spin_offs',
'rights'
); 

foreach ($tables as $value)
{
	$result = mysql_query('SELECT id, name FROM ' . $value) or exit(mysql_error());
	echo '<b>' . $value . '</b><br>';
	while ($row = mysql_fetch_assoc($result))
	{
		echo '<a href="corporate_actions.php?id=' . $row['id'] . '">' . $row['name'] . '</a><br>';
	}
	echo '<br>';
}

obrienkev

Cheers Devin!!