Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- using mysql count
November 20, 2006, 5:15 pm
rate this thread
select id, order_no, price, count(item_no), sum(price) from production
WHERE item_no = '27714'
group by item_no;
When I setup my query in php, I use:
$query2 = "SELECT id, order_no, price, count(item_no) as count from
production where item_no = '27714";
I keep getting an error in on my php page:
Warning: mysqli_error() expects exactly 1 parameter, 0 given in
c:\Inetpub\wwwroot\production\production_line_daily.php on line 24
Error in query: SELECT id, order_no, price, count(item_no) as count from
production where item_no = '27714'.
If I remove the count(item_no) as count from the query - it works fine. I
really want to put the count in though. And eventually the sum.
Any help that you can provide is greatly appreciated.
- Christoph Burschka
November 20, 2006, 5:27 pm
Re: using mysql count
It looks like you didn't copy the whole query. It's cut off after '27714. Note
also the missing single quote at the end.
To make it work correctly, copy the complete query - including the "group by
item_no". Without a GROUP BY clause, as MySQL likes to inform you, "mixing of
GROUP columns with non-GROUP columns is illegal".
Re: using mysql count
Here is my whole query:
/*** create a new mysqli object with default database***/
$connection = mysqli_connect($hostname, $username, $password, $dbname) or
die ("Unable to connect");
$query = "SELECT distinct item_no FROM production WHERE date > '2006-11-11'
group by item_no";
$result = mysqli_query($connection, $query) or die ("Error in query: $query.
////create list of variables from query results
while(list($item_no) = @mysqli_fetch_row($result))
echo "<b>Item Number:</b> $item_no";
//query details about item numbers (from above query)
$query2 = "SELECT id, order_no, serial_no, price, count(item_no) as
count, sum(price) as sum from production where item_no = '$item_no' group by
$result2 = mysqli_query($connection, $query2) or die ("Error in query:
//echo out results
while(list($id, $order_no, $serial_no, $price, $count, $sum) =
echo "Item Number Count: <font
color=red>$count</font> Grand total: <font
// close connection
When I put back in the 'group by' during the second query, I lose the
ability for each line for the item_no to listed out. I'm trying to get each
item_no to be title and then each serial_no listed out for that item_no. I
would like to get the count and the sum for each item_no.
Hopefully that make sense to everyone. I'm not sure if I should to the work
in the query or in PHP.