problem reading array data

--00504502cc65305d930483b23c5f
Content-Type: text/plain; charset=ISO-8859-1

I need to read an array's content using a while loop and inside the loop
read another array's content. Using the variables from the two arrays I need
to execute a query. My problem is the inner while loop reads all records of
the array whereas the outer while loop exits after reading the first record.
That is after execution of the inner while loop finishes, the control does
not move to the outer while loop to read the next array element.

I am appending my code below. Please help me solve this problem

$arrdata = mysql_query("SELECT OldDeptCode, MajorCode FROM
tblolddeptcodemajorcode");

$result2 = mysql_query("SELECT SSN, DeptCode, ActCode FROM
tblapprovedactivitydetail");

while($info = mysql_fetch_assoc($arrdata))
{
$OldDeptCode = $info['OldDeptCode'];
$MajorCode = $info['MajorCode'];

while($row2 = mysql_fetch_assoc($result2))
{
$SSN = $row2['SSN'];
$DeptCode = $row2['DeptCode'];
$ActCode = $row2['ActCode'];

$query = "INSERT INTO test1 (SSN, MajorCode, ActCode) VALUES
('$SSN', '$MajorCode', '$ActCode')";

if($OldDeptCode != 'COAS' && $OldDeptCode != 'CSS' && $OldDeptCode
!= 'EC' && $OldDeptCode != 'EECS' && $OldDeptCode != 'FW' && $OldDeptCode !=
'GEO' && $OldDeptCode != 'SED' && $OldDeptCode != 'VM' && $OldDeptCode ==
$DeptCode)
{
mysql_query($query);
}
}
echo "done";
}
echo "all done";



Thank you

--00504502cc65305d930483b23c5f--

Re: problem reading array data

On Wed, 2010-04-07 at 21:37 -0700, Karthick Subramanian wrote:
> I need to read an array's content using a while loop and inside the loop
> read another array's content. Using the variables from the two arrays I need
> to execute a query. My problem is the inner while loop reads all records of
> the array whereas the outer while loop exits after reading the first record.
> That is after execution of the inner while loop finishes, the control does
> not move to the outer while loop to read the next array element.
>
> I am appending my code below. Please help me solve this problem
>
> $arrdata = mysql_query("SELECT OldDeptCode, MajorCode FROM
> tblolddeptcodemajorcode");
>
> $result2 = mysql_query("SELECT SSN, DeptCode, ActCode FROM
> tblapprovedactivitydetail");
>
> while($info = mysql_fetch_assoc($arrdata))
> {
> $OldDeptCode = $info['OldDeptCode'];
> $MajorCode = $info['MajorCode'];
>
> while($row2 = mysql_fetch_assoc($result2))
> {
> $SSN = $row2['SSN'];
> $DeptCode = $row2['DeptCode'];
> $ActCode = $row2['ActCode'];
>
> $query = "INSERT INTO test1 (SSN, MajorCode, ActCode) VALUES
> ('$SSN', '$MajorCode', '$ActCode')";
>
> if($OldDeptCode != 'COAS' && $OldDeptCode != 'CSS' && $OldDeptCode
> != 'EC' && $OldDeptCode != 'EECS' && $OldDeptCode != 'FW' && $OldDeptCode !=
> 'GEO' && $OldDeptCode != 'SED' && $OldDeptCode != 'VM' && $OldDeptCode ==
> $DeptCode)
> {
> mysql_query($query);
> }
> }
> echo "done";
> }
> echo "all done";
>
>
>
> Thank you

OK, at a 5 minute glance:
1.) I think the reason it isn't working is that MySQL/PHP don't support
reading from two buffered result sets concurrently: read from one then
the other.

2.) The code you've written is very inefficent. Databases are fast at
mangling database results whilst PHP isn't. You can filter and combine
both data set into the single query:
SELECT OldDeptCode, MajorCode, SSN, DeptCode, ActCode FROM
tblolddeptcodemajorcode old
INNER JOIN tblapprovedactivitydetail new on new.DeptCode=old.OldDeptCode
where old.OldDeptCode not in
('COAS','CSS','EC','EECS','FW','GEO','SED','VM')

If locking tables isn't a problem in your envrioment the entire loop can
be replaced with and insert into test1 SELECT .... statement. Use SQL
for record manipulation, that's its purpose. For maximum efficeny make
sure both tblolddeptcodemajorcode.OldDeptCode and
tblapprovedactivitydetail.DeptCode have indexes and the same format. Use
explain on the query to check its performance.

3.) If this is part of a long running script or library make sure you
use mysq_free_result() on the result set

4.) You might want some error handling for the queries failing.

HTH

Nigel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: problem reading array data

--001485e7e61a18cccb0483b74ccb
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Apr 8, 2010 at 10:25 AM, nwood wrote:

> OK, at a 5 minute glance:
> 1.) I think the reason it isn't working is that MySQL/PHP don't support
> reading from two buffered result sets concurrently: read from one then
> the other.
>

Specifically, it doesn't support multiple buffered result sets *on one
connection*. That means that you *can* do this, but you'll have to open a
separate connection for each buffered resultset.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001485e7e61a18cccb0483b74ccb--