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=arch...@jab.org
