retriving entries from a MySQL Database and displaying them

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
revski
Forum Newbie
Posts: 5
Joined: Mon Jan 11, 2010 12:29 pm

retriving entries from a MySQL Database and displaying them

Post 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
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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'];
    }
revski
Forum Newbie
Posts: 5
Joined: Mon Jan 11, 2010 12:29 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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.
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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";
revski
Forum Newbie
Posts: 5
Joined: Mon Jan 11, 2010 12:29 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: retriving entries from a MySQL Database and displaying them

Post by social_experiment »

Try the following :

Code: Select all

 
<?php
 $query  = "SELECT id, team1, team2 FROM results ORDER BY id DESC  LIMIT 0, 5";
?>
 
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
revski
Forum Newbie
Posts: 5
Joined: Mon Jan 11, 2010 12:29 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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 :D

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

Code: Select all

<? print "$id" ?>
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.
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: retriving entries from a MySQL Database and displaying them

Post by MicroBoy »

I think that the last code it is not OK it has to end with an ;

Code: Select all

<? print "$id"; ?>
revski
Forum Newbie
Posts: 5
Joined: Mon Jan 11, 2010 12:29 pm

Re: retriving entries from a MySQL Database and displaying them

Post by revski »

i have tried using

Code: Select all

<? print "$id"; ?>
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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</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.
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: retriving entries from a MySQL Database and displaying them

Post 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.
divito
Forum Commoner
Posts: 89
Joined: Sun Feb 22, 2009 7:29 am

Re: retriving entries from a MySQL Database and displaying them

Post 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>  
Post Reply