Jump to content

adding a like clause to a query!


will_1990

Recommended Posts

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!

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.