I need to make a database containing information on pharmaceutical drugs.
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?
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]}"
}
while ($row = mysql_fetch_assoc($result)) {
echo "Drug Name: $row[drugName] Drug Weights: $row[drugWeights]";
}
You didn't indicate the names of your columns, so I'm guessing.
Please do not use ext/mysql in new code. Switch to PDO or mysqli.