tough array question

Anon

I'm having a lot of trouble figuring out how to do this. It seems like there should be an easier way.

first, I need to get the distinct dates from a table for a pull-down select menu.

$sql="select distinct section_date from bo_section";
$result=mysql_query($sql, $sql_link);
$dates_available=mysql_fetch_array($result);

then, for each of those dates, I need to populate an array with the site and site id that is available on that date.

$site_sql = "Select bo_site.name, bo_section.section_id from bo_section left join bo_site on bo_section.site_id = bo_site.site_id where bo_section.section_date = '$date'";
$site_result=mysql_query($site_sql, $sql_link);
$site_info=mysql_fetch_array($site_result);

the problem is the arrays overwrite each other as they get to the second and third, etc. I need to get to the info to create an array for JavaScript code.

there's gotta be an easier way. If anyone has any hints, I'm all ears (if you understood what I was trying to say).

craig

Anon

I have this:

$sql="Select bo_section.section_date as date, bo_site.name as name, bo_section.section_id as id from bo_section left join bo_site on bo_section.site_id = bo_site.site_id";
$result=mysql_query($sql, $sql_link);

I need to get several arrays out of this:

1) all the distinct date
2) an array for each date containing the name and id that occur with that date

can you help?

Anon

Hi Craig,

If the tables you mention are in the same database, why not combine the two quries, and let mySQL do the work for you comparing dates? Then you could simply have a form option script similar to the one below:

<FORM action="360_choose_diagnostic.phtml" name="diagform">
<select name=diagnostic_id onChange="javascript:diagform.submit();">
<option value="0"> -- Select a 360° program --</option>
<? while ($diag_list = mysql_fetch_array($diag_list_query) ) { ?>
<option value="<? print("$diag_list[0]"); ?>" label="<? print("$diag_list[1]"); ?>"><? print("$diag_list[1]"); ?></option>
<? } ?>
</select>
</form>

Hope this helps. The value for each option might be a URL to pass to your javascript, or a value that an "in-between" page processes for your JavaScript.

PS. If you only need ONE site per date entry where there is more than one URL, a sneaky work-around might to just use MAX of URL or MIN of URL (I think mySQL supports alphabetic MIN/MAX?).

Cheers,
Duncan.

mp96brbj

Try using a multi-dimensional array:

(Insert code to prepare $dates_available.)

$final_array = array();
foreach ($dates_available as $date)
{
$final_array[$date] = array();

(Insert code to fetch sites for this date and put them in $site_result.)

while ($row = mysql_fetch_array($site_result))
{
$final_array[$date][] = $row;
}
}

Now, try this, just to have a look at what we've accumulated in $final_array:

print "<pre>";
print_r($final_array);
print "</pre>";

Anon

Yeah - that's more along the lines of the direction I went. I was up until 2 am coming up with the following code - which works, but may not be the best. Any comments are welcome. I was hoping to be able to do this without calling the DB so much.

begin pop-up select list building

get distinct dates for first pop up selects: $unique_dates[]

$unique_dates=array();
$sql="select distinct section_date from bo_section";
$result=mysql_query($sql, $sql_link);
while ($row=mysql_fetch_array($result)) {
$unique_dates[]=$row['section_date'];
}
##print out array
while (list($key, $val) = each($unique_dates)) {
print ("$key: $val<BR>\n");
}
reset($unique_dates);

build array of sites by date

refer to sites as such: $site[$unique_dates[$i]][$j]["name'] (or ["id"])

$sites=array();
while (list($key, $val) = each($unique_dates)) {
$sql="Select bo_site.name as name, bo_section.section_id as id from bo_section left join bo_site on
bo_section.site_id = bo_site.site_id where bo_section.section_date = '".$val."'";
$site_result=mysql_query($sql, $sql_link);
while ($row=mysql_fetch_array($site_result, MYSQL_ASSOC)) {
$sites[$val][]=array("name" => $row['name'], "id" => $row['id']);
}
}
reset($unique_dates);
reset($sites);
print ("<HR>");

END pop-up select list building

print out lists just for kicks

$site_array=array();
$site=array();
while (list($key1, $val) = each($unique_dates)) {
while (list($key2, $site_array) = each($sites)) {
Print ("<B>DATE: ".$key2."</B><BR>");
while (list($key3, $site) = each($site_array)) {
print ("site: ".$site[name]."<BR>");
print ("id: ".$site[id]."<BR><BR>");
}
}
}
reset($unique_dates);
reset($sites);
print ("<HR>");