MYSQL - How come it misses about 75% of the records?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

MYSQL - How come it misses about 75% of the records?

Post by yandina »

Here is the guts of my DB access query.
I reworked it from a similar one.
It works but only delivers about every 3rd or 4th entry from the table.
How come it doen't find all matching entries?

$result = MYSQL_QUERY("SELECT * FROM $Registrations");
while($row = MYSQL_FETCH_ARRAY($result)){
if($row['SLUPC'] == "10017") echo "<".$row['OwnerKey'].">";
}
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

yandina wrote:Here is the guts of my DB access query.
I reworked it from a similar one.
It works but only delivers about every 3rd or 4th entry from the table.
How come it doen't find all matching entries?

$result = MYSQL_QUERY("SELECT * FROM $Registrations");
while($row = MYSQL_FETCH_ARRAY($result)){
if($row['SLUPC'] == "10017") echo "<".$row['OwnerKey'].">";
}
Take out the if() clause (comment it out) & see if you're getting the data you expect then.
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

Hmm, thanks.
If I remove that it will give me all the keys, thousands of them.
The "if" clause seems to be working. It is in fact picking out only the records with SLUPC of 10017 but not ALL of them.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

yandina wrote:Hmm, thanks.
If I remove that it will give me all the keys, thousands of them.
The "if" clause seems to be working. It is in fact picking out only the records with SLUPC of 10017 but not ALL of them.
In that case, make sure that the string contains only 10017.. not 10017<space>, etc.

For reach of the records, print out just the SLUPC field, surrounded by pipes.. ie:

print "|" . $row['SLUPC'] . "|\n";

Check for spaces or any other characters that shouldn't be there.
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

The field is actually numeric. It is filled from another program, not entered manually so is very unlikely to have extraneous characters. I originally did a numerical test on == 10017 which had the problem so I changed it to "10017" in case it was floating instead of integer and not exactly equal, but both produce the same result with 2/3 of the entries missing.

I'm only a novice in .php and MYSQL but this seems so simple. It was copied and modified from another program that was working fine. Is there some limit to array size? Am I running out of space in $Result and losing data? I think it has about 20,000 data entries but not large files.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

yandina wrote:The field is actually numeric. It is filled from another program, not entered manually so is very unlikely to have extraneous characters. I originally did a numerical test on == 10017 which had the problem so I changed it to "10017" in case it was floating instead of integer and not exactly equal, but both produce the same result with 2/3 of the entries missing.

I'm only a novice in .php and MYSQL but this seems so simple. It was copied and modified from another program that was working fine. Is there some limit to array size? Am I running out of space in $Result and losing data? I think it has about 20,000 data entries but not large files.
Doubt it's a memory issue... what is the field type in MySQL? INT? FLOAT? DOUBLE? VARCHAR(...)? etc.
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

The SLUPC field is integer(11).
The OwnerKey is varchar(36).
There are 5 other fields, one varchar(48), 2 int(11) one int(6) and one varachar(10)
All the fields are entered by software so they should not have typos in them.

Ooo hold your horses.

I have a clue!! This should help.
The OwnerKey can start with alpha or numerical characters.
ONLY the keys starting with a numeral are being reported.
Don't know why but I'm sure that will ring a bell for someone.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

print out the value of $Registrations, and show that here please.
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

Here is the whole program.

<?php
$server= "xxxx.perfora.net"; /* Address of 1&1 database server */
$user= "yyyyy"; /* Database username */
$password= "zzzzz"; /* Database Password */
$database= "xxxxxx"; /* name of database */ /* Name of table, you can select that */
$Registrations= "Registrations"; /* Name of table, you can select that */

/* Accessing SQL-Server and querying table */
MYSQL_CONNECT($server, $user, $password) or die ( "<H3>Server unreachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database non existent</H3>");
$result = MYSQL_QUERY("SELECT * FROM $Registrations");
while($row = MYSQL_FETCH_ARRAY($result)){
if($row['SLUPC'] == "10017") echo "<".$row['OwnerKey'].">";
}

MYSQL_CLOSE();
?>
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

yandina wrote:Here is the whole program.

<?php
$server= "xxxx.perfora.net"; /* Address of 1&1 database server */
$user= "yyyyy"; /* Database username */
$password= "zzzzz"; /* Database Password */
$database= "xxxxxx"; /* name of database */ /* Name of table, you can select that */
$Registrations= "Registrations"; /* Name of table, you can select that */

/* Accessing SQL-Server and querying table */
MYSQL_CONNECT($server, $user, $password) or die ( "<H3>Server unreachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database non existent</H3>");
$result = MYSQL_QUERY("SELECT * FROM $Registrations");
while($row = MYSQL_FETCH_ARRAY($result)){
if($row['SLUPC'] == "10017") echo "<".$row['OwnerKey'].">";
}

MYSQL_CLOSE();
?>

Why don't you change your query? It'd be a lot faster..

$result = mysql_query("SELECT * FROM $Registrations WHERE SLUPC='10017'") or die(mysql_error());
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

Thanks, tried that, makes more sense, but still only finds the keys that start with a numeral, not an alpha character. :(

OK another clue. It has to be that either in .php or the echo function that the "<" is a reserved character. If I change it to "*" it gets all the keys.

I'll look up reserved characters and see if it needs a backslash or double << to make it work.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by Mirge »

yandina wrote:Thanks, tried that, makes more sense, but still only finds the keys that start with a numeral, not an alpha character. :(

OK another clue. It has to be that either in .php or the echo function that the "<" is a reserved character. If I change it to "*" it gets all the keys.

I'll look up reserved characters and see if it needs a backslash or double << to make it work.
So, what exactly are the conditions that must be met to display the row?
yandina
Forum Newbie
Posts: 7
Joined: Wed Oct 21, 2009 1:04 pm

Re: MYSQL - How come it misses about 75% of the records?

Post by yandina »

So far as I can see from doing some research you can't have the < charater as the first character in an echo string that has to be displayed on an HTML browser. I'm sure that if I created a full set of HTML commands to reproduce a normal HTML page there would be a way to do it but all I needed was the data, not a fancy HTML display.

As soon as you have the <, the HTML browser expects a command. Commands don't start with numerals so that is why just the keys with a numeral for the fist character worked OK.

SO no problem. I substituted the character ^ for the < in the .php script and it only takes a second to find/replace all ^ with < in a word processer to get the desired result.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: MYSQL - How come it misses about 75% of the records?

Post by onion2k »

In future, don't trust what you see in the browser window. Use "View Source" to see what's actually being displayed.

htmlentities() will help too.
Post Reply