Moving through records

I've asked this question on the database board but I'm not sure if its mainly to do with SQL or PHP so I'm asking on this board as well...

1. Firstly I want to be able to do a search, for instance search for only posts within a certain category (this would dependent on the SQL query I'm guessing) and then cycle through only these....

2. My viewpost.php form is also passed a postid (eg. viewpost.php?postid=100) and I thought to go back back n forwards just send post=99 or post=101 etc. However this might not be such a good idea since what if post 99 or 101 didnt exist? Is there a better method to do this?

Thx :wave:

1) Use a SELECT statement to get records from the posts table and use whatever string comparison operators your DBMS provides to match the query text.
If you want to preserve the results, use the CREATE VIEW statement.

2) SELECT * FROM table_name WHERE post_id < 100 ORDER BY post_id DESC LIMIT 1

Hmmm ok thanks for that -- so really PHP does not come into this search thing much its just dependant on the SQL code??

Well, it's a good idea to try and make the database system do as much of the work as it can. The more expressive your SQL queries, the less data has to be transferred and the less work you have to do in the PHP script.

For your first question, if you want to search by categories you can just select the categories you want, as long as you have a category field in your table. If you also want to do some key word searches and you're not sure how to do those with MySQL, you can use the LIKE operator combined with wildcards. It can be used like so:

PHP Code:

---

<?php
  $keyword = mysql_real_escape_string($_POST['keyword']);
  $sql = "SELECT * FROM table WHERE name LIKE '%" . $keyword . "%'";
  //match any name with the $keyword in it
  $query = mysql_query($sql);
  echo mysql_num_rows($query) . ' results found<br /><br />';
  while($array = mysql_fetch_array($query)){
    //...
  }
?>

---

For your second question, depending on your situation, you might want to just do the query selecting whichever post is in the $_GET['post']. If the post doesn't exist, you can simply print out "this post doesn't exist/was removed." This will only be useful if your postid is a key that is auto_incremented, which you sometimes delete from (this would be like some type of forum, or comment system). If you want your thing to automatically find next closest post going downward or upward, you can basically use what penagate suggested with some minor changes (this would be for browsing part numbers on a merchandise site, or something like that).