How to match pass strings to a php function that checks mysql database

Question

I have written a function which takes a database and a string and finds a match in the database and returns the corresponding values of book ISBNS..However it's not working for me....I think the problem is when I am matching the names....so for example I have Jack Thorne....When I pass it to the function am I supposed to put a comma in between since am getting the first name Jack from the first name column and the last name from the last name column....I have tried deliberately passing it a name of an author I know exists but it's not returning anything..Please help Here is the function

function booksByAuthor($db,$author){
    $query='SELECT isbn FROM books_authors WHERE author_id = (SELECT author_id FROM authors WHERE first_name,last_name = :author)';
    $statement = $db->prepare($query); 
    $statement->bindValue(':author', $author);
    $statement->execute(); 
    $isbns= $statement->fetchAll();  
    $statement->closeCursor();  
    return $isbns; 
}

WHERE x,y=z is a meaningless statement. You mean WHERE x=z OR y=z presumably. — tadman
– tadman, Commented Nov 4, 2016 at 2:23
Possible duplicate of Combine two columns in SQL for WHERE clause — RST
– RST, Commented Nov 4, 2016 at 2:26
I'm not sure this is the best schema. A lot of authors don't have both first and last names, plus splitting the name up is often non-trivial. How do you split Cyrano de Bergerac? Arthur C. Clarke? Plato? — tadman
– tadman, Commented Nov 4, 2016 at 2:26

barudo · Accepted Answer · 2016-11-04 02:39:05Z

1

Try this:

$query='SELECT isbn FROM books_authors 
WHERE author_id IN 
(SELECT author_id FROM authors WHERE first_name =:author OR last_name = :author)';

the equals is changed to IN - since the SELECT author_id ... statement will create several results.

the first_name =:author OR last_name = :author - to get results that will look into both first_name or last_name column.

answered Nov 4, 2016 at 2:39

barudo

6651 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will Barangi Over a year ago

Thanks for the suggestion, that actually pointed me into the right direction, since I was passing the name as one string I decided to explode it and then use your querry..It worked...

Collectives™ on Stack Overflow

How to match pass strings to a php function that checks mysql database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related