Duplicate table with MySQL
Posted on Monday 27 September 2010
Just replace the new_table with the name of your new table and existing_table with the table you want to duplicate
CREATE TABLE new_table SELECT * FROM existing_table
Resetting the auto increment number with SQL (MySQL)
Posted on Tuesday 19 January 2010
When you have a table with 10 records and an auto increment field ID and you delete the tenth record e.g.The next time you insert a record, the ID will be 11 and not 10. You can reset the auto increment number by using this SQL:
ALTER TABLE table_name AUTO_INCREMENT=10
This will reset the next auto increment value to current largest value in the auto increment column + 1. That way the auto increment will start with 10 instead of 11.
MySQL replace function - Find and replace values in database with sql
Posted on Tuesday 12 January 2010
A great piece of sql that can save you a lot of time. The replace sql function finds and replaces values in a database.
Just fill in the table_name, table_field, the value your searching for and with which value you want to replace it with.
UPDATE table_name SET table_field = REPLACE(table_field, 'value', 'new_value') WHERE my_field LIKE '%value%';
Database table with all the countries, Belgian cities and dropdown with all the countries
Posted on Tuesday 3 March 2009
Below you can download a zip file with SQL dumps of all the countries and another SQL dump with all of the Belgian cities with their postal code, longitude and latitude. I‘ve also included a dropdown with all the countries. I found this on the Snipplr website.
Creating views with MySQL instead of filtering out records with SQL
Posted on Wednesday 18 February 2009
I was just reading Pro PHP security and I‘ve learned something very handy. When you‘re, for example using an active field which marks active pages in your table, Instead of including “WHERE active=‘1’” every time you want the get the active pages you can create a view table that filters out these records. In my example, I‘ve used a table ‘pages’ and created a view table ‘pages_view’. My pages table contains these fields:
ID (PK, INT)
title (varchar)
text (varchar)
active (ENUM 0,1)
SQLbuddy
Posted on Thursday 5 February 2009
This is a nice and worthy opponent for phpMyAdmin and other MySQL tools. It‘s also open source. SQLbuddy is extremely easy to install. You just copy the folder to your webspace and then type in your database information like database name, login, password and database server. That‘s it. It also has SQLlite support. phpMyAdmin is so hideous and not very user-friendly. The layout of SQLbuddy is intuitive and clean.