Sql - Dynamic query

would this work?

SELECT day, product, count(*)
FROM tracker
GROUP BY day, product

If you only want certain days:

SELECT day, product, count(*)
FROM tracker
WHERE day BETWEEN 1 and 30
GROUP BY day, product

And with only certain products:

SELECT day, product, count(*)
FROM tracker
WHERE day BETWEEN 1 and 30
AND product in ('toy','car','food','can')
GROUP BY day, product

or is there more to this than I am understanding?

OK:

This is the actual code... I tried different variations of what you suggested but no luck. Note that the actual field id DEED (not product) and the USERID is being pulled from the URL (i.e. http://www.bitmaplove.com/test.php?user=david

THIS IS WORKING?


$sql='SELECT day, product, count(*) FROM tracker GROUP BY day, product WHERE user ="'.$userid.'"';
$result=mysql_query($sql);      
$count=mysql_num_rows($result);      

echo '<table border="1">';
   
    while($rows=mysql_fetch_array($result)){            
            echo '<tr>';
            echo '<td>';
            echo $rows['user'];
          echo '</td>';
            echo '<td>';
            echo $rows['deed']; // this is the product field
          echo '</td>';
            echo '<td>';
            echo $rows['postd']; // this is the day field
          echo '</td>';
            echo '<td>';
            echo $count; // this is the total of product on that day 1.2.3.4.5
          echo '</td>';
            echo '</tr';
      }

echo '</table>';

You probably need an alias on the count so you can refer to it more easily, e.g.

$sql='SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ="'.$userid.'"';

I also changed 'product' to 'deed' and 'day' to 'postd'.

then you can use

while($rows=mysql_fetch_array($result)){            
          echo '<tr>';
          echo '<td>';
          echo $userid;
          echo '</td>';
          echo '<td>';
          echo $rows['deed']; // this is the product field
          echo '</td>';
          echo '<td>';
          echo $rows['postd']; // this is the day field
          echo '</td>';
          echo '<td>';
          echo $rows['cnt']; // this is the total of product on that day 1.2.3.4.5
          echo '</td>';
          echo '</tr';
      }

Select allOpen in new window

The online man pages have some good learning resources:
http://php.net/manual/en/tutorial.forms.php

I feel the query line is the problem? I tried both 1 and 2 as commented and simply get no response - It is connecting properly as I use the infamous "You have successfully connected to MySQL" line . The table is working as I added the "data to go here" row as a test...

ADDED image attached of table also...

Any thoughts...

// -- (1) -- $sql='SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ="'.$userid.'"';
// -- (2) -- $sql="SELECT postd, deed, count(*) FROM tracker WHERE postd BETWEEN 1 and 30 GROUP BY day, deed";

$result=mysql_query($sql);      
$count=mysql_num_rows($result);      

echo '<table border="1">';
echo '<tr><td colspan="3">data to go here</td></tr>';
   
    while($rows=mysql_fetch_array($result)){            
          echo '<tr>';
          echo '<td>';
          echo $userid;
          echo '</td>';
          echo '<td>';
          echo $rows['deed']; // this is the product field
          echo '</td>';
          echo '<td>';
          echo $rows['postd']; // this is the day field
          echo '</td>';
          echo '<td>';
          echo $rows['cnt']; // this is the total of product on that day 1.2.3.4.5
          echo '</td>';
          echo '</tr';
      }

echo '</table>';

Screen grab of sql table...
table.jpg

add
  echo $sql
to see what the final string looks like.  Then take the SQL string and run in against the database directly to see what is returned.

I a bit of a greenie - but it printed out the literal ---

SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ="davo"

In where you grabbed the screen shot of the data ... there usually is a way to run an SQL statement (at tab called SQL or something).  Is it phpmyadmin or someother database viewer?
I do not have access to you database etc. so you must run the SQL statement.  We need to prove whether the SQL works or not first to pinpoint the problem.

I ran it as:
SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ="davo"

and got:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user ="davo" LIMIT 0, 30' at line 1

Then I ran it as:

SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed

and it worked...

BUT the user name is being pulled from the url - the user name is working beccause I echo it out and it appears correctly

??

And for the record - I ran (in SQLAdmin environment):

SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ='davo';

and
SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ="davo";

and still get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user ='davo' LIMIT 0, 30' at line 1

try with single quotes ... ie.
SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ='davo'

Tried both - same result

Remember:

SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed;

worked fine in terms of delivering the output  (but did not filter user)

Worked!!!! Excellent.