Pear DB and “DB Error: already exists” On Insert

Note that this post relates to using a MySQL database in conjunction with the PEAR DB module, or any other PEAR module that depends on DB – like DB_TABLE (which I’ve been enjoying).

You might run into this error if you switch over from using PHP’s built-in MySQL functions, over to a database abstraction layer, which is what I did on a recent project. After much banging-the-head-on-the-desk, I finally figured out that PEAR DB doesn’t use the same auto-increment sequence that the actual MySQL engine does.

For example, you may have a database using a CMS that doesn’t use PEAR. You do your various inserts, and inevitably, your deletes. So, your auto-increment column might start out in a perfect sequence like 1 – 2 – 3 – 4 – 5, etc. But then say the first two get deleted, so now your sequence id’s are 3 – 4 – 5. But then you insert again, and your CMS, using the DBMS directly, knows to use 6 for the next auto-increment value.

But then say you switch over to PEAR DB, and why wouldn’t you? It’s great – and readily bundled into most PHP installations. But then you notice, taking the above example, that your next insert starts at 1. Then the next insert produces an id of 2. And then you get to 3 and WHOA what happens, “DB Error: already exists” that’s wtf happens. PEAR DB ignores MySQL’s auto-increment sequence and makes its own (jump down to the notes/warnings in red).

You may also notice that you have table names with “_seq” at the end.

Why is this? I have no idea – but it’s stooooopid. Is there some reason for this? Seriously, please enlighten me! I mean, what’s an example of where you’d like to use more than 1 sequence?

seqSo what to do? You need to alter those “_seq” tables, that’s what. So if you’re having trouble inserting into a table named “products” – get MySQL’s “Next Autoindex” value, which you can easily do in phpMyAdmin on a table’s “structure” tab (see the screenshot, where it’s 20).

Now, go into the products_seq table, and change the id to that number (20 in this case). Now you shouldn’t have any problems.

So the moral of the story is, if you start out using a PEAR database abstraction layer, you’ll never have this problem. But if you’re unlucky enough to not have done that in the first place and switch over to it, you may have this issue and this is a quick fix.

Fancybox: Reload Parent Page When Closed

This caused me a bit of a headache. I knew you had to use the onClosed option in your Fancybox call, but wasn’t sure how to use it. For example, if you have a page full of images, and use Fancybox to add/edit those images, then you want that parent page to refresh when you close the Fancybox to show any changes you made.

In this example, I attach a Fancybox to any link with the class “editImage.” When it’s closed, the parent will refresh:

$("a.editImage").fancybox({
  'width': 800,
  'height': 450,
  'onClosed': function() {
    parent.location.reload(true);
  }
});

If you’re not sure what this means or how to use Fancybox, you should hop over here.

Update August 10, 2011:

Note that if you have something like this in your file:

$(".iframe").fancybox({
  'width': 800,
  'height': 400   
});

That it should go before anything else, otherwise if your fancy box link has both of these classes, the iframe class will cancel out anything you declare before it. So in this example, if my link was like <a class=”iframe editImage” href=”whatever”> then in my javascript, I’d want to declare the iframe before the editImage, like so:

$(".iframe").fancybox({
  'width': 800,
  'height': 400   
});

$("a.editImage").fancybox({
  'width': 800,
  'height': 450,
  'onClosed': function() {
    parent.location.reload(true);
  }
});

TextExpander Stopped Working? Try this.

Suddenly I noticed that my beloved TextExpander just stops working/expanding for no apparent reason. Restarting the app didn’t work, but what does work is restarting Firefox.

I guess there’s some conflict between Firefox version 3.6.x and the TextExpander engine. So the quick fix seems to be to just restart Firefox when this happens.

Evidently, this isn’t fixed in TE3 – so no need to upgrade with hope of this getting fixed (at least not of this writing).

Cheers!

UPDATE January 4, 2011:
It has been pointed out in the comments that closing Chrome can also fix the problem – thanks folks!

Baiduspider, Twiceler, and Yeti – Bad Robots!

One of the websites I manage had been experiencing tremendous use in bandwidth. “Sweet!” we thought, a solid boost in traffic is a good thing. But as time went by, it kept going up and up in an unreal way – something was awry. After checking the log files, I saw that the 3 bots mentioned above seemed to be literally attacking the website. After some Googling, I found that some or all of these robots have run amuck on other websites too, and are generally worthless as far as supplying any valuable traffic – so I just blocked them all by placing the following in my .htaccess file:

Options +FollowSymLinks
RewriteEngine on

# block bad bots

RewriteCond %{HTTP_USER_AGENT} ^Baiduspider [NC]
RewriteRule ^.*$ http://google.com/ [R,L]

RewriteCond %{HTTP_USER_AGENT} ^Mozilla/5(.*)Twiceler
RewriteRule . http://www.cuill.com/your_bot_sucks [R=301]

RewriteCond %{HTTP_USER_AGENT} ^Yeti [NC]
RewriteRule ^.*$ http://google.com/ [R,L]

# end bad bots

The above is a combination of various Googling (I wish I kept the links so I can thank the fine folks that helped me address this). You can send them away to any site you like – in the first and last I just kicked them to Google, and for the second I sent the robot back to it’s own site. My bandwidth now seems to back down to normal levels. Try them out, monitor your bandwidth and log files, and tweak as needed – good luck!

Update – December 2010

I’ve consolidated the code, and added another bot:

# block bad bots

RewriteCond %{HTTP_USER_AGENT} ^Baiduspider [OR]
RewriteCond %{HTTP_USER_AGENT} ^Mozilla/5(.*)Twiceler [OR]
RewriteCond %{HTTP_USER_AGENT} ^Yeti [OR]
RewriteCond %{HTTP_USER_AGENT} ^Java.* [OR]
RewriteCond %{HTTP_USER_AGENT} ^Mail.Ru.*
RewriteRule ^(.*)$ http://help.naver.com/robots/ [R,L]

Drupal – Select List “default_value”

After beating my head against the desk the past hour, I finally figured out that to set a default value on a plain select list, you need to use “#value” and not “#default_value”. So, for example, say I have an array of banks consisting of the bank id and the bank name, like so:

$banks = array(14=>"bank one", 22=>"bank two);

I then want to construct a simple select form element, this WILL NOT work:

$form['drop'] = array(
 #id' => "bank_drop",
 '#type' => 'select',
 '#title' => 'test',
 '#options' =>; $banks,
 '#default_value' => 22  // will NOT work!
);

But this works:

$form['drop'] = array(
  #id' => "bank_drop",
  '#type' => 'select',
  '#title' => 'test',
  '#options' => $banks,
  '#value' => 22  // works!
);

This was on Drupal 6.15

Hope that saves someone some headache :)

Sizing and Positioning Fancybox

You’ve probably seen Fancybox all over the place. In a way, it’s the living-successor of ThickBox and Lightbox.

I was recently on a project where I had to both size and place it. Sizing it is easy. Say you have a link tag with an id of opener, like this:

Google

To create and size the fancybox that will open when it’s clicked, you’d just add the following javascript to your page:


Note that pixels are assumed, you should not say something like “390 pixels” like you would in css, it won’t work then.

So, above we have defined a fancybox that is 390 pixels wide, and 400 pixels high. Next we need to position it.

(Note this is based on Fancybox 1.3 – I can’t say if it’ll work with other versions.)

Fancybox puts everything in a layer with an id of “fancybox-wrap” – so we can easily manipulate it with basic css, like so:

#fancybox-wrap {
  margin: -70px 0 0 290px;
}

You can do the same with padding, maybe even positioning.

Hope that helps someone!