Help With PHP/Mysql Table/List

ryanhami

Hello,

I am having trouble with a list that I am trying to make in php and mysql. I have got the basic list script and mysql table setup. Its all working all right and I want to make it a bit more advanced. The script is for people to submit there friend codes for popular apps on the ipod touch and iphone. I have got that part done but I want it so that it only displays 100 at a time and that the new codes get put to the top and not at the bottom of the table.

The code is as follows :

This is the index.php code where the table is shown and the submit form.

<html>
<body>

<form action="insert.php" method="post">
Code: <input type="text" name="code" />
<input name="submit" type="button" value="Submit" />
</form>

</body>
</html> 

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

mysql_select_db("prx2_codes", $con);

$result = mysql_query("SELECT * FROM codes");

echo "<table border='1'>
<tr>
<th>Position</th>
<th>Codes</th>
</tr>";


while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['position'] . "</td>";
  echo "<td>" . $row['code'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

This is the insert.php that is used in the form.

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

mysql_select_db("prx2_codes", $con);

$sql="INSERT INTO codes (code)
VALUES
('$_POST[code]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Code Added To List";

mysql_close($con)
?>

If you could please help me in doing what I said it would be much appreciated.

bpat1434

Well, to get teh most recently added listed at the top, then you want to ORDER DESC in the sql select query 😉 I'm hoping you have a timestamp associated with when the record was inserted. If so, use that to order DESC, otherwise, you can use the auto_increment ID which you are hopefully assigning every new row.

To limit to the top 100, use the LIMIT clause. Now if you want pagination, the limit clause looks like:
LIMIT [start], [number]

So if I were on the second page, it would be: LIMIT 100, 100

If you just want the first 100 results, it's LIMIT 100 (or more appropriately LIMIT 0, 100).

ryanhami

Hello,

Thanks for that I has helped a lot. I should of explained the 100 at a time a bit better. What I want it to do is, when people keep adding there codes to the database and the list the ones that are at 100 disappear so its not just the first 100 that are posted stay there.

bpat1434

Right... so if you order by a timestamp DESC it will list from the most recent to the oldest. So when someone comes along, their code will be at the top, and everyone elses will shift down one spot. And the next person will come in and submit their code, then it will be on top, and the previous person will will be in spot #2. And so on and so on.

ryanhami

Thankyou,

This is my new upgraded code that is practically finished, but the only thing I want it to do now is to only display the newest 100. Could somebody please help me in doing that.

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

mysql_select_db("ryanhami_codes", $con);

$result = mysql_query("SELECT * FROM imob ORDER BY timedate DESC");

echo "<table border='1'>
<tr>
<th>Friend Codes</th>
</tr>";


while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><center>" . $row['code'] . "</center></td>";


  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

Thanks Ryan

bradgrafelman

You might want to be more careful about posting your DB credentials; I've removed them from this thread twice now. 😉

As for the "first x number" bit, take a look at MySQL's LIMIT syntax.

ryanhami

Thanks For Removing Them, Completely Forgot.

Thanks For The Link, Will Have A Look Now