mysqli, prepared statements, and INSERT-SELECTs
mysql, mysqli, php, prepared-statement
Solution
First you create the statement very much like a normal statement you have made
$stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text)
SELECT c.id, ?
FROM categories AS c WHERE c.id = ?;");
Get the statement bound to the parameter 's' stands for string data and i for integer
$stmt->bind_param('si', $joke_text,$category_id); // bind to the parameters
/* execute prepared statement */
$stmt->execute();
Problem
Let's pretend that I have two tables in an InnoDB database: `categories` and `jokes`; and that I'm using PHP/MySQLi to do the work. The tables look like so: ``` 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 a previous answer on here, I discovered that you could do the following in order to add a new joke comprised 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 enables me, without the use of foreign keys, to be sure that the `$category_id` value refers to an existing category (please ignore the issue of foreign keys, as my question is aimed at helping me learn "complicated" prepared statements). So that worked just fine. However, I am now trying to learn prepared statements and, after spending all day on it, I finally have the basics down. Unfortunately, I have ABSOLUTELY NO IDEA how to to execute the above SQL query with prepared statements, under mysqli, and I have not been able to find any info online regarding such an issue. If anyone can help me out, I'd be very appreciative.