PHP Forum

**Gilligan** · 06-22-2008, 12:52 PM

Using auto_increment in an' id' column in my mysql database, I want to display the 5 newest entries in this table

PHP Code:


			
$select = mysql_query("SELECT * FROM recent_updates ORDER BY id DESC LIMIT 0,5");
$row = mysql_fetch_array($select);

        echo '<p>' . $row['date'] . '</p>
         <p>' . $row['update_text'] . '</p>';

That only shows the newest one result, even though I've set the limit correct (i think)

Can someone help me?

**tripy** · 06-22-2008, 02:49 PM

you mixed the arguments.
The full syntax (that i prefer because less error prone) is:

Code:

select * from recent_updates 
order by id desc
limit 5 offset 0

**Gilligan** · 06-22-2008, 02:55 PM

its still just displaying the one

**dansgalaxy** · 06-22-2008, 04:58 PM

try just having:

PHP Code:


			
$select = mysql_query("SELECT * FROM recent_updates ORDER BY id DESC LIMIT 5");
$row = mysql_fetch_array($select);
 
        echo '<p>' . $row['date'] . '</p>
         <p>' . $row['update_text'] . '</p>';

TP apprieciated if it works

**tripy** · 06-22-2008, 05:23 PM

Ok, I see why, you don't loop over the result set from the db:

PHP Code:


			
$q="SELECT * FROM recent_updates ORDER BY id DESC LIMIT 5 OFFSET 0";
$r=mysql_query($q);
while($o=mysql_fetch_object($r)){
  echo<<<HTML
<p>Date: {$o->date}</p>
<p>text: {$o->update_text}</p>
<hr/>
HTML;
}

And that way, you should have every rows displayed

**JeremyMiller** · 06:40 PM

It would be prudent to include a check to make sure that the query worked. I could have sworn I answered this question recently.

EDIT: I did at http://www.webmaster-talk.com/php-fo...t-updates.html

Is this a mistaken double post?

**Gilligan** · 06:59 PM

I didn't know what MySQLi was, and so left it as I'm working with a deadline. I realise now you have replied.

i took your Mysql query and used it in the more basic code, but forgot to loop over the results (thanks tripy)

TP for both of you (and I'll through one in for dan for trying

)

EDIT: How would I make it so you can edit the date and text of it? Thanks

**JeremyMiller** · 06-22-2008, 07:21 PM

Oh. Next time just ask and I can convert it for you. I always start with what I consider the best and then take it from there. Here is the adjusted code:

PHP Code:


			
<?php
$recent_updates = '';
if ($my_query = mysql_query("SELECT `date`, update_text FROM recent_updates ORDER BY id DESC LIMIT 5")) {
  if (mysql_num_rows($my_query) >0) {
    while ($a_result = mysql_fetch_object($my_query)) {
    $recent_updates .= '
<p>'.date('m/d/y',strtotime($a_result->date)).'</p>
<p>'.htmlentities($a_result->update_text).'</p>';
    }
  }
}
if (strlen($recent_updates) > 0) {
  echo '<div class="recent_updates">'.$recent_updates.'</div>';
}
?>

**JeremyMiller** · 06-22-2008, 07:34 PM

Saw your edit. It's a bit much to come to a forum and ask people to write a whole script for you, but since you've been nice, I'll wave my usual resistance to this.

Here you go:

PHP Code:


			
<?php
if (isset($_POST['recent_update'])) {
  $error_message = '';
  foreach ($_POST['recent_update'] as $id=>$fields) {
    $id = (int)$id;
    if ($id > 0) {      
      $date = date('Y-m-d',strtotime($fields['date']));
      $update_text = mysql_real_escape_string(stripslashes($fields['update_text'])); //Can remove stripslashes if your server is configured properly.
      if (!mysql_query("UPDATE recent_updates SET date='".$date."', update_text='".$update_text."' WHERE id=".$id." LIMIT 1")) {
        $error_message .= 'There was an error updating record '.$id.'.  MySQL reported '.mysql_error().'<br />';
      }
    }
  }
  if (strlen($error_message) > 0) {
    echo '<div class="error_message">'.$error_message.'</div>';
  }
}
$recent_updates = '';
if ($my_query = mysql_query("SELECT id, `date`, update_text FROM recent_updates ORDER BY id DESC LIMIT 5")) {
  if (mysql_num_rows($my_query) >0) {
    while ($a_result = mysql_fetch_object($my_query)) {
    $recent_updates .= '
<p>'.date('m/d/y',strtotime($a_result->date)).'</p>
<p>'.htmlentities($a_result->update_text).'</p>
<div onClick="document.getElementById(\'recent_update_'.$a_result->id.'\').style.display=\'block\';">Edit</div>
<div id="recent_update_'.$a_result->id.'" style="display:none;">
<input type="text" size="10" name="recent_update['.$a_result->id.'][date]" value="'.date('m/d/y',strtotime($a_result->date)).'" />
<textarea name="recent_update['.$a_result->id.'][update_text]" rows="8" cols="80">'.htmlentities($a_result->update_text).'</textarea>
</div>';
    }
  }
}
if (strlen($recent_updates) > 0) {
  echo '<form method="post"><div class="recent_updates">'.$recent_updates.'</div><input type="submit" value="Save Changes" /></form>';
}
?>

EDIT: I'll take a cut of your payment when you get it.

j/k of course.

**dansgalaxy** · 06-22-2008, 09:45 PM

Wow im slow, i missed the whole loop thing

Dan

**Gilligan** · 06-23-2008, 04:20 AM

Thank You!!

**Gilligan** · 06-23-2008, 01:33 PM

Sorry its displaying yyyy-mm-dd

Can it be dd/mm/yyyy please?

**JeremyMiller** · 06-23-2008, 01:44 PM

Not sure where you're talking about, but it sounds like you need to check out the date() syntax. It's at http://www.php.net/date . Check that out and let me know how your attempts go.

**tripy** · 06-23-2008, 02:24 PM

or rather than PHP date(), look for mysql DATE_FORMAT() to have the db return you the date directly in the right format
http://dev.mysql.com/doc/refman/5.0/...on_date-format

**wayfarer07** · 06-23-2008, 02:55 PM

Here is the function I use to convert MySQL dates to any format I wish:

PHP Code:


			
function convert_date($date, $format) {
$d = strtotime($date);
$d = date($format, $d);
return $d;
}

Where $date is the MySQL formatted date, and $format the desired output. I have this saved as a code snippet because it comes up so often in my day to day work.