adding a like clause to a query!

[will_1990]

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!

[Mchl]

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.

[will_1990]

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?

 

 

[Mchl]

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

[will_1990]

It doesnt work even with that changed. still get the same error. Thanks all the same.

[Mchl]

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)

[will_1990]

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!

[will_1990]

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

 

[Mchl]

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 ";