PHP, MySQL and Case Sensitivity of Field Names

MySQL is always case insensitive for field names (but not table names on *nix).

Now here's the subtlety PHP will adopt what ever case you define in your query so if out of perversity you write your query , :-

"select SuRnAmE from addresses"

Then you will have to remember to use the same messy casing in PHP

$surname=$row['SuRnAmE'];

so better
"select surname from tbl_addresses"

$surname=$row['surname'];


Play Safe always use lowercase!

MySQL Merging Two Identical Tables

The problem is that a straight * insertion will fail because of the primary keys which are likely to clash.

The solution is specifically mention all the the other fields

INSERT INTO table_1(col2, col3, col4) SELECT col2, col3, col4 FROM table_2;

The primary key of table_1 should be auto_increment .

MySQL Subquery or Multiple Select Queries

Hi I have a table with one record per person and per item of clothing

so

peter, jumper,blue
peter,trousers,green
sue,dress,green
peter,jumper,red
gordon,jumper,green


I want to write a query to select all people with green jumpers but only if they have no other color jumper

So in the above case it would ONLY select Gordon not greedy old Peter

A solution provided is a sub select or multiple select query

SELECT *
FROM myTable AS t1
WHERE t1.clothing = 'jumper' AND t1.color = 'green'
AND NOT EXISTS(SELECT *
FROM myTable AS t2
WHERE t2.person = t1.person AND t2.clothing = 'jumper'
AND t2.color <> 'green')

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.

Search for names of a certain length

Suppose if we want to find the names that contain exactly five characters, we use a special character "_"(underscore). The following query will list all the five letter names from the table employees.


select * from employees where firstname like '_____';