A quick way of duplicating a row in a table without running into duplicate key errors or having to type out all the field names…
Not quite a one-liner, but very straightforward.
/*
Duplicate row 58 from mytable
*/
CREATE TEMPORARY TABLE tmptable SELECT * FROM mytable WHERE id = 58;
/*
Change the unique key
*/
UPDATE tmptable SET id=0;
/*
Insert the duplicate row into the original table
*/
INSERT INTO mytable SELECT * FROM tmptable;
/*
Drop the temporary table
*/
DROP TABLE tmptable;
I’m always finding myself looking up strings to format PHP and MySQL dates. A simple d/m/Y is pretty straight forward but sometimes things can get a bit fiddly. Where ever possible I try and leave my date formatting in the database, but I still find myself doing it in PHP all the time.
Here are a few simple cut and paste date formats.
| PHP Date | MySQL Date | PHP Format | MySQL Format | Notes |
|---|---|---|---|---|
| 7/9/2009 | 7/9/2009 | j/n/Y | %e/%c/%Y | |
| 07/09/2009 | 07/09/2009 | d/m/Y | %d/%m/%Y | |
| 7/9/2009 8:07 | 7/9/2009 8:07 | j/n/Y G:i | %e/%c/%Y %k:%i | |
| 7/9/2009 8:07 AM | 7/9/2009 8:07 AM | j/n/Y G:i A | %e/%c/%Y %k:%i %p | |
| 07/09/2009 8:07 AM | 07/09/2009 8:07 AM | d/m/Y G:i A | %d/%m/%Y %k:%i %p | |
| 07/09/2009 08:07 AM | 07/09/2009 08:07 AM | d/m/Y H:i A | %d/%m/%Y %H:%i %p | |
| 07/09/2009 8:07 am | d/m/Y G:i a | |||
| Mon 7th Sep 2009 | Mon 7th Sep 2009 | D jS M Y | %a %D %b %Y | |
| Monday 7th September 2009 | Monday 7th September 2009 | l jS F Y | %W %D %M %Y | |
| 2009-09-07T08:07:50+01:00 | Monday 7th September 2009 | Y-m-d\TH:i:sP | %W %D %M %Y | PHP DATE_ATOM constant |
| Mon, 07 Sep 2009 08:07:50 +0100 | Mon, 07 Sep 2009 08:07:50 | D, d M Y H:i:s O | %a, %d %b %Y %T | PHP DATE_RSS constant |
echo(date('d/m/Y', strtotime('2009-09-07 08:07:50')));
//or
$dateTime = new DateTime("now");
echo $dateTime->format("Y-m-d H:i:s")
$sqlstring = "SELECT DATE_FORMAT('2009-09-07 08:07:50', '%d/%m/%Y') AS formated_date FROM some_table" ;
If you haven’t looked at the PHP manual closely for a while you should definitely (re)acquaint yourself with the DateTime Class.
The other day I needed a list of UK post towns matched up to postcode areas, which I found on wikipedia.
Anyway I have tidied it up and put it into two database tables postcode and town
You can download the SQL file here towns.zip
Hope it comes in useful.
Quite often you end up in situations where you are required to check and see if one time period overlaps another time period. Probably the most common situation this occurs in is when you are building a booking system – be it for tables or cars or rooms.
You have a series of entries in a database with a start (date)time and an end (date)time and before adding a new record you need to check that it doesn’t overlap with another booking.
The solution is fairly simple but I always end up making a quick diagram on a piece of paper to check.
As you can see two time periods can either be sequential (i.e. there is no overlap at all) or they can overlap in one of four ways. Put this into a diagram and the solution becomes pretty obvious.
There is an overlap if end_time_1 > start_time_2 AND start_time_1 < end_time_2
| Time | start_1 | start_2 | end_1 | end_2 | end_1 > start_2 | start_1 < end_2 |
|---|---|---|---|---|---|---|
| 1 | 09:00 | 08:00 | 11:00 | 10:00 | true | true |
| 2 | 09:00 | 10:00 | 11:00 | 12:00 | true | true |
| 3 | 09:00 | 08:00 | 11:00 | 12:00 | true | true |
| 4 | 09:00 | 09:30 | 11:00 | 10:30 | true | true |
| 5 | 09:00 | 07:00 | 11:00 | 08:00 | false | true |
| 6 | 09:00 | 12:00 | 11:00 | 13:00 | true | false |
An example SQL query would be something like the one below but make sure your datetime formats are correct – in MySQL the default format is YYYY-MM-DD hh:mm:ss
SELECT * FROM bookings WHERE room_id = '" . $room_id . "' AND ((date_end > '" . $start_date . "') AND (date_start < '" . $end_date . "')) ";
The above query will pull back the records where the times overlap.