#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Denmark
    Posts
    82
    Rep Power
    14
    Hi

    Within my Database, there is a table with countrynames and module ID's.

    example:
    ID ModulID Country
    1 1000 Afghanistan
    2 1001 Albanien
    3 1002 Algeriet
    4 1003 USA
    5 1004 Angola

    The table consists of more than 250 counties, all with uniqe ModuleID's.

    I only display one country on a page (with additional infomation from another table with identical ModuleID's)

    2 buttons on the page makes it possible to navigate back and forward among the countries, but the countries dosn't appear in an alphabetic order, since I control the navigation using the ModuleID (just moduleID+1 or moduleID-1) - so after "Algeriet" comes "USA", but I would like to get "Angola" instead.

    What I would like to do is this. I get all the countries from the "country table" like this:

    $sql = "SELECT * FROM countryTable ORDER BY country";
    $result = mysql_query($sql);

    And then I display the countries in a dropdown box. Let's say I select "Algeriet" in the dropdown box - I now get all the information from the other tables, and I want to se the next country using my navigation buttons - now I get USA as the next country.......

    Here's the question that you have been waiting fore :-)

    Is there a way that I can step up and down in my list of countries without using the moduleID's or do I have to re-index the entire database?

    Any help is appreciated

    Best regards
    Jens Jakobsen
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Ontario, Canada
    Posts
    498
    Rep Power
    18
    You can use an asort() to sort the country names alphabetically.

    ------------------
    To alcohol! The cause of, and solution to, all of life's problems. -- Homer Simpson

    [This message has been edited by cka (edited August 17, 2000).]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Denmark
    Posts
    82
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by cka:
    You can use an asort() to sort the country names alphabetically.

    [/quote]

    Hi Again

    Thanks for the quick answer, perhaps I didn't explain it good enough, but I can't see how this will help me.

    I need to know how I fetch the next row of data in the country table without referring to the ModuleID's. The countries is already sorted alphabetic when I select it from the database ("ORDER BY country") - but currently I fetch the next country using the ModuleID's and these are are out of Sync when I sort the countries alphabetic.

    I have had a look at the mysql_data_seek() command, I wonder if this can help me??

    Best regards
    Jens Jakobsen


  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Niteroi, RJ, Brazil
    Posts
    30
    Rep Power
    14
    I think I got your point.

    Try something like this:

    SELECT * FROM table ORDER BY country LIMIT 10

    SELECT * FROM table ORDER BY country LIMIT 10, 10

    SELECT * FROM table ORDER BY country LIMIT 20, 10

    SELECT * FROM table ORDER BY country LIMIT 30, 10

    Look at th MySQL manual for additional help. but I can explain a little:

    LIMIT 10: just 10 rows
    LIMIT 10,10: just 10 rows, starting at row 10 (next 10 rows)
    LIMIT 20,10: 10 rows from row 20 to 30 (next 10 rows)
    ...

    ------------------
    Eduardo Teixeira Cardoso
    PHP Programmer from Brazil
    idnotfound@vircio.com.br
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Denmark
    Posts
    82
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by IdNotFound:
    I think I got your point.

    Try something like this:

    SELECT * FROM table ORDER BY country LIMIT 10

    SELECT * FROM table ORDER BY country LIMIT 10, 10

    SELECT * FROM table ORDER BY country LIMIT 20, 10

    SELECT * FROM table ORDER BY country LIMIT 30, 10

    Look at th MySQL manual for additional help. but I can explain a little:

    LIMIT 10: just 10 rows
    LIMIT 10,10: just 10 rows, starting at row 10 (next 10 rows)
    LIMIT 20,10: 10 rows from row 20 to 30 (next 10 rows)
    ...

    [/quote]

    Hi

    Thanks for your answer but I dont think that you got my point :-)

    I just want to navigate up and down in my alphabetic sorted table, I do not want to LIMIT it to anything.

    Best regards
    Jens Jakobsen


  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    OK, in the page where you display the results for a country, let's assume the currently displayed country name is in $country and the module is in $module.

    To find the module numbers for the previous and next country sorted alphabetically you can do this:

    $next=mysql_query("select ModuleID,Country from countryTable where Country>'$country' order by Country ASC limit 1");
    list($NextModuleID,$NextCountry)=mysql_fetch_row($next);
    $prev=mysql_query("select ModuleID,Country from countryTable where Country<'$country' order by Country DESC limit 1");
    list($PrevModuleID,$PrevCountry)=mysql_fetch_row($prev);

    Just pass those values in the appropriate links
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Denmark
    Posts
    82
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    OK, in the page where you display the results for a country, let's assume the currently displayed country name is in $country and the module is in $module.

    To find the module numbers for the previous and next country sorted alphabetically you can do this:

    $next=mysql_query("select ModuleID,Country from countryTable where Country>'$country' order by Country ASC limit 1");
    list($NextModuleID,$NextCountry)=mysql_fetch_row($next);
    $prev=mysql_query("select ModuleID,Country from countryTable where Country<'$country' order by Country DESC limit 1");
    list($PrevModuleID,$PrevCountry)=mysql_fetch_row($prev);

    Just pass those values in the appropriate links
    [/quote]


    --------------------------------------------------------------------------------------------------------------------------

    Hi there

    IT WORKS :-)

    Thank you very much, I really appreciated you help - just some minor ajustment, and now everything works just right.

    All I had to add was an extra variable with the "$CurrentCountry", then it worked.

    Here's the code, if someone else needs it.


    $db = mysql_connect($servername, $serverusername, $serverpassword);
    mysql_select_db($MySQLDB, $db);

    $sql = "SELECT * FROM countrytable ORDER BY country";
    $result = mysql_query($sql);

    while ($myrow = mysql_fetch_array($result)) {

    if ($myrow["moduleID"] == $selectedModuleIDInComboBox) {

    $CurrentCountry=$myrow["country"];

    $NextCountry=mysql_query("SELECT moduleID,country FROM countrytable WHERE country>'$CurrentCountry' ORDER BY country ASC LIMIT 1");
    list($NextModuleID,$NextCountry)=mysql_fetch_row($NextCountry);

    $PrevCountry=mysql_query("SELECT moduleID,country FROM countrytable WHERE country<'$CurrentCountry' ORDER BY country DESC LIMIT 1");
    list($PrevModuleID,$PrevCountry)=mysql_fetch_row($PrevCountry);

    }
    }

    The rest of the code is just links using the variable, I guess there's no need to paste them here :-)

    Once again - thanks for all replies and help

    Best regards
    Jens Jakobsen
    --------------------------------------------------------------------------------------------------------------------------

Similar Threads

  1. back and forward
    By iivanov2 in forum Visual Basic Programming
    Replies: 1
    Last Post: February 8th, 2004, 06:23 PM
  2. forward only resultset
    By tjtee in forum Java Help
    Replies: 3
    Last Post: January 29th, 2004, 12:34 AM
  3. jsp forward vs redirect +patterns
    By bculp2000 in forum Java Help
    Replies: 1
    Last Post: December 27th, 2003, 11:49 AM

IMN logo majestic logo threadwatch logo seochat tools logo