Month: March 2008

Insert where not exists

If you want to insert values into a MySQL table, but only if those values don’t already exist, and you don’t want to use a primary key on the table (and deal with the resulting error suppression ), here’s an elegant one-query method of doing so.

INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
WHERE NOT EXISTS(
    SELECT [column1] FROM [same table name]
    WHERE [column1]='[value1]'
    AND [column2]='[value2]' LIMIT 1
)

OK, technically it’s not a single query. But it is only one round trip to the MySQL server.

Hat tip to Matt Mongeau

iZombie

So I finally bought an iPhone. It’s rockin. I’m going to be getting on a plane to Vegas for the weekend, and I have a 6-hour layover at Dallas-Fort Worth. Between books and phone video, I think I’ll be sufficiently entertained.

I really do like the phone. It’s so convenient. I’ll be walking along somewhere, and thinking of something I need to do (add it to calendar) or wondering about the answer to a question (mobile web browser). I’ll pull out the phone, and start typing, reading, whatever. Next thing you know, I’ll lose the group of people I was with, forget what I was originally doing, or start bumping into people. I’m becoming an iZombie.

Orlando Restaurants (Fratellos & El Cerro)

If you’re in the East Orlando area, and are looking for a good bite to eat, let me clue you in on some local deliciousness.

Fratellos

Fratellos is an Italian-American takeout/delivery store with some amazing pizza and subs. Everything they serve looks like real food, no mystery mush. They say their dough and sauce are made fresh every day, and I believe it. The dough alone has a good yeast taste, so it must be fresh. The oven-baked subs are also awesome.

El Cerro

Mexican-American. Nothing too authentic, but it’s a good choice for a group with picky eaters.

This is the hardest-working staff in the food service industry. The way I see it, if my cup (water, sweet tea, or other delicious thirst-quenching liquid) doesn’t sit empty, the staff is paying attention to the table. It’s like the food service equivalent of “keep your eye on the ball”. At El Cerro, my cup hardly ever drops below half-full. The staff is always friendly, and they get the food to you very quickly.

Also, definitely get the queso dip. It’s delicious.

BTW: Here’s a plug for eFoodi.com from Demetri Spanos. Demetri’s working with us on the Loud3r project (or “those ‘3r’ sites”), and he’s got a thing for making data very useful, so I look forward to good stuff from the site. I’ve started using his site to keep track of my food-related stuff. I like how it’s not just a restaurant site, or a recipe site, but also drinks and informational articles for utensils/techniques.

Quick Tip: Save time on escaping from mysql_real_escape_string()

All data should be escaped before going into a query, to prevent a SQL-injection attack. The current “best practice” is to use mysql_real_escape_string(), which connects to the DB, checks how strings should be escaped, and then returns the safe string.

Unfortunately, this requires a round trip to the DB, and it takes time and resources. Here’s a trick to make things faster.

If the data you’re escaping is supposed to be an integer, and not a string, you can do this:

$_data = (int)$data;

This will force the value to be an integer. There’s no way to do an SQL-injection with a number alone, and this is something that gets done very quickly (as compared to the mysql_real_escape_string).