Sql - Dynamic query

I want to output a table that lists each day of month and a ttl row count from one field.

$sql="SELECT * FROM tracker WHERE day ='1' AND product = 'toy'";

This query will run through each day of the month, echo out one product and it total row count for that day. I am after a table something like this:

1 - toy - 5 (5 toys logged on day 1)
1 - car - 2
1 - food - 9
1 - can - 7

2 - toy - 2
2 - car - 6
2 - food - 1
2 - can - 1

3 - toy - 1
3 - car - 12
3 - food - 4
3 - can - 5

How can I dynamically change the day and product name in the query and run run the query without writing out 100 queries...
edavoAsked:
Who is Participating?
 
lwadwellCommented:
woops ... silly me, wasn't paying close enough attention or looking beyond the obvious ... the GROUP BY goes last.  So try

SELECT postd, deed, count(*) as cnt
FROM tracker
WHERE user ='davo'
GROUP BY postd, deed
0
 
lwadwellCommented:
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?
0
 
edavoAuthor Commented:
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>';
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
lwadwellCommented:
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';
      }

Open in new window

0
 
Ray PaseurCommented:
The online man pages have some good learning resources:
http://php.net/manual/en/tutorial.forms.php
0
 
edavoAuthor Commented:
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>';
0
 
edavoAuthor Commented:
Screen grab of sql table...
table.jpg
0
 
lwadwellCommented:
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.
0
 
edavoAuthor Commented:
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"
0
 
lwadwellCommented:
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.
0
 
edavoAuthor Commented:
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

??
0
 
edavoAuthor Commented:
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
0
 
lwadwellCommented:
try with single quotes ... ie.
SELECT postd, deed, count(*) as cnt FROM tracker GROUP BY postd, deed WHERE user ='davo'
0
 
edavoAuthor Commented:
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)
0
 
edavoAuthor Commented:
Worked!!!! Excellent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.