Mysqli prepared statements and INSERT-SELECTs

Question

Suppose I have two tables in an InnoDB database: categories and jokes ; and that I use PHP / MySQLi to do the job. The tables look like this:

 CATEGORIES id (int, primary, auto_inc) | category_name (varchar[64]) ============================================================ 1 knock, knock JOKES id (int, primary, auto_inc) | category_id (int) | joke_text (varchar[255]) ============================================================================= empty 

Thanks to the previous answer here, I found that you can do the following to add a new joke consisting of: $joke_text , $category_id .

 INSERT INTO jokes (category_id, joke_text) SELECT c.id, '$joke_text' FROM categories AS c WHERE c.id = $category_id; 

This allows me, without the use of foreign keys, to be sure that the value of $category_id belongs to an existing category (please ignore the problem of foreign keys, as my question is aimed at helping me study the "complex" prepared statements).

It so happened that everything is fine. Nevertheless, I am now trying to learn the prepared statements and, having spent the whole day on this, finally, I have the basics. Unfortunately, I have ABSOLUTELY NO IDEA how to execute the above SQL query with prepared operations in mysqli, and I could not find any information on the Internet regarding such a problem.

If anyone can help me, I will be very grateful.

Answer 1

First you create an expression very similar to the usual expression you made

 $stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text) SELECT c.id, ? FROM categories AS c WHERE c.id = ?;"); 

Getting the operator associated with the 's' parameter means string data and I for integers

 $stmt->bind_param('si', $joke_text,$category_id); // bind to the parameters 

/ * execute the prepared statement * /

 $stmt->execute(); 

Answer 2

I actually wrote the code, but I realized that you were also looking for it on the Internet. So instead, I got a link to a PHP site where you can see many of them.

Prepared Statements

mysqli in general

Since you are learning MySQLi, why not just move on to PDO. It is very similar, but I think where the future of this is directed.

http://php.net/manual/en/book.pdo.php

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers