ordering a text field numerically (A MySQL trap)

If whatever reason you have numbers stored in a text field (char, varchar etc)then watch out if you try and do an order by as it will do the sort alphanumerically this means it will sort 1,10,2,3,4,5,6,7,8,9 when you would expect 1,2,3,4,5,6,7,8,9,10

There is a work-around namely

SELECT names FROM your_table ORDER BY names + 0 ASC

where the +0 forces a numeric sort

This trap occurs in quite a few programming languages and can be hard to spot

How much intelligence in the MySQL Query how much in the Code?

SQL queries provide highly efficient access to parallel data (records). Very often you can get the exact data you require with your query and then all you have to do with your code is present it. Other times it simply isn't possible or isn't convenient to do it all in the SQL. In these cases typically you download the query results into an associative array, then you can further manipulate your data at leisure with all the power of PHP, Perl etc.

What do I mean by convenient? well sometimes it might be possible to develop a complex query which drilled right down to the exact data but this might take time to perfect when you could have coded it much quicker.

What's more I have found that over-complex queries are not very future proof, by that I mean a small change in the specification and they have to be rewritten. Further more they may not be very easy for other programmers to understand/maintain.

So do the best query you can and then manipulate the data in your code.

Truism: SQL can never be so flexible as a scripting/programming language such as PHP/Perl

Drawing Dynamic Online Graphs from MySQL Data

I was terrified at the idea of creating dynamic statistical graphs, fortunately there is a free solution where the complicated graphical stuff is reduced to a library include. JpGraph are the providers of this PHP based solution which appears to be the de-facto package.

Learn more here http://www.aditus.nu/jpgraph

JpGraph is a Object-Oriented Graph creating library for PHP >= 4.3.1 The library is completely written in PHP and ready to be used in any PHP scripts (both CGI/APXS/CLI versions of PHP are supported).

The library can be used to create numerous types of graphs either on-line or written to a file. JpGraph makes it easy to draw both "quick and dirty" graphs with a minimum of code as well as complex graphs which requires a very fine grained control. The library assigns context sensitive default values for most of the parameters which minimizes the learning curve. The features are there when you need them - not as an obstacle to overcome!

Curiously the MySQL part is trivial, you just have query your database for what ever data you require and then pass this to the JpGraph "plotline" function.

They provide hundreds of working examples one of which should match your requirements.