Previous/Next MySQL Query

[suttercain]

Hi everyone,

 

I use this script:

 

<?php
$sql = mysql_query("SELECT * FROM comics WHERE comic_id ='".mysql_real_escape_string($_GET['id'])."'") or die(mysql_error());
	 $row = mysql_fetch_assoc($sql);

	 $sql1 = "SELECT comic_id FROM comics WHERE title='" . $row['title'] . "' AND issue_number > '" . $row['issue_number'] . "' ORDER BY issue_number + 0 LIMIT 1";
    if ($result1 = mysql_query($sql1)) {
      if (mysql_num_rows($result1)) {
        $row1 = mysql_fetch_assoc($result1);
      }
    }

$sql2 = "SELECT comic_id FROM comics WHERE title='" . $row['title'] . "' AND issue_number < '" . $row['issue_number'] . "' ORDER BY issue_number + 0 DESC LIMIT 1";
    if ($result2 = mysql_query($sql2)) {
      if (mysql_num_rows($result2)) {
        $row2 = mysql_fetch_assoc($result2);
      }
    }

if (isset($row2['comic_id']) || isset($row1['comic_id'])) {
		  echo "<div class='comicImgLeftPrevNext'>";
		  $echoDiv = "</div>";
		    }
if (isset($row2['comic_id'])) {
		  echo "</a><a href='http://www.supermandatabase.com/comics/" . $row2['comic_id'] . "/'/>PREV</a>";
		  	}
		  if (isset($row2['comic_id']) && isset($row1['comic_id'])) {
		  echo " | ";
		  	}
		  if (isset($row1['comic_id'])) {
		  echo "</a><a href='http://www.supermandatabase.com/comics/" . $row1['comic_id'] . "/'/>NEXT</a><br>";
		  	}
?>

 

This is used to go to the next comic book in a series... example Supergirl 21... hit next, goto Supergirl 22.

 

The Problem is there may be 3 supergirl 22 because there were different volumes. Is it possible to increment with the next based not only on the issue number but also the date? I have the dates in a 9/1/1938 format which may complicate things... but this would solve my issue since supergirl 21 in january 1995 will obviously be followed by supergirl 22 in February 1995 and not supergirl 22 in December 1978.

 

Thanks in advance for the help.

 

SC

 

[suttercain]

thought I had it... I tried

 

<?php
$sql = mysql_query("SELECT * FROM comics WHERE comic_id ='".mysql_real_escape_string($_GET['id'])."'") or die(mysql_error());
	 $row = mysql_fetch_assoc($sql);

	 $sql1 = "SELECT comic_id FROM comics WHERE title='" . $row['title'] . "' AND issue_number > '" . $row['issue_number'] . "' AND cover_date > '" . $row['cover_date'] . "'  ORDER BY issue_number + 0 LIMIT 1";
    if ($result1 = mysql_query($sql1)) {
      if (mysql_num_rows($result1)) {
        $row1 = mysql_fetch_assoc($result1);
      }
    }

$sql2 = "SELECT comic_id FROM comics WHERE title='" . $row['title'] . "' AND issue_number < '" . $row['issue_number'] . "'  AND cover_date < '" . $row['cover_date'] . "' ORDER BY issue_number + 0 DESC LIMIT 1";
    if ($result2 = mysql_query($sql2)) {
      if (mysql_num_rows($result2)) {
        $row2 = mysql_fetch_assoc($result2);
      }
    }
?>

 

but it didn't work...

[Ninjakreborn]

The way you are trying to do it is a little messy.

I advice getting a class for it, (you can google some good classes) and keep it cleaner.

This will allow you to modify it, and will probably fix the errors you are having naturally.

Pagination can be a pain without having controlled modules to help figure out what's causing the

problems and a class can help you do that.

[suttercain]

Okay, I did a google and yahoo search and could not find any classes that solved this problem. No way to edit my existing code?

[Ninjakreborn]

Pagination is not a simple task.  There are classes out there for it, pagination is one of my least favorite types of programming.  I have a script I carry around and re-use.  There are some good classes out ther eyou just have to have some programming knowledge to implement them properly.

[suttercain]

Hi businessman,

 

Thanks for responding. I have pagination on my pages and have used it numerous times. My problem is getting it to work using the date as opposed to results based on a title or integer. But I'll keep messing with it.

[Ninjakreborn]

If it's based on date try using it as a unix timestamp instead, maybe they are trying to use 2 different types, and something is off.  Make them timestamps so we can get an exact match.

[suttercain]

I think you're right my problem is the date format of 1/1/2000 which is a basic us calendar date as opposed to the mysql format of 2000-01-01. I don't even know how to convert all 5000 plus records to the mysql format. Do you know if there is a simple way of doing the conversion?

 

Thanks again.

[Ninjakreborn]

Check up for mysql functions it has a date function you can put right intothe query to change them all as they are pulled from the database.  Like an automatic way to do it, or form a query to go through and update them all in the db.

[suttercain]

I am dying here...

 

I am trying to manipulate the date per the STR_TODATE mysql function as such:

 

<?php
$sql1 = "SELECT comic_id, STR_TO_DATE(cover_date, '%m/%d/%Y') AS cover_date FROM comics WHERE title='" . $row['title'] . "' AND issue_number > '" . $row['issue_number'] . "' ORDER BY cover_date, issue_number + 0 LIMIT 1";
?>

 

I was able to fix the date order using the function in the following statement:

 

$sql = mysql_query("SELECT STR_TO_DATE(cover_date, '%m/%d/%Y') AS cover_date FROM comics ORDER BY cover_date LIMIT 0, 245") or die(mysql_error());
while ($row = mysql_fetch_array($sql)) {
echo "1. ".$row['cover_date'];

 

But when I tried to apply that function to the bigger query (the first code box above) it won't increment the issue number by one and then the next date... I am going bonkers.

[suttercain]

K, let me ask this... does anyone know how to do a mysql query to check DATE greater than DATE?

 

I ask because DATE > DATE doesn't seem to work.

 

Thanks.

[Ninjakreborn]

http://www.google.com/search?hl=en&q=mysql%2C+comparing+dates

[Ninjakreborn]

Make sure they are in unix timestamp format or the same format nonetheless.  Then you can say

date > date or whatever just using mysql's built in comparison operators.