Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

How to explode string into pieces then insert into mysql?

I want to insert every word by the sentence, then insert into mysql database. But I am not sure what is the length of the $str, it may be 4 words, also can be 10 words.

So how to make a foreach, insert each word into a database (each word in one line), Thanks.

$str = "This is a test";
$piece = explode(' ',$str);
$num=0;
for(){
...
mysql_query("INSERT INTO test (words) SELECT '".addslashes($piece[$num])."' FROM dual WHERE not exists (SELECT words FROM test WHERE test.words = '".addslashes($piece[$num])."')");
$num++; 
}
share|improve this question
1  
If your installation supports it, you might want to use PDO and then you can create the query and simply update your params for each iteration. – ldg Aug 28 '11 at 21:47
I have PDO, but I have never studied it. Thanks. – fish man Aug 28 '11 at 21:58

4 Answers

up vote 1 down vote accepted 
$str = "This is a test";
$piece = explode(' ',$str);
foreach($piece as $substr){
    mysql_query("INSERT INTO test (words) SELECT '".mysql_real_escape_string($substr)."' FROM dual WHERE not exists (SELECT words FROM test WHERE test.words = '".mysql_real_escape_string($substr)."')");
}
share|improve this answer
up vote 2 down vote

Pretty easy, also don't use addslashes(), I'd use mysql_escape_string() for everything mysql related.

$str = "This is a test"; 
$pieces = explode(' ', $str);
foreach($pieces as $piece)
    mysql_query('insert into test (words) values (\'' . $piece . '\');');

(Of course you can add more conditions again, e.g. ensure words are unique, etc.)

foreach() is rather easy to use:

foreach($array as $value)

or

foreach($array as $key => $value)

where $key and $value are updated each iteration.

share|improve this answer
up vote 1 down vote 

$str = "This is a test";

// make sure there are only letters and spaces in your text (prevents
// something like "demo," in your database
$str = preg_replace("/[^a-zA-Z0-9\ ]/", " ",$str);

// explode it
$words = explode(' ',$str);

// make sure we have unique words
$words = array_unique($words);

foreach ($words as $word) {
    mysql_query("INSERT INTO test (word) values ('".$word."')";
}
share|improve this answer
up vote 1 down vote

Similarly, if you want to decrease the number of queries you run (which, in turn, will decrease the amount of time your script takes to run):

<?php
$str="This is a test";
$words=explode(' ',mysql_escape_string($str));
$query="insert into test (words) values ('".implode("'),('",array_unique($words))."')";
$result=mysql_query($query);
?>

It essentially replaces all spaces with '),(' and then wraps the whole thing into a query

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.