I can't retrieve the values from database

Question

$raw_results=mysql_query("SELECT resort_name FROM resorts WHERE resort_id=(SELECT resort_id FROM resort_place WHERE place_id=(SELECT place_id FROM place WHERE place='$query')) ") or die(mysql_error());
$check_num_rows=mysql_num_rows($raw_results);
$solutions = array();
while($row = mysql_fetch_assoc($raw_results)) {
$solutions[] = $row['solution'];
}

This is my code and it returns an error message like

Warning: mysql_query() [function.mysql-query]: Unable to save result set in C:\xampp\htdocs\search\news.php on line 131 Subquery returns more than 1 row

can any one help me to retrieve the values from the data base...

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Kermit, Jan 14 '13 at 22:33

score 1 · Answer 1 · edited May 23 '17 at 11:56

this will yield the same result with you multiple subquery.

SELECT  DISTINCT a.resort_name 
FROM    resorts a
        INNER JOIN resort_place b
            ON a.resort_id = b.resort_id
        INNER JOIN place c
            ON b.place_id = c.place_id
WHERE   c.place='$query'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score 0 · Answer 2 · answered Jan 14 '13 at 22:35

Use prepared statements using mysqli_ or PDO functions instead. Your query can be accomplished using an explicit JOIN:

SELECT DISTINCT resorts.resort_name
FROM resorts
JOIN resort_place ON resort_place.resort_id = resorts.resort_id
JOIN place ON place.place_id = resort_place.place_id
WHERE place.place = '$query'

score 0 · Answer 3 · answered Jan 14 '13 at 22:36

use IN operator like place_id in (your sub query here)

  $raw_results=mysql_query("SELECT resort_name FROM resorts WHERE resort_id IN 
       (SELECT resort_id FROM resort_place WHERE place_id IN 
            (SELECT place_id FROM place WHERE place='$query')
       ) 
  ") or die(mysql_error());

score 0 · Answer 4 · answered Jan 14 '13 at 22:44

0

The other answers are wise, you could do better than nesting queries.

If you really want to do AND my_column_id = (SELECT something FROM ...) make sure that the subquery returns only one row, maybe by ending it with LIMIT 0, 1.

answered Jan 14 '13 at 22:44

Nicolas Dermine

628
5
8

I can't retrieve the values from database

4 Answers4