need a sql statement to filter top 30 results

[dotty]

Hi,

I had a look at maani_us charts but the demo only shows how to use with fixed data - also it's Flash. Will take a look again at this for future projects. For this project I needed simple html graphs.

I downloaded JPGraph and got that working with data from a database. There's a good tutorial at : [URL=http://]http://www.macromedia.com/devnet/mx/dreamweaver/articles/php_graphics_11.html[/URL] and their own documentation is very good, contains examples with full source code.

I am using it to set up a mini logging system on a site and needed the graph for generating a report. Now I need to limit the amount of data outputted to the graph so the graph won't become unreadable.

SELECT date,  user, count(user) as totalcount FROM tblstats GROUP BY user

of course returns all users - can anyone tell me how to limit to the top 30 results?

thanks

note added: I thought I was replying to another thread hence the reference at top to maani

[synergypoint]

Uh....have you tried looking up the select syntax description in the MySQL documentation?

http://www.mysql.com/doc

Use the Limit syntax.

select ... from ... where ... order by...limit 0,30

That selects 30 records starting with the first one from the result of the query.

;-)

[dotty]

will that not just return the first 30 results?

How can I make sure to return the top 30 - therefor those who visisted most?:queasy:

[synergypoint]

Limit x,y

x is the starting record number of your query result
y is the number of records to return

You have to make sure your query returns your records in the order that you want by using the WHERE and ORDER clause.

In your case you want x=0 and y=30.

[dotty]

I added the limit 0,30 and it returns 30 results. But it's the 'where' part I'm having difficulty with. I need the results to show the users who most visited. So, how do I say,
select ... from ... where (users most visited??) ... order by...limit 0,30

This is probably fairly basic but I don't know how to do it! :rolleyes:

[tekky]

something like...

SELECT * FROM mytable ORDER BY numvisits DESC LIMIT 0,30

that should return them from most vists to least visits and only the first 30....

[dotty]

Yes, I thought so too but when I add the ORDER BY to the following:

SELECT date,  user, count(user) as totalcount FROM tblstats GROUP BY user limit 0,30

I get an error.

Maybe it is not possible to use ORDER and GROUP together?

[tekky]

Whats the error say?

$result = mysql_query($blahblah) OR die("error: " . mysql_error());

that will display the textual error from mySQL

[dotty]

error: Query was empty

whould it really annoy you if I post the whole code? I hope not! here goes:

<?PHP 
require_once("../../jpgraph/jpgraph.php"); //for making graph
require_once("../../jpgraph/jpgraph_bar.php"); //this too
require_once('../Connections/brokerid.php'); ?>
<?php
mysql_select_db($database_brokerid, $brokerid);
$query_ChartRS = "SELECT date, user, count(user) as totalcount FROM tblusers GROUP BY Broker limit 0,30 ";//so if I put the ORDER BY here it shows the above error
$result = mysql_query($ChartRS) OR die("error: " . mysql_error());
$ChartRS = mysql_query($query_ChartRS, $brokerid) or die(mysql_error());
$row_ChartRS = mysql_fetch_assoc($ChartRS);
$totalRows_ChartRS = mysql_num_rows($ChartRS);

$datay=array();
$labelx=array();
do {
array_push ($datay, $row_ChartRS['totalcount']);
array_push ($labelx, $row_ChartRS ['user']);
 } while ($row_ChartRS = mysql_fetch_assoc($ChartRS));

 $width = 350;
 $height = 500;
 $graph = new Graph($width,$height);
 $graph->SetScale("textlin");
 $graph->yaxis->scale->SetGrace(10);
 $top = 60;
$bottom = 30;
$left = 80;
$right = 30;
$graph->Set90AndMargin($left,$right,$top,$bottom);
 $graph->SetShadow();
 $graph->title->Set("Top 30 users");
// Label align for X-axis
$graph->xaxis->SetLabelAlign('right','center','right');

// Label align for Y-axis
$graph->yaxis->SetLabelAlign('center','bottom');
#$graph->yaxis->title->Set("Number of times accessed \n");
 #$graph->xaxis->title->Set("User ID");
 #$graph->title->SetFont(FF_FONT1, FS_BOLD, 14);
 #$graph->yaxis->title->SetFont(FF_FONT1, FS_NORMAL,12);
 #$graph->xaxis->title->SetFont(FF_FONT1, FS_NORMAL, 12);
 $bplot = new BarPlot($datay);
 $graph->xaxis->SetTickLabels($labelx);
 $graph->Add($bplot);
 $bplot->SetWidth(0.2);
 $bplot->SetFillColor("orange");
#$bplot->SetShadow();
 $bplot->value->Show();
 $graph->Stroke();

 mysql_free_result ($ChartRS);
?>

[tekky]

well it looks like you may not be able to order by with the group by....

lets see, do you have phpmyAdmin access? if so try some of these.... using phpMyAdmin will make testing the queries easier however you can still test these in your script if you do not have access to MyAdmin

"SELECT date, user, count(user) as totalcount FROM tblusers GROUP BY Broker ORDER BY visits LIMIT 0,30 ";

"SELECT date, user, count(user) as totalcount, visists FROM tblusers GROUP BY Broker ORDER BY visits LIMIT 0,30 "; //rename visits to whatever your visits column is

"SELECT date, user, count(user) as totalcount FROM tblusers WHERE 1 GROUP BY Broker ORDER BY visits LIMIT 0,30 ";

hmmm thats all I can think of right now...
over all what does your table structure look like and what is the overall result you are trying to get from this query?

[dotty]

Okay this works in phpmyadmin: It returns the top 30 users.

SELECT date, user, count( user ) as totalcount FROM tblusers GROUP BY user ORDER BY totalcount DESC LIMIT 0, 30'

... but not in my file. I am using the sql to create an array for making a graph using jpGraphs. It produces a lovely graph until I try to add the ORDER BY totalcount then I get the error!

[dotty]

Hi tekky!

It's working!! The

SELECT date, user, count( user ) as totalcount FROM tblusers GROUP BY user ORDER BY totalcount DESC LIMIT 0, 30'

worked but only after I removed the line:
$ChartRS = mysql_query($query_ChartRS, $brokerid) or die(mysql_error());

Maybe this line cannot be used with chart outputs! I don't know but I'm happy with the result now.

Thanks tekky and synergypoint for all your help! 🙂 🙂

[tekky]

I wasnt too clear about that $result line...

should have replaced $result with your resultset variable $charterRS or whatever, 2 queries can and usually will cause problems

glad its working -- what does the final code look like?

[dotty]

Hi Karl

I had replaced the query name with my own - see below, commented out. The final code I am using is:

<?PHP 
require_once("../jpgraph/jpgraph.php"); //for making graph
require_once("../jpgraph/jpgraph_bar.php"); //this too
require_once('../Connections/brokerid.php'); ?>
<?php
mysql_select_db($database_brokerid, $brokerid);
$query_ChartRS = "SELECT date, user, count(user) as totalcount FROM tbluser GROUP BY user ORDER BY totalcount DESC LIMIT 0, 30 
";
#$result = mysql_query($ChartRS) OR die("error: " . mysql_error());
$ChartRS = mysql_query($query_ChartRS, $brokerid) or die(mysql_error());
$row_ChartRS = mysql_fetch_assoc($ChartRS);
$totalRows_ChartRS = mysql_num_rows($ChartRS);

$datay=array();
$labelx=array();
do {
array_push ($datay, $row_ChartRS['totalcount']);
array_push ($labelx, $row_ChartRS ['user']);
 } while ($row_ChartRS = mysql_fetch_assoc($ChartRS));

 $width = 350;
 $height = 500;
 $graph = new Graph($width,$height,'auto');
 $graph->SetScale("textlin");
 $graph->yaxis->scale->SetGrace(10);
$graph->SetMarginColor('#d4d4d4');
 $top = 60;
$bottom = 30;
$left = 80;
$right = 30;
$graph->Set90AndMargin($left,$right,$top,$bottom);
 $graph->SetShadow();
 $graph->title->Set("Top 30 users");
// Label align for X-axis
$graph->xaxis->SetLabelAlign('right','center','right');

// Label align for Y-axis
$graph->yaxis->SetLabelAlign('center','bottom');
#$graph->yaxis->title->Set("Number of times accessed \n");
 #$graph->xaxis->title->Set("Broker ID");
 #$graph->title->SetFont(FF_FONT1, FS_BOLD, 14);
 #$graph->yaxis->title->SetFont(FF_FONT1, FS_NORMAL,12);
 #$graph->xaxis->title->SetFont(FF_FONT1, FS_NORMAL, 12);
 $bplot = new BarPlot($datay);
 $graph->xaxis->SetTickLabels($labelx);
 $graph->Add($bplot);
 $bplot->SetWidth(1.0);
 $bplot->SetFillColor("orange");
#$bplot->SetShadow();
 $bplot->value->Show();
 $graph->Stroke();

 mysql_free_result ($ChartRS);
?>

thanks again for all your help!