using mysql count

[Auddog]

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

[Christoph Burschka]

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

[Auddog]

"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

[Jerry Stuckle]

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
==================