I am writing an app that interacts with a MySQL database.
I have 3 tables, 'categories', 'books' and 'book_category'.
/* stores properties for categories */
`categories` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(140) NOT NULL UNIQUE,
PRIMARY KEY (`id`)
);
/* stores properties for books */
`books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` int(11) NOT NULL,
`read` tinyint(1) NOT NULL default 0,
`creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
/* matches books against categories */
`book_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
I have 2 variables to perform the search.
<?php
$categoryString = 'python'; // books saved under python
$readBool = 1; // all books that have been read
?>
I would like to write the following SQL query:
Select all python books that have been read(i.e, book.read = 1).
My SQL knowledge is very limited, please help.