Appending data to an array while cycling through another array.

[andrewmoir]

Hello Guys/Girls,

I apologise for my poor PHP etiquette and layout skills (Uppercase, and lack of tabs before code)

I am attempting to populate a drop down list from my database.

I am populating the first array from a password table -it gets the customers and sites (from the username), then I do a search for all dates in the sample database which have that customer/site name. The dates are then posted to another array

1st - Get all the customers and sites
2nd - get dates from sample db where samples are in the Customer/site.

So its two arrays here - 1- while going through all the customer/sites, -2-select all dates from database that have those customer/sites

<?php
$sqlcs="SELECT distinct customer,site FROM PERMISSIONSCUSTSITE WHERE USERNAME = '$usernam' order by customer, customer desc";
$resultcs=mysql_query($sqlcs);

while($rowcs=mysql_fetch_array($resultcs)){

$cust1=$rowcs["customer"];    

$site1=$rowcs["site"];
echo "$cust1";           //just checking that all is ok
echo "$site1";            //just checking that all is ok

$sqlans="SELECT distinct registerdate FROM sample where customer='$cust1' and site='$site1' order by registerdate desc";
$result=mysql_query($sqlans);
}
?>

<FORM name="custform">
.......
<?php
while($row=mysql_fetch_array($result)){
echo "<OPTION VALUE=\"".$row['registerdate']."\">".$row['registerdate']."</OPTION>";
}
?>
.........
</FORM>

However only the dates from the last Customer/Site (in the customer/site array) are displayed in the form. So each time the "while($rowcs=mysql_fetch_array($resultcs))" is called it seems to reset the "$result=mysql_query($sqlans);" array instead of adding to it.

let me explain with an example of how I think its working:

1st: select customer and site from permissionscust where user = 'andrew'

put in array e.g.
..................... 'customer' ........... 'site'
so $resultcs = ....rotek ............ hot strip mill
.................= .....SAB............... Newlands

2nd:Now while passing through $resultcs : (mysql_fetch_array($resultcs))

select dates from sample where customer='customer' and site='site' (eg. Rotek and hot strip mill / and SAB and Newlands)

now let $result array = this select statement

However: The $result array is only showing dates where sample = SAB and Newlands (ie. the last Sql query done in our while loop) it is not including the dates from the query where sample = rotek and site = hot strip mill. The $resultcs is being overwritten and not appending the data like I assume it should.

Thanks for being so patient.

Why does it do this, I thought everytime you added to an array it simple appended the previous data to the current.

sorry if its confusing I tried my hardest to make it simple.
Thanks for the help.
Andrew

[paulnaj]

Hi andrew,

As you thought, you are just overwriting the same variable ($result) on each iteration of the first loop.

You have two choices ...

1) Save the data to an array in the first loop, then use that array in the second loop to display the options ...

<?php  

$registerdates = array();
$resultcs = mysql_query(" 
    SELECT DISTINCT customer, site 
    FROM PERMISSIONSCUSTSITE 
    WHERE USERNAME = '".$usernam."' 
    ORDER BY customer, customer desc 
"); 
while($rowcs = mysql_fetch_array($resultcs)){   

    $resultans = mysql_query(" 
        SELECT DISTINCT registerdate 
        FROM sample 
        WHERE customer='".$rowcs["customer"]."' AND site='".$rowcs["site"]."' 
        ORDER BY registerdate DESC 
    "); 
    while($rowans = mysql_fetch_array($resultans)){ 
		$registerdates[] = $rowans['registerdate'];
    } 
}
?>  

<FORM name="custform"> 
....... 
<?php  

foreach($registerdates as $registerdate)
	echo '<OPTION VALUE="'.$registerdate.'">'.$registerdate.'</OPTION>';  

}
?>  

......... 
</FORM>

OR

2) Just run the second loop inside the first ...

<?php 
$resultcs = mysql_query("
	SELECT DISTINCT customer, site 
	FROM PERMISSIONSCUSTSITE 
	WHERE USERNAME = '".$usernam."'
	ORDER BY customer, customer desc
");
while($rowcs = mysql_fetch_array($resultcs)){  

    $resultans = mysql_query(" 
	    SELECT DISTINCT registerdate 
    	FROM sample 
	    WHERE customer='".$rowcs["customer"]."' AND site='".$rowcs["site"]."' 
    	ORDER BY registerdate DESC
	"); 
?> 
<FORM name="custform"> 
....... 
<?php 
	while($rowans = mysql_fetch_array($resultans)){ 
		echo '<OPTION VALUE="'.$rowans['registerdate'].'">'.$rowans['registerdate'].'</OPTION>'; 
	}
?> 
......... 
</FORM> 
<?php 
} 
?> 

(I couldn't resist laying out your code 😉)

Paul.

[andrewmoir]

Thank you very much for your well worked reply - it works great.

I hate to correct a master but one correction was necessary:
the for each loop on the form needs a '{' as it begins.

<?php  

foreach($registerdates as $registerdate){
    echo '<OPTION VALUE="'.$registerdate.'">'.$registerdate.'</OPTION>';  

}
?>   

regards
Andrew

[paulnaj]

Er ... I put it in to see if you'd spot it (joke)!

Glad it worked out.

Paul 🙂