[SOLVED] Page Iteration Function Help Needed

[NoSalt]

I am working on a site that has a page where people can submit items and another page where people can read items that have been submitted. I have a default "authorised" flag set so that any new submissions must be read over by me before they are viewable by the general public. I will change the flag so that the approved submission is authorised and, thereby, viewable. That is where I need some help.

 

When the page loads, it will print the very first submission in the table; the page shows only one submission at a time. I have a column called "id" that is +1 auto-incremented. I have three buttons on the page that will take a visitor to the "previous page/submission", "next page/submission", or "random page/submission". Everything works great with a "select * from table where id=<some number>", but when I add in "&& authorised = 'true'" the whole thing breaks. What I am trying to figure out is a good way to efficiently and fluidly iterate to the next authorised table entry.

 

Any help in getting this figured out would be greatly appreciated.

 

Here is some code:

 

$whereNow = ($_GET['whereNext']) ? $_GET['whereNext'] : '1';

                                                                               

$sqlQuery = "select * from table where id=" . $whereNow . " && authorised='true'";

$sql = mysql_query($sqlQuery);

                                                                               

$recordQuery = mysql_query("select count(*) from table");

$recordResult = mysql_result($recordQuery, "total");

                                                                               

$previous = ($whereNow == 1) ? null: ($whereNow-1);

$random = rand(1, $recordResult);

$next = ($whereNow == $recordResult) ? null : ($whereNow+1);

 

Thanks   

[sasa]

try

$whereNow = ($_GET['whereNext']) ? $_GET['whereNext'] : '1';
                                                                               
$sqlQuery = "select * from table where authorised='true' limit $whereNow, 1";
$sql = mysql_query($sqlQuery);
                                                                               
$recordQuery = mysql_query("select count(*) as total from table");
$recordResult = mysql_result($recordQuery, "total");
                                                                               
$previous = ($whereNow == 0) ? null: ($whereNow-1);
$random = rand(0, $recordResult - 1);
$next = ($whereNow == $recordResult - 1) ? null : ($whereNow+1);

[NoSalt]

Sasa ... thank you for replying but that didn't work. What that did was iterate throught all of the "authorised='false'" submissions (starting with the second one and not the first) and then stop at the last one and not keep going. If I were to use:

 

$sqlQuery = "select * from table where id=" . $whereNow"

 

It will iterate through all of the posts (starting with the first post) and then start back over with the first post at the end, creating a giant neverending loop. It's just that darn "authorised" column that is giving me the headache. I would just select them all, create a new "virtual" table and loop throught those but I am hoping that the database will get to be pretty big and that would be pretty inefficient to grab them all everytime somebody wanted to read posts. Here are my navigation links in case they help.

 

<div class="navigation">
    <span class="nav" onclick="document.location='<?php echo $_SERVER['PHP_SELF']; ?>?whereNext=<?php echo $previous; ?>'">&#171; previoius</span>
    <span class="nav" onclick="document.location='<?php echo $_SERVER['PHP_SELF']; ?>?whereNext=<?php echo $random; ?>'">&#171; random &#187;</span>
    <span class="nav" onclick="document.location='<?php echo $_SERVER['PHP_SELF']; ?>?whereNext=<?php echo $next; ?>'">next &#187;</span>
</div>

 

I guess what I need is to find a fast and efficient way to figure out if the table entry at id = $whereNow is "authorised" or not. If it is not "authorised", I need to keep skipping to the next iteration until I find one that is authorised. Or ... maybe I should just forget all of this "authorised" business anyhow. What I am hoping to avoid is somekind of legal action if some idiot posts something deflamatory or libelous or slanderous on my site. The LAST thing I want is to be caught up in something like that, something you read about on Digg or Reddit all the time. Ugh ... the legal world certainly makes things difficult for everybody.

[sasa]

i don't test code and i see that i make same mustakes

try

$whereNow = ($_GET['whereNext']) ? $_GET['whereNext'] : '0'; //startin from 1st record
                                                                               
$sqlQuery = "select * from table where authorised='true' limit $whereNow, 1"; //i don't figure out haw it output authorised='false'
$sql = mysql_query($sqlQuery);
                                                                               
$recordQuery = mysql_query("select count(*) as total from table where authorised='true' ");// add criteria
$recordResult = mysql_result($recordQuery, "total");
                                                                               
$previous = ($whereNow == 0) ? $recordResult - 1: ($whereNow-1);// before 1st is last
$random = rand(0, $recordResult - 1);
$next = ($whereNow == $recordResult - 1) ? 0 : ($whereNow+1); // after last is 1st

what is hapend in your code if you delete one row from table?

[NoSalt]

Sasa ... that worked GREAT, thank you very much!!! Problem now solved.

 

[NoSalt]

what is hapend in your code if you delete one row from table?

 

I haven't tried to delete a row yet and I'm not sure what will happen if I do. I think I may just keep those rows I want to delete marked as unauthorised and forget it.