Display last 4 entries

[whiteyoh]

Hi,

I have a search box which allows you to look up something and display. What i cant figure out is how to amend the script to display, for example, 5 random stories.

Any help is appreciate. The code used to display the search is below

// The basic SELECT statement
$select = 'SELECT DISTINCT id, joketext';
$from   = ' FROM joke';
$where  = ' WHERE 1=1';

$aid = $_POST['aid'];
if ($aid != '') { // An author is selected
  $where .= " AND authorid='$aid'";
}

$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
  $from  .= ', jokecategory';
  $where .= " AND id=jokeid AND categoryid='$cid'";
}

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND joketext LIKE '%$searchtext%'";
}
?>

<table>
<tr><th>Joke Text</th><th>Options</th></tr>

<?php
$jokes = @mysql_query($select . $from . $where);
if (!$jokes) {
  echo '</table>';
  exit('<p>Error retrieving jokes from database!<br />'.
      'Error: ' . mysql_error() . '</p>');
}

while ($joke = mysql_fetch_array($jokes)) {
  echo "<tr valign='top'>\n";
  $id = $joke['id'];
  $joketext = htmlspecialchars($joke['joketext']);
  echo "<td>$joketext</td>\n";
  echo "<td><a href='editjoke.php?id=$id'>Edit</a> | " .
      "<a href='deletejoke.php?id=$id'>Delete</a></td>\n";
  echo "</tr>\n";
}
?>

</table>

[whiteyoh]

well having mulled over this for a while, i came up with the following solution BUT i need it to display 4 records, and not just one.

Any ideas anybody?

Thanks

<?php 
$dbh=mysql_connect ("localhost", "dbase", "password") or die 
('I cannot connect to the database because: ' . mysql_error()); 
mysql_select_db ("something"); 
$rs = mysql_query("SELECT joketext FROM joke ORDER BY RAND() LIMIT 1"); 
$row = mysql_fetch_row($rs); 


echo $row[0]; 

?>

[Markus]

well having mulled over this for a while, i came up with the following solution BUT i need it to display 4 records, and not just one.

Any ideas anybody?

Thanks

<?php 
$dbh=mysql_connect ("localhost", "dbase", "password") or die 
('I cannot connect to the database because: ' . mysql_error()); 
mysql_select_db ("something"); 
$rs = mysql_query("SELECT joketext FROM joke ORDER BY RAND() LIMIT 1"); 
$row = mysql_fetch_row($rs); 


echo $row[0]; 

?>

I'm getting deja vu!
Maybe setting the query into a function, then calling the function in a for loop would produce 4 random entries?

function rand_entry()
{
     $rs = mysql_query("SELECT joketext FROM joke ORDER BY RAND() LIMIT 1"); 
     $row = mysql_fetch_row($rs); 
     return $row[0];
}

for( $i = 0; $i < 4; ++$i )
{
     echo rand_entry();
}

Let me know if this helps.

[Atli]

How about just:
[code=mysql]
SELECT joketext FROM joke ORDER BY RAND() LIMIT 4
[/code]

[Muddasir]

May be this will work

Change LIMIT 1 to LIMIT 4

Regards

[whiteyoh]

Hi All,

Thanks for the advice,

Still no joy as it only displays 1.

I tried both ideas separately.

Any more?

[whiteyoh]

ive been tinkering all morning and it just wont get past displaying more than 1 entry!

[Markus]

ive been tinkering all morning and it just wont get past displaying more than 1 entry!

I think it's the way you're using this part:

$rs = mysql_query("SELECT joketext FROM joke ORDER BY RAND() LIMIT 1"); 
$row = mysql_fetch_row($rs); 
return $row[0]; # this line is the problem i think.

I think you'll need to loop through the results - makes sense, right?
Try something like this:

$rs = mysql_query("SELECT joketext FROM joke ORDER BY RAND() LIMIT 1") or die (mysql_error()); 

while( $rows = mysql_fetch_array( $rs ) )
{
    echo $rows[0];
}

[whiteyoh]

fantastic, thank you very much