使用phpmyadmin，我如何在数据库中存储一系列数据？ （利用多对多的关系）

Question

I need to make a database containing information on pharmaceutical drugs.

• Column one is the unique identifier field with auto-increment. e.g., "1"
• Column two contains the name of the drug. e.g., "Tramadol"
• Column three contains a list of weights. e.g., "30g , 40g , 50g , 60g"

Using a php while loop and an array, I could echo this data using the following method:

while ($row = mysql_fetch_array($result)) {
   echo "Drug Name: {$row[1]} Drug Weights: {$row[2]}"
}

While this would work, I know it is definitely not the best way. The list of weights would have to be a varchar, rather than actual individual integers. What would be the best way to store and echo a range of information for a single product, as seen in column three?

Answer 1

This is actually a many-to-one relationship, as you have multiple dosage amounts in relation to a single drug: http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/

As for your problem, this depends on the context of how you want to use the data. If the dosage amounts are meant to be used as reference to the various products offered at the pharmacy (i.e. a small bottle of Tramadol and a large bottle), then I would probably just add a new row for every dosage amount, as you will probably have a limited set of values to choose from in the first place, and it's more representative of what a store's inventory would look like. You could then iterate through it with nested loops like this:

$drugs = array()
while ($row = mysql_fetch_array($result)) {
    $drugs[$row['name']][] = $row['dosage_amount']
}
foreach ($drugs AS $drug => $dosages) {
    echo '<h2>'. htmlentities($drug, ENT_COMPAT, 'UTF-8') .'</h2>';
    foreach ($dosages AS $dosage) {
        echo '<br><em>'. htmlentities($dosage, ENT_COMPAT, 'UTF-8') .'</em>';
    }
}

Reference: PHP MySQL display multiple rows grouped by common fields

On the other hand, if it's used to track prescriptions, then have a separate table along the lines of id | drug_id | dosage_amount, along with any other information you would require.

If you went with the second option, you would use an INNER JOIN to get the required data:

SELECT drugs.name, prescriptions.dosage_amount
FROM drugs
INNER JOIN prescriptions
ON drugs.id=prescriptions.drug_id

Reference: http://www.w3schools.com/sql/sql_join_inner.asp

From there, you can iterate through it the same way you were doing it already:

while ($row = mysql_fetch_array($result)) {
    echo "Drug Name: {$row[0]} Drug Weight: {$row[1]}"
}