需要帮助创建SQL查询的MySQLi PHP
我有两个表 COMPANY:COMPANY_ID,COMPANY_NAME 导读:REVIEW_ID,COMPANY_ID,REVIEW_TEXT需要帮助创建SQL查询的MySQLi PHP
我努力让自己在网站的搜索功能。我尝试显示特定公司的所有评论。例如,我有搜索字段,我进入那里“微软”,然后我需要关于微软的所有评论。
我尝试做这样的查询,但它不工作
$company_name = $_POST["company_name"] ?? '';
$review_text = $_POST["review_text"] ?? '';
$searchc = $_POST["searchc"] ?? '';
if(isset($_POST['show'])){
$result2 = $mysqli->prepare(
"SELECT review_text
FROM review
WHERE company_id
IN (HERE I THINK I NEED EMBED QUERY... BUT NOT SURE..) ?");
$result2->bind_param("s", $searchc);
$result2->execute();
$result2->store_result();
$row2 = mysqli_fetch_assoc($result2);
}
<div class="right">
<form method="post">
<br/>
<input name="searchc" type="text" value=""><br/>
<input type="submit" value="show" name="show">
</form>
Text: <?=$row2['review_text']?><br />
</div>
你只需要执行INNER JOIN
像
SELECT review_text
FROM review
JOIN COMPANY on review.company_id = COMPANY.company_id;
好,万一你得到的公司名称作为输入,然后你几乎接近
SELECT review_text
FROM review
WHERE company_id
IN (select company_id from company where company_name = $company_name)
你可以在你的表上执行SQL连接
$sql = "SELECT review.*, company.company_name
FROM review
INNER JOIN company ON review.company_id = company.company_id WHERE
company.company_name = ?";
$result2->bind_param("s", $searchc);
请用绑定变量语法更新您的答案。 – gview
我已更新它。 –
不幸的是没有为我工作。在我的代码中,我使用了这个 - $ result2 = $ mysqli-> prepare(...我试图添加你的查询sql来准备,但是不能正常工作 –
试试这个:
SELECT
REVIEW.*, COMPANY.company_name
FROM
REVIEW
INNER JOIN COMPANY ON REVIEW.company_id = COMPANY.company_id
WHERE
COMPANY.company_name = "$search";
结果与$搜索= '苹果':
"4" "2" "Apple Review 1" "Apple"
"5" "2" "Apple Review 2" "Apple"
"6" "2" "Apple Review 3" "Apple"
试试这个:
SELECT
REVIEW.*, COMPANY.company_name
FROM
REVIEW
INNER JOIN COMPANY ON REVIEW.company_id = COMPANY.company_id
WHERE
COMPANY.company_name = "$search";
结果与$搜索=“苹果':
"4" "2" "Apple Review 1" "Apple"
"5" "2" "Apple Review 2" "Apple"
"6" "2" "Apple Review 3" "Apple"
EDIT 1:
我使用PostgreSQL,在我所要告诉你,唯一的区别,就是当你创建表和插入数据。执行你所需要的SQL语句是一样的。
首先,创建2个表company
和review
CREATE TABLE company
(
company_id integer NOT NULL,
company_name character varying(255) NOT NULL,
CONSTRAINT company_pkey PRIMARY KEY (company_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE company
OWNER TO postgres;
兼评表:
CREATE TABLE review
(
review_id integer NOT NULL,
company_id integer NOT NULL,
review_text character varying(255),
CONSTRAINT review_pkey PRIMARY KEY (review_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE review
OWNER TO postgres;
在已经创建的表,在其中插入数据。对于你的问题我用下面...
Company Table:
-----------------------------
company_id | company_name
-----------------------------
| 1 | Microsoft |
| 2 | Apple |
| 3 | NASA |
-----------------------------
兼评表:
-----------------------------------------------------
review_id | company_id | review_text |
----------------------------|------------------------
| 1 | 1 | Microsoft Review 1 |
| 2 | 1 | Microsoft Review 2 |
| 3 | 1 | Microsoft Review 3 |
| 4 | 2 | Apple Review 1 |
| 5 | 2 | Apple Review 2 |
| 6 | 2 | Apple Review 3 |
| 7 | 3 | NASA Review 1 |
| 8 | 3 | NASA Review 2 |
| 9 | 3 | NASA Review 3 |
-----------------------------------------------------
在此之后,您可以查看下面的SQL语句的输出与您的SQL管理员(Navicat的,MySQL工作台或者任何你使用)... 新的SQL查询和你写的:
SELECT
review.*, company.company_name
FROM
review
INNER JOIN company ON review.company_id = company.company_id
WHERE
company.company_name = 'Apple';
,输出将...
---------------------------------------------------------------------
review_id | company_id | review_text | company_name|
---------------------------------------------------------------------
|"4" | "2" | "Apple Review 1" |"Apple" |
|"5" | "2" | "Apple Review 2" |"Apple" |
|"6" | "2" | "Apple Review 3" |"Apple" |
---------------------------------------------------------------------
要在您的PHP代码中使用该SQL语句,它很简单...
$result2 = $mysqli->prepare("SELECT
review.*, company.company_name
FROM
review
INNER JOIN company ON review.company_id = company.company_id
WHERE
company.company_name = $company_name");
....
首先,没有办法以你没有描述过的方式工作。其次,该语法不适用于PHP。 – gview
你能告诉我如何使用我的代码使用这个查询?如果我添加这个SQL查询,而不是我的,它不工作,可能是因为我使用php7和占位符和绑定功能? –
查看我的编辑.... –
你需要这样的嵌套查询:
$result2 = $mysqli->prepare("SELECT review_text FROM review WHERE company_id
IN (SELECT company_id from company where company_name=?");
$result2 = bind_param("s", $searchc);
$result2->execute();
他不必须从用户输入 – Ice76
@ Ice76公司ID,请在答案..希望编辑现在有意义 – Rahul
是查询看起来不错,但如果我尝试在我的代码中使用它,它不工作......我的代码使用占位符? '',可能是这个原因吗? –