Page 1 of 1
retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 12:42 pm
by revski
hi im new to PHP and have just started learing about it.
i would like to know how to retrieve information from a database table, and display it on a page in a table, split into the correct rows:
the MySQL table is layed out like:
ID:
Team1:
Team2:
it only has 3 rows.
i want to be able to display entries from this database in a table on a page, but it must only display the 5 latest entries into the database.
using print, i have the table drawn onscreen when the page is executed.
the table isnt much hehe, but only thing im interested in right now is getting it to display the results i need.
just to make it clear.
i have the table on the page with the names of the DB rows as headings, i want to display the 5 latest values from the database in the each of the rows below the headings.
The code for the entire page is:
Code: Select all
<?
include 'config.php';
$query = "SELECT id, team1, team2 FROM results";
$result = mysql_query($query);
if (!$result) {
die('Could not query:' . mysql_error());
}
{
print"<HTML><table width=\"200\" border=\"1\">
<tr>
<td>ID:<br></td>
<td>Team1:</td>
<td>Team2:</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table></html>";
}
?>
i dont know the correct MySQL function to use or even how to code it correctly, could somebody please help me out.
im sorry if ive done everything completely wrong in the code above, but im completely new to PHP and have never used it before.
many thanks
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 12:52 pm
by MicroBoy
About Showing the data from MySQL.
Code: Select all
while ($row = mysql_fetch_array($result))
{
echo $row['ID'];
echo $row['Team1'];
echo $row['Team2'];
}
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 2:50 pm
by revski
ok i can use that code to retrieve the values from the database
my code now looks like this:
Code: Select all
<?
include 'admin/config.php';
$table = "results";
$query = "SELECT id, team1, team2 FROM results";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
echo "ID:{$row['id']} <br>" .
"Team 1: {$row['team1']} <br>" .
"Team 2: {$row['team2']} <br><br>";
?>
but my problem is that code writes all the values in the database to the screen, repeating ID, Team1 and Team2 every time, what i am looking for now is code to write the 5 highest ID values, to the screen.
so for example out of 30 database entries i only want 25 - 30 to print on the screen, but the total number of entries in the database will always be random.
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 3:11 pm
by MicroBoy
Here you have some examples, I hope this is what are you looking for:
Code: Select all
This will display the first 10 results from the database:
$query = "SELECT id, team1, team2 FROM results LIMIT 0, 10";
This will show records 6, 7, 8, 9, and 10:
$query = "SELECT id, team1, team2 FROM results LIMIT 5, 5";
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 3:48 pm
by revski
that is almost what im looking for, except i want the code to take the latest results from the database, as in i want the 5 latest database entries displayed, using Limit 0, 5 i am able to make it display the FIRST 5 entries in the database, but they are old entries.
each time a new entry is added i want that to be at the top of the query followed by the 4 entries behind it.
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 4:09 pm
by social_experiment
Try the following :
Code: Select all
<?php
$query = "SELECT id, team1, team2 FROM results ORDER BY id DESC LIMIT 0, 5";
?>
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 4:42 pm
by revski
that worked perfect, the first time i had tryed changing the order i manually, changed the table in phpmyadmin, which didnt make any difference at all
thanks for helping with that problem.
i adjusted the code to the following
Code: Select all
include 'php/config.php';
$table = "results";
$query = "SELECT id, team1, team2 FROM results ORDER BY id DESC LIMIT 0, 5";
$result = mysql_query($query);
echo "ID: Team1: Team2: <br>";
while ($row = mysql_fetch_array($result))
echo "{$row['id']} {$row['team1']} {$row['team2']} <br>";
so that ID team1 and team2 are not repeated,
now the last thing i wanted to do was display the results in a table on a page
how would i get each of the seperate values into the correct cells of a table?
how can i add the values from my query into a table?
To add the values to the DB im using the following code:
Code: Select all
<?
include 'config.php';
$table = "results";
$sqlquery = "INSERT INTO $table
VALUES('$id','$team1','$team2')";
$results = mysql_query($sqlquery);
mysql_close();
?>
then i am using
etc.. in each of the seperate table cells. I have tried using print "$id" the same way, to get the 5 latest values into a table as i want them but print doesnt return anything to the screen.
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 4:46 pm
by MicroBoy
I think that the last code it is not OK it has to end with an ;
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 5:18 pm
by revski
i have tried using
in a table cell but this does not work.
This is the entire code for the page:
Code: Select all
<?
include 'php/config.php';
$table = "results";
$query = "SELECT id, team1, team2 FROM results ORDER BY id DESC LIMIT 0, 5";
$result = mysql_query($query);
echo "ID: Team1: Team2: <br>";
while ($row = mysql_fetch_array($result))
echo "{$row['id']} {$row['team1']} {$row['team2']} <br>";
?>
<center><table width="500" border="0">
<tr>
<td>ID</td>
<td>Team 1</td>
<td>Team 2</td>
<center> </center>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</center>
The query lists the values above the table, but i need to know how to put the values into the table.
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 6:30 pm
by MicroBoy
I hope this will be useful, (I hope I did not make any mistakes):
Code: Select all
<?
include 'php/config.php';
$table = "results";
$query = "SELECT id, team1, team2 FROM results ORDER BY id DESC LIMIT 0, 5";
$result = mysql_query($query);
echo '</br><table border="1" cellpadding="0" cellspacing="0">';
echo '<tr> <th>ID</th> <th>Team1</th> <th>Team2</th> </tr>';
while ($row = mysql_fetch_array($result))
{
echo '<tr><td>';
echo $row['id'];
echo '</td><td>';
echo $row['team1'];
echo '</td><td>';
echo $row['team2'];
echo '</td></tr>';
}
echo '</table>';
?>
p.s. I do not think that you have to set $table variable because as I see here you are not using it.
Re: retriving entries from a MySQL Database and displaying them
Posted: Mon Jan 11, 2010 6:44 pm
by divito
Basically the same idea as the previous poster, though I tend to keep as much HTML as possible separate from the PHP, unless I need it with the query. Thus, I've set the table outside the query and made the output more concise.
Code: Select all
<center><table width="500" border="0">
<tr>
<td>ID</td>
<td>Team 1</td>
<td>Team 2</td>
<?php
include 'php/config.php';
$query = "SELECT id, team1, team2 FROM results ORDER BY id DESC LIMIT 0, 5";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$team1 = $row['team1'];
$team2 = $row['team2'];
echo "<tr>
<td>".$id."</td>
<td>".$team1."</td>
<td>".$team2."</td>
</tr>";
}
?>
</table>
</center>