Inserting multiple SQL statements in codeIgniter

Question

I have the following SQL that works directly in MySQL

 INSERT INTO `my_tabel` (`data`) VALUES ("my_value");
 SELECT * from `my_tabel` ORDER BY `id` DESC LIMIT 1

It inserts a row, and then gets the new row so that I can return the new ID to use later.

When I try to run the SQL in codeIgniter I get an error message stating that I have an error in my SQL

$m = new my_model();
$sql = 'INSERT INTO `my_tabel` (`data`) VALUES ("'.$my_value.'"); SELECT * from `my_tabel` ORDER BY `id` DESC LIMIT 1';
$m->query($sql);

Running a single SQL statement works fine in codeIgniter, but not when I add the second SELECT... statement.

Any ideas (or alternative solutions)?

Thanks

Don't put values in queries like that, it's very unsafe. Do something like this: $m->query('INSERT INTO `my_tabel` (`data`) VALUES (?); SELECT * from `my_tabel` ORDER BY `id` DESC LIMIT 1', array($my_value));. More info at the bottom of this page: codeigniter.com/user_guide/database/queries.html — Oscar Broman
– Oscar Broman, Commented May 17, 2012 at 16:13
in php I'm adding mysql_real_escape_string($value), I didn't add it to the question to keep the code simpler. I can see your solution adds security, so I will add that too — Eruant
– Eruant, Commented May 17, 2012 at 16:20
Thing is, if you use the question marks, you don't need to use mysql_real_escape_string anymore. — Oscar Broman
– Oscar Broman, Commented May 17, 2012 at 16:37

Rahul · Accepted Answer · 2012-05-17 16:06:15Z

2

This is not a limitation of CI but that of the database client libraries.

only a single query can be executed at a time.

answered May 17, 2012 at 16:06

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

1 Comment

He Hui Over a year ago

+1 PHP only allows a query to run 1 command at a time, in a single string. If one wants to run multiple queries, you'll need to use the multi_query method. But since you're using CI, thats not recommended.

wallyk · Accepted Answer · 2012-05-17 16:02:47Z

0

Why not run two $m->query() statements? That way you can check the success of each individually from CI.

In most SQL interfaces, having multiple statements in a query is problematic because of the different types of result from each. Even when calling a stored procedure, it is necessary to carefully craft the stored procedure not to return extraneous results, though even that can usually be handled with a special, relaxed api which allows multiple results.

answered May 17, 2012 at 16:02

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

3 Comments

Eruant Over a year ago

Originally that was how I set it up, but I get the same error message. Running them both in separate functions works, but not when they are called at the same time.

wallyk Over a year ago

@Eruant: Huh? How can you call separate functions at the same time?

Eruant Over a year ago

Sorry to confuse. If I run the insert command on it's own it works. If I then edit the function to only run the select command, that works too. The problem happens when a write them both into the same function.

Nick Pyett · Accepted Answer · 2012-05-17 16:06:33Z

0

insert_id() is your friend!

$this->db->insert_id()

From here http://codeigniter.com/user_guide/database/helpers.html

So something like:

$insert_data = array(
  'data' => $my_value
);

$this->db->insert('my_table', $insert_data);

$last_id = $this->db->insert_id();

answered May 17, 2012 at 16:06

Nick Pyett

3,4181 gold badge26 silver badges27 bronze badges

Comments

Adam · Accepted Answer · 2012-05-17 16:11:27Z

0

Instead of running 2 queries, to get the id created after the insert query use:

$this->db->insert_id();

edited May 17, 2012 at 16:11

answered May 17, 2012 at 16:04

Adam

1,69414 silver badges18 bronze badges

Comments

Eric Cope · Accepted Answer · 2012-05-18 05:19:40Z

0

As noted in the other answers, use $this->db->insert_id() is better because there is a chance that another user inserts a row between your two queries. Since PHP can only execute one query at a time, this can become a race condition.

answered May 18, 2012 at 5:19

Eric Cope

8871 gold badge12 silver badges26 bronze badges

Comments

Eruant · Accepted Answer · 2012-05-18 08:22:37Z

0

Thanks for everyone's help. The solution that worked for me was

$m = my_model();
$m->data = $value;
$m->save();
echo $m->id;

answered May 18, 2012 at 8:22

Eruant

1,8462 gold badges14 silver badges22 bronze badges

Comments

ChristoKiwi · Accepted Answer · 2015-05-04 05:11:58Z

0

http://www.codeigniter.com/userguide2/database/active_record.html#caching You can use Caching to control multiple queries without conflicts.

answered May 4, 2015 at 5:11

ChristoKiwi

2895 silver badges10 bronze badges

Collectives™ on Stack Overflow

Inserting multiple SQL statements in codeIgniter

7 Answers 7

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related