MySQL to Create an HTML Hyperlink from an URL

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

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?

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

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

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

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

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;