Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

Question

0.00/5 (No votes)

See more:

below is my database table, I need to select title xyz if category is fiction, for this I am using CASE condition but I am gettin error please anyone help me

------------------------------------------
     title         |     category
                   |     
-----------------------------------------

    xyz                  fiction

    adapt                fiction

    adaxx                 fiction

    follow                journal

    your                   acadamic
--------------------------------------------

<br />
SELECT  CASE WHEN category = fiction THEN title LIKE '%xyz%' END FROM books<br />

Its not working please help me.

Posted 23-Feb-15 6:57am

Member 11406049

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-02-23T07:16:00

Solution 1

The query looks very odd. You can't build conditions in SELECT clause. For example the following would be a valid statement

SQL

SELECT CASE WHEN category = 'fiction' THEN 'found' END 
FROM books
WHERE title LIKE '%xyz%'

Also note that LIKE is an operator that can be used in WHERE clause, not much elsewhere.

Posted 23-Feb-15 7:16am

Wendelius

Comments

Member 11406049 23-Feb-15 13:20pm

will you tell me please what is 'found' in above query? and yes it's odd I am sorry, I am beginner...

Wendelius 23-Feb-15 13:34pm

Your CASE statement inspects if the field category on a row contains a value 'fiction'. I wasn't sure what you wanted the result to be so I just added a constant literal 'found'. In other words for each row the select returns you get the text 'found' if the category is fiction. Is this something like you we're after or did you have something else in mind?

Member 11406049 23-Feb-15 13:43pm

actually I have three columns title,author,fiction user may search by title name or author name by selecting the "category", if user search for 'adapt' its a 'title' name by selecting the "category fiction", I have to show 'adapt' and 'adaxx' because both are similar right, hence I used LIKE, another ex: if user is searching for 'xyz' by 'title' and by selecting "fiction category" I have to show result as 'xyz' is found...

Wendelius 23-Feb-15 14:04pm

Can you post a small example, based on the data in the question, what would your conditions be and what would you expect as a result?

Member 11406049 23-Feb-15 14:07pm

LINK

please check the above link, that is my previous post. and thank you very much for your replies..

Wendelius 23-Feb-15 14:19pm

Not sure if I understood the logic but based on your code in the other question you have an AND in the where clause for both Title conditions. This would mean that the title should be adapt and adaxx at the same time but that's not possible.

So you probably should have a query like
SELECT *
FROM Books b
WHERE ( b.Title LIKE '%adapt%' or b.Title LIKE '%adaxx%')
AND Category = 'fiction'.

However if you use the full texts for the title you don't need like and you can use IN operator. Like this
SELECT *
FROM Books b
WHERE b.Title IN ('adapt', 'adaxx')
AND Category = 'fiction'.

Is this getting towards correct direction?

Member 11406049 23-Feb-15 14:28pm

can I use like this?
SELECT *
FROM Books b
WHERE ( b.Title LIKE '%adapt%' or b.author LIKE '%adapt%')
AND Category = 'fiction'.

if query passed in search box by selecting the category as fiction or whatever it may be, it should search in both title and author, if value got in title then no need to search in author, if not need to search in author,

Wendelius 23-Feb-15 14:33pm

In technical sense that is a valid statement so if adapt can appear in both fields title and author, then the query should be correct.