PHP: Stuck On Next / Previous Article Buttons

[ibeblunt]

It's not what you think.

Okay, heres the situation. I have about 1,000 rows of articles in a MySQL database. Bad part, they were not inserted in the order that they appear. So for example:

1 | How To Program PHP | 4/20/2000
2 | Discovering Java | 8/15/1998
3 | Take Up Yoga | 4/19/2000

The first column is the ID, the second the title of the article, the third the date. In Cold Fusion I managed to do a SQL statement that got the ID's of the articles and put them in chronlogical order for me. Making the articles list like so:

2 | Discovering Java | 8/15/1998
3 | Take Up Yoga | 4/19/2000
1 | How To Program PHP | 4/20/2000

I turned the results of the ID into a comma delimited list:

EDITORIAL_LIST = 2,3,1

Now here's the tricky part. If the user was on article ID 3, both NEXT and PREV buttons will appear. If the user is on article ID 2, only the NEXT button would appear. If the user is on article 1, on the PREV button would appear.

First I did the query which I named PAGING_DAILYHIPHOPNEWS. Then I used the VALUELIST function to get all of the EDITORIALID's in the result and put comma's inbetween it.

For this example let's say we're on a page that the EDITORIALID is set to 2. So basically the LISTFIND function, says FIND 2 in this list. Then it sets the numbers for NEXT and PREV by a negative or a postive - depending on the situation. Keep in mind, that it's not ADDING or SUBTRACTING this number. Its getting the next number in the comma-delimited row.

Here's the code in Cold Fusion:

<CFQUERY NAME="PAGING_DAILYHIPHOPNEWS" DATASOURCE="#DATASOURCE#"
SELECT TBL_EDITORIAL.EDITORIALID,
TBL_EDITORIAL.SUBSECTIONID,
TBL_EDITORIAL.LIVE,
TBL_EDITORIAL.GOLIVE,
TBL_EDITORIAL.GODOWN,
TBL_SUBSECTIONS.SUBSECTIONID,
TBL_SUBSECTIONS.SECTIONID,
TBL_SECTION.SECTIONID
FROM TBL_EDITORIAL,
TBL_SUBSECTIONS,
TBL_SECTION
WHERE TBL_EDITORIAL.SUBSECTIONID = TBL_SUBSECTIONS.SUBSECTIONID
AND TBL_SUBSECTIONS.SECTIONID = TBL_SECTION.SECTIONID
AND TBL_EDITORIAL.SUBSECTIONID = 1
AND TBL_EDITORIAL.LIVE = 1
AND TBL_EDITORIAL.GOLIVE < #TODAYIS#
AND TBL_EDITORIAL.GODOWN > #TODAYIS#
ORDER BY TBL_EDITORIAL.GOLIVE DESC,
TBL_EDITORIAL.EDITORIALID DESC
</CFQUERY>

I'd like to accomplish the same thing in PHP. I'm sure it can be done but I'm just not figuring out the right PHP functions.

This is what I have so far:

<?php

require("../application.php");

$db = "sohh";
include("../application_db_connect.php");

$query = "SELECT * FROM tbl_writers WHERE writerID = $writerID";

$result = mysql_query($query);

if (!$result) {
echo "<B>Error performing query/B>&nbsp;&nbsp;" . mysql_error();
exit;
}

$querylist = "SELECT writerID FROM tbl_writers";

$resultlist = mysql_query($querylist);

$recordcount = mysql_num_rows($resultlist);

$count = 0;

while($row = mysql_fetch_array($resultlist)) {

$writerID = $row["writerID"];

if ($count<$recordcount) {
$writerIDstr .= $writerID . ",";
} else {
$writerIDstr .= $writerID;
}

$count++;

}

$editoriallist = $writerIDstr;

?>

I'm not getting any errors with this but I'm not getting the results I want either.

Steven
<Edited by ibeblunt on 01-12-2001 at 09:35 AM>

[aspen]

I think some simple SQL will help you do what you want and make it easier for you in both the Cold Fusion and PHP applications.

First you should use the order clause

Select *
From Table
Where x = y
Order by Date.column

Should automatically order your rows chronologically. If by chance that puts the most recent article first, add DESC after Date.column. Honestly I forget how dates are ordered, but either like above, or with DESC after it, will do what you want.

Finally limits can be a wonderful thing.

In Cold Fusion you'd use the startrow and maxrow attributes on the CFOUPUT tag. For instance if you know you're on article 2 in the list you can pass that number as a variable and then do

<CFOUTPUT QUERY = "Myquery" MAXROWS = "1" STARTROW = "#VARIABLE#">

And get either the next or previos article id like that.

In php you'd do it at the SQL level which is outlined here:

http://www.webmasterbase.com/article...d=228&pid=1039

What'd I'd do in CF, and what I think would be easier than your approach and may help figure out how to do it in PHP.

Is just run a query like above to get an ordered list of articles by date.

Then run it through an output loop.

<CFoutput query = "querydate">
<CFIF QueryDate.ID = ArticleQuery(article I'm viewing)ID>
<CFSET prev = QueryDate.CurrentRow>
</CFIF>
</CFoutput>

That'd get me the row number of the current article, then its just adding 1 to it that'd give me the next one. Of course you can run a counter on that loop too and see how many results are returned then compare that to the number you got to see what buttons you need to use. Basically always show a prev button unless the number you got back was 1, and always show a next button unless the number you got back was equal to the counter number end result.

In PHP the coding will be different but you can use the same logic to do it. Just find out what the Current Row function is for the result loop and you're all set.


<Edited by aspen on 01-12-2001 at 07:01 AM>

[Vinay]

as far as the query was concerned, all you needed was an ORDER BY statement in your SQL query to get them ordered appropriately in a chronological form.. if you want them order by ID, you could always do ORDER BY ID ASC

so your query would be like:

SELECT * FROM tbl_writers WHERE writerID=$writerID ORDER BY date_column ASC

now, for the next/prev buttons you could use the data here:
http://www.geekarea.org/articles.php?action=show&id=12

regards,

[ibeblunt]

I'm going to check out those links. However, adding and subtracting 1 is not the solution in my case. My database holds different sections of information. Take this as an example:

ID|HEADLINE|DATE|SECTION

2 | Discovering Java | 8/15/1998 | Java Coding
3 | Take Up Yoga | 4/19/2000 | Exercising
1 | How To Program PHP | 4/20/2000 | PHP Coding

So going in the order of rows is not going to get me the desired results. I'd have to ORDER BY DATE ASC. Note: I pasted the entire CF code since the snippets may have thrown off some people.

You might want to look at my site the way it is now to see exactly what I'm talking about.
http://www.onlinehiphopawards.com/sohh/thewire/

Click on any news article and look at the EDITORIALID.

You'll notice gaps in the numbers because those articles are probably being used for another section.

The key here is the information is ordered by DATE and not by ID number. The dates are not in sequence order.


<Edited by ibeblunt on 01-12-2001 at 09:38 AM>

[aspen]

Yes.

You order it by date.

Then you check the order of that result set.

54
23
12
16

Lets say you got that order.

Right now you're viewing #23.


For the purpose of this segment I'll write variables with the value they'd be beside them.

//Query to get the article
<CFQUERY name = "ArticleQuery" datasource = "Whatever">
Select *
From Articles
Where ID = #ID(23)#
</CFQUERY>



//Query to get next/prev buttons

<CFQUERY name = "buttongetter" datasource = "Whatever">
Select *
From tbl_writers
Where writer_id = #writer_id#
</CFQUERY>


//output loop that establishes the position of your current 
//article in a date ordered query, and the total number of 
//articles returned in such a query
//counter = total number returned
//seed = your article's position

<CFOUPUT QUERY = "buttongetter">
<CFSET counter = ButtonGetter.CurrentRow>
<CFIF QueryDate.ID = ArticleQuery(article I'm viewing)ID> 
<CFSET seed = QueryDate.CurrentRow> 
</CFIF> 
</CFOUTPUT>

<CFIF seed = 1>
//Dont print out prev button since seed is 1
<CFELSE>
//Print out button using value seed-1
</CFIF>

<CFIF seed = counter>
//Dont print out next button since seed is last in list
<CFELSE>
//Print out button using value seed+1
</CFIF>

And thats all there is to it. Because the date query isn't going to change you can count on the order of the results as a constant. So you find out where your current article is in that order using the result loop and then you check to see if it requires next/prev buttons.






<Edited by aspen on 01-12-2001 at 10:24 AM>

[ibeblunt]

Chris, how would I do this in PHP? I'm not sure if I bugging or not but it seems like some of the fields in your example arren't re-used so it makes it kind of hard to follow.