Nested SELECT statement (php)

[dbewick123]

Okay so i am having issues with a mySQL nested select statement. I will post code below but what i am trying to achieve is to compare a value from table 1 (books) and table2 (userbooks) and return the row from books 2 if the comparison is correct. Here is my code for the query

 

 

                        $fields = '`' . implode('`, `', $funcGetArgs) . '`'; // this will be fields from database, so values stored under book name etc.

                       

                        $bookData = mysql_fetch_assoc(mysql_query(“SELECT $fields FROM `books` WHERE `tag1` = (SELECT `tag1` FROM `userBooks` WHERE `user_id` = $user_id”)));

 

As you can see I define the $fields via $funcGetArgs so that the passed in parameters are the fields that are retrieved.

To recap I want to be able to SELECT a value from `books` compare it with a value from `userBooks` and return the row data if the two values are the same (if true is returned from the comparison). If anyone can think or a way I could do this, even if its not with the ‘mysql_fetch_assoc’ method please help.

 

Thanks

[requinix]

What you need is a JOIN.

SELECT $fields FROM `books` JOIN `userBooks` ON `books`.`tag1` = `userBooks`.`tag1` WHERE `userBooks`.`user_id` = $user_id

If any of the $fields are ambiguous as to which table it comes from (eg, tag1 is in both tables) then you need to prefix the table name (eg, books.tag1 or userBooks.tag1).

[dbewick123]

@requinix, thanks for your response, it makes a bit of sense to me...

 

however when i try and execute the line 

 

$bookData = mysql_fetch_assoc(mysql_query("SELECT $fields FROM `books` JOIN `userBooks` ON `books`.`tag1` = `userBooks`.`tag1` WHERE `userBooks`.`user_id` = $user_id")); 

 

it gives me an error saying "mysql_fetch_assoc() expects parameter 1 to be resource, boolean given". can you tell me why this is returning a boolean value, or perhaps what i am doing wrong :/ 

 

Thanks in advance 

[requinix]

That means the query failed. Unless I'm going blind I don't see anything obviously wrong.

 

Use mysql_error() to get an error message about what happened.

[Barand]

When he nests functions like that it isn't easy to check if the query failed and output MySQL_error() before continuing.