Let me begin with this warning. The procedure below will alter all the rows of your tables. If you don’t feel comfortable doing this please contact us here at box201.com and we can talk about a maintenance or rescue operation. Here is the scenario which led down the resequence path. While building a website, we realized newly registered users would not have all the information  and the “not-null” columns needed something other than NULL but not the default value.

So how do I correct a table which has an order I want to keep but allow us to insert a new entry for id=1. How about a procedure? Here is a way to pass the table name and last record id to start from and reorder the table.

Couple notes here notice the ‘_Set’. You must have a space before the Set or the table name looks like `sometableSet` due to the concat. Also setting the Delimiter is the only way to create the stored procedure with “;” after each line.

DELIMITER $$

DROP PROCEDURE IF EXISTS reSequence $$
CREATE PROCEDURE reSequence (tableName varchar(30), maxid int)
BEGIN

set @v1 = maxid;
set @stmt = concat('UPDATE ', tableName, ' SET id = @v1 + 1
 WHERE id = @v1');
PREPARE stmt2 FROM @stmt;

  WHILE @v1 > 0 DO
    EXECUTE stmt2;
    SET @v1 = @v1 - 1;
  END WHILE;
 
DEALLOCATE PREPARE stmt2;

END $$

DELIMITER ;

Call reSequence like this….

mysql>CALL reSequence('cars', 1415);
mysql> ALTER TABLE colors AUTO_INCREMENT = 1416;
Query OK, 1415 rows affected (0.05 sec)
Records: 1415  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Where `cars` is the table name and 1415 is the last populated id in the table. As long as no foreign keys exist the procedure will take id=1415 and update the id to id=1416 and work it’s way down to 1 setting the starting record at “2” so you can update row id=1416 to row id=1. The problem is the auto_increment gets set forward one to 1417 so you will need to reset auto_increment back to 1416;

Example - my db is innodb and the colors table looks like this:

mysql> describe colors;

+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | red |
| 2 | green |
| 3 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> CALL reSequence('colors',3);
ERROR 1451 (23000): Cannot delete or update a parent row: a
foreign key constraint fails (`my_testdb`.`bicycles`,
CONSTRAINT `bicycles_ibfk_2` FOREIGN KEY (`color_id`)
REFERENCES `colors` (`id`))
mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | red |
| 2 | green |
| 4 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select * from bycycles;
+----+---------------+----------+
| id | name | color_id |
+----+---------------+----------+
| 1 | schwinn | NULL |
| 2 | high_flyier | NULL |
| 3 | blue_flame | NULL |
| 4 | hot_mongoose | 3 |
+----+---------------+----------+

Here we need to adjust the parent table foreign key to the new id then continue. color_id=3 is the color blue. Select first the records which are offending. Since the query stopped at id=3 and didn’t touch id=2 so we can assume id=4 color_id=3 is the offending row.

mysql> select id from bicylcles where color_id = 3;
+----+
| id |
+----+
| 4 |
+----+
1 row in set (0.00 sec)

mysql> select id from bicycles where color_id = 4;
Empty set (0.00 sec)

#### NOTE HERE. We are safe here and only need to fix this one row to complete the resequence. More complex data may require this process a few times. ####


mysql> update bicycles set color_id = 4 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Remember we do not want to change id=4 anymore. The blue bike is still blue with id=4, we just restart where we left off.

mysql> call reSequence('colors',2);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 2 | red |
| 3 | green |
| 4 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select id from bycycles where color_id = 3;
Empty set (0.00 sec)

We are clear so now I can update colors table with id=1;

mysql> insert into colors (id, name) values (1, 'white');
Query OK, 1 row affected (0.43 sec)

mysql> select * from colors;
+----+---------------+
| id | color |
+----+---------------+
| 1 | white |
| 2 | red |
| 3 | green |
| 4 | blue |
+----+---------------+
4 rows in set (0.00 sec)

There is a problem here as auto_increment is now set at “6”;


mysql> SELECT Auto_increment FROM information_schema.tables
    -> WHERE table_name='colors';
+----------------+
| Auto_increment |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)

mysql> alter table colors auto_increment = 5;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into colors (`name`) values ( 'black');
Query OK, 1 row affected (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | white |
| 2 | red |
| 3 | green |
| 4 | blue |
| 5 | black |
+----+---------------+
5 rows in set (0.00 sec)

mysql>

Thats how to re-sequence a table without manually updating each row. It works great on smaller tables but be very cautious the changes are very fast.

freelance php development

| | Comments (0) | TrackBacks (0)

Let’s start this off with a note about services. Our servers connected to the net via a cable provider. One day our email stopped working so after inquiry with the service provider they advised us we were not smart enough to run a legitimate server behind our firewall so they blocked all ports by telling every black list in the world the providers network IP range and told them to black out any email from these IP’s.

 

We understand the typical consumer probably doesn’t know how to administrate a email server but some of us do. We continued to jump thru email hoops and finally when the provider also said they wanted to become the internet police we finally switched to another provider and they too had issues but a lot less than the first.


My point here is that was terrible customer service. There is just no need to have those kinds of internet police on as network providers where they actively hand our accounts to unscrupulous spammers but rule with an iron fist against the customers. We are now managing our own IP, named, email, and hardware. Viva la OpenSource!


FreeBSDrocks.png 

 Our recommendation for servers is FreeBSD. “Them folks can code!” We strongly recommend a FreeBSD system for its ease of install, simple configuration and bulletproof security. How many useful Operating Systems can be downloaded in an afternoon, and have a web/email/firewall server done before evening arrives. With the cost at less than a buck for a DVD to burn the image on.










One last statement. We are looking for freelance PHP work, Database development or graphic design jobs. Please stop by http://box201.com for details.

Firefox and text-align center

| | Comments (0) | TrackBacks (0)

Have you ever been developing a website and it looks fine and centered in Internet Explorer but in Mozilla it aligns hard left? We were developing an in-house template system to make site creation a snap. We built the test pages and got the stylesheets all lined out only to find the pages all hard left in our firefox browsers. Not good. This would be a little hard to offer as a commercial product and still reasonably remain afloat. Our stylesheet had this tag/attribute:

body {
text-align:center;
}

The expected result was to shift everything to center. Text-align: center works well in IE but not in firefox. Dabbling around with the templates, we found an added “<center></center>” tag would render the text and pictures in the middle just like the text-align: center was already doing in Internet Explorer.  The problem was we needed to add the tags before and after each section in our templates. That meant before and after the header, main, and footer. Also If we set our <DocTypes in all our templates and only had 1 template per page we could do it but the point in a template is to organize the structure of the code.

Templates make it easy to change all the headers at the same time like adjusting the buttons on the navigation bar or changing the header background. We built a 3 template system which included a header, main content, and footer. When we separated our headers from the main body the mozilla hard left problem manifested itself with a vengeance. The only known fix was to put the <center></center> tags at the beginning and ending of each tier in our templates. This produced xhtml non-compliant code with generated pages looking like this:

<center>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en" xmlns="http://www.w3.org/1999/xhtml" xmlns:mt="http://www.sixapart.com/ns/mt">
<head>
<title>this is a test</title>
</head>
</center>
<center>
<body>
~~
</body>
</center>
<center>
<p> footer info </>
</html>
</center>

Don’t code like this. It kinda works but is so very non compliant we almost didn’t include it here as our example. Templates help you cookie cut basic layouts so the hard part of adding content can be more dynamic and changeable without reloading a huge static html file. Building a cookie cutter which generates abnormal html code is not elegant or excellent. Coders should code out problems not make functions which make more problems.

The Fix:

So our lead developer did what any serious programmer does to fix abnormalities  in browsers. He turned to Google. Amazing how someone else had the same problem and it was fixed with a simple command.

text-align: -moz-center;
/* this fixes the bug in firefox which pays
* no attention to text-align center */

The other website developer stated he didn’t know why mozilla needed their own center code and assumed it was because mozilla developers realized their browser didn’t parse the center command very well so instead of fixing it, for some reason, they just created their own tag. Whatever the reason we were just pleased to add the new attribute to our body tag and keep moving. However this made IE slam hard left. Reading further revealed the proper fix for both IE and Firefox.We decided to put both the body tags in our Cascading Style Sheet. Example:

body /* Internet Explorer */
{
/* this centers the page in IE browsers.  */
text-align: center;
}

body /* Mozilla Firefox alignment */
{
/* this fixes the bug in firefox which pays no attention to text-align center */
text-align: -moz-center;
}


http://box201.com has an example of this in production. On the main home page take a look at the stylesheet and you will see both text-align:center; text-align: -moz-center; in the body tag. That single command helped enormously. Preventing all kinds of “jumps through hoops” to code around mozilla browsers. Let us know if this is as helpful to you too.

Keep in the code,

Box201 Developers…

Tag Cloud

Find recent content on the main index or look in the archives to find all content.