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

e.g.
------

<?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.

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

[Colin F]

A different solution: add the values to an array themself with something like
$custs['$rowcs[site]'] = $rowcs["customer"];

Then, run through them with something like

foreach($custs AS $key => $value)
{
}


You might also want to group it a bit better, so that you don't have an endless number of queries.


And, try to use the [php] and [code] tags ;)

[andrewmoir]

Thanks Colin F (Farrel?) for that super fast response - must be a record.

I am a bit of a novice (I must admit) when it comes to using arrays in the manner you described. The customer/site eg. Rotek - Temper mill are to be used together and are not actually independent of each other even though they are in different fields e.g.

Rotek - Hot strip mill
Rotek - Temper mill
Rotek - Air Liquide
SAB - Newlands

If you could flesh the answer out a bit just so's I can understand, once creating your first array of Customer and site, how does one conduct the next sql statement for selecting the date?

regards

[Colin F]

Colin Frei actually :)

<?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))
{
$custs["$rowcs[site]"] = $rowcs["customer"];
}
foreach($custs AS $key => $value)
{
$sqlans="SELECT distinct registerdate FROM sample where customer='$value' and site='$key' 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>
------


This should work...
</font>

[andrewmoir]

Thanks Colin for your help: But unfortunately I got the exact same result as with my original code:

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.

[andrewmoir]

<?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">
<SELECT id=custlinks name=custlinks size='0' style="font:8pt" onchange='OnChange(this.form.custform);'>

<OPTION SELECTED value="choose_date_above">- Select Date -

<?php
foreach($registerdates as $registerdate){

echo '<OPTION VALUE="'.$registerdate.'">'.$registerdate.'</OPTION>';
}
?>
</SELECT>
</FORM>