By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

using mysql count

P: n/a
I have the following query that works in mysql:

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_dail y.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.

A
Nov 20 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Auddog wrote:
I have the following query that works in mysql:

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_dail y.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.

A

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".
--
Christoph Burschka
Nov 20 '06 #2

P: n/a

"Christoph Burschka" <ch****************@rwth-aachen.dewrote in message
news:4s************@mid.dfncis.de...
Auddog wrote:
>I have the following query that works in mysql:

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_dai ly.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.

A


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".
--
Christoph Burschka
Here is my whole query:

<?php

include 'config.php';

/*** create a new mysqli object with default database***/
$connection = mysqli_connect($hostname, $username, $password, $dbname) or
die ("Unable to connect");

//create query
$query = "SELECT distinct item_no FROM production WHERE date '2006-11-11'
group by item_no";

//excute query
$result = mysqli_query($connection, $query) or die ("Error in query: $query.
".mysqli_error());

////create list of variables from query results
while(list($item_no) = @mysqli_fetch_row($result))
{
//print item_no
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
item_no";

//excute query
$result2 = mysqli_query($connection, $query2) or die ("Error in query:
$query2. ".mysqli_error());

//echo out results
echo "<ul>";
while(list($id, $order_no, $serial_no, $price, $count, $sum) =
@mysqli_fetch_row($result2))
{
echo "<li>$order_no &nbsp;&nbsp;&nbsp;
$serial_no&nbsp;&nbsp;&nbsp;$price<P>";
echo "Item Number Count: <font
color=red>$count</font>&nbsp;&nbsp;Grand total: <font
color=red>$sum</font>";
}
echo "<p>";
echo "</ul>";
}
// close connection
mysqli_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.

A
Nov 20 '06 #3

P: n/a
Auddog wrote:
I have the following query that works in mysql:

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_dail y.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.

A

If item_no is a numeric type should should NOT have single quotes around
it. Single quotes indicate a string.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Nov 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.