MySql: Refer parent query fetched array in subquery

Question

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

Focus on getting the MySQL query right. The PHP stuff is just noise for this question. — GolezTrol, Oct 03 '15 at 14:41
http://stackoverflow.com/questions/1891789/sql-select-first-10-rows-only and https://dev.mysql.com/doc/refman/5.0/en/logical-operators.html — , Oct 03 '15 at 14:48
And why are you suppressing errors instead of handling them? — , Oct 03 '15 at 14:49
This is a [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attack waiting to happen. Please do not create SQL statements by concatenating strings including user input. — Stig Brautaset, Oct 03 '15 at 15:48

score 1 · Accepted Answer · answered Oct 03 '15 at 16:02

1

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

answered Oct 03 '15 at 16:02

Hidde

11,493
8
43
68

But he doesn't need to use a subquery, does he? Can't he just do `WHERE title = :title ORDER BY id DESC LIMIT 10`? – Oct 03 '15 at 18:49
I think he also wants the 10 articles before the article with the specified title. In that case the other articles do not have the same title. – Hidde Oct 03 '15 at 18:51

MySql: Refer parent query fetched array in subquery

1 Answers1