Pulling MySQL Query Results as Arrays?

12sharks

I realize this is probably a really obvious thing, but I'm getting stuck at one point of this form I'm making. Basically, it's a 'store edit' page, where all the form fields are sticky. I originally imploded all of the results and saved them as strings in fields in the 'Stores' table, but upon 'devinemke's' excellent suggestion, have now created several separate 'link' tables for each multi-select field. Now, I'm trying to make the forms sticky again, but instead of pulling from an exploded string array, pulling from a MySQL query. Below is the code for the 'radio' attribute.

print "<td rowspan=3 bgcolor=#FFFFFF><pp>Radio Stations:<br></pp>
<em><select name=\"radioss[]\" multiple=\"multiple\" size=\"18\">";

$pullRadioLinks = mysql_query("SELECT radioID FROM ksdrsSt_R_link WHERE storeID='$store'", $ksdx);
$rl = mysql_fetch_array ($pullRadioLinks);
$rlarray = $rl['radioID'];
$pullRadios = mysql_query("SELECT rCallID, radioID FROM ksdrsRadio ORDER BY rCallID ASC", $ksdx);
for ($x = 0; $x < mysql_num_rows($pullRadios); $x++) {
$r = mysql_fetch_assoc($pullRadios);
$rad = $r[rCallID];
$rID = $r[radioID];
print "<option value=\"" . $rID . "\"";
if (in_array($rID, $rlarray)) {
print " selected=\"selected\"";
} else {
}
print ">" . $rad . "</option>";
}
print "</select></td></tr>";

So, basically, there are two queries, one for radio links with the storeID in them, and one for all the radio stations, by which the select field is populated. What I want to say is 'if the radio option is in the result of the link query, print selected', but for some reason it's not printing 'selected'. I've checked via a foreach-print that the stations are coming up in the link query, and I have a feeling that my array terminology isn't exactly right. Am I missing quotes? Is the 'in_array' not the best way to do it?

Thanks,

--Samuel

devinemke

$result = mysql_query("SELECT radioID FROM ksdrsSt_R_link WHERE storeID = '$store'") or exit(mysql_error());
$radioID = mysql_result($result, 0);

$result = mysql_query('SELECT rCallID, radioID FROM ksdrsRadio ORDER BY rCallID') or exit(mysql_error());

echo '<select name="radios[]" size="18" multiple>';

while ($row = mysql_fetch_assoc($result))
{
	echo '<option value="' . $row['radioID'] . '"';
	if ($row['radioID'] == $radioID) {echo ' selected';}
	echo '>' . $row['rCallID'] . '</option>';
}

echo '</select>';

12sharks

devinemke, thanks again. Unfortunately, this code still produces a 'select' on only the last of the results returned in the 'link' query. I can't think of any way to get around that other than an 'in_array', but by looking at your code, it seems that that's not the way you say to do it.

Is there some other option, or perhaps a change to the code you provided (it works exactly correctly, except for the last result only problem)?

Thanks again,

--Samuel

devinemke

my bad. i thought you were only expecting a single result from the first query. try this:

$links = array();
$result = mysql_query("SELECT radioID FROM ksdrsSt_R_link WHERE storeID = '$store'") or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$links[] = $row['radioID'];}

$result = mysql_query('SELECT rCallID, radioID FROM ksdrsRadio ORDER BY rCallID') or exit(mysql_error()); 
echo '<select name="radios[]" size="18" multiple>'; 
while ($row = mysql_fetch_assoc($result)) 
{ 
    echo '<option value="' . $row['radioID'] . '"'; 
    if (in_array($row['radioID'], $links)) {echo ' selected';} 
    echo '>' . $row['rCallID'] . '</option>'; 
} 
echo '</select>';

12sharks

It works excellently. Thanks a ton!

If I could ask you one more question, as far as the link tables go, I had run into a little trouble trying to figure out how to edit the qualifiers (the genres, formats, etc.). I have it set up in a form with multi-select fields, and you select as many options as you want for any/all of the fields and click 'update'. I had it set up that if the link already exists, then don't do anything, but if it doesn't, make one. I realized that, though this worked, if someone de-selected something, there was no way to delete its respective link entry in the link table. So, I thought that perhaps it would be best, if the field was set, to first delete all instances, then insert new ones pertaining to the options selected when the user clicked update.

This serves its purpose, but there are going to be thousands of stores, and each of those with probably 2-6 selections per field, and if someone makes a change, not only will it create a new instance by this method, it will also drop the older instances and re-enter them with an auto-incremented number. I don't see the fields changing that much, but it seems like there could be some serious gaps in the numbers after a year or two.

Is this the recommended way to do it, or is this a stupid method? I'm not that concerned with it, but I thought I may inquire of your expertise.

But most of all, thanks again for your help.

--Samuel