Storing array in multiple rows

Maharg105

Hey people,

Wondered if you might be able to give me a quick hand with something.

I have a form that allows a user to enter a number of keywords, seperated by commas, into a text box. So the user might type "penguin, sausage, spider, table".

What I want to do is then explode this string into an array and store each element in the array as a separate record in my keywords table.

The code I currently have is

$keywords = $_POST['keywords'];
$keywords = preg_replace("/[^a-z0-9,. ]/i", "", $keywords);

 $keywords = explode(",",$keywords);

 foreach($keywords as $keywords => $VALUE) {
 mysql_query("INSERT INTO keywords (keyword) VALUES ($VALUE)");}

Now I am probably making a very fundamental error with this code and I wondered if someone might be able to point it out for me?

As always, thanks for all your help 🙂

bradgrafelman

You're replacing several characters - one of those being a comma - with an empty string. Then, you try to explode on the comma characters... the same ones you just removed in the preceding statement.

EDIT: Okay, so I'm stupid and didn't read the code very carefully. Oops. :o

EDIT2: You've got a few problems:

You're having SQL problems, but you aren't trying to debug them. Whenever you have SQL problems, check if mysql_query() is returning false and, if so, output the error message returned by the SQL server via [man]mysql_error/man.
Your SQL error is that you didn't delimit a string value with quotes.
In addition, note that user-supplied data should never be placed directly into a SQL query, else your code will be vulnerable to SQL injection attacks (and/or just plain SQL errors). Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man (for string data).

Now on to some optimizations..

Rather than sending many queries in a loop (almost always a sign of unoptimized code), you could send a single INSERT statement that inserts multiple rows. Rather than looping through the keywords array, however, there's a neat trick you can do with [man]implode/man:

$values = "('" . implode("'), ('", $array) . "')";

// results in something like:
// ('value1'), ('value2'), ('value3')
// this allows you to do something like...
$sql = "INSERT INTO foo VALUES $values";

Maharg105

Once again Brad, you have added the metaphorical cherry to the incomplete ice cream sundae that was my code.

Thanks for that mate, you save me a little bit of my hair 🙂

bradgrafelman

Glad I could help; don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu.