Source language: Translate to:

MySQL / Select madness

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL / Select madness

Postby beno » Tue May 06, 2008 5:46 am

Hi NeoBookers,

I know this is not the right place to post this, but this one has burned my last neuron.

I have a MySQL database in which I like to select a set from a integer field. Take a set or records from a value range.

My SQL string is:

Code: Select all
SELECT * FROM `ficha` WHERE `NumInv` BETWEEN 100 AND 200


NumInv is a Integer.

If I type this in my phpMyAdmin panel it works Ok; I got 100 records

If I use NeoBookDBPro it works Ok, again I have my 100 records.

But if I do this via a PHP file I got 392!!!

With:

Code: Select all
SELECT * FROM `ficha` WHERE `NumInv` BETWEEN 1 AND 10


I obtain 3!!! :shock: (3 with php, 10 with NeoBookDBPro and 10 trough phpMyAdmin panel)

Phew!, I have been wrestling hours ... my last neuron is melting.

Any advice will be real appreciated,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby luishp » Tue May 06, 2008 7:47 am

Beno, have you tried a diferent syntax?:

Instead of

Code: Select all
SELECT * FROM 'ficha' WHERE 'NumInv' BETWEEN 100 AND 200


Try this to get results where 'NumInv' is >100 and <200:

Code: Select all
SELECT * FROM 'ficha' WHERE 'NumInv'>=100 AND 'NumInv'<=200


Or if you just want the first 100 results:

Code: Select all
SELECT * FROM 'ficha' LIMIT 0, 100
User avatar
luishp
 
Posts: 357
Joined: Wed May 23, 2007 10:17 am
Location: Spain

Postby Gaev » Tue May 06, 2008 8:03 am

beno:

a) This may be besides the point ... but assuming you have one record for every (integer) value of NumInv ... shouldn't the first query return 101 records (if mySQL treats the two values as inclusive) or 99 (otherwise) ? ... and same for second query (return 9 or 11) ?

b) It looks like the error is due to a mis-match between the internal representation of the database field NumInv and the (integer) values for your selection range by php ... the help file states that ...
Code: Select all
The size of an integer is platform-dependent, although a maximum value of about two billion is the usual value (that's 32 bits signed). PHP does not support unsigned integers. Integer size can be determined using the constant PHP_INT_SIZE, and maximum value using the constant PHP_INT_MAX since PHP 4.4.0 and PHP 5.0.5.

Perhaps you can provide details of how your script goes about composing the query (SELECT * from etc. etc.) string ... and the exact field definition used when creating NumInv in table ficha
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby beno » Tue May 06, 2008 8:37 am

Hi Gaev,

First thanks a lot for your time.

NumInv is INT(11)

So this type could hold positive/negative values. My database uses only positive values for this field. They were set by manual typing.

You are right; 100 to 200 is 101. Maybe there are some "holes" or duplicates in my database. I can see the table and I´m sure it has at least 100 records in this range. But, as stated, I´m getting false results with my php script.

And, as stated, NeoBookDBPro returns nice answers with the same sql string.

About other syntax; yes I have tried it with no luck.

What I am trying to do is to create a dynamic string to retrieve a range of values from a field. Say, if I type in a input box:

100-200

I would like to retrieve all the records that match these values in its NumInv field.

This is how I am building my sql string.

I have a input box for a variable named $filter.


Code: Select all
// check if there is a hyphen
  $foo = stripos($filter,"-");

  $filterlength = strlen($filter);
// get the input string length

  if($foo > 0)
  {
// yes, hyphen found

   $start = substr($filterstr,0,$foo);
// isolate the first number

   $end = substr($filterstr,$foo+1,$filterlength);
// now take the second

   $sql = 'SELECT * FROM `ficha` WHERE NumInv BETWEEN ' .$start .' AND ' .$end;
  }
  else
     $sql = "SELECT * FROM ficha";

// output sql string

echo($sql);

// execute sql

  $res = mysql_query($sql, $conn) or die(mysql_error());

... more code...



This looks real simple at first, but I am stuck.

Sorry to post this problem here. I have googled for something similar but do not find a clue.

I have tested this:

- NeoBookDBPro - Ok
- MySQL + PHP at localhost - No Luck (php 5.2.3 MySQL 5)
- MySQL + PHP at website - No Luck (very similar versions)

Thanks for your help on this.

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Gaev » Tue May 06, 2008 9:42 am

beno:

a) how many records are there in your database table ? ... this might explain the results of 392 and 3 records being returned !!!

b) try and place this command ...
Code: Select all
$sql = "SELECT * FROM ficha WHERE NumInv BETWEEN 100 AND 200" ;
... immediately BEFORE ...
Code: Select all
echo($sql);
... this should isolate any differences between php and mySQL structures ... see if you get 100 (or 99 or 101) records returned.

c) Also, either the else condition should be wrapped in {...} ... or insert an endif command after it ... since you want echo($sql); and everything after it ... to be serviced under ALL conditions.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby beno » Tue May 06, 2008 10:04 am

Hi Gaev,

a) There are 5400+ records in my database

b) I was using the echo($sql); statement as a primitive way to look at my dynamic string. But in fact I do not need it in my project. So I can get rid of it.

I also placed the brackets for the else part.

If I try:

Code: Select all
$sql = "SELECT * FROM ficha WHERE NumInv BETWEEN 100 AND 200"


NeoBookDBPro reports 104 records, yes there are some duplicates, typing humans are not error free...

104 records is nice. It makes sense.

My php code (fixed with your suggestions) still reports 394.

These records are displayed after the query, there are the 104 "valid" ones and after these the rest are blank. Yes, with blank I mean records with blank content in all the fileds.

But:

Code: Select all
$sql = "SELECT * FROM ficha WHERE NumInv BETWEEN 1AND 10"


Reports 3, and it displays: 3, 4 and 5 for NumInv... but looking at the records I have: 0, 2, 3, 4, 5, 6, 7, 8, 9, 11, etc

So I would expect to get: 2, 3, 4, 5, 6, 7, 8, 9 (8 records, not 3)

Thanks a lot for your time.

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Gaev » Tue May 06, 2008 12:25 pm

beno:

a) In the php code you posted, I noticed that ...

$foo = stripos($filter,"-");
$filterlength = strlen($filter);

... use the variable $filter ... but ...

$start = substr($filterstr,0,$foo);
$end = substr($filterstr,$foo+1,$filterlength);

... refer to variable $filterstr.


b) Also ...

$end = substr($filterstr,$foo+1,$filterlength);

... will only work if the variable $filterstr contains nothing after the second number ... i.e. something like "100-200" ... not "100-200 " ... note $filterlen is the length of $filter ... not $filterstr

c) This is a "Hail Mary" suggestion ... try using (directly for now) ...
Code: Select all
$sql = "SELECT * FROM ficha WHERE CAST(NumInv,SIGNED) BETWEEN CAST(100,SIGNED) AND CAST(200,SIGNED)"
... or ...
Code: Select all
$sql = "SELECT * FROM ficha WHERE CONVERT(NumInv,SIGNED) BETWEEN CONVERT(100,SIGNED) AND CONVERT(200,SIGNED)"


d) <Clutching at straws>
Finally, you might want to double check that field NumInv is indeed a signed integer of length 11 ... and if it is not too much effort, try and specify NumInv as integer(7) ... for 32 bit systems, php's default maximum integer value is 2 billion ... i.e. 10 character positions ... perhaps comparing the smaller field with mySQL's larger field messes things up.
</Clutching at straws>
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby beno » Tue May 06, 2008 6:06 pm

Hi Gaev,

First of all: thanks a lot for your time and attention to this forum. You are a very kind person.

Second, problem solved. I have to say that the problem is me. I can not write here the words I have in my mind for myself.

The problem was this:

Some lines later the code I wrote here there was some code to count the records from the query. Something like:

1.- Query database with

Code: Select all
$sql = "SELECT * FROM ficha WHERE NumInv BETWEEN " . (int) $start ." AND " . (int) $end;


2.- Count the records:
Code: Select all
"SELECT COUNT(*) FROM `ficha` WHERE NumInv BETWEEN " . (int) $start." AND " . (int) $end;


Me, in my stubborn slip erased the Count() part of the second line. Makes sense now, I screwed the count part... :(

I started with a nice database php UI skeleton (provided by a superb freeware from www.sqlmaestro.com) and have done many modifications to suit my project.

In order to trying to make clear this situation, I started a mini project from the ground and while trying to make this modifications I found my brutish mistake.

Auf!, please accept my sincere apologies for distracting you with my donkey neurons.

beno :?
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests

cron