PHP/MySQL Query Help

[froger]

Hello, I am currently facing a problem with a query of mine. You see, it wastes to much resources. I have an idea of how to fix it, but I just don't know how to implement it.

I have a table named Test with 4 columns. X, Y, Z, textimage. The table has a whole bunch of rows with x, y, z co ordinates with a text image. Is there another alternative than to have to query the database so many time to just get the table rows text image and to be scalable with x, y, z?

$sql = mysql_fetch_array(mysql_query("SELECT * FROM Test WHERE X='1' AND Y='1' AND Z='1'"));

for ($y=$sql['Y'];$y<=65;$y++)
  {
  for ($x=$sql['X'];$x<=100;$x++)
    {
    $query = mysql_fetch_array(mysql_query("SELECT * FROM Test WHERE X='{$x}' AND Y='{$y}' AND Z='{$sql['Z']}'"));

    echo $query['textimage'];
    }
  echo '<br/>';
  } 

*EDIT* I have gotten up to this point,

$query = mysql_query("SELECT * FROM Test WHERE (X BETWEEN 1 AND 100) AND (Y BETWEEN 1 AND 65) AND Z='1' ORDER BY Y, X");

$y=1;

while ($row = mysql_fetch_array($query))
  {
  if ($row['Y'] == $y)
    {
    echo $row['Image'];
    } else {
    echo '<br/>';
    $y++;
    }
  } 

What I need is 2 variables that will auto increment as the query runs.

$query = mysql_query("SELECT * FROM Test WHERE (X BETWEEN 1 AND 100) AND (Y BETWEEN 1 AND 65) AND Z='1' ORDER BY Y, X");

$y=1;

//auto increment variables
$yi = 0;
$xi = 0;

while ($row = mysql_fetch_array($query))
  {
  if ($row['Y'] == $y)
    {
    echo $row['Image'];
    $xi++;
    } else {
    echo '<br/>';
    $y++;
    $yi++;
    }
  } 

But I just can't seem to get it right. For some reason, the above statement doesn't work as intended for me. Is there an easier way to do this?

[dlukin]

What are you trying to do?

From what I can see, you are just getting all textimages for z=1 and 1<= x < = 100 and
1 <= y <= 65, right?

[froger]

What I am trying to do is display all the text images in their coordinated spot. You see X goes all the way up to 100, and Y goes all the way up to 65.

What that statement does is querys the database to get the textimage for each coordinate. So if X goes up to 100 and Y goes up to 65 then than is 100x65 queries to the database. You see how this can become pretty memory intensive? I would like to just query once to the database and use some type of method to display the data in a orderly fashion.

[dlukin]

$sql_query = "SELECT textimage FROM Test
WHERE Z=1 AND X>=1 AND X<=100 AND Y>=1 AND Y<=65
SORT BY Y ASC, X ASC"

I think should work.

[froger]

$sql_query = "SELECT textimage FROM Test
WHERE Z=1 AND X>=1 AND X<=100 AND Y>=1 AND Y<=65
SORT BY Y ASC, X ASC"

I think should work.

I was using something similar to this...

$sql = mysql_query("SELECT * FROM Test WHERE (X BETWEEN 1 AND 100) AND (Y BETWEEN 1 AND 65) AND Z='1' ORDER BY X");

but for some reason when echoing the data, it will order x something like this...

1, 1, 1
10, 1, 1
100, 1, 1
2, 1, 1
3, 1,1

I don't know why it doesn't do it in order. Mabe its because the column is set to varchar. 

*EDIT* Yea, I set my columns to INT and everything started coming aligned. Thanks.

[froger]

updated my original post.

[dlukin]

What do you want to do?

Explain it in words.

[misson]

updated my original post.

Which makes it confusing, since you're making a new request by changing the original. On a site like SO, updating the original post is the correct action, but this is a forum; it's fine (rather, preferred) to write updates in new posts.

For some reason, the above statement doesn't work as intended for me.

What happens instead, and how is it different than what you want? When describing a problem, state the behavior you expect/desire and what you get.

To break up the result set into lines, you can check for when the y-value changes.

<div>
  <div class="line">
    <?php
    $prevY = 1;
    // $db is a PDO instance
    // DB access should be performed in a separate layer
    foreach ($db->query('SELECT x, y, z, textimage FROM test WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 65 ORDER BY y, x') as $piece) {
      if ($prevY != $piece['y']) {
        ?>
        </div>
        <div class="line">
        <?php
      }
      echo $piece['textimage'];
      $prevY = $piece['y'];
    }
    ?>
  </div>
</div>

As noted in the comment, DB access should be isolated in a data access layer. The query would then become a prepared statement so that the ranges of x and y values can be altered.

$imgQuery = $db->prepare('SELECT x, y, z, textimage 
    FROM test 
    WHERE x BETWEEN :xmin AND :xmax 
      AND y BETWEEN :ymin AND :ymax 
    ORDER BY y, x');
$imgQuery->execute(array(':xmin' => 1, ':xmax' => 100, ':ymin' => 1, ':ymax' => 65 )); 

What do the assembled textimage pieces make? A map? ASCII art?

[froger]

1. What do the assembled textimage pieces make? A map? ASCII art?
2. What happens instead, and how is it different than what you want?

To answer question 1, yes the text images make up a ASCII art map.

To answer question 2, you see, I can change the text into images with smaller images. When I use the imagecopymerge function the map seems seriously altered.

*EDIT* I found the solution to my problem. Thanks.

[misson]

To answer question 1, yes the text images make up a ASCII art map.

In that case, amend my earlier example to structure the data as pre-formatted content:

<pre><?php
$prevY = 1;
foreach ($db->query('SELECT x, y, z, textimage FROM test WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 65 ORDER BY y, x') as $piece) {
    if ($prevY != $piece['y']) {
        echo "\n";
    }
    echo $piece['textimage'];
    $prevY = $piece['y'];
}?></pre>

All in all, it illustrates that storing two-dimensional data in a relational DB is, at best, problematic.

*EDIT* I found the solution to my problem. Thanks.

What was the solution?