MySQL Tips & Tricks

PHP, MySQL and Case Sensitivity of Field Names

2009-05-21T14:09:00.000-07:00

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

2009-05-10T16:03:00.000-07:00

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

2009-05-03T04:29:00.000-07:00

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)

2009-02-12T12:57:00.000-08:00

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?

2009-02-12T06:08:00.000-08:00

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

2009-02-06T14:09:00.000-08:00

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

2009-01-30T05:59:00.000-08:00

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 '_____';

Creating Tables with CSS rather than HTML

2009-01-08T14:24:00.000-08:00

Finally got to grips with this today, seemed pretty tricky but of course HTML tables can be troublesome as well. Nearly abandoned the attempt before I made a breakthrough and started to get a feel for it!

Also understood why this method is popular, it's because it cleans up your HTML instead of having zillions of tr, td tags you can black-box all the finetuning inside the stylesheet.

So should see some cleaner MySQL html templates from me now!

Moving a Wordpress Blog to a New Domain

2008-12-15T04:27:00.001-08:00

To update WordPress options with the new blog location, use the following SQL command:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

After that you will need to fix URLs of the WordPress posts and pages, which translated from post slug, and stored in database wp_posts table as guid field. The URL values in this field are stored as absolute URLs instead of relative URLs, so it needs to be changed with the following SQL query:

UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com');

If you have linked internally within blog posts or pages with absolute URLs, these links will point to wrong locations after you move the blog location. Use the following SQL commands to fix all internal links to own blog in all WordPress posts and pages:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

from http://www.mydigitallife.info

PHP Primitive to Create an HTML Form Dropdown from a MySQL table

2008-11-24T12:25:00.000-08:00

$options_html=fn_make_form_selection_element($selected);

function fn_make_form_selection_element($selected_value)
{
$select_html="";
$query = "select * from lookup_offices order by office_id";
$result = mysql_query($query) or die ("query not valid".mysql_error());

while($row = mysql_fetch_assoc($result))
{
$db_value=$row['office_id'];
$db_name=$row['office_real_name'];

$option_selected='';
if ($db_value==$selected_value) $option_selected='selected';
$select_html.=<<<_STR_
< option value="$db_value" $option_selected>$db_name< /option>\n
_STR_;
}
return ($select_html);
}

MySQL to Create an HTML Hyperlink from an URL

2008-11-18T08:33:00.000-08:00

SELECT url,href FROM tbl_password where private = '1' and url NOT like '%squid%';

update tbl_password
set href=concat('< a href="',url,'">',url,'< /a>')
where private = '1' and url NOT like '%squid%';

I really wanted to strip the http:// off the anchor text as well

update tbl_password
set href=concat('< a href="',url,'">',SUBSTRING(url,7),'< /a>')
where private = '1' and url NOT like '%squid%' ;

Note here how flexible concat is with multiple parameters

MySQL Table Design : The Two Types of Table

2008-10-10T12:57:00.001-07:00

In a simple database there will likely be two types of tables, data tables and lookup tables.

A lookup table will contain say a list of Counties/States

eg
state_id,state
1, Alabama
2) Carolina

The principle is that a lookup table defines in one place and one place only in this case the list of states. This has a posh name "Normalisation" it prevents the problems caused by different tables using slightly different names or indexes for States

A Data Table contains as you might expect the data, they are usually more complex and have a unique index (primary key).

I usually name my data tables tbl_xxxx and my lookup tables lu_xxx to distinguish then

How can you test if/when a database was last used/updated?

2008-10-08T06:45:00.000-07:00

SHOW TABLE STATUS for database01;

This lists create_time & update_time it is easy to visually scan to see latest update date.

3 Types of MySQL Comments

2008-08-29T08:55:00.000-07:00

mysql> SELECT 1+1; # This comment continues to the end of line

mysql> SELECT 1+1; -- This comment continues to the end of line

mysql> SELECT 1 /* this is an in-line comment */ + 1;

mysql> SELECT 1+ /* this is a multiple-line comment */ 1;

A Technique for Testing an Update Safely

2008-08-24T06:10:00.000-07:00

Create a separate table
create table testtable as select * from realtable;

And execute the statement there.

In fact it wouldn't hurt to have an entire duplicate test database, which you can easily create with a data dump

Accidently destroying data with an UPDATE

2008-08-23T04:38:00.000-07:00

At times the syntax of MySQL can appear overly strict eg when you are hurriedly hand typing a query and it keeps throwing up a syntax error. At other times however MySQL will output no error when you might wish it would. An example of this might be string concatenation

If you forgot that you needed to use Concat for this
ie

update tbl_password set url=concat('http://',url) where url not like
'http://%' limit 1; ### CORRECT###

but instead wrote

update tbl_password set url=url+'http://' limit 1; ### INCORRECT ###

You would destroy the contents of the field URL

Updating Data Strings with Concat

2008-08-02T04:07:00.000-07:00

I needed to prefix http:// before any urls that didn't already have it.

update tbl_password set url=concat('http://',url) where url not like 'http://%';

(the following is my test statement which I used to avoid the risk of destroying data, by limiting it to one record)

update tbl_password set url=concat('http://',url) where url not like 'http://%' and url like '%supanames%';

Alternatively limit change to one record with 'limit'

update tbl_password set url=concat('http://',url) where url not like
'http://%' limit 1;

Perl DBI MySQL read a Record

2008-06-10T09:04:00.000-07:00

A secret of programming is to have your own personal library of "primitives" I have them for Perl,PHP & ColdFusion MySQL routines. eg for connecting to MySQL, Inserting/Deleting/selecting records, dumping records into hashes or arrays. The following is a simple Perl DBI MySQL function.

sub fn_read_record_db()
{
$brochure_id=shift;
$sql=qq|select txtdevname from ytbl_development where id='$brochure_id'|;
$sel = $dbh->prepare($sql);
$sel->execute;
if ($hash_ref=$sel->fetchrow_hashref())
{
return $hash_ref->{'txtdevname'};
}
return 'Not Found';
}

Click here Free PHP/MySQL/cPanel Webhosting No ads no catches, I didn't believe it either!

Test your MySQL queries inside a MySQL GUI before coding them

2008-06-06T15:23:00.000-07:00

Before you start coding your application with PHP or whatever remember to test out your MySQL in your MySQL GUI such as SQLYOG or even the CLI

What are the advantages?

You don't have to worry about the Script/MySQL syntax clashes (quoting etc)
You only have to think MySQL and not say PHP as well
You can see the data produced or not produced
You can test your concepts

MySQL Standard Query Templates (Aide-Memoire)

2008-06-06T15:11:00.000-07:00

Create a grep-able text file with the standard MySQL queries this will help you recall the different query structure of update/insert/select/delete add to your list everytime you write something useful:-
(This wont look too good inside this blog but should copy/paste OK)

template: alter table activity_log add column loginemail varchar(40) first;
template: insert into tbl_xxx (field1) values ('value1')
template: update employee_data set salary=220000, perks=55000 where title='ceo';
template: update tbl_websitepages set page_content=replace (page_content,'http://www.site.co.uk/website/cms/','/website/images/cms/');
template: insert into bad_table2(id,name) select id,name from bad_temp2;
template: into tbl_music_selected (registrant_email,music_id,music_title) select '$registrant_email',music_id,music_title from tbl_music where music_id='$music_id'
template: select * from tbl_lighter_note ORDER BY RAND() LIMIT 1
template: select * from tbl_orders order by count limit 0,10 (select top ten)
template: update tbl_lighter_note set count=count+1
template: select * from ytbl_region_county_lookup as t1 inner join ytbl_mapregions mp on t1.region_id = mp.intID where t1.region_id = '14' order by t2.DivisionName;
template: update tbl_readings set abc = ucase(reading_title); #will automatically truncate if field shorter
template: update tbl_readings as r1,tbl_readings3 as r3 set r1.reading_content= r3.reading_content,r1.abc=r3.abc,r1.reading_category=r3.reading_category where r1.record_id=r3.record_id; #update taking data from another table (join)

Querying By Date

2008-05-17T07:44:00.000-07:00

We are so frequently required to create date based queries, eg how many orders last week, last month, how many orders between two dates. It is necessary to avoid the trap of treating date as a number, instead convert the date into a handy unit such as days

# select all records from today
select * from tbl_orders where TO_DAYS(NOW()) = TO_DAYS(dtmdevdate);

# select all records from last three days
select * from tbl_orders where (TO_DAYS(NOW())-3) > TO_DAYS(orderdate);

MySQL provides a whole range of date functions become familiar with them here

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Creating a Derived Data Column (for efficiency)

2008-04-16T08:23:00.000-07:00

Hi
Frequently we query our database for a value but then have to always do the same manipulation on it before we can use it. Well it can often be useful to do this "once" in the database and then store the manipulated value in a separate column of the database.

Here is a very simple example
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*3.14159);

Where col2 is set to the value of col1*pi

You could also do more complex calculation/filtering/shortening in your code eg php,ASP etc

Can you see the power of this technique?

Recovering a database from the raw .frm .MYI .MYD files on Vista

2008-01-29T09:29:00.000-08:00

(All this presupposes you failed to do a recent & proper backup/dump of your data, but that's why you are reading this)

MySQL requires its datafiles ,*.MYI,*.frm,*.MYD to have exactly the right permissions and ownership otherwise MySQL refuses to see them.
Now this is a problem for me on Vista (de-Vista-ted) because I still haven't worked how to change ownership.

Solution:
1) In new Database create a table with same name , populate with a record or two (doesnt have to have same structure)
1a) I didnt need to do this as I used a month old backup
2) Use Windows Explorer to copy your "good" frm,MYI,MYD on top of the dummy files you created
3) They will automatically inherit the correct owner/permissions
4) When I did this, I was saddened to see however that I couldn't see my new records (probably due to different MySQL versions)
5) I did a Table diagnostics and saw there was an error (using SQLYOG)
6) Did a Repair Table in same Table Diagnostics and wow there were my lovely records!
7) Implement an automatic nightly backup of your databases

So it is possible to recover your database from the raw files, but watch out for :-
file permissions & file ownership and try to have the same MySQL version.
You could do all this on a spare PC and then do a proper backup/dump which is then portable!

Technorati the Specialist Blog Search Engine

2007-11-19T12:20:00.000-08:00

I'm trying to get this blog registered with Technorati the Specialist Blog Search Engine.

I have to post this to show that I own the Blog

Technorati Profile

PHPRunner saved me from a Nervous Breakdown!

2007-11-16T06:39:00.000-08:00

We know we have to work cleverer rather than harder.

I was working on a large PHP/MySQL website where the specification kept changing. It was bad enough redesigning that data and recoding the PHP all the time. Fortunately by using PHPRunner I was able to rebuild the admin side each time in just minutes. Using PHPrunner you can build your PHP Pages (or ASP using ASPRunner) which allow you to search and list your records and subsequently create, modify or delete them. You know that tedious repetitive stuff.

I know PHPrunner can also build the Web side as well, but I haven't looked into that yet , but will let you know.

There is an evaluation version, download it and get hooked!

Download Here