Retrieving Data And Displaying In Boxes

FirefoxRocks

Feb 15 2007, 10:56 PM

I have successfully setup a MySQL Database with a few tables in it to store user input!

Woohoo!

Now the problem is displaying the data. I want 4 boxes echoing across with 3 rows down so that makes 12 boxes (12 of the most recent records). I have no clue how to do this because I cannot use the W3Schools example of echoing into a table. Here is the code I used but it echoes the same information across the entire row of 4 boxes.

CODE

<?php
$con = mysql_connect("localhost","myDatabase name","myDatabase password");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("myTable", $con);

$result = mysql_query("SELECT * FROM myField1 ORDER BY myField2 DESC");

echo "<table summary='myTable summary'>";
while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "</tr>";
 }
echo "</table>";
mysql_close($con);
?>

*Field/database information was edited out to protect privacy.

As you can see, it tells the database to echo one full row of the same info, then loop through the whole thing as many times as there are records. First of all, I only want a total of 12 records being echoed. Second of all, I want the 4x3 boxes layout.

Could someone please give me some modifications to the code I am using right now? I tried using <div>s also, didn't work.
I'm designing this site for a major section for a game site.

Comment/Reply (w/o sign-up)

TavoxPeru

Feb 16 2007, 05:23 AM

QUOTE(FirefoxRocks @ Feb 15 2007, 05:56 PM)

I have successfully setup a MySQL Database with a few tables in it to store user input!

Woohoo!

Now the problem is displaying the data. I want 4 boxes echoing across with 3 rows down so that makes 12 boxes (12 of the most recent records). I have no clue how to do this because I cannot use the W3Schools example of echoing into a table. Here is the code I used but it echoes the same information across the entire row of 4 boxes.

CODE

<?php
$con = mysql_connect("localhost","myDatabase name","myDatabase password");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("myTable", $con);

$result = mysql_query("SELECT * FROM myField1 ORDER BY myField2 DESC");

echo "<table summary='myTable summary'>";
while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "<td style='width:25%'>XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'] . "</td>";
 echo "</tr>";
 }
echo "</table>";
mysql_close($con);
?>

*Field/database information was edited out to protect privacy.

As you can see, it tells the database to echo one full row of the same info, then loop through the whole thing as many times as there are records. First of all, I only want a total of 12 records being echoed. Second of all, I want the 4x3 boxes layout.

Could someone please give me some modifications to the code I am using right now? I tried using <div>s also, didn't work.
I'm designing this site for a major section for a game site.

First of all change your select statement to the following:

CODE

$result = mysql_query("SELECT * FROM myField1 ORDER BY myField2 DESC limit 12");

What this will do is to select only the last 12 or whatever number of records from your table.

Now for echoing correctly your data something like this will work:

CODE

<?php
$nRows=mysql_num_rows($result)/4;
$i=0;
echo "<table summary='myTable summary'>\n";
while($i<$nRows){
 echo "<tr>\n";
 for ($h = 0; $h < 4; $h++) {
 echo "<td style='width:25%'>";
 if($row = mysql_fetch_array($result)) {
 echo "XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'];
 }
 else echo " ";
 echo "</td>";
 }
 echo "</tr>\n";
 $i++;
}
echo "</table>\n";
?>

Best regards,

Comment/Reply (w/o sign-up)

FirefoxRocks

Feb 16 2007, 06:36 PM

Before I use the code into my page, could you please explain the \n part of the code that you put in after the table tags? The code looks really good

And I hope it works. I will try it soon. The only thing I don't understand is the \n parts.
***about 1 hour later***
Great! It works! Now I need even more advanced coding. Read the stuff below:
If the $row['type'] is offensive, I want the table cell background to be #f00.
If the $row['type'] is defensive, I want the table cell background to be #48d1cc.

I tried this:

CODE

if($row = mysql_fetch_array($result))
{
if($row['type'] = off)
    {
    $cbkgd="background-color:#f00";
    }
elseif($row['type'] = def)
    {
    $cbkgd="background-color:#48d1cc";
    }
else
    {
    $cbkgd="background-color:#fff";
    }
}

It didn't work, the whole row went to 1 colour and only 1 record/row was displayed. Could someone please help me do this?

Comment/Reply (w/o sign-up)

mastercomputers

Feb 17 2007, 02:33 AM

The "\n" part is just formatting for the source code really. Since you're outputting the content as is, it doesn't take into account newline characters which is what "\n" is.

e.g.

If you did:

CODE

<?php
echo '<table>';
echo '<tr>';
echo '<td>some content</td>';
echo '</tr>';
echo '</table>';
?>

And viewed the source you'll get:

CODE

<table><tr><td>some content</td></tr></table>

Similar to doing:

CODE

<?php
echo '<table><tr><td>some content</td></tr></table>';
?>

Now if you did:

CODE

<?php
echo "<table>\n\t<tr>\n\t\t<td>some content</td>\n\t</tr>\n</table>";
?>

When you view the source it'd look like:

CODE

<table>
 <tr>
 <td>some content</td>
 </tr>
</table>

"\n" is newline characters and "\t" is tab characters.

"\n" is not the HTML element.

Also remember that using PHP, special characters like \n and \t must be in double quotes, not single quotes unless you want to output exactly those characters.

Just test it out if you like:

CODE

<?php
echo '\n'; // displays \n
echo "\n"; // wont display anything but viewing source would have a newline there (just like you pressed enter when typing it)
?>

Read up strings and interpolation in the PHP manual for more understanding and finding more special characters that can be used.

I haven't gone over your code yet, but by the looks of things you'd be better off using HEREDOC strings, again you'll find more information by looking into the strings in the PHP manual.

Cheers,

MC

Comment/Reply (w/o sign-up)

mastercomputers

Feb 17 2007, 02:44 AM

QUOTE(FirefoxRocks @ Feb 17 2007, 07:36 AM)

CODE

if($row = mysql_fetch_array($result))
{
if($row['type'] = off)
    {
    $cbkgd="background-color:#f00";
    }
elseif($row['type'] = def)
    {
    $cbkgd="background-color:#48d1cc";
    }
else
    {
    $cbkgd="background-color:#fff";
    }
}

In this code you're using the assignment operator '=', not the comparison operator '=='. You're also using constants instead of the required string, however if the constants don't exist the string is automatically guessed but this is poor programing. I'm not sure, but I think conditions always evaluate to true if it's assigned any value unless you assign an empty/null/0 value.

You could use a switch statement like:

CODE

switch($row['type']) {
  case 'off':
    $cdkgd = 'background-color: #f00';
    break;
  case 'def':
    $cdkgd = 'background-color: #48d1cc';
    break;
  default:
    $cdkgd = 'background-color: #fff';
    break;
}

Or you could use an if-else statement:

CODE

if($row['type'] == 'off') {
  $cdkgd = 'background-color: #f00';
}
elseif($row['type'] == 'def') {
  $cdkgd = 'background-color: #48d1cc';
}
else {
  $cdkgd = 'background-color: #fff';
}

I still haven't gotten round to looking at your code yet.

Cheers,

MC

Comment/Reply (w/o sign-up)

mastercomputers

Feb 17 2007, 03:41 AM

OK, I have looked at your code and here's my suggestion:

CODE

<?php
$row = array(
 'user' => 'AbUser',
 'victim' => 'Jack the Ripper',
 'turns' => 2,
 'side' => 'right',
 'gold' => 120,
 'lost' => 0,
 'killed' => 0,
 'mxp' => 48,
 'points_b' => 1800,
 'points_a' => 50,
 'type' => 'melee',
 'power' => 'average'
); //remove this array
//$con = mysql_connect('localhost', 'myDatabaseName', 'myDatabasePassword') or exit('Could not connect: '.mysql_error());
//mysql_select_db('myTable', $con);
//$result = mysql_query('SELECT * FROM `myField1` ORDER BY `myField2` DESC LIMIT 12;');
$i = 0;
$tmp_table = '';
//while($row = mysql_fetch_array($result)) {
 if(empty($tmp_table)) {
 $tmp_table .= '<table summary="myTable summary">'."\n\t".'<tr>'."\n";
 }
for($k = 0; $k < 15; $k++) { //remove this for loop
 if($i != 0 && $i % 4 == 0) {
 $tmp_table .= "\t".'</tr>'."\n\t".'<tr>'."\n";
 }
 $tmp_table .= "\t\t".'<td style="width: 25%; border: thin solid black;">XKingdom Member: '.$row['user'].
 ' Name of victim: '.$row['victim'].
 ' Attack turns: '.$row['turns'].
 ' Side of attack: '.$row['side'].
 ' Gold stolen: '.$row['gold'].
 ' Units lost: '.$row['lost'].
 ' Units killed: '.$row['killed'].
 ' MXP Gained: '.$row['mxp'].
 ' Victim\'s points: '.$row['points_b'].
 ' Your points: '.$row['points_a'].
 ' Type of battle: '.$row['type'].
 ' Battle power: '.$row['power'].
 '</td>'."\n";
 $i++;
 if($i == 12) {
 break;
 }
}
//mysql_close($con);
if(!empty($tmp_table)) {
 $tmp_table .= "\t".'</tr>'."\n".'</table>';
 echo $tmp_table;
}
?>

Now since I don't have your database or table, I commented those parts out and just wanted to show a working example. I'm using the same content from my array. To use it, just remove the array, all the comments at the beginning of a line and remove the for loop line and hopefully it's get the results you want. I also added a border around the cells, just to show seperation.

Here's the source code view of HTML, just thought I would add that:

CODE

<table summary="myTable summary">
 <tr>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 </tr>
 <tr>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 </tr>
 <tr>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>

 <td style="width: 25%; border: thin solid black;">XKingdom Member: AbUser Name of victim: Jack the Ripper Attack turns: 2 Side of attack: right Gold stolen: 120 Units lost: 0 Units killed: 0 MXP Gained: 48 Victim's points: 1800 Your points: 50 Type of battle: melee Battle power: average</td>
 </tr>
</table>

Cheers,

MC

Comment/Reply (w/o sign-up)

TavoxPeru

Feb 18 2007, 02:14 AM

QUOTE(FirefoxRocks @ Feb 16 2007, 01:36 PM)

Before I use the code into my page, could you please explain the \n part of the code that you put in after the table tags? The code looks really good

And I hope it works. I will try it soon. The only thing I don't understand is the \n parts.
***about 1 hour later***
Great! It works! Now I need even more advanced coding. Read the stuff below:
If the $row['type'] is offensive, I want the table cell background to be #f00.
If the $row['type'] is defensive, I want the table cell background to be #48d1cc.

I tried this:

CODE

if($row = mysql_fetch_array($result))
{
if($row['type'] = off)
    {
    $cbkgd="background-color:#f00";
    }
elseif($row['type'] = def)
    {
    $cbkgd="background-color:#48d1cc";
    }
else
    {
    $cbkgd="background-color:#fff";
    }
}

It didn't work, the whole row went to 1 colour and only 1 record/row was displayed. Could someone please help me do this?

The \n means a newline and is simply use to code formatting.

Now to add the background color to the table cell you need to modify my code, because as you see it first echoes the TD tag and then echoes the data, also, check your type data, i assume that it is a string data, so, try this instead:

CODE

<?php
$nRows=mysql_num_rows($result)/4;
$i=0;
echo "<table summary='myTable summary'>\n";
while($i<$nRows){
 echo "<tr>\n";
 for ($h = 0; $h < 4; $h++) {
 $cbkgd="#fff;";
 echo "<td style='width:25%;background-color:";
 if($row = mysql_fetch_array($result)) {
 if($row['type'] == 'off') {
 $cbkgd="#f00;";
 }
 elseif($row['type'] == 'def') {
 $cbkgd="#48d1cc;";
 }
 echo $cbkgd . "'>";
 echo "XKingdom Member:" . $row['user'] . " Name of victim:" . $row['victim'] . " Attack turns:" . $row['turns'] . " Side of attack:" . $row['side'] . " Gold stolen:" . $row['gold'] . " Units lost:" . $row['lost'] . " Units killed:" . $row['killed'] . " MXP Gained:" . $row['mxp'] . " Victim's points:" . $row['points_b'] . " Your points:" . $row['points_a'] . " Type of battle:" . $row['type'] . " Battle power:" . $row['power'];
 }
 else echo $cbkgd . "'> ";
 echo "</td>";
 }
 echo "</tr>\n";
 $i++;
}
echo "</table>\n";
?>

Best regards,

Comment/Reply (w/o sign-up)

Retrieving Data And Displaying In Boxes - How do I make a grid of boxes?

Retrieving Data And Displaying In Boxes - How do I make a grid of boxes?