[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql - Dynamic query

Posted on 2012-08-19
15
Medium Priority
?
453 Views
Last Modified: 2012-08-20
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...
0
Comment
Question by:edavo
  • 8
  • 6
15 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310209
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
 

Author Comment

by:edavo
ID: 38310261
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310268
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38310273
The online man pages have some good learning resources:
http://php.net/manual/en/tutorial.forms.php
0
 

Author Comment

by:edavo
ID: 38310463
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
 

Author Comment

by:edavo
ID: 38310471
Screen grab of sql table...
table.jpg
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310481
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
 

Author Comment

by:edavo
ID: 38310584
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310603
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
 

Author Comment

by:edavo
ID: 38310668
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
 

Author Comment

by:edavo
ID: 38310684
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
 
LVL 25

Expert Comment

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

Author Comment

by:edavo
ID: 38310724
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38310730
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
 

Author Comment

by:edavo
ID: 38312253
Worked!!!! Excellent.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 9 hours left to enroll

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question