mysql query result in something weird

Question

0.00/5 (No votes)

See more:

Hi guys... i just want to ask why is it that my query is acting weird? you set i just created a query that will search a result based on the id number but when my query generated a result i was surprised because it returned a result... heres my query

SELECT id,name,status from tbl_priviledge where  id >= 'sdfasdfasdf' LIMIT 0, 2

i was expecting to return an empty result but it returned a result... which i know there is no id with a value of

'sdfasdfasdf'

so how do i fix this?

Posted 20-Feb-12 19:14pm

Madzmar25

Add a Solution

4 solutions

Solution 1

Don't use ">=" - use "=" instead:

SQL

SELECT id,name,status from tbl_priviledge where  id >= 'sdfasdfasdf' LIMIT 0, 2

Becomes

SQL

SELECT id,name,status from tbl_priviledge where  id = 'sdfasdfasdf' LIMIT 0, 2

SQL can compare strings asa well as numbers...

Posted 20-Feb-12 20:46pm

OriginalGriff

Comments

Madzmar25 21-Feb-12 3:24am

actually i need to use >= since i'm generating this code also
SELECT id,name,status from tbl_priviledge where id >= 'sdfasdfasdf' and id < '10' LIMIT 0, 2

That is assuming that the user just type in a bogus input on the first text field and a valid input on the second textfield. i'm providing them with 2 textfield , one is from what id starts and second is to where the id will end

OriginalGriff 21-Feb-12 3:31am

Then you will have to accept that GIGO still happens! :laugh:

Solution 2

If you want to test for inequility, (i.e. you don't want to search for id = 'sdfasdfasdf') use <> instead of >=. I think the SQL compares the string values using the ascii values of the individual letters. So anything starting with a character bigger than 's' (i.e. t,u,v,...,z) is considered larger than the strings that start with 's'

Or you can use:

SQL

SELECT id,name,status from tbl_priviledge where NOT (id = 'sdfasdfasdf') LIMIT 0, 2

Posted 20-Feb-12 21:08pm

krumia

Updated 20-Feb-12 21:10pm

v2

Solution 4

pay us............;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Posted 20-Feb-12 22:36pm

vicky patole

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)

Graham Breach · Accepted Answer · 2012-02-20T22:24:00

Solution 3

You should validate your inputs before you use them in queries.

MySQL has some rules for how it compares different types, which explains why 'sdfasdfasdf' evaluates as 0:
http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html[^]

Posted 20-Feb-12 22:24pm

Graham Breach

Comments

vicky patole 21-Feb-12 4:38am

so graham..you done that