Dropdown Menus 'selected="selected"'

[Iain M]

As per my previous thread (https://vborg.vbsupport.ru/showthread.php?t=268813), I am making an edit page where users can edit multiple rows at once, I have the updating working.
I'm now stuck at getting it to show what option the user has selected, when they view the edit page again.

// Get list of countries for Country option
$countryOptions = '';
$getCountries = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "countries ORDER BY title ASC");

// Get user services
$editServices = $vbulletin->db->query_read("
SELECT *
FROM " . TABLE_PREFIX . "services
WHERE userid = '" . $vbulletin->userinfo['userid'] . "'
ORDER BY id DESC
");

while ($editService = $vbulletin->db->fetch_array($editServices))
{
while($viewCountry = $db->fetch_array($getCountries))
{
// Check which Country is seleted
$getChecked = $db->query_first("SELECT id, country FROM " . TABLE_PREFIX . "services WHERE country = " . $viewCountry['id'] . " AND id = " . $editService['id']);

$optiontitle = $viewCountry['title'];
$optionvalue = $viewCountry['id'];
$optionselected = ($viewCountry['id'] == $getChecked['Country']) ? 'selected="selected"' : '';
$optionclass = '';

$countryOptions .= render_option_template($optiontitle, $optionvalue, $optionselected, $optionclass);
}
// Register 'editservicebit' template variables
$templater = vB_Template::create('editservicebit');
$templater->register('id', $editService['id']);
$templater->register('title', $editService['title']);
$templater->register('description', $editService['description']);
$templater->register('country', $editService['country']);
$templater->register('free', $editService['free']);
$templater->register('active', $editService['active']);
$templater->register('countryOptions', $CountryOptions);
$editservicebit .= $templater->render();
}

The code above only seems to be matching $viewCountry['id'] == $getChecked['Country'] on the first row from the editServices query, and applying selected="selected" to that country in each row, even when another country has been selected for that service. It isn't looping with while($viewCountry = $db->fetch_array($getCountries)). I had tried a foreach loop instead, and had got somewhere, but it still wasn't working, so I went back to the start to try again, but it feels like I'm banging my head against a brick wall, and going round in circles.

To help you understand what I mean, here's an example of the HTML output:

<td><select class="primary" name="country[3]" tabindex="1">
<option value="1" class="" >Australia</option>
<option value="2" class="" >Canada</option>
<option value="3" class="" selected="selected">New Zealand</option>
<option value="4" class="" >United Kingdom</option>
<option value="5" class="" >United States</option>
</select></td>
<td><select class="primary" name="country[2]" tabindex="1">
<option value="1" class="" >Australia</option>
<option value="2" class="" >Canada</option>
<option value="3" class="" selected="selected">New Zealand</option>
<option value="4" class="" >United Kingdom</option>
<option value="5" class="" >United States</option>
</select></td>


Can someone point me in the right direction again?


Cheers for your help!

Iain

[kh99]

Maybe the problem is that you've used a capital letter in 'Country' in this line:

$optionselected = ($viewCountry['id'] == $getChecked['Country']) ? 'selected="selected"' : '';


when the column is named 'country'. That way, $getChecked['Country'] is null which would always match the first id if it were 0.

[Iain M]

Thanks kh99. Still nothing =/ I've been on this for the past 2 days haha =[

[kh99]

Does the services table only have one record per service? If so, then I don't think you need that extra query to get the selected option, because it's always going to return the same record as is already in $editService. If that's the case then to set $optionselected you'd just want:

$optionselected = ($viewCountry['id'] == $editService['country']) ? 'selected="selected"' : '';


but to be honest I don't see why that would solve the problem.

[Iain M]

In the service table would be a service that a user has added, and the country is where the service is available.

So for example, User 1 could say offer Dog Trainer in UK (row 1), Baby Sitter in US (row 2), Plumber in NZ (Row 3).

At the moment it is pulling all the services, and saying they are all in NZ. As I have the ID field sorted descending order (in $editServices). So from what I can gather it's completely ignoring while($viewCountry = $db->fetch_array($getCountries)) as a loop.

Because, I think it should be running
$getChecked = $db->query_first("SELECT id, country FROM " . TABLE_PREFIX . "services WHERE country = " . $viewCountry['id'] . " AND id = " . $editService['id']);
each time it pulls a country. It is pulling all the countries in the database. I know it isn't great if there's a lot of countries, as it should be running for each country that it pulls, if someone knows a better way, I'd love to hear it.

[kh99]

But you're getting a list of services, each with a list of countries, so it has to be doing both loops, right? It seems like it's just the test for the selected one that isn't working.

Oh, I think I know - you have to repeat the 'countries' query each time. Try putting


$db->data_seek($getCountries, 0);

right after the 'editservicebit' template render.

[Iain M]

I seriously owe you a drink! Thank you!

Now, I just need to duplicate it for another drop down, and radio options =]

Thank you so much! =]

Where did you find that?

[EDIT]
Oh wait.. that bumps my queries on that page up from 35 to 83, that's without the other drop down, and radio options which could make it increase further. There must be a better way? =/

[kh99]

Where did you find that?
The vbulletin db functions are pretty much just a wrapper for the mysql functions which you can see here: http://us2.php.net/manual/en/book.mysql.php


Oh wait.. that bumps my queries on that page up from 35 to 83,...

Well, now this query
$getChecked = $db->query_first("SELECT id, country FROM " . TABLE_PREFIX . "services WHERE country = " . $viewCountry['id'] . " AND id = " . $editService['id']);


must be getting done more times, right? Are you sure you actually need that one (I'm still not completely clear on the db structure so I'm not sure).

[Iain M]

Thanks for the link. I had never came across that function before. I guess I didn't look hard enough!

Well, now this query... must be getting done more times, right? Are you sure you actually need that one

Yes, that's right.

If I remove that query and use
$optionselected = ($viewCountry['id'] == $editService['country']) ? 'selected="selected"' : '';

as you suggested above, it adds selected to each row for each item.

For example:
Row 1: Dog Trainer in UK
<td><select class="primary" name="country[3]" tabindex="1">
<option value="1" class="" >Australia</option>
<option value="2" class="" >Canada</option>
<option value="3" class="">New Zealand</option>
<option value="4" class="" selected="selected">United Kingdom</option>
<option value="5" class="" >United States</option>
</select></td>

Row 2: Baby Sitter in the US
<td><select class="primary" name="country[3]" tabindex="1">
<option value="1" class="" >Australia</option>
<option value="2" class="" >Canada</option>
<option value="3" class="">New Zealand</option>
<option value="4" class="" selected="selected">United Kingdom</option>
<option value="5" class="" selected="selected">United States</option>
</select></td>

Row 3: Plumber in NZ
<td><select class="primary" name="country[3]" tabindex="1">
<option value="1" class="" >Australia</option>
<option value="2" class="" >Canada</option>
<option value="3" class="" selected="selected">New Zealand</option>
<option value="4" class="" selected="selected">United Kingdom</option>
<option value="5" class="" selected="selected">United States</option>
</select></td>

I think you're on the right track tho, because that is the query running over and over and over and over again... by commenting it out, it dropped to 11 queries.

Thanks Kevin.

I'm gonna head off (almost 2am here), and play with this again tomorrow.

[kh99]

You know, I think maybe you just need a

$countryOptions = '';


after the render to reset that variable. But I don't know why you're not seeing the options building up each time, with the contries repeating over and over.

[nhawk]

There is something you should know..

After you parse through the data returned by getCountries once, you can't parse the same query results a second time. The pointers are already set to the end of the data after the first while { } loop and the pointer either needs to be reset or the data stored in an array for future use during the first parse.

[Iain M]

Thanks Kevin $countryOptions = ''; after the render worked.

Thank you nhawk for that explanation =]

[nhawk]

But you're getting a list of services, each with a list of countries, so it has to be doing both loops, right? It seems like it's just the test for the selected one that isn't working.

Oh, I think I know - you have to repeat the 'countries' query each time. Try putting


$db->data_seek($getCountries, 0);

right after the 'editservicebit' template render.

LOL, it's too early. KH99 already caught that here :)

I need.... more.... coffee :D

[kh99]

LOL, it's too early. KH99 already caught that here :)

I need.... more.... coffee :D

Yeah, but I'm slightly embarrassed that I didn't notice that right off. To be honest I didn't even know that you could get the results of two queries at the same time like that.

[Marco64Th]

A static list like the countries should really just go into the datastore as a serialized array. Just 1 time read them from the datastore (using the standard vB functions) into the $vbulletin object and you're done.

[Iain M]

I will look into using the datastore. Thank you Marco.