[coolblu]

Hi all. I'm new here so I thought I would start off with a request for some PHP and mySQL help

I am having a bit of trouble selecting information from a database I have created in MySQL. The database is for a web site that will reference a music collection.

So far I am using the following query, which selects the information I need:

PHP Code:

$query = 'SELECT * FROM records LEFT JOIN labels ON (records.label = labels.labelid) ORDER BY artistname ASC'; 

Here I have the tables "records" and "labels", which hold the details of the music and the label they were recorded on respectively. This function is being used to output all of the data in the database.

From this query I am then using a while loop to extract information:

PHP Code:

    while ($row= mysql_fetch_array($query)) {

        $getID = $row["id"];

        $title = $row["title"];

        $artistname = $row["artistname"];

        $description = $row["description"];

        $price = $row["price"];

        $labelname = $row["labelname"];





print <<<END

$count.) $title by $artistname ($labelname)

END;

echo "\n";

        echo "<b>$description</b>\n<br><br>\n";

        echo "Now only <font color=red>$price</font>\n\n<br><br>";

        $count++ ;

    } 

So far this gets all of the data I need.

However the problem arises when I want to get the condition of the record (literally record as in vinyl ) from a table called "condition". Here there are a number of conditions, given a "conditionid" (unique) and a "abbr" (abbreviation) e.g. Mint condition is referenced as conditionid "1" and abbr "M".

I think the problem lies in the fact that each record in the "records" table has two condition ratings (an integer which links to the conditionid in the condition table). These values are "rcon" (The condition of the record) and "scon" (The condition of the sleeve of the record e.g. its cover). In effect these could be either the same (both the record and the sleeve are in the same condition) or could be different (the condition of the sleeve is not the same as the record).

I thought that the following query would be valid:

PHP Code:

$query = 'SELECT * FROM records

LEFT JOIN labels AS labels ON ( records.label = labels.labelid ) 

LEFT JOIN condition AS recordcondition ON ( records.id = recordcondition.conditionid ) 

LEFT JOIN condition AS sleevecondition ON ( records.id = sleevecondition.conditionid ) 

ORDER BY artistname ASC' 

However I cant seem to get the correct information out of the database using a while loop (I think maybe I need to perform the query some other way to do so).

If you have got this far down the page thanks for reading about my problem If you have any ideas about how I can solve this or need a better explanation (I can try ) please reply below.


Thanks

'blu

[blaw]

Hello,

What is your database structure? It looks like there is one-to-many relationship between `records` (one) and `recordcondition` (many), and the same for `records` (one) and `sleevecondition` (many).

If these condition tables hold values like 'Mint' or some sort like you said, shouldn't these relationship be the other way around?

You have record's ID matching condition's ID, but there should be condition fields in records table that store each condition ID.

If you can post your table definition, I may be able to help you further.