Jump to content

vincej

Members
  • Posts

    155
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling
  • Location
    Calgary , Canada

vincej's Achievements

Regular Member

Regular Member (3/5)

0

Reputation

  1. First of all I'm not even sure if it's the query I need help with or with the PHP. Here is the situation. I have built an app. The app has a report which generates a list of all the products sold with the quantity and price for each location. The problem I have is that every line item has it's location repeated, and repeated - this is the consequence of the foreach loop used. so for example: Chicago 200 chickens $2000 Chicago 100 hams $1000 Chicago 300 Beef $6000 You can see the problem ... Chicago is repeated on every line. What is even more boring is that the document reports on 50 cities with 50 products each. So there is immense repetition. So I want to get to is: Chicago 200 chickens $2000 100 hams $1000 300 Beef $6000 Washington 200 chickens $2000 100 hams $1000 300 Beef $6000 etc etc Here is the SQL I have delivering the flawed outcome: SELECT products.name, order.prodid, sum(order.quantity) as 'Quantity', order.pickupdate, sum(order.ordervalue) as 'Order Value', locations.location, locations.locationid FROM `products`, `order`, `locations` where order.prodid = products.id and locations.locationid = order.deliverylocationid AND order.status = 'open' group by order.deliverylocationid, order.prodid order by locations.location asc; Here is the Foreach Loops which generates the results of the flawed result: <?php foreach ($ordersbylocation as $key => $value){ ?> <tr> <td width="150"><?php echo anchor('admin/reports/SpecificLocation/' . $value['locationid'], $value['location']);?></td> <td width="60" align="center"><?php echo $value['prodid'];?></td> <td width="60" align="center"><?php echo $value['Quantity'];?></td> <td width="250"><?php echo $value['name'];?></td> <td width="150"><?php echo $value['Order Value'] . "<br>";?></td> </tr> <?php } ?>
  2. Thanks CPD .. I did try using AND, but the problem is that the query will not skip over a value and go to the next value. By chaining together a collection of AND statements if only on eof those statements fails then the whole query fails. Many Thanks !
  3. Hi - Lets start with a sample of my table: pickupid pickuplocation Date1 Date2 Date3 Date4 1 Collingwood 1328079600 1330585200 1333260000 1335852000 2 Varsity 1328079600 1330585200 1333260000 1335852000 3 Canmore 1328079600 1330585200 1333260000 1335852000 4 Westbrook 1328079600 1330585200 1333260000 1335852000 I need a query which will select only those dates which are in the future. If a date is in the past the query must skip over it. I have tried using Select with Have, Or, Where & AND. My queries fail because if one of the dates happens to be in the past then the whole query comes back with zero results. Yes, I know that if I orientated my table around the other way ie with locations in the columns and dates in the rows it could make it easier - I've tried that, but I run into other problems as my HTML report has to accommodate over 50 locations, but only 4 dates. Many Many Thanks for All your Help !! MYSql 5.5.16
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.