how to avoid mysqli prepared statement everytime in php

Question

I am newbie to php. I have a php file which would insert values to mysql table. So considering the efficiency that this would be hit very frequently, I decided to go with mysqli_prepare statement in my php file.

I am wondering for everytime this php file is executed, it would prepare statement which might be a burden which is not necessary. Is there any way to avoid it by preparing the statement only once or Am I missing some concept of prepared statement?

Thanks, Marutha

The simple act of creating a prepared statement is not a burden. Where did you learn this? — Charles, Nov 29 '12 at 07:33
I am newbie. so read the wikipedia article about prepared statement. I read that prepared statements would be parsed, compiled query optimized by DBMS and shall be kept in cache for later execution. So I came to conclusion that when ever you prepare the statement it would be parsed,compiled by DBMS and thought that would be expensive to prepare every time. Appreciate if you could throw some information on how this is handled. — Marutha, Nov 30 '12 at 05:49
For MySQL, prepared statements only last as long as the statement handle is open. Are you perhaps thinking of stored procedures? It used to be that it was best practice to do most of the work in stored procs, as the parsing of SQL and creation of a query plan could be a real burden in comparison. It's been over a decade since that was best practice. Until you know, for *measurable* fact, that your way of creating queries is a performance burden, don't pay it any mind. Use prepared statements for the nifty value placeholders, and make sure your data is indexed, and you'll be just fine. — Charles, Nov 30 '12 at 05:58

score 1 · Answer 1 · answered Nov 29 '12 at 07:31

1

mysqli_query

But i use PDO and my code seems like this:

$query = 'INSERT INTO tbl (param, new_param) VALUES (:param, :new_param)';
$stmt  = DB::prepare($query);

foreach( $data as $param => $new_param )
{
  $stmt->execute(':param' => $param, ':new_param' => $new_param);
}

OR like this

DB::query("INSERT "); // PDOChild::getInstance()->query("INSERT ");

If you insert data from users input you should to prepare statement...

answered Nov 29 '12 at 07:31

Nikita Melnikov

209
1
8

can you post the entire code? you must be calling DB::prepare($query) quite often if your code is executed frequently. – Marutha Nov 29 '12 at 07:34
@Maruthavanan entire code of DB class or entire code of DB::prepare($query) method? – Nikita Melnikov Nov 29 '12 at 07:42

score 0 · Answer 2 · answered Nov 29 '12 at 07:40

You may want to consider caching SQL result, or caching entire php script output (page cache) if script do not contain any changeable information.

For caching check more about it here.

If you are using some kind of php framework like ZendFramework, Symfony, Kohana, they usually have much more flexible caching handlers.

how to avoid mysqli prepared statement everytime in php

2 Answers2