SQL

Posts about using SQL

MySQL Logo

Change MySQL Table Name

23rd April 2008 - 2 minutes read time

To change a table name in MySQL you can use the ALTER TABLE command with the parameter RENAME TO. Here is an example of a query that will rename the table "atable" to "newtable".

ALTER TABLE atable RENAME TO newtable;

You could also use the RENAME TABLE command with the same effect.

RENAME TABLE atable TO newtable;

This is also useful for renaming all of the tables in the database, just separate each table rename command with a comma.

RENAME TABLE atable TO newtable, anothertable TO anothername;

This should enable you to rename all tables in the database, perhaps with a new prefix.

Read the full article

MySQL Logo

Get MySQL Version Information Through PHP

13th April 2008 - 2 minutes read time

There is little syntactical difference between MySQL 4 and MySQL 5, but sometimes finding that difference can pinpoint a bug. The mysql_get_server_info() function will tell you what version of MySQL you are using. You can call it with no parameters, in which case it picks the most recently created MySQL resource, or with the resource handle created with mysql_connect().

Here is an example of how to use it.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
echo mysql_get_server_info();

You can achieve the same effect with a simple MySQL query.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$query = mysql_query('SELECT VERSION() as mysql_version');

Read the full article

MySQL Logo

Random Number Range In MySQL

5th April 2008 - 2 minutes read time

To create a number between one value and the next you can use the following formula, where i is the lower end of the range and j is the higher end of the range.

FLOOR(i + RAND() * (j – i))

Rather than put in (j-i) in your query you should put in the result. So for a number between 1 and 10 you would make i = 1 and j = 11. 11-1 = 10 so the query would run like this.

SELECT FLOOR(1 + (RAND() * 10));

For a number between 64 and 104 you would use the following query.

SELECT FLOOR(64 + (RAND() * 41));

Read the full article

MySQL Logo

Finding Duplicate Values In A MySQL Table

6th February 2008 - 3 minutes read time

To find duplicate values you need to use the MySQL COUNT() function and then pick out all of the counts that are greater than one.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) > 1)
ORDER BY Count DESC;

Conversely you can also select the rows that only have a single entry.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) = 1)
ORDER BY Count DESC;

However, it is very nice to pick out the duplicate entries in a table, but you might still need to do something with them. Here is a query to delete any duplicate rows from a table. It does a simple self join and deletes the row value with the lowest ID.

Read the full article

MySQL Logo

MySQL Error Reporting In PHP

4th February 2008 - 3 minutes read time

Using MySQL as a database engine in PHP is very powerful, but one thing that can be a pain is trying to debug code. Spotting the difference between a PHP error and a MySQL error can be hard with larger systems.

A good way of debugging MySQL code is by using the mysql_errno() and mysql_error() functions. These functions print off the last error that yuour MySQL server encountered so it can tell you exactly what is wrong with your SQL statements.

The following example code tries to get data from a non-existent table. The $result variable is set to false as the call failed so the error functions print off the SQL error.

Read the full article

MySQL Logo

Logging Onto A MySQL Database

28th January 2008 - 4 minutes read time

If you have command line access to your MySQL database server you will need to use certain parameters to log in. Most web hosts will not allow you to do this, so you might want to install MySQL into a local computer and give it a go.

To log into mysql you must run the program called mysql with certain parameters. Here is an example.

./mysql -u username

One thing you must realise is that all usernames are associated with a host so if the user you specified can't access the server from this host then you won't get far. To specify the host location enter the -h flag.

./mysql -h hostaddress -u username

If your user is able to access this server then you will be asked for a password. You can set the password in the string using the -p flag. If this doesn't work then leave out the space between the -p and the password.

Read the full article

Getting A Random Row From A PostgreSQL Table

10th January 2008 - 1 minute read time

To get a random row from a PostgreSQL database you need to use the RANDOM() function. This is similar to the MySQL function RAND() and will generate a new random number for each row and order them by that new number. This is used in conjunction with the LIMIT clause to limit the amount of returned rows to one.

SELECT value FROM table ORDER BY RANDOM() LIMIT 1

Read the full article

Getting A Random Row From A MS SQL Table

9th January 2008 - 1 minute read time

To get a random row from a Microsoft SQL database you need to use the NEWID() function. This will generate a new random number for each row and order them by that new number. This is used in conjunction with the TOP clause to limit the amount of returned rows to one.

SELECT TOP 1 value FROM table ORDER BY NEWID()

 

Read the full article

MySQL Logo

Toggle a TINYINT Field in MySQL

31st December 2007 - 2 minutes read time

MySQL uses the datatype TINYINT to store boolean values. MySQL stores the value as TINYINT(1) which is the same as a bit so the value is either 0 (false) or 1 (true). Using boolean fields can be very useful, but it can be costly in processing as to change the value you have to query the database, find out the value of the field and then act accordingly.

Here is a simple MySQL query that can be used to toggle the value already present in the TINYINT field without having to do any pre-querying.

UPDATE table SET field = 1 - field

Read the full article