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 (known as camel case)

$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')