mysql_query w/ JOIN results giving unexpected results w/ PHP (not regular SQL though)
| From: | Thoenen, Peter Mr. EPS | Date: | Wed, 18 Sep 2002 23:04:37 +0000 |
| Subject: | mysql_query w/ JOIN results giving unexpected results w/ PHP (not regular SQL though) | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-116805@lists.php.net to get a copy of this message | ||
Hello everybody,
Having an issue where I am getting unexpected results with a simple query
and at a lost to explain why.
At the end is my code. The problem I believe though I have narrowed down to
the following statement.
1. When I run SELECT accounts.id, accounts.action, people.last,
people.first, people.rank FROM accounts JOIN people WHERE (accounts.account
= people.id OR accounts.id = people.id) AND accounts.status='submitted';
directly into mysql (NOT USING PHP) I get the proper results. (1 row)
2. When I run it below using PHP I get unexpected results. (All 4 rows)
Database looks like:
-----ACCOUNTS-------
ID ACTION STATUS DATE_SUBMITTED ACCOUNT
1 create pending NULL 56
2 modify pending NULL 58
3 new pending NULL 65
4 delete submitted 2002-09-10 89
----PEOPLE----------
ID LAST FIRST RANK
56 A GUY NULL
58 B GUY NULL
65 C GUY NULL
89 D GUY NULL
AFTER JOIN STATEMENT SHOULD LOOK LIKE: (when ran directly with mysql
bypassing PHP)
ID ACTION LAST FIRST RANK
4 delete D GUY NULL
Instead I am getting (when using below code): (when ran with PHP)
ID ACTION LAST FIRST RANK
1 create A GUY NULL
2 modify B GUY NULL
3 new C GUY NULL
4 delete D GUY NULL
Debugging:
Have did mysql_num_rows($query); on the JOIN query and it returns 1 so it is
returning the proper number of rows.
Have also added an iterator to the while($row) to verify loop count == row
count. This also returns 1=1
Yet somehow the 'users' file has ALL 4 entries in it at end of the run.
At a complete loss how this can be so.
Any help on this would be appreciated.
Thanks,
-Peter
##### START CODE ###########
require_once 'auth.php';
$date=date("ymd");
if (isset($submit)){
foreach ($submit as $value){
digit($value);
mysql_query("UPDATE accounts SET status='submitted',
date_submitted='$date' WHERE id=$value;");
}
}
$query=mysql_query("SELECT site, address FROM unit WHERE
name='$unit'");
$row=mysql_fetch_array($query);
extract($row);
$query=mysql_query("SELECT accounts.id, accounts.action,
people.last, people.first, people.rank FROM accounts JOIN people WHERE
(accounts.account = people.id OR accounts.id = people.id) AND
accounts.status='submitted';");
if (mysql_num_rows($query)>0){
$fp=fopen('users', 'w');
while ($row=mysql_fetch_array($query)){
extract($row);
$s="MAILBOX";
$s.="\t$action";
$s.="\tKFOR\\$last".substr($first,0,1);
$s.="\t$last".substr($first,0,1);
$s.="\t$first";
$s.="\t$last";
$s.=isset($middle)?"\t$middle":'';
$s.="\t$rank";
$s.="\tBONDSTEEL2";
$s.="\t$last, $first";
$s.=isset($middle)?"$middle.":'';
$s.=" $rank";
$s.=$rank=='CIV'?$unit:'';
$s.="\t$phone";
$s.="\t$unit";
$s.="\t$address, $site";
$s.="\tYugo";
$s.="\t09340";
$s.="\tAE";
$s.="\tAPO\n";
fwrite($fp, $s);
}
fclose($fp);
unlink('users');
}
##################################
Peter Thoenen - Systems Programmer
Commercial Communications
Camp Bondsteel, Kosovo
##################################
"Stumbled Upon"...heh (Score:5, Funny) /.
by $carab on 23:00 23 August 2002 (#4131637)
"ForensicTec officials said they stumbled upon the military networks about
two months ago, while checking on network security for a private-sector
client."
Someone new to a Dvorak probably tried to type in "lynx
http://www.google.com" but instead got "nmap -v
-p 1-1024 -sS -P0 army.mil
-T paranoid".