Display latest 10 results on search page before searching

[bruisr]

I have a search page and everything works fine but I would like to add the latest 10 rows to the page so there is something to look at or click on before the user searches.

 

The page is at: www.giantstridedives.com/locations. You can see that it's pretty boring. Hit submit and you'll see the entries so far (only 3 right now). I'd like to have the latest 10 display there by default but then when a user searches they are replaced by the search results. Make sense?

 

Here's the code I'm using for the search:

 

<?php
if (isset($_POST['submit'])) {

require_once('dbconnection.php'); 
mysqli_select_db($conn, $dbname); 
$searchterm = $_POST['search']; 
$searchterm = strip_tags($searchterm);
$searchterm = trim ($searchterm);
$q ="SELECT * FROM locations WHERE loc_name LIKE '%" . $searchterm . "%' AND loc_approved != '0'"; 
$r = mysqli_query($conn, $q) or die(mysql_error($q)); 
$row = mysqli_fetch_assoc($r); 
$rowcount = mysqli_num_rows($r);
}
?>

<h1>Search for Scuba Dive Locations</h1>
<form id="searchform" name="searchform" method="post" action="index.php">  
    <input type="text" name="search" id="search_bar" />
    <input type="submit" name="submit" id="submit" value="Submit" /> </form>
<span style="color: #333;">Search for the name of the location, ie: Devils Den or USS Oriskany.</span><br />    


<?php 
if (isset($_POST['submit'])) {

if ($rowcount == 0)  
{
echo "Sorry, but we can not find an entry to match your query: <strong>$searchterm</strong>";
}
else { //this tells the table below to display since $totalRows_Products_res is not 0
?> 
<table style="font-size: 14px;" border="0" width="650">
<tr>
  <td colspan="6"><p>You searched for <strong><?php echo $searchterm; ?></strong>. Results are displayed below.</p></td>
</tr>
<tr>
  <td><strong>Location Name</strong></td>
  <td><strong>City</strong></td>
  <td><strong>State</strong></td>
  <td><strong>Depth (ft)</strong></td>
  <td><strong>Fees</strong></td>
  <td><strong>Gear Rentals</strong></td>
  <td><strong>Map</strong></td>
</tr>
<?php do { ?>
  <tr>
   <td><a href="loc_details.php?loc_id=<?php echo $row ['loc_id']; ?>">
<?php echo $row ['loc_name']; ?> </a></td>
<td><?php echo $row ['loc_city']; ?></td>
<td><?php echo $row ['loc_state']; ?></td>
<td><?php echo $row ['loc_depth']; ?></td>
<td>$<?php echo $row ['loc_fee']; ?></td>
<td><?php if($row['loc_gear'] == 1) {
	echo 'Yes';
} else { echo 'No'; } ?></td>
<td><a href="http://maps.google.com/maps?q=<?php echo $row ['loc_lat'];?>,<?php echo $row ['loc_lon'];?>" target="_blank">View Map</a></td>
   </tr>
  <?php } while ($row = mysqli_fetch_assoc($r)); ?>
</table>
<?php 
mysqli_free_result($r); 
} // this closes out results else
}
?>

 

I figure I can use the below query to get the results.

select * from table WHERE id >  ((SELECT MAX(id) from table) - 10);

I'm just not sure how I can display them by default and then have them be replaced by the search results.  Anyone have any ideas for me? As always I appreciate the help as I continue to learn this beast.

[Psycho]

No, you would ORDER the results by their date and then use LIMIT to get only 10. At least if you want the last 10 records you should have a date. Otherwise you could use the ID value - but that is not the proper method. Just replace 'date' with 'id in the provided query below.

 

The following fixes a lot of problems with your code that I'm too lazy to go into right now. But, let me know if you have any questions

<?php

//Define count of records to show by default
$recentResultLimit = 10;

require_once('dbconnection.php'); 
mysqli_select_db($conn, $dbname); 

//Create and run query
if (isset($_POST['search']))
{
$searchterm = mysql_real_escape_string(trim($_POST['search']));
$query = "SELECT * FROM locations WHERE loc_name LIKE '%{$searchterm}%' AND loc_approved != '0'"; 
}
else
{
$query = "SELECT * FROM locations WHERE loc_approved != '0' ORDER BY date DESC LIMIT {$recentResultLimit}"; 
}
$result = mysqli_query($conn, $query) or die(mysql_error($query));

//Generate message and output
$message = '';
if(isset($_POST['search']))
{
    $resultCount = mysqli_num_rows($result);
    if($resultCount==0)
    {
        $message = "Sorry, but we can not find an entry to match your query: <strong>{$searchterm}</strong>";
    }
    else
    {
        $message = "Your search for <strong>{$searchterm}</strong> returned {$resultCount} results.";
    }
}
else
{
    $message = "Here are the {$recentResultLimit} most recent results.";
}

$output = '';
while($row = mysqli_fetch_assoc($result))
{
    $rentals = ($row['loc_gear'] == 1) ? 'Yes' : 'No';
    $output .= "<tr>\n";
    $output .= "<td><a href='loc_details.php?loc_id={$row['loc_id']}'>{$row['loc_name']}</a></td>\n";
    $output .= "<td>{$row['loc_city']}</td>\n";
    $output .= "<td>{$row['loc_state']}</td>\n";
    $output .= "<td>{$row['loc_depth']}</td>\n";
    $output .= "<td>\${$row['loc_fee']}</td>\n";
    $output .= "<td>$rentals</td>\n";
    $output .= "<td><a href='http://maps.google.com/maps?q={$row['loc_lat']},{$row['loc_lon']}' target='_blank'>View Map</a></td>\n";
    $output .= "</tr>\n";
}

mysqli_free_result($result);

?>

<h1>Search for Scuba Dive Locations</h1>
<form id="searchform" name="searchform" method="post" action="index.php">  
<input type="text" name="search" id="search_bar" />
<input type="submit" name="submit" id="submit" value="Submit" /> </form>
<span style="color: #333;">Search for the name of the location, ie: Devils Den or USS Oriskany.</span><br />    

<table style="font-size: 14px;" border="0" width="650">
<tr>
  <td colspan="7"><p><?php echo $message; ?></p></td>
</tr>
<tr>
  <th>Location Name</th>
  <th>City</th>
  <th>State</th>
  <th>Depth (ft)</th>
  <th>Fees</th>
  <th>Gear Rentals</th>
  <th>Map</th>
</tr>
<?php echo $output; ?>
</table>

[bruisr]

No, you would ORDER the results by their date and then use LIMIT to get only 10. At least if you want the last 10 records you should have a date. Otherwise you could use the ID value - but that is not the proper method. Just replace 'date' with 'id in the provided query below.

 

mjdamato you're the man. Thanks for that. I did need to change from date to id since I don't have a date field set up. I should do that though, so thanks for reminding me about that.

 

It worked perfectly to display the latest results but when I did a search, it returned this error at the top of the page:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user ''@'localhost' (using password: NO) in C:\websites\giantstridedives\locations\index.php on line 12

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\websites\giantstridedives\locations\index.php on line 12

 

I removed the 'mysql_real_escape_strings()' code and it worked. After doing some searching, I found someone who said "Turns out i needed a database link in code before the rest. " I assume that he means the connection to the database. So after taking the advice in your signatures, I checked the php.net on mysql_real_escape_strings() and found an example that had a 'mysql_connect()' before the escape strings. So I added it above that line:

mysql_connect($dbhost, $dbuser, $dbpass);
$searchterm = mysql_real_escape_string(trim($_POST['search']));

Problem solved.

 

Thanks again for your help!