SELECT * FROM WHERE?

[Farmgirl]

I'm struggling with the right code to get the answer I want from a small 7 table database using PHP and MySQL.

 

I'm pretty much a beginner when it comes to PHP & MySQL...having only started this about three weeks ago.

 

The main PHP code for the page works OK, but it's just one small element I want to refine.

 

The page I made has a search facility which works at two different levels. I got the code from a tutorial I found HERE, and adapted it to fit my database model.

 

Here's my page...

Tractor Pulling Database

 

 

In one of the search results it lists

tractor name (tractor.tractor_name)

weight class (weight.w8) (class.class_name)

 

Because each tractor can vary its pulling power by adding another engine, (some tractor pullers in the USA NTPA have up to seven engines fitted to their tractors! in the UK we are a little more reserved and tend to have about four), I want to be able to list the name of the tractor, perhaps like a heading, with the various weights/classes in a list under the tractor name.

 

At the moment the list consists of the tractor name with the weight class underneath, and repeats for each change of weight added, like so...

 

tractor name (tractor.tractor_name)

weight class (weight.w8) (class.class_name)

 

tractor name (tractor.tractor_name)

weight class (weight.w8) (class.class_name)

 

...

 

...

 

Thanks...

[artacus]

Was there a question in here?

 

Where is your problem? Doing the search, joining the tables, displaying the results?

[Farmgirl]

...I want to be able to list the name of the tractor, perhaps like a heading, with the various weights/classes in a list under the tractor name...

 

Thanks...

I did ask a question, see above quote, but it was not that visible, or perhaps lost in my blurb!

 

This is the code I have at present... where 'trackid' is passed via the URL when the user selects the name of the tractor displayed after the initial search result is displayed.

 

<?php  if(isset($_GET['trackid'])){
$TrackID=$_GET['trackid'];

//connect to the database
$db=mysql_connect ("localhost","user","password") or die ('I cannot connect to the database because: ' . mysql_error());

//-select the database to use
$mydb=mysql_select_db("tractors");

//-query the database table
$sql="SELECT tractor.tractor_name,class.class_name,weight.w8 FROM `tractorw8`,`tractor`,`class`,`weight` WHERE tractor.tractor_id = tractorw8.tractor_id AND weight.w8_id = tractorw8.w8_id AND tractor.class_id = class.class_id AND tractor.tractor_id = " . $TrackID;


//-run the query against the mysql query function
$result=mysql_query($sql);

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

      $Tractor=$row['tractor_name'];
	    $Class=$row['class_name'];
	       $W8=$row['w8'];

//-display the result of the array

echo "<ul>\n";
echo "<li>" . $Tractor . "</li>\n";
echo "<li>" . $W8 . " " . $Class . "</li>\n";
echo "</ul>";
}
}
?>

 

I want to display $Tractor on its own, with a list containing all the differant instances of its corresponding $W8 $class which would display a list similar to this...

 

Starlight Express 1

 

3.5t Modified

4.5t Modified

5.5t Modified

 

Having just written out this reply, I've suddenly seen a possible solution. If it works I'll be back...in the mean time I'll go 

 

Thanks