please check the mistakes of this php code?

[shams]

hi,
i have the patients mysql table name is treatment, patients will collect thier drugs each month in the same date, it means for each patiens will be multiple entries with deferent id's because the id is auto_increment, ecach drug has it is own column, so the quantity will be entered in the ecah drugs column, i will enter the drug name and the months for that drug will be taken by the patients, this script will tell me the quanttity of that drug i need for the future:
[code]
<?php
$drug = 'PZA'; #$_POST['drug'];
$months = '4'; #$_POST['type'];

mysql_connect("localhost","root") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$result = mysql_query("SELECT SUM($drug) as total FROM treatment");
$total = mysql_result($result, 0, 'total');
$no = mysql_query("SELECT count($drug) FROM treatment");
$newtot = (($total/$no)*$months) - $total;
if($newtot) {
echo "You need ".$newtot." " ."tab of " .$drug;
}
else { echo "Sorry no match found";
}

?>
[/code]
but the output of this script is sorry no match found which is not the correct answer, any one can help please?

[wildteen88]

Your SQL queries are little wrong i belive. I think you should be doing something like this instead:
[code]$result = mysql_query("SELECT SUM(drug_col) as total FROM treatment WHERE drug_col='$drug'");[/code]
Note: before using that query make sure you change any instance of drug_col to the column name you are getting drug name from.

Also your secound query will need to be modified like above too.

[crouchjay]

[code]mysql_connect("localhost","root") or die(mysql_error()); [/code]

I am not totally sure, but I do believe you need a password in there.

Jay

[wildteen88]

[!--quoteo(post=373776:date=May 14 2006, 05:56 PM:name=crouchjay)--][div class=\'quotetop\']QUOTE(crouchjay @ May 14 2006, 05:56 PM) [snapback]373776[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]mysql_connect("localhost","root") or die(mysql_error()); [/code]

I am not totally sure, but I do believe you need a password in there.

Jay
[/quote]
He most probably took the password out for security reasons or hes developing the script locally on his local PC and his seup of mysql has no password set. So that isn't the problem but mainly to do with his SQL queries.

[trq]

And the fact that mysql_query() returns a result resource, not an actual result.

Look at [man]mysql_fetch_assoc[/man]().

[shams]

thanks to all for replies, as i menttioned the drug_col is same as $drug, i means $drug is the column name as well the drug name, each indivdual query work fine, but don't know how to combine them in one php code.

[Barand]

As Thorpe indicated,

change
[code]$no = mysql_query("SELECT count($drug) FROM treatment");[/code]


to
[code]$result = mysql_query("SELECT count($drug) FROM treatment");
$no = mysql_result ($result,0,0);[/code]