will_1990 Posted March 4, 2009 Share Posted March 4, 2009 Hello again PHPFreaks! I am trying to create a search query for my "library" database. the problem is that it does not find a book when i enter the search term i am using echo mysql_num_rows($result); to echo the results however i get a message say "query was empty when the query is performed! So i was thinking if i could add the like clause to my variable in the query it would match anything related to the entered search term correct? So how would i go about adding this? Here is the query: sq1 = "SELECT BtId, BtName, BcId, PubName, AuthorId AuthorName, FROM BookTitle, BookCopy, Loan, Publisher, Author WHERE BookTitle.BtId = BookCopy.BtId AND BookCopy.BcId = Loan.BcId AND BookCopy.BtId = Authorship.BtId AND Authorship.AuthorId = Author.AuthorId AND loan.Dateback IS NOT NULL AND $bn = BookTitle.BtName AND $an = Author.AuthorName"; $result = mysql_query($sql) or die(mysql_error()); echo mysql_num_rows($result); Many thanks! Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/ Share on other sites More sharing options...
Mchl Posted March 4, 2009 Share Posted March 4, 2009 First try to run the query in console or phpMyAdmin with some exemplary data to see if you can make it work. It might be that no data actually matches the conditions. Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776780 Share on other sites More sharing options...
will_1990 Posted March 4, 2009 Author Share Posted March 4, 2009 I have tried thos but am getting this error: " MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM BookTitle, BookCopy, Loan, Publisher, Author WHERE BookTitle.BtId = Book' at line 3 " with this query: SELECT BookTitle.BtId, BookTitle.BtName, BookCopyBcId, PubName, AuthorId AuthorName, FROM BookTitle, BookCopy, Loan, Publisher, Author WHERE BookTitle.BtId = BookCopy.BtId AND BookCopy.BcId = Loan.BcId AND BookCopy.BtId = Authorship.BtId AND Authorship.AuthorId = Author.AuthorId AND loan.Dateback IS NOT NULL AND LIKE %Harry% = BookTitle.BtName AND LIKE %row% = Author.AuthorName"; any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776789 Share on other sites More sharing options...
Mchl Posted March 4, 2009 Share Posted March 4, 2009 You have commas in wrong places: SELECT BookTitle.BtId, BookTitle.BtName, BookCopyBcId, PubName, AuthorId AuthorName, should be SELECT BookTitle.BtId, BookTitle.BtName, BookCopyBcId, PubName, AuthorId, AuthorName Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776798 Share on other sites More sharing options...
will_1990 Posted March 4, 2009 Author Share Posted March 4, 2009 It doesnt work even with that changed. still get the same error. Thanks all the same. Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776803 Share on other sites More sharing options...
Mchl Posted March 4, 2009 Share Posted March 4, 2009 Try this query SELECT bt.BtId, bt.BtName, BookCopyBcId, PubName, AuthorId, AuthorName FROM BookTitle AS bt CROSS JOIN BookCopy USING (BtID) CROSS JOIN Loan USING (BcID) CROSS JOIN Publisher USING (??) CROSS JOIN Author USING (AuthorID) WHERE loan.Dateback IS NOT NULL AND bt.BtName LIKE '%Harry%' AND Author.AuthorName LIKE '%row%'"; You did not provide any relation to Publisher table (hence ?? above, you have to fill it in with actual field name) Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776806 Share on other sites More sharing options...
will_1990 Posted March 4, 2009 Author Share Posted March 4, 2009 Thank you so much!!!! With a little tweaking i was finally able to perfom the query correctly!! Here is what i used in the end incase you're interested! SELECT bt.BtId, bt.BtName, BookCopy.BcId, PubName, AuthorId, AuthorName FROM BookTitle AS bt CROSS JOIN BookCopy USING (BtId) CROSS JOIN Loan USING (BcId) CROSS JOIN Publisher USING (PubId) CROSS JOIN Authorship USING (BtId) CROSS JOIN Author USING (AuthorId) WHERE Loan.DateBack IS NOT NULL AND bt.BtName LIKE '%Harry%' AND Author.AuthorName LIKE '%row%' Many thanks for all your help! Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776827 Share on other sites More sharing options...
will_1990 Posted March 4, 2009 Author Share Posted March 4, 2009 Despite being able to get the query to work sucessfully in phpmyadmin with static data, when i swap this out for my variable input this does not work. this works in phpmyadmin and returns the expected data on the webpage. $sql = 'SELECT bt.BtId, bt.BtName, BookCopy.BcId, PubName, AuthorId, AuthorName' . ' FROM BookTitle AS bt' . ' CROSS JOIN BookCopy USING (BtId)' . ' CROSS JOIN Loan USING (BcId)' . ' CROSS JOIN Publisher USING (PubId)' . ' CROSS JOIN Authorship USING (BtId)' . ' CROSS JOIN Author USING (AuthorId)' . ' WHERE' . ' Loan.DateBack IS NOT NULL' . ' AND' . ' bt.BtName LIKE \'%Harry%\' AND Author.AuthorName LIKE \'%row%\' LIMIT 0, 30 '; However when it is edited to: . ' bt.BtName LIKE \'%$bn%\' AND Author.AuthorName LIKE \'%$an%\' LIMIT 0, 30 '; [/code] the expected output is completely blank. Why is this is there a way to solve this or a work around? thanks again Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-776850 Share on other sites More sharing options...
Mchl Posted March 5, 2009 Share Posted March 5, 2009 To have $variables parsed in a string, you have to enclose it in double quotes "" $sql = "SELECT bt.BtId, bt.BtName, BookCopy.BcId, PubName, AuthorId, AuthorName FROM BookTitle AS bt CROSS JOIN BookCopy USING (BtId) CROSS JOIN Loan USING (BcId) CROSS JOIN Publisher USING (PubId) CROSS JOIN Authorship USING (BtId) CROSS JOIN Author USING (AuthorId) WHERE Loan.DateBack IS NOT NULL AND bt.BtName LIKE '%$bn%' AND Author.AuthorName LIKE '%$an%' LIMIT 0, 30 "; Quote Link to comment https://forums.phpfreaks.com/topic/148000-adding-a-like-clause-to-a-query/#findComment-777080 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.