MySQL: Limit output according to associated ID

Question

So here's my situation. I have a books table and authors table. An author can have many books... In my authors page view, the user (logged in) can click an author in a tabled row and be directed to a page displaying the author's books (collected like this URI format: viewauthorbooks.php?author_id=23), very straight forward... However, in my query, I need to display the books for the author only, and not all books stored in the books table (as i currently have!) As I am a complete novice, I used the most simple query of:

SELECT * FROM books 

This returns the books for me, but returns every single value (book) in the database, and not ones associated with the selected author. And when I click a different author the same books are displayed for them...I think everyone gets what I'm trying to achieve, I just don't know how to perform the query. I'm guessing that I need to start using more advanced query clauses like INNER JOIN etc. Anyone care to help me out :)

Answer 1

The basic syntax is SELECT * FROM table WHERE column=value;

But you really need to study more SQL, I'd suggest going through sqlzoo tutorial. http://sqlzoo.net/

Answer 2

Enters the WHERE clause:

The WHERE clause is used to extract only those records that fulfill a specific criteria. In your case, all you need to do is:

SELECT * FROM tasks_tb WHERE author_id = '23';

You will obviously need to change the '23' with the value passed in the URL querystring, so that each page lists the books of each relevant author.

Since it is never too early to start reading about best practices, note that for public websites it is really dangerous to include any un-sanitized input into an SQL query. You may want to read further on this topic from the following Stack Overflow posts:

• XKCD sql injection - please explain (with pictures!)
• What is SQL injection?
• Is SQL injection a risk today?
• SQL Injection Topics on Stack Overflow