Querying multiple tables

zeron

I am writing a system from tracking systems. I have 1 table called parts which contains id, type and description.

I have another table systems that contains name, front_module, back_module, builddate. front_module and back_module are numbers that correspond to the id in the parts table.

I want to be able to list all systems, including the parts. I tried doing "select * from systems", then durin the while{} loop for that I had "select 'description' from 'parts' where 'id' = $system['id']", but when I did that, only the first one showed, and all tehj data after that didn't appear. It looks like that starting the second query ended the first one.

How would I be able to do this?

   -Zeron

zeron

Forgot to mention that I am using MySQL and PHP 4.2

bingo333

Perhaps you should use a different query and result identifier for that...

$result1 = mysql_query($query1)
$resultpointer1 = mysql_fetch_array($result1)
do
{
//bla...
$result2 = mysql_query($query2)
$resultpointer2 = mysql_fetch_array($result2)
//bla...
} while

If you had used the same $result1 or the same $resultpointer1 inside the while loop, it SHOULD be overwritten!

Hope it helps.

zeron

I did use different identifiers, but it didn;t work.

Vigil

Wouldn't you want to use "select 'description' from 'parts' where 'id' = $system['front_module']"?

Also, if you only have two parts per system and you want to do it all in one query you can try using joins and table aliases. Try the following query:

SELECT
systems.name,
front.description AS front_description,
back.description AS back_description,
systems.builddate
FROM systems LEFT JOIN parts front ON systems.front_module = front.id LEFT JOIN parts back ON back_module = back.id;

This will give you the front module description as front_description and the back module description as back_description in the systems query. You can get whatever other fields you want from the parts table as well, just alias them the same way.

This will probably be faster than doing a sub-query for each system to get its parts, but it will only work with a discrete number of relations (in this case, two: front_module and back_module). You'll have to add new JOINS and new field-aliases for every new relation you want for every field you want and it'll get messier from there.

zeron

I can't get that to work.

Here is a simplified version of my system:

phpMyAdmin MySQL-Dump

version 2.3.1

http://www.phpmyadmin.net/ (download page)

Host: localhost

Generation Time: Oct 08, 2002 at 08:15 AM

Server version: 3.23.49

PHP Version: 4.1.2

Database : `test`

--------------------------------------------------------

Table structure for table `parts`

CREATE TABLE parts (
id int(11) NOT NULL auto_increment,
type varchar(15) NOT NULL default '',
desc varchar(30) NOT NULL default '',
KEY id (id)
) TYPE=MyISAM;

Dumping data for table `parts`

INSERT INTO parts VALUES (1, 'front', 'Front Part A');
INSERT INTO parts VALUES (2, 'back', 'Back Part A');
INSERT INTO parts VALUES (3, 'back', 'Back Part B');
INSERT INTO parts VALUES (4, 'back', 'Back Part C');
INSERT INTO parts VALUES (5, 'back', 'Back Part D');
INSERT INTO parts VALUES (6, 'front', 'Front Part B');
INSERT INTO parts VALUES (7, 'front', 'Font Part C');
INSERT INTO parts VALUES (8, 'front', 'Front Part D');

--------------------------------------------------------

Table structure for table `systems`

CREATE TABLE systems (
system_num int(11) NOT NULL default '0',
front_module int(11) NOT NULL default '0',
back_module int(11) NOT NULL default '0'
) TYPE=MyISAM;

Dumping data for table `systems`

INSERT INTO systems VALUES (1, 1, 2);
INSERT INTO systems VALUES (2, 8, 3);
INSERT INTO systems VALUES (3, 6, 5);
INSERT INTO systems VALUES (4, 7, 3);
INSERT INTO systems VALUES (5, 1, 5);

I need to be able to list all the sytems, including the description of their parts.

I also need to make a 'edit' page which will show the system as configured, but have all the parts in a drop down so you can change to a different part. For this I treid to do:

<?
$systems = mysql_query("select from systems where id = 1");
$row = mysql_fetch_array($systems);
?>
<form action="edit.php" methond="post">
<input type="text" name="id" value="<?echo $row["id"];?>">
<select name="front">
<?
$front_modules = mysql_query("select from parts where type = 'front'")
while ($front_row = mysql_fetch_array($front_modules)){
echo "<option value=\"".$front_row["id"]."\">".$front_row["desc"]."\n";
}
?>
</select>

<select name="back">
<?
$front_modules = mysql_query("select * from parts where type = 'back'")
while ($front_row = mysql_fetch_array($front_modules)){
echo "<option value=\"".$front_row["id"]."\">".$front_row["desc"]."\n";
}
?>
</select>

whch displayed the boxed, but if I want to display more stuff from the first query for example, I would get nothing

eg:
<? echo $row["front_module"]; ?>
would return nothing.

zeron

Ok, I got the part of querying while in the while{} loop of another query to work, problem was I was doign three queries in the loop, but on one I missed the resultpointer.

I still can't figure out how to get the unit number, and front and back descriptions.