unable to perform two queries!

claire

I have a simple script displaying a whole table that works fine!

$result = mysql_query(\"SELECT * FROM tbl\") or die (\"msg\");

while ($row = mysql_fetch_array($result)) { ...

echoing out all information in a tbl

}

For one of the columns I now want to add the sum at the end of the table.

I added the query before entering the while loop:

$result = mysql_query(\"SELECT * FROM tbl\") or die (\"msg\");

$sql = mysql_query(\"SELECT SUM (Col) AS total FROM tbl \") or die (\"msg2\");

$total = mysql_fetch_row($sql);

while ($row = mysql_fetch_array($result)) { ...

echoing all information in a tbl

}

echo $total;

It doesn\'t work. It doesn\'t connect to the database for the second query. I use mysql_pconnect earlier in the script - shouldn\'t that connection stay permanent?

Any ideas?

crusty_php

try
echo $total[total];

claire

Hi again Martin!

I can try that, but the problem is much earlier in the script.

It runs the first query, but is unable to open the database for the second query.

alfoxy

What happens if you take out the $total line

try

$result = mysql_query(\"SELECT * FROM tbl\") or die (\"msg\");

$sql = mysql_query(\"SELECT SUM (Col) AS total FROM tbl \") or die (\"msg2\");

//$total = mysql_fetch_row($sql);

while ($row = mysql_fetch_array($result)) { ...

echoing all information in a tbl

}

and see if you get any errors, if you do get errors try again but get rid of the $result query maybe its your sql syntax thats wrong
let me know what errors you get

claire

Hi Al

You're right. If I block out the $total line I don't get any error msg.
I tried typing list ($total)= (mysql...) didn't do any difference.

What is the correct syntax?

alfoxy

Well then try something like

$result = mysql_query(\"SELECT * FROM tbl\") or die (\"msg\");

$sql = mysql_query(\"SELECT SUM (Col) AS total FROM tbl \") or die (\"msg2\");

$total = 0;

while ($row = mysql_fetch_array($result)) { ...
echoing all information in a tbl
}

while ($row2 = mysql_fetch_array($sql)) { ...

$total += $row2['total'];
}

I think this should work
Al

claire

still dies at msg2.

alfoxy

hang on a sec ill look into the syntax of count .. i think thats where the problem is.

alfoxy

Probably clutching at straws but try changing the name total to something like totalcol and get rid of the space beween SUM and (Col)
Any luck ?
Al

claire

THANKS!!

The space between the SUM and Col seem to be important (as well as all the other changes! still kept the word total though...)

It works fine!!

/claire