I need some help adding a PHP code to my MySQL. Here is the code:
$query_qPosts = "SELECT * FROM front_news WHERE draft = 1 AND department_id = 1 OR department_id = 0 ORDER BY id DESC";
Where it says "department_id = 1" I need to replace 1 with <?php echo $id ?>.
$query_qPosts = "SELECT * FROM front_news WHERE draft = 1 AND department_id = $id OR department_id = $id ORDER BY id DESC";
In php you can encapsulate varibles inside double quotes and they will form part of the string. e.g
$foo = 'foo';
$echo = "I am $foo";
----> I am foo
Where as for single quotes you need to concatenate them in. E.g.
$foo = 'foo';
$echo = 'I am ' . $foo;
----> I am foo
In terms of which one you would use, it depends on the context you are using them in and normally it can be down to personal preference. Single quotes execute faster then double quotes.
If you use mysqli, it makes this process very simple:
$db = new mysqli("localhost", "user", "password", "mydatabase");
$query_qPosts = $db->prepare("SELECT * FROM front_news WHERE draft = 1 AND department_id = ? OR department_id = 0 ORDER BY id DESC")
$id = 1;
$query_qPosts->bind_param("i",$id)
if (!$query_qPosts->execute()) {
echo "There was an error (" . $query_qPosts->errno . ") " . $query_qPosts->error;
}
You have to group with parenthesis when you use "AND" and "OR" together in a SQL query. Otherwise the results are unpredictable or the query fails.
If department can be 0 or 1 and draft has to be 1 do this:
"SELECT * FROM front_news WHERE draft = 1 AND (department_id = 1 OR department_id = 0) ORDER BY id DESC"
or if draft has to be one and department has to be 1 or department is 0, try this:
"SELECT * FROM front_news WHERE (draft = 1 AND department_id = 1) OR department_id = 0 ORDER BY id DESC"
I'm not sure which you want to do.
You are doing double quotes so every "$" inside the code is executed as a variable. To ensure that happens you can wrap in curly braces if you want "{$id}".
try
"SELECT *
FROM front_news
WHERE draft = 1
AND department_id = ".<?php echo (int)$id?>."
OR department_id = 0
ORDER BY id DESC";
$query_qPosts = "SELECT * FROM front_news WHERE draft = 1 AND department_id = 1 OR department_id = ".$id." ORDER BY id DESC";
Try this code:
$query_qPosts = "SELECT * FROM front_news WHERE draft = 1 AND department_id = $id OR department_id = 0 ORDER BY id DESC";
Please to make sure that $id is number and not null
