Using explode function in PHP and insert into a database using for loop

Question

Here's what I want to do in three steps.

1-) Get names from the form. Names are being entered into the form using a comma between them.

2-) Get the ID's of the names from the database.

3-) Insert these names into a table.

I hope I made clear what I want to do.

Here's what I've done so far. (I am working with a single page, excluded the form here)

$writer_name = "Franz Kafka, Agatha Christie, George Bernard Shaw"; // I used a
//variable instead of using a form value here
$thing_id = 10;

$values_select = explode(",", $writer_name);
echo $values_select[0]; // returns Franz Kafka, our explode function worked

$who = "SELECT writer_id FROM writer_name WHERE writer_name = '$values_select[]'"; 

// I stuck here, I need to get all the writer's ID's and store it in an array 
// in order to insert below X times (depends on how many writers are entered), right? But how?

$who_result = $sqli->query($who);
$who_row = $who_result->fetch_array();
$who_finish = $who_row['writer_id']; 

$values = explode(",", $writer_name);
$control = count($values) - 1;
$new_values = $sqli->prepare("INSERT INTO book(thing_id, person_id) VALUES(?,?)");
for ($i = 0; $i < count($values); $i++) {
    if (!empty(trim($values[$i]))) {
        $new_values .= "('" . trim($values[$i]) . "')";
    }
    if ($control == $i) {
        $new_values .= ";";
    } else $new_values .= ",";
}
    $thing->bind_param("ii", $thing_id, $values[$i]); // using integer because I insert data into a junction table that accepts only ID values)
    $thing->execute();

Use WHERE writer_name IN ($writer_name) You can pass IN comma separated lists and it'll return those records writer_name is in the list. See example:techonthenet.com/sql/in.php — i-CONICA
– i-CONICA, Commented Jan 2, 2015 at 14:18
Also, SQL is much more powerful than you think. Most of your PHP code can be ditched and the work offloaded to the DB. — i-CONICA
– i-CONICA, Commented Jan 2, 2015 at 14:19
... = '$values_select[]' is "impossible" code. You're generating the literal query text ... = 'Array[]', which will match nothing. — Marc B
– Marc B, Commented Jan 2, 2015 at 14:41
@Marc B, I used it as a dummy text to show it, didn't know what to put here. — salep
– salep, Commented Jan 2, 2015 at 14:57

Sergiy T. · Accepted Answer · 2015-01-02 14:46:47Z

1

You may query all array values:

$who_finish = array();
while(list($k, $v)=each($values_select)) {
    $v = $sqli->real_escape_string(trim($v));
    $who = "SELECT writer_id FROM writer_name WHERE writer_name = '$v'";
    $who_result = $sqli->query($who);
    $who_row = $who_result->fetch_array();
    $who_finish[] = $who_row['writer_id'];
}

or better (btw do you check for typos etc.?) is to query all at once like this: SELECT writer_id, writer_name FROM writer_name WHERE writer_name IN ("Franz Kafka", "Agatha Christie", "George Bernard Shaw")

To insert values into table (I do not understand your code well so assuming here):

$new_values_q = $sqli->prepare("INSERT INTO book(thing_id, person_id) VALUES(?,?)");

while(list($k, $v)=each($who_finish)) {
    $new_values_q->bind_param("ii", $thing_id, $v); // $v should be author_id
    $new_values_q->execute();
}
$new_values_q->close();

edited Jan 2, 2015 at 14:46

answered Jan 2, 2015 at 14:22

Sergiy T.

1,4531 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

salep Over a year ago

I don't check typos for now(but will), all records are being entered correctly since I copy/paste values in bulk from the same resource. I am trying your solution now.

salep Over a year ago

This query works, but I can't still make the insert. echo $who_finish[0]; // gives first writer's ID echo $who_finish[1]; // gives second writer's ID echo $who_finish[2]; // gives third writer's ID But how can I insert these using a loop?

Sergiy T. Over a year ago

@salep I added insert piece to answer, but i do not understand your code well so I made assumption of what you need.

naveen · Accepted Answer · 2015-01-02 14:39:42Z

1

it may be helpful, not tested.

 $writer_name = "Franz Kafka, Agatha Christie, George Bernard Shaw";

$values_select = explode(",", $writer_name);

foreach($values_select as $sel){
    $query = "INSERT INTO book (thing_id, person_id)
SELECT $thing_id, location
FROM   writer_name
WHERE  writer_name  = ".trim($sel);
$sqli->query($query);
}

answered Jan 2, 2015 at 14:39

naveen

1,0681 gold badge13 silver badges26 bronze badges

Collectives™ on Stack Overflow

Using explode function in PHP and insert into a database using for loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related