[PHP & MySQL] SQL Queries

[coolblu]

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:

$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:

    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:

$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

 

[coolblu]

anybody :crying5:

 

[bloodyveins]

Your last query is not valid. You try to "LEFT JOIN" the same table twice. This query won't be executed since there are "multiple" conditions on the same table.

If you want to make realtion between tables, set the referenced table as "index" and not as a "unique" one.

You can make a better query by separating "record condition" and sleeve condition" into two tables and the refer it to your "records" table or find your own better solution.