Delimited Strings

[Medyman]

Hey guys...

If I have a field in a MySQL table that has a bunch of strings seperated by commas (i.e. apple, orange, banana, grape).

How would I go about querying the table for just one of those values. That is, say I want to query for all values with 'apple' as a tag.

I know the explode() will put it into an array but I don't know if that will be useful in this instance.

Thanks :)

[james438]

Sounds like you are looking for something like the following:

<?php
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password') or die(mysql_error());
mysql_select_db("mysql_user",$conn) or die(mysql_error());
$query = "SELECT * FROM tablename WHERE field like \"%apple%\"";
$res = mysql_query($query);
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
echo "$row[field]<br><br>";
}
?> In this example $res is the query and MYSQL_ASSOC will fetch the array in the alpha format such as $res[field] as opposed to the numeric format like $row[1]. It can get more advanced if you want to sort the array or search through multiple fields in multiple tables or search for multiple terms or what not, but create a test page and try out what you see here and see how you like it. Remember to make the obvious username and password changes where necessary.

[Twey]

You can do this, but you shouldn't. Your database needs normalising, multiple values should not be stored in one field.

[Medyman]

You can do this, but you shouldn't. Your database needs normalising, multiple values should not be stored in one field.

Why not? And what would the alternative be?

[Twey]

It's inefficient. You need to rethink your database design -- it needs normalising.

[Medyman]

ehh...a little "inefficiency" never killed anyone. Creating at least another table and another SQL query when one field does the job seems unnecessary.

Thanks for the info though.

[james438]

If you do not expect to be creating many new tables or adding a lot of new and/or different info to your database any time soon or later this would be fine, especially if it is going to be a mostly static site. For sites that plan on growing, especially ones that are going to grow quickly, then becoming efficient early on is rather important. If you don't then it will become a lot harder to manage later on as you find yourself creating programs just to sort all of the information in your database.

Of course it is usually better to do what makes sense to you though.

[Medyman]

Of course it is usually better to do what makes sense to you though.

Right...exactly. I'll be using that application in a pretty small scale application. The site/data will grow but nothing to a point that would warrant rethinking the organization and logic behind a 90% complete website :)

[Twey]

It's a single join query, and you have the database layer do all this for you, which is a) neater and b) more efficient.

[james438]

Here is a great article on database normalization for mysql: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

I am a bit new to normalization as well and have not heard of it before this thread, but it is a fascinating article that explains how to set up an efficient database that makes sense. I have read half of it and it appears to be a simple and not very difficult to read article. I'll get to the rest of it later, but over here it is very late at night.

I just wanted to post this for those that want to read a little more about it.

[Medyman]

Thanks...

I'll have a look :)