[MySql] range query
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: [MySql] range query

Hybrid View

  1. #1
    Join Date
    May 2010
    Posts
    14

    [MySql] range query

    Hi,

    I want to make a world map for a game but before i (can) do that i need to learn how to and what the possibilities are. Therefor i am building a test environment. But i'm already kinda stuck on my first query.

    I have made this table called "world" with these columns:
    systemID -> not really used yet as there is just 1 system/world.
    orderID -> dunno if i really need this one as i could probably order my table from the xpos and ypos.
    xpos
    ypos
    tileID -> refers to the primary key of the table where i store my tiles.

    I have filled this table with 25 rows to represent a 5x5 grid. where all the outer tiles are 3 (water) the middle tile (3,3) is 2 (desert) and the rest are 1 (grass).

    Now my query needs to select all the "tiles" around the players location. Unfortunately my query only returns the current position to me where i expect it should give me 9 values. Here's the code:

    Code:
    $xpos = 3; //this variable should get feeded by a $_GET or $_POST or something.
    $ypos = 3; //this variable should get feeded by a $_GET or $_POST or something.
    
    //build query
    $mapquery = mysql_query ("
    SELECT
    tileID
    FROM
    world
    WHERE
    (xpos BETWEEN '$xpos - 1' AND '$xpos + 1')
    AND
    (ypos BETWEEN '$ypos - 1' AND '$ypos + 1')
    ") or die (mysql_error());
    
    $mapgen = mysql_fetch_assoc($mapquery);
    
    print_r ($mapgen);  //this only gives me the tile i'm currently on ->   Array ( [tileID] => 2 )

  2. #2
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,839

    Re: [MySql] range query

    It would be best if you calculate the minimum and maximum X and Y coordinates before the query. Then use those instead of your addition/subtraction within the SQL query.

    PHP Code:
    $minX $xpos 1;
    $maxX $xpos 1;
    $minY $ypos 1;
    $maxY $ypos 1
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    May 2010
    Posts
    14

    Re: [MySql] range query

    You might be right, but i found out that it actually works the way i did it without the ' marks at the BETWEEN command. Becaus if i do a num_rows on my query it actually returns 9.

    The problem is probably that it overwrites the array[tileID] so is there a mysql function that prevents this from happening or should i write a function that puts it correctly into an array?

  4. #4
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,839

    Re: [MySql] range query

    You aren't even looping through the query return!

    PHP Code:
    $mapIDs = array();
    while (
    $row mysql_fetch_object($mapquery)) {
      
    $mapIDs[] = $row->tileID;
    }

    print_r($mapIDs); 
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  5. #5
    Join Date
    May 2010
    Posts
    14

    Re: [MySql] range query

    Shame on me... i should have figured that out myself. Thanks for helping out!

    In planning for today: figuring out how to get this data into my HTML file with javascript/AJAX. Could i for instance make a foreachloop like this:

    PHP Code:
    <?php
    $mapgen 
    = array();
    while (
    $row mysql_fetch_object($mapquery)) {
      
    $mapgen[] = $row->tileID;
    }

    foreach (
    $mapgen as $tile
    {
    ?>
        <div class="tile tile-<?php echo "$tile";?>"></div>
    <?php
    }
    ?>
    in the PHP file and get this div output into my map container inside the html file? Or should i look to somehow get the array data into my HTML file and make the loop there to generate the map asynchronous with javascript/AJAX?
    Last edited by menyo; May 31st, 2010 at 01:17 AM.

  6. #6
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,839

    Re: [MySql] range query

    You could...but then you're actually causing extra processing which doesn't need to be. Nor do you need to be using up extra system resources.

    Rather than create the whole $mapgen array, simply output the <div> in the while() loop.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center