In SQL, you cannot use a column alias in the where clause that's been defined in the select clause, for example an alias created by a sub-select. Today i discovered a derived temporary table workaround, this creates a table with my select statement and this allows me to use the alias with a conditional statement. [...]
Sep 25 2008
Backing up a mysql table, using SQL.
Today i found a nice and simple way of backing up a mysql table without renaming / exporting. The technique i found creates a new table with the same structure and copy across the data for the current table, sounds simple and it is, take a look at the following. Simply replace the [NEW_TABLE] and [...]
Feb 27 2008
Select Insert Statement
A very simple way of passing data from one table to another is by using the select insert statement in sql. INSERT INTO table (forename, dayphone, address1, address2, address3, postcode, country, email) select r.first_name r.phone_number, r.address1, r.address2, r.address3, r.post_code, r.country, r.email from table2 r;
Feb 27 2008
Concatenation
Concatenating in SQL is pretty simple, it's purpose is to connect fields and string together as one string, like so. If you have a database table field called forename and you would like to have the word MR before each name, this is how it would be done. SELECT CONCAT('Mr. ', forename) FROM table; You [...]
Feb 27 2008
SQL Case Statement
You can use a case statement in SQL like so: SELECT id, `name`, title, CASE id WHEN 2 THEN 1 WHEN 3 THEN 1 ELSE 0 END AS selectedCase FROM table
Feb 27 2008
PhpBB Latest Threads
You can use the following code to get the latest threads from a PHPbb forum, it extracts the post information and the user information. Remember to replace "tblprefix" with your defined table prefix. SELECT p.post_id, p.topic_id, p.forum_id, p.post_time, p.poster_id, pt.post_subject, pt.post_text, u.username FROM `tblprefix_posts` p INNER JOIN `tblprefix_posts_text` pt ON pt.post_id = p.post_id LEFT JOIN [...]