Category: MySQL

Categories
Database MySQL

Re-numbering an auto-increment column

An auto-incremented column can become a little cluttered if records are deleted from the table, and you may wish to delete the slack of the sequence in your id column for instance doesn't bear any relationship to the number of records in the table.

Before doing anything about that to a primary key, bear in mind that there are those who say that a primary key should never, ever be changed.  Do you really need to re-number your primary key?

If you really want to do it, read the caveat below before proceeding.

CAVEAT: I haven't actually tested this!

First, be safe and backup your database.

Next copy your table:

CREATE TABLE mytable2 SELECT * FROM mytable1

Now run the following commands, modified as necessary for your situation, on the copy:

ALTER TABLE mytable2 DROP mycolumn;
ALTER TABLE mytable2 ADD mycolumn int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Have a look at the new table.  Browse the records.  Do you like what you see?  Run as many tests as are appropriate to your situation.  More is better than less.

If you're satisfied, it's time to disable mytable1 and make mytable two current.

Rename mytable1 to mytable1_old, then rename mytable2 to mytable1.

Keep a close an eye on the situation.  If you're not happy, you can revert to mytable1_old or even restore the backup.

Categories
MySQL PHP

Converting IP addresses to sortable form in MySQL & PHP

IP addresses can't readily be sorted numerically because they are strings and the periods in the dotted-quad format common to IP addresses confuses things.  For instance, in a sorted list, 143.16.20.11 will sort before 23.45.1.78 even though what you really want is to sort by the first octet (23 & 143), then by the second octed (16 & 45) and so on.

PHP and MySQL provide functions to convert IP addresses to sortable format.

MySQL

Two functions are provided for use in MySQL query.  When your IP addresses are stored in a database, you can pull them ready to sort.

INET_ATON() Returns the numeric value of an IP address
INET_NTOA() Returns the IP address converted from a numeric value

Remember that if you need both representations you can pull the value twice in the same query, i.e.

SELECT ip AS ip_dotted_quat,INET_ATON(ip) as ip_sortable FROM mytable

This provides a clean set of data for your PHP or other scripting code to work with.

PHP

You have similar options in PHP.

ip2long("127.0.0.1") converts the IP to a long integer.

long2ip() converts the long integer back to a dotted-quad IP address.

The online PHP manual suggests using the two in combination to validate an IP as shown here

<?php
// make sure IPs are valid. also converts a non-complete IP into
// a proper dotted quad as explained below.
$ip = long2ip(ip2long("127.0.0.1")); // "127.0.0.1"
$ip = long2ip(ip2long("10.0.0")); // "10.0.0.0"
$ip = long2ip(ip2long("10.0.256")); // "10.0.1.0"
?>
Categories
Database MySQL PHP

Time Stamp differences in MySQL and PHP

MySQL and PHP handle time and data data in different ways, and it's important to be aware of the difference.

Both these environments have a TIMESTAMP construct, but they're not entirely compatible.

While PHP uses a UNIX timestamp format (an integer representing the number of seconds since January 1st, 1970) MySQL's TIMESTAMP data type uses a YYYY-MM-DD HH:MM:SS format.

You can overcome this by using the mysql funtion UNIX_TIMESTAMP() to pull dates in the UNIX format native to PHP.

Making this conversion in your SQL query is generally more efficient and simple than converting in PHP.  Remember, you always want to work your data as much as you can in the SQL query and deliver a clean set of data to PHP.

In fact, if you like you can pull the same attribute in different ways in the same query, creating what I'll call pseudo-attributes, as in;

[SOURCECODE language='sql']SELECT UNIX_TIMESTAMP(date_attribute) AS unix_date,date_attribute AS nice_date FROM mytable;[/SOURCECODE]

This gives you two dates to work with in PHP, represented as unix_date and nice_date for a more human readable format, and balances the processing between your web and database servers.

Categories
MySQL

Conditional Queries in MySQL

Probably the first thing we learn in SQL (Structured Query Language) is a simple SELECT statement to draw information out of the database.

Let's assume we're working with a membership table (members) in our database that has first name (fname) and last name (lname) attributes.  We can draw the names out of the database like this:

1

SELECT fname,lname FROM members;

So far so good.  Now lets say we want to include informal names for those who use them, like 'Rob' for 'Robert' or 'Bill' for 'William', so we add another attribute, iname for the informal name.

However not everyone has an informal name, so we can't just change our query to reflect iname instead of fname.  We need to use a conditional query to select iname if it exists, otherwise fname. Here's how to do it;

1

SELECT IF(iname !='',iname,fname),lname FROM members;

In plain English (or pseudocode) we are saying "If there's a value for iname, use it, otherwise use the value in fname."

We can dress this up just a bit more with the concat() function, which we use to contactenate, or paste, strings together.

1

SELECT concat(IF(iname !='',iname,fname),' ',lname) AS &quot;FULL Name&quot; FROM members;

This will return the name as one attribute called "Full Name" using the informal name if available (otherwise the first name) followed by a space and the last name.

There are some important advantages to doing this in the database query rather than a server-side scripting language like PHP or ASP.  First, you're moving some of the processing work to the SQL server, which balances the load between servers.  Second, you're simplifying our server side scripting code by delivering it a clean set of data which can be used as it is.  The conditional clause in the SQL query is much simpler than the code that would be required to do the same thing in PHP.

It's generally a good idea to do as much of your conditional processing as possible in the SQL query so your PHP or other server-side script code will be easier to write, maintain, and understand.

Categories
Database MySQL PHP

Pulling a single value from the database

Sometimes you just want to pull a single value from the database without a whole lot of work.  Here's how to do it in a single line;

MySQLi

// Assumes that a mysqli connection ($db) has been made to the database
$value = $db->query("SELECT value FROM table WHERE condition = 'met'")-&gt;fetch_row()[0];

MySQL (Deprecated)

// Assumes that a connection exists to the database (mysql_connect()) and a database is selected (mysql_select_db())
$value = mysql_result(mysql_query("SELECT value FROM table WHERE condition = 'met'"), 0);