Page 1 of 1
MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 1:17 pm
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'].">";
}
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 2:27 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 3:05 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 3:10 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 3:39 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 3:40 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 4:13 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 4:15 pm
by Mirge
print out the value of $Registrations, and show that here please.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 4:34 pm
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();
?>
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 4:46 pm
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());
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 10:00 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 10:40 pm
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?
Re: MYSQL - How come it misses about 75% of the records?
Posted: Wed Oct 21, 2009 11:08 pm
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.
Re: MYSQL - How come it misses about 75% of the records?
Posted: Thu Oct 22, 2009 2:25 am
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.