I am trying to refer the parent query fetched array in sub query of same statement. I have a news table and I want to get a specific news by its title and 10 more news which have id lower than that specific news. I want in one statement of Sql and i am php to fetch array.
<?php
// $_GET['q'] is title
include('db.php');
$result = array();
$sel = "SELECT * FROM news WHERE title = '".$_GET['q']."' "; // AND 10 MORE NEWS WHICH HAVE ID LOWER THAN THIS $_GET['q'] ID .
$qry = @mysqli_query($conn , $sel);
$num = mysqli_num_rows($qry);
while($row = @mysqli_fetch_array($qry)) {
array_push($result, array('id' => $row['id'] , 'title' => $row['title'] , 'desc' => $row['about'] , 'image' => $row['image'] , 'time' => $time , 'htitle' => $row['Htitle'] , 'habout' => $row['Habout']));
}
echo json_encode(array('result' => $result));
?>
Your original query is SELECT * FROM news WHERE title = :title
.
If you really want to use a subquery use something along the lines of
SELECT
*
FROM news
WHERE id <
(SELECT
id
FROM news
WHERE title = :title
LIMIT 1)
ORDER BY id DESC
LIMIT 10
A final note: PLEASE use parameters in your query, because you are WIDE OPEN to SQL injection (think about when $_GET['q']
has a value of ; DROP TABLE news;--
).