-1

Can someone tell me why this doesn't work? If I try to loop it I end up with '0'.

$lines=file('test.txt');

foreach ($lines as $value)
{
$query="select sum(dailyUnitsSold) as dus, sum(dailyGrossSales) as dgs,  count(*) as cnt from mydatatable where category like '%$value%'";
$joejoe=@mysql_query($query) or die (mysql_error());
$row = mysql_fetch_assoc($joejoe);
echo $row['dus'].'<br />'.$row['dgs'].'<br />'.$row['cnt'].'<br />';
}

I'm trying to pull some sold totals from a single table based on each product's category. For example, total sales for blue widget category, total sales for green widget category, purple widget category, etc. The problem is that the category field has multiple entries in each field, so one product row might have three or four categories, hence the LIKE bit.

Thanks for any thoughts here.

Konrad Krakowiak
  • 12,285
  • 11
  • 58
  • 45
Dave Campagnolo
  • 1
  • 1
  • 6
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). Remove the `@` to quit suppressing the errors. Does each query return only one line? – Jay Blanchard May 29 '15 at 15:36
  • yes, each query returns one line. – Dave Campagnolo May 29 '15 at 15:41
  • if you end up with zero, then the query found no matching records and your sum/count came out to be zero. e.g. everything's working fine, it's your logic that needs fixing. – Marc B May 29 '15 at 15:44
  • 1
    show the text file contents, or similar strucuture if sensitive info, to better understand what 'multiple entries in each field' means – hubson bropa May 29 '15 at 15:46
  • text file has one category per line. line 1: blue widget, line 2: green widget, line 3: purple widget. a row of data might look like this: id, product name, a sales number, then a category field with "green widget; blue widget; purple widget. so one product can be in more than one category. – Dave Campagnolo May 29 '15 at 16:04
  • @marcB: thanks for the response. i get valid results (not 0) when I run each query by hand. – Dave Campagnolo May 29 '15 at 16:19
  • try running the query directly in the database? also, looping through the query each time is not very efficient on the db; you are better off running the query and then looping through the results – nomistic May 29 '15 at 23:20

1 Answers1

0

What do you get if you run it like this:

$lines=file('test.txt');

foreach ($lines as $value){
    $query="select sum(dailyUnitsSold) as dus, sum(dailyGrossSales) as dgs,  count(*) as cnt from mydatatable where category like '%$value%'";
    $joejoe=@mysql_query($query) or die (mysql_error());
    if (mysql_num_rows($joejoe) != 0) {
        $row = mysql_fetch_assoc($joejoe);
        echo $row['dus'].'<br />'.$row['dgs'].'<br />'.$row['cnt'].'<br />';
    } else {
        echo "Empty Value.<br />";
    }
    mysql_free_result($joejoe);
}
Twisty
  • 30,304
  • 2
  • 26
  • 45