|
|
Posted by Auddog on 11/20/06 17:54
"Christoph Burschka" <christoph.burschka@rwth-aachen.de> wrote in message
news:4se6sgFuj9reU1@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_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.
>>
>> 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
$serial_no $price<P>";
echo "Item Number Count: <font
color=red>$count</font> 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
Navigation:
[Reply to this message]
|