The Arcive of Official vBulletin Modifications Site.

Iain M · #1 08-22-2011, 08:09 PM

As per my previous thread, 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.

PHP Code:


			
    // 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:

HTML Code:

<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 · #2 08-22-2011, 08:54 PM

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

PHP Code:


			
$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 · #3 08-22-2011, 09:31 PM

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

kh99 · #4 08-22-2011, 09:50 PM

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:

PHP Code:


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

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

Iain M · #5 08-22-2011, 10:25 PM

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

PHP Code:


			
$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 · #6 08-22-2011, 10:31 PM

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

PHP Code:


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

right after the 'editservicebit' template render.

Iain M · #7 08-22-2011, 10:50 PM

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 · #8 08-22-2011, 11:24 PM

Quote:

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

Quote:

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

Well, now this query

PHP Code:


			
            $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 · #9 08-22-2011, 11:40 PM

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

Quote:

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

PHP Code:


			
$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

HTML Code:

<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

HTML Code:

<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

HTML Code:

<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 · #10 08-23-2011, 12:01 AM

You know, I think maybe you just need a

PHP Code:


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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04233 seconds Memory Usage 2,309KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (4)bbcode_html (8)bbcode_php (3)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!