Some help please

philosophology

I need some advice and ideas on how to go about this:
I created a blog and it is getting pretty big since all are posted on one page. I want to have a page to let me show them one by one in a sequence. For example I would have 2 buttons on the page, on to go forward and one to go backward (in time) to show the blog entries, how do I go about doing this? Any help would be appreciated.

dougal85

Assuming your table has something for the time/date it was posted. Change your SQL to add "ORDER BY datefield ASC" and "ORDER BY datefield DESC" to change the order.

philosophology

I'm sorry maybe I described it badly. When I meant forward and backward I didn't mean ascending and descending. I meant having one blog entry and then you click the forward button and the next page is the second blog entry and so on...

dougal85

Ah,

Well, do you have a page for viewing an individual post?

Just add links there to load up the post with the next and previous id...

philosophology

actually I just have one page that shows all the entries. how do i construct the links to show just one... how do I distinguish between the posts? by id? by index? could you give me an example of a link that does that? I cannot get the syntax right.

philosophology

here is my query right now:
SELECT entryTxt, DATE_FORMAT(entryDat, '%M %D, %Y, %T') AS date FROM tblBlg ORDER BY entryDat DESC

how do i make a query for taking the one before a date or however possible to get the last one...

philosophology

Wow! no body with query building skills better than me?!

leatherback

Hey Phylosophy,

It is quite easy, really.

You first need to know how to just retrieve one record from the database, and build a page that does just that. Ideally you retrieve the record based on the ID of the post. (e.g., select FIELD1, FIELD2 from TABLE where IDCOLUM = $IDVALUE)

Then you need to get the ID for the requested post. You can do this by passing the Id in the url, and using the $_GET syntax to get the var on the next page. If you do not know how to do this: http://bio-vision.nl/coding/GET_url.php

I think this should get you started.

You then of course need to create links to the next and previous page. I have a function to get previous & next values based on the current record. Have a look and play:


// Gets the next record in the database, and returns the record with the ID and the next up. If no next exists, it returns the current & the first

require_once(_GET_RECIPE_F_);
function get_next($StartRecord, $table, $identifier, $where='1=1')
  {
  $outdata = array();

  $q = "select ".$identifier." from ".$table." where (".$identifier." > ".$StartRecord.") and (".$where.") order by ".$identifier." asc limit 1";

  $grDBH = db_connect();  

  $res = mysql_query($q) or handle_errors('get.nxt',__LINE__, mysql_error(), $q);

  $returndata = array();

  if(mysql_num_rows($res) == 0)  // The last was reached
	{
	$q2 = "select ".$identifier." from ".$table." order by ".$identifier." asc limit 1";
      $res = mysql_query($q2) or handle_errors('get.nxt',__LINE__, mysql_error(), $q2);
	  $t = mysql_fetch_array($res);
	  $returndata = $t[$identifier];
	}
  else
    {
	$t = mysql_fetch_array($res);
	$returndata = $t[$identifier];
	}


return($returndata);
  }

philosophology

I do not have an ID column! All I have is the date and the entry columns. Can I add an ID column without destroying the contents of the table?

laserlight

Can I add an ID column without destroying the contents of the table?

Yes, by using an ALTER TABLE statement.

philosophology

Ok, I added the ID field but all of them are 0
How do I fix it? Also, auto increment doesn't work because my date field is already auto increment.

laserlight

Oh, I really should read the question before answering.

Firstly, why is your date field auto incremented? That does not make sense, since you would want to record the actual date, not some number one greater than the previous number.

Next, you do not need a separate id column since your date column serves as a suitable primary key (though more realistically you should store date/time, since you might have two blog entries on the same day). What you do need to do is select the date before and after the date of the current blog entry.

This can be done by sorting the rows according to date, and then selecting the first row whose date is less than the current entry's date, and the first row whose date is greater than the current entry's date.

philosophology

This can be done by sorting the rows according to date, and then selecting the first row whose date is less than the current entry's date, and the first row whose date is greater than the current entry's date.

How do I do that?

laserlight

Assuming $date is a PHP variable containing the current entry's date in a suitable MySQL recognised date format, you could execute the query:

SELECT `date` FROM Entries WHERE `date`<$date ORDER BY `date` DESC LIMIT 1
UNION
SELECT `date` FROM Entries WHERE `date`>$date ORDER BY `date` LIMIT 1

Then the first row of the result set would contain the date of the previous entry, and the second row would contain the date of the next entry, assuming the syntax of my example is correct. I am not sure what the result set will be when the current entry is the first or the last, so you may need to experiment. At worst you could execute two queries instead of using the UNION.