CODEWITHSUNDEEP
phpmysqlsqldatabase
user1978657
user1978657

Reputation: 1

I can't retrieve the values from database

$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...

Upvotes: 0

Views: 108

Answers (4)

Nicolas Dermine
Nicolas Dermine

Reputation: 648

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.

Upvotes: 0

Sree
Sree

Reputation: 971

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

Upvotes: 0

John Woo
John Woo

Reputation: 263803

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.

Upvotes: 1

Kermit
Kermit

Reputation: 34063

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'

Upvotes: 0

Related Questions