Anyone want to share favorite coding techniques?

[Vigilante]

There are a lot of general practices in programming theory one can make use of to make things more efficient or just possible.

Of particular interest are php tips but something that carries over across languages would be pretty handy too.

Here's a technique that came to me as soon as I read the page for explode() on php.net. I've seen some code with data storage limitations that obviously does not use this technique for storing data that is extremely handy, if not always easy to access. Maybe someone out there will find this useful. Chances are, if you're reading this you're already motivated enough to learn that you already know it, in which case I hope you'll also share a useful technique.

Stick different data in one string instead of separate fields. This saves database queries and overhead because instead of 10 int fields you can have one varchar that holds ten ints separated by a delimiter. When I want to manage the string with str_replace(), the lazy way to do it, I would use '|' or something as a delimiter and store strings like this:

$str = '1244|1245|1320|';

Then split the string with explode()

$arr = explode('|', $str);

In the case of SQL queries I've frequently stored IDs in this way. Once you have the IDs in an array I build a query with them.
$where = '';
foreach ($arr as $id) {
$where .= " id=$id OR";
}
$where = substr($where, 0, -3);
mysql_query("SELECT * FROM table WHERE $where");

That's it for me. So.. does anyone have a fancier way of efficiently storing similar data or a useful algorithm or anything? I always want to learn more.

[piersk]

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10240608
http://www.phpbuilder.com/board/forumdisplay.php?s=&forumid=24

[ahundiak]

Vigilante,
You might look up the IN operator:

$personIDs = '22,55,77';
$sql = "SELECT * from person WHERE person_id IN ($personIDs)";

[LordShryku]

I've never agreed with that method. It completely goes against any reason to have a relational database. So what happens when you need to delete from there? You'll have to do fairly silly things to that string that you wouldn't have to do if you spilt it up into seperate records. Then, if your tables get more complicated, and the need to join ever comes about, you really can't with that string of id's. And what about math? If you needed to do some basic math in your SQL query, you can't, because those are no longer number fields. Plus, you're really not saving that much space. A couple of bytes per record at most. Anyway, when I first got into databases, I tried something like that, and within a week needed to tear it down and do it right. It'll eventually cause you more haedaches than it will do good.
</soapbox>

[stolzyboy]

yep, what they said, and usually, SQL is faster than using the language itself...

always do your searching with SQL, good db's can handle millions of records, and if you set up relations in a good coherent way, you won't get into trouble with result sets anyway

[Vigilante]

When you need to delete from it you load the string into an array and rebuild the array with one less of the ID you want to take out. Not as simple as deleting a row but unless your string has 10000 ids you probably won't care after you've written the function that does that.

I recently wrote a 'related topics' feature for my message board. When you create a topic it searches and topic title search index and puts topic IDs from matches in a field stored as previously described. This way uses one varchar field that you would never need to JOIN anyways. By one simple setting I can control whether the maximum "related topics" are five, ten or twenty. That's more flexible then creating a separate int field for each topic id. Since the number of IDs is always low and you don't delete from the string I'd say it's the most efficient way to store them.

I used this for storing my users inventories on my sites rpg system and some users have more then 1000 items with no discernible slowdown. The only comparable way I could think of managing item inventories is a user_items table which uses a new record for each item and stored the user id and item id. Probably no significant functional difference.

[stolzyboy]

but why would you wanna take the time to do it in the first place...

and when you start getting into the hundreds of thousands of records, you WILL want to use SQL more, it will be noticeably faster...

i just don't see the need to store ID's like 5465|55654|1551

seems kinda silly to me, let SQL do the work for you...

[LordShryku]

You've never had to do math, like even count()-ing id's? Sorry, it's just not optimal. It defies the need to even use a database. In fact, if all you have is a big string, you'd be better off storing that in a text file. And it seems to me this kind of metality comes in play only for people who do things like this with a front end. Now, I do half of my work right out of a SQL prompt, so to get the information I needed, I couldn't use that sort of setup, because SQL can't parse that string as seperate records.

Tim Perdue agrees (http://www.devarticles.com/c/a/PHP/A_Test_To_See_If_You_Write_Sloppy_Software/1/)

Relational Database

Another good sign of bad design is using explode()/implode() on your data before accessing the database. If you are storing a bunch of values in one big text field, say a big list of states, that's a horrible design. In the original "drivers" table above, the states this driver is available in is stored as a comma-separated list in one field. The proper solution is to create a third table like this:

Driver_states
----------------
driverid int, (relates to drivers table)
state_id char(2) (standard 2-digit state code)

Now that single awful table is broken into three properly-normalized database tables. This may sound like a pain to do, but later if you expand your application, you will appreciate having properly-formatted data.

[Vigilante]

Actually for the item inventory I think a user_items table would have done the trick easier.

As for related topics I'd just hate to use five to twenty int fields when one varchar would do it and it never needs to be counted or modified. I did it the same way for thread subscriptions and ignored forums, in both cases I assumed there would be more overhead in creating more tables and querying them just storing a few user-specific IDs in a varchar field.

I guess explode has precious few uses when you have a database.

Even though no one posted anything except to criticize my post I'm glad I posted this, I'll reconsider next time I want to use explode().

[dalecosp]

How 'bout "never return a variable when you can return an array" ??