Closed Thread Icon

Topic awaiting preservation: getting PREVIOUS and NEXT values from db (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=12337" title="Pages that link to Topic awaiting preservation: getting PREVIOUS and NEXT values from db (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: getting PREVIOUS and NEXT values from db <span class="small">(Page 1 of 1)</span>\

 
Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 07-31-2002 23:58

Let's say I have this database that has tons of records in it (in this case, articles). At the bottom of each page, I'd like to do a PREVIOUS ARTICLE and a NEXT ARTICLE link. So, I need to get the info from the database in order to construct the URLs for these links.

Using $aid as a URL variable to indicate the CURRENT article, I'm doing this ('article' in the select statements is the field in the table that holds the ID number ($aid)):

<?php
if (!isset($aid)){
$aid = 500; // just in case I'm lazy
}

// Let's get the NEXT value
$sql = "select article from articles where article > $aid order by article asc LIMIT 1";
mysql_query($sql) or die ("<b>SQL query failed getting NEXT value:</b><br />$sql<br /><br />");
$result = mysql_query($sql);
$myrow = mysql_num_rows($result);

if ($myrow > 0){
while ($row = mysql_fetch_row($result)) {
$next = $row[0];
}
}else{
$next = "No rows, bud";
}
// Let's get the PREVIOUS value
$sql = "select article from articles where article < $aid order by article desc LIMIT 1";
mysql_query($sql) or die ("<b>SQL query failed getting PREVIOUS value:</b><br />$sql<br /><br />");
$result = mysql_query($sql);
$myrow = mysql_num_rows($result);

if ($myrow > 0){
while ($row = mysql_fetch_row($result)) {

$previous = $row[0];
}
}else{
$previous = "No rows, bud";
}
print "Previous: $previous<br />\n";
print "Target: $aid<br />\n";
print "Next: $next<br />\n";
?>

As you can see, I'm making two selects, and checking them each with a mysql_num_rows to make sure there is actually data being returned. Is this the best way?

I thought about doing a select like:
$sql = "select article from articles where article = $aid-1 order by article asc LIMIT 3";

and then looping through them. The first is the PREVIOUS record, and the last is the NEXT record. But if the user is at the first record, the select will likely fail, since there won't be a record with a negative value.

My method posted above DOES work. I'm just curious as to if this is the best way.


jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 08-01-2002 20:30

Best I can figure, your other idea should work fine (although you put = rather than >=).

select article from articles where article >= $aid-1 order by article asc LIMIT 3

Granted, you still need to check to make sure that the first value is less than $aid to solve the beginning problem (and also check whether only 2 results were returned for the ending case), but that strikes me as more efficient than making more DB queries.

-jiblet

Bmud
Bipolar (III) Inmate

From: Raleigh, NC
Insane since: Mar 2001

posted posted 08-13-2002 00:29

Check the webmoney.com tutorial on mySQL and php. If I recall, queries are numbered much like arrays are, no? If not, you can always use mysql_fetch_array. Um.. the currect number can be held in the variable and then you simply link to x - 1 and x - 2.

Shine and shine.

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 08-13-2002 02:49

That's assuming that there are no "missing" numbers. In my case, there are. And, I need to check several things, such as
"select id from articles where status = 'online' and id > '$id' order by id limit 1";

« BackwardsOnwards »

Show Forum Drop Down Menu