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

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

mysql query result in something weird

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

mysql query result in something weird

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us