Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Programming/Scripts

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 13th January 2010, 12:50
Leszek Leszek is offline
Senior Member
 
Join Date: Nov 2006
Location: Poland,Włocławek
Posts: 369
Thanks: 22
Thanked 42 Times in 35 Posts
Send a message via Skype™ to Leszek
Default PHP/MySQL datetime before after question

Hi,

I've written a simple photo gallery and now am in need of making a simple "before" and "after" links to let people view previous and next photos sorted by date.
My MySQL gallery table contains: id, name, description, category_id, date, filename, views.

The date field is a datetime. It contains date and time in this format: 2010-01-09 18:11:46

I need to read one previous and one next id of the photo using a given date (correct me if I'm wrong):

Code:
$query = "SELECT * FROM `gallery` WHERE `date` < `".$actual_photo_date."` AND `category_id`=".$category." LIMIT 1;";
$prev = mysql_query(mysql_real_escape_string($query));
$prevrecord = mysql_fetch_assoc($prev);
$prev = $prevrecord['id'];
It returns an error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ....

What am I doing wrong ? What's wrong with the query ? Should I surround $actual_photo_date in ' or " or maybe ` signs ?

Thanks for the help.
__________________


Last edited by Leszek; 15th January 2010 at 00:40.
Reply With Quote
Sponsored Links
  #2  
Old 15th January 2010, 00:39
Leszek Leszek is offline
Senior Member
 
Join Date: Nov 2006
Location: Poland,Włocławek
Posts: 369
Thanks: 22
Thanked 42 Times in 35 Posts
Send a message via Skype™ to Leszek
 
Default

I've solved the problem.
I've converted the "date" field from "datetime" to "int" and also converted the dates to php Unix timestamp. The rest is:

Code:
$previous = mysql_query(mysql_real_escape_string("SELECT * FROM `gallery` WHERE `date` > ".$date." AND `category`=".$category." ORDER BY `date` ASC LIMIT 1;"));
$prevrecord = mysql_fetch_assoc($previous);
$previous = $prevrecord['id'];
$next = mysql_query(mysql_real_escape_string("SELECT * FROM `gallery` WHERE `date` < ".date." AND `category`=".$category." ORDER BY `date` DESC LIMIT 1;"));
$nextrecord = mysql_fetch_assoc($next);
$next = $nextrecord['id'];
Now everything is working as expected.
__________________

Reply With Quote
The Following User Says Thank You to Leszek For This Useful Post:
falko (15th January 2010)
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Networking: Routing / subnet question paulievox HOWTO-Related Questions 1 15th February 2010 23:45
Dumb Virtualhost Question DantePasquale Server Operation 1 16th December 2009 07:43
Hostname question, and DNS question andrewfashion General 1 20th April 2009 13:30
Traditional DNS easy question dayknight HOWTO-Related Questions 3 22nd May 2008 12:21
emails domains and co-domains question bug or a feature omadon Installation/Configuration 3 27th November 2006 10:17


All times are GMT +2. The time now is 07:31.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.