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?

 

[kendu4shizzle67]

can any one help me with a cgi relay??

 

[misson]

can any one help me with a cgi relay??

Probably. Will we? Probably not.

• Don't threadjack.
• This forum is for programming. If you're not writing your own CGI relay, you're in the wrong place.
• Ask specific questions, not ill-defined, open ones.
• Specify what you want and what you have. If you're dealing with non-functioning code, this means state what behavior you want and what you're getting, including any error messages.
• Include concise (i.e. complete yet minimal) examples of what you're dealing with. All examples should be self-contained, and work (or fail, as the case may be) out of the box. Your problems need to be reproducible, and we shouldn't have to write code in an attempt to make your code fail the same way it fails for you (indeed, it may not fail for us after we flesh out an example).
• Proxies aren't allowed on free hosting. Install a CGI relay and your account will be closed under the zero tolerance policy.

 

[froger]

What was the solution?

The reason the map seemed seriously altered was because, in some of the y rows, I only had x going up to 99. It caused the map to be altered. After I fixed the map and aligned everything properly, the script worked perfectly.

 

[lapricon200839]

how to connect MySQL? sorry for the stupid question that where you want to drive well, login password website address, etc. ... if not difficult

 

[misson]

@lapricon200839:
It's not a stupid question, but:

• Don't threadjack. I wrote this to just two posts above yours. Not paying attention to what's been written previously in a thread is another breach of netiquette.
• Search the forums first. This has been covered many times before.

 

[lapricon200839]

thanks ))

@lapricon200839:
It's not a stupid question, but:

• Don't threadjack. I wrote this to just two posts above yours. Not paying attention to what's been written previously in a thread is another breach of netiquette.
• Search the forums first. This has been covered many times before.