Page 1 of 1

trying to reduce script size

Posted: Mon May 17, 2010 11:29 am
by IGGt
I currently have a php script set up that executes 25 MySQL queries, creates 25 variables, and then places these into a html table.

They are along the lines of:

Code: Select all

$result1 = mysql_query("SELECT colour from db1.squares WHERE row=1 and `column`=1")
$result2 = mysql_query("SELECT colour from db1.squares WHERE row=1 and `column`=2")

$row1 = mysql_fetch_array( $result1 );
$row2 = mysql_fetch_array( $result2 );

<td bgcolor=<?php echo $row1['colour']?>>&nbsp;</td>
<td bgcolor=<?php echo $row2['colour']?>>&nbsp;</td>

So I have $result1, $result2, $result3 . . . and so on up to $result25 (and the same for the $row)


In SQL I could simply use the query:

SELECT colour from db1.squares

and this would give me all the results I need in one go.

Is there a way I could use this query, and simply call the results one after the other, thus much reducing the size of my php script? I'm still new to PHP, so any help would be appreciated.

Re: trying to reduce script size

Posted: Mon May 17, 2010 11:42 am
by mikosiko
One option:

Code: Select all

$result = mysql_query("SELECT colour from db1.squares");

while ($myrow = mysql_fetch_assoc($result))               // or mysql_fetch_array if you prefer
{
   <td bgcolor=<?php echo $myrow['colour']?>>&nbsp;</td>
}

Re: trying to reduce script size

Posted: Mon May 17, 2010 12:06 pm
by AbraCadaver
mikosiko is on the right track, but you probably want to order the results and then start a new table row when the row changes. Something like:

Code: Select all

$result = mysql_query("SELECT `colour` FROM `db1`.`squares` ORDER BY `row`, `column` ASC");
$prev_row = 1;

echo '<tr>';
while($row = mysql_fetch_array($result)) {
	if($row['row'] != $prev_row) {
		echo '</tr><tr>';
	}
	echo '<td bgcolor=' . $row['colour'] . '&nbsp;</td>';
}
echo '</tr>';

Re: trying to reduce script size

Posted: Tue May 18, 2010 11:15 am
by IGGt
cheers, that seems to be nearly there. The squares are appearing (albeit in a single column, not in a 5x5 square - but I can probably tidy that up later) with the correct colours in them.

The only thing is each square now says

"Notice: Undefined index: row in C:\wamp\www\squares5.php on line 23"

The code I have put in is:

Code: Select all

$result = mysql_query("SELECT `colour` FROM `db1`.`squares` ORDER BY `row`, `column` ASC");
$prev_row = 1;

echo '<table cellspacing="3" cellpadding="10" height="300px" width="300px">';
echo '<tr>';
while($row = mysql_fetch_array($result)) {
        if($row['row'] != $prev_row) {
                echo '</tr><tr>';
        }
        echo '<td bgcolor=' . $row['colour'] . '&nbsp;</td>';
}
echo '</tr>';
echo '</table>';
?>
Do I need to declare row as a variable or something?

Re: trying to reduce script size

Posted: Tue May 18, 2010 11:50 am
by AbraCadaver
My bad. I just copy and pasted your query. You need to select the `row` column also:

Code: Select all

$result = mysql_query("SELECT `colour`, `row` FROM `db1`.`squares` ORDER BY `row`, `column` ASC");

Re: trying to reduce script size

Posted: Wed May 19, 2010 2:42 am
by IGGt
Ah, that makes sense.

cheers