mysqli，准备好的语句和INSERT-SELECTs [英] mysqli, prepared statements, and INSERT-SELECTs

问题描述

假设我在 InnoDB 数据库中有两个表:categories和jokes;并且我正在使用 PHP/MySQLi 来完成工作.表格看起来像这样:

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

感谢 上的答案，我发现您可以执行以下操作来添加一个新的笑话，其中包括:$joke_text，$category_id.

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;

这使我能够在不使用外键的情况下确保$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).

所以效果很好.但是，我现在正在尝试学习准备好的语句，经过一整天的学习，我终于掌握了基础知识.不幸的是，我绝对没有想法如何在mysqli下使用准备好的语句执行上述SQL查询，而且我无法在线找到有关此问题的任何信息.

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.

推荐答案

首先，您创建的语句非常类似于您创建的普通语句

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 = ?;");

获取绑定到参数s的语句代表字符串数据，i代表整数

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();

这篇关于mysqli，准备好的语句和INSERT-SELECTs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！