php mysql save mysql with for loop

Question

TABLE:

09:00 -- id_timeslot = 1
09.15 -- id_timeslot = 2
09.30 -- id_timeslot = 3
09.45 -- id_timeslot = 4
10.00 -- id_timeslot = 5

PHP MYSQL:

for($i=0; $i<=2; $i++) {  
  $mysqli->query("INSERT INTO bookslot(id_timeslot, id_member, b_ref, id_doctor, status)   
  VALUES  ('" . ++$id_timeslot . "', '" . $id_member . "', '" . $b_ref . "', '" . $id_doctor . "', 1)" )  
}

I want the data to be saved twice and increment the id_timeslot.

The above code working fine, but when save cliked. it didnt pick up the right id_timeslot?

for example: if user click on id_timeslot:1, soon it save to database the id_timeslot starts from 2 instead of id_timeslot 1?

outis · Accepted Answer · 2011-02-21 19:17:46Z

if user click on id_timeslot:1, soon it save to database the id_timeslot starts from 2 instead of id_timeslot 1?

This is because you're using a pre-increment rather than a post-increment. If $id_timeslot is 1 before entering the loop, the value of ++$id_timeslot is 2, so the first generated query is:

"INSERT INTO bookslot(id_timeslot, id_member, b_ref, id_doctor, status)   
 VALUES  ('2', '$id_member', '$b_ref', '$id_doctor', 1)"

If the id_timeslot column is supposed to be an ID for the bookslot record, the best approach is to declare it with the AUTO_INCREMENT attribute and don't insert values for that column:

-- run just once in some MySQL client
ALTER TABLE bookslot MODIFY id_timeslot INT UNSIGNED PRIMARY KEY AUTO_INCREMENT;

// in PHP
$stmt = "INSERT INTO bookslot(id_member, b_ref, id_doctor, status)   
         VALUES  ('$id_member', '$b_ref', '$id_doctor', 1)";

When using double quoted strings, you don't need to concatenate variables. Compare the above statement with your own.

If id_timeslot isn't a unique ID, then you can simply switch to post-increment.

$stmt = "INSERT INTO bookslot(id_timeslot, id_member, b_ref, id_doctor, status)   
         VALUES  (" . $id_timeslot++ . ", '$id_member', '$b_ref', '$id_doctor', 1)";

This may or may not be a correct approach for various other reasons. Without knowing more about the schema, it's impossible to say.

Off Topic

Depending on where the values for $id_member, $b_ref $id_doctor originate, your script could be open to SQL injection. Rather than inserting values directly into the string, use a prepared statement. MySQLi supports them, as does PDO, which is simpler to use. New code should use PDO, and old code should get updated over time.

hsz · Accepted Answer · 2011-02-21 16:57:58Z

0

You have to fetch last id_timeslot from database and then increase it PHP during inserting.

answered Feb 21, 2011 at 16:57

hsz

153k63 gold badges268 silver badges320 bronze badges

Collectives™ on Stack Overflow

php mysql save mysql with for loop

2 Answers 2

Off Topic

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Off Topic

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related