MySQL Feed Reversed


#1

I have a blog styles website in which information from a MySQL database is put onto the homepage. I just added pagination, but now the feed is reversed so that the oldest news shows first and new posts show last. How can I switch it?


#2

Really need help please :expressionless:


#3

Well, without seeing any code, we can just guess… But, it sounds like you are pulling the info from your database. And, most likely you are using a query. So, it might be something like this:
$query=“SELECT * from CurrentNews WHERE blah=xyz”;

So, to order this by, let’s say date, you would add just that to the end of the query… Like:
$query=“SELECT * from CurrentNews WHERE blah=xyz order by DateField ASC”; (or DESC)

Here is a link about this option:
http://www.w3schools.com/sql/sql_orderby.asp

Hope that is what you need… Good luck…


#4

Sorry, forgot to paste some of the code. Here’s the bit about posting the info on the page. I hope that’s what you need.

[php] $data_p = mysql_query(“SELECT * FROM blogData $max”) or die(mysql_error());

//This is where you display your query results

while($info = mysql_fetch_array( $data_p ))

{
?>

<?php Print $info['title']; echo "
"; ?>
<?php echo "Post By:"; ?> <?php Print $info['category']; echo "
"; ?>
<?php Print $info['content']; echo "
"; echo "
"; ?>
<?php }

echo “

”;[/php]


#5

Well, I gave you the answer in the last post. You just need to ORDER your SELECT in the query.

Look at the database where you pull the info. There must be a date field when it was entered or posted.
Use that to reorder the info. You current query is:
“SELECT * FROM blogData $max” (Not sure what the $max is!)

Change that to add the date field or however else you want it sorted by. Something like this:
“SELECT * FROM blogData $max ORDER BY datefield” Sorted by a datefield, just guessing on name…

OR
“SELECT * FROM blogData $max ORDER BY category” Sorted by category field (can be anything!)

With the “ORDER BY” option you can sort it based on any field in your database…
Not sure what the $max was, the order by might have to be before the $max, depending on what that is…
Hope this helps…


#6

Okay, I get it now. However it raises a new question. I have a page where you type out your message and it shows up (Kind of like posting a blog), and it inserts everything from the text fields into the MySQL table blogData. How can I make it insert the info into the datefield?

I’m a complete idiot at MySQL, so really try to simplify it for me.


#7

Well, normally, when you create a blog or message site, you have several items that are stored into the database when someone posts to it.

You usually include some of these and others as needed:
Name of User or at least the UserID
Date of the post (Which is shown when displayed to others)
Actual post (Which is the blog entry like I am typing now or a message, etc)
Formatting info if any (Such as colors selected by the user, icons selected, importance level, etc)
Any other info that is important to the owner of the site such as IP address it was posted from

These are always placed into the database when the user pressed “POST” or “SEND”. The PHP code usually check it for possible errors or offending language, whatever the ower wants to remove, then it save all of the various data into the database. The date and time of day is helpful. I alway “timestamp” all of my database inserts or updates with a nice date and time. I can use this to review what has happened during the last week, month, year, etc… Just need to sort it by the timestamp. This is my timestamp code, maybe it might help: $TimeStamp=date(‘Y-m-d H:i:s’); It basically places year first so it never starts with a zero. It also includes the time down to the seconds so it usually is completely unique.

So, now that your posting or sending code has placed a data into the database, you can use this to sort your displays with. Wherever you load data from the database, you could order it by date. When reading a thread on this PHPhelp site, you read it from the beginning. You would sort your data oldest first. So, you must make sure if you want it ASC (ascending) or DESC (decending) . This depends on what you are reading. For messages, it may be newest first. For reading a thread as you are now, it may be from the beginning or oldest first. Your choice for your needs.

I hope this helps get you started out. Good luck…


#8

Sorry, one more question. What type should “datefield” be? I assumed int, but it only shows the 2012. Thanks again. Like I said, I’m new to MySQL


#9

Well, a date field like the one that I set up would be a string. This is because I format it into one result.
This result is stored in a way that makes it easy for me to access and sort by. It sorts based on the order it is created. In my sample’s case, it starts by year. For your use, you may want it some other way. For example, you may not need the year or the minutes. I always store them all just in case it is ever needed.

Some people create the timestamp in actual time format. This is a special type of format that is a very special number. I find this is hard to use. So, now that I have complicated it, you can use TIME or STRING. I use STRING formatting. In my example it is year-month-day-hour-minute-second, so it is almost always a unique value. Nobody will press the send button exactly at the same second and the server will delay one of the submit, so it is a good way to record the date. Hope that makes sense. I gave you more info than you need, but, I want you to understand it for future use… Good luck.


#10

Thank you SO MUCH! You have no idea how happy I am that I finally got this system to work! I run a blog(ish) website, and I didn’t want to do anything from 3rd party sites, so the fact that I got all of this without anything (except help from people) is awesome for me. It now have pagination and it goes it order! Once again, thank you!


#11

One minor problem, I’d like to change the date format, but when I try the database won’t accept it and it just sets the date as 0000-00-00…


#12

Well, you can use the “time” format in the database. But, then you have to use time functions to pull out the various parts. This will work just fine. Or, you can use “string” format in the database and store your date any way you want to format it.

In my example, I used the “string” format inside the database for the timestamp. I call the field either $Timestamp or $LastUpdated depending on it’s use. Anything will work for the name… Then, in the
PHP code I use, as in the example, $TimeStamp=date(‘Y-m-d H:i:s’); to create the data. Then, I write
this info into the field in the database whenever the user inserts data or when I update data. If the
data is updated in my code, I use the name $LastUpdated because it makes more sense to me.
To pull parts of the timestamp out, you have to use format functions or whatever, but, it is just a
string, so it is easy to manipulate. By the way, it sounds like you may have set up the database to
use the “time” format and may be attempting to write a string to it. That would always give you a
result of 0000-00-whatever… If you use the “time” format, date IS your timestamp, so the code
would be something like $TimeStamp=date; (No formatting!) Hope that helps…


#13

I was actually using the “DateTime” format for the datefield. For some reason there is no “string” option, at least I can’t find it… Sorry.


#14

Well, if you use the DateTime format, it is the actual clock time from the server.
So, this must be loaded use the “date” function in PHP. So, let’s say you call this field in the database,
let’s say “DatePosted”. Then, when you INSERT the record with your message, you would insert like
this:
$query="INSERT INTO MyTable message, userid, etc, DatePosted (“blah”, “999”, “etc”, date);

Note that in an INSERT SQL command, you list your fields and then the data. In this case the data for the DatePosted field would be the date which is pulled from the server, not your computer!

Then, when you pull this date out, you must use formatting functions to break it down to month-day-year. Like $date=month(date), etc… Hope that helps…

(I like using the other way that I use it as a string. Works much easier in my humble opinion!)

Good luck…