Select doesn't show some records???

[jimmyking123]

Hi,
This problem is driving me crazy. I've got a table with about 200 records in it. It contains a list of domains.

When I do a select * from table it works shows all records. But when I do a select * from table where url="

http://www.xxxxxx.com/blahblah"

it shows most records but it doesn't show certain ones. At something like record 150 to 160 select * from table where url="

http://www.xxxxxx.com/blahblah"

doesn't work.

So on about 10 records the select doesn't work it just returns 0 records. This is really confusing me because it's strange i've made certain the characters are exactly the same as the record i'm searching for(copy & paste as well) but it just shows 0 records.

I know these records are there but it doesn't work so any ideas anyone.

Mysql Version : 3.23.58
OS : Fedora Core 3

Thanks

Jimmy

 

[steven290]

are you sure all 200 records have the url field with

http://www.xxxxxx.com/blahblah

as the value

 

[r937]

run this to make sure you can count all the rows --

select url
     , count(*) as rows
  from yourtable
group
    by url

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jimmyking123]

steve
All records have different domains

r937
Yep all the records show up when up I run that.

there's nothing much different on the domains just characters I can't understand why they don't show up when searching just for them.

 

[steven290]

if thats the case then

select * from table where url='

http://www.xxxxxx.com/blahblah'

will only show records that have

http://www.xxxxxx.com/blahblah

as the value (which might be 160) any difference and it won't be selected

 

[steven290]

how about try this

select * from table where url<>''

this should pick all records where url is not empty

 

[jimmyking123]

I do change the select each time to search for the one i want to search for.

select * from table where url<>'' works but that's not what i'm trying to do.

Why would a select function not find the record when it's there and I have the correct search parameter?

 

[ericbrunson]

I'm guessing you don't have the correct search parameter.

 

[azzazzello]

can you cut and paste an excerpt of the (records it IS getting) AND (records it is NOT getting but SHOULD be getting) under your search parameter?

 

[jimmyking123]

I'm deffinately running the correct search parameter as i've tested it again and again with a load of different domains and it's only certain ones in a row of 10 records all together that don't work they are all in the same format.

I've got a feeling that Mysql is somehow missing them out when I use the where clause because it works with a straight forward select without the where clause.

I don't want to post the domains has they are porn domains and I don't think it would be appreciated me posting links to porn sites. They are all in the same format anyway.

Any more ideas thanks people.

I've searched google and don't see anyone else having this prob so i'm just stuck at mo.

 

[TonyGroves]

I don't want to post the domains has they are p**n domains

You should have just said they're "confidential"; don't assume everyone here is cool about p**n!

There might be invisible characters embedded in your URLs. You can test for that:[tt]
SELECT * FROM tbl
WHERE LENGTH(url)=LENGTH('

http://www.tek-tits.com')

[/tt]

 

[azzazzello]

Tony, I HAVE to ask - was an accidental typo in your last post?

 

[ethorn10]

freudian slip. had to be.

 

[TonyGroves]

;-)

 

[steven290]

jimmy your not making any sense

of course that when you use a where and you don't use it they will be different.

Mysql is somehow missing them out when I use the where clause because it works with a straight forward select without the where clause

- your where is only picking records that match that where

- without the where all records will show

 

[r937]

Tony, brilliant slip

best laugh i've had all week

thanks

p.s. LENGTH(url) was pretty funny too

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jimmyking123]

Tony
You the man you spotted the problem great cheers. Now just gotta write a short perl script to get rid of them hell never knew invisible characters could be there.