[RESOLVED] NEXT and LAST filed??

mehrzadsoft

Hi dear friends

I have a confusing problem with my web site. I use one database table for different subdomains. like this:

MY DB TABLE:

Post_Table

id	title	  bodytext	year	month	 day	time	cat
---	-----	  ------------	-----	----	----	---
1	...	    ...		2005	05	06	12:20	blog
[B]2	...	    ...		2005	05	07	13:30	blog[/B]
3	...	    ...		2005	05	07	13:40	e-zine
4	...	    ...		2005	05	07	15:50	e-zine
[B]5	...	    ...		2005	05	08	12:01	blog[/B]
6	...	    ...		2005	05	09	02:09	e-zine
7	...	    ...		2005	05	12	15:19	e-zine
[B]8	...	    ...		2005	06	25	13:13	blog[/B]

and like this DB table i use one table for my e-zine and blog. with "cat" i will show just e-zine or blog content in my pages.

Now my question is here:

How can i show "NEXT" and "PREVIOUS" titles for id=5 ?
this "NEXT" or "PREVIOUS" titles must be in the same "cat".

the result must show "TITLE" of id 8 and 2.

please help me to write correct QUERY .
thanks
😕 😕

Lars_Berg

select max(id) 
from post_table
where cat = 'blog'
and id < <yourCurrentId>

select min(id) 
from post_table
where cat = 'blog'
and id > <yourCurrentId>

You can use a union if you want it as one query.

mehrzadsoft

Am sorry but i test your codes and they did not work.

what can i dooooooo????

leatherback

You can post the code you ar eusing so we can see.
And you can tell us whether there are error messages or just an empty output page

mehrzadsoft

My DB:

CREATE TABLE weblog_post (
  id int(10) unsigned NOT NULL auto_increment,
  title text NOT NULL,
  body text NOT NULL,
  author text NOT NULL,
  idcat text NOT NULL,
  time text NOT NULL,
  fayear int(4) NOT NULL default '0',
  famonth int(2) NOT NULL default '0',
  faday int(2) NOT NULL default '0',
  enyear int(4) NOT NULL default '0',
  enmonth int(2) NOT NULL default '0',
  enday int(2) NOT NULL default '0',
  wshow int(1) unsigned zerofill NOT NULL default '0',
  dshow decimal(1,0) unsigned zerofill NOT NULL default '0',
  des text NOT NULL,
  show decimal(10,0) NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

My codes:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body><?php
$idd=$id;
echo("<p dir=\"rtl\" align=\"right\" style=\"margin-bottom: -15\">");
require ("sys_db.php");
$tblname="post";
$link = mysql_connect($host, $user, $password);
$query="select max(id) FROM $tblname WHERE wshow=1 and id < $idd " or die(mysql_error());
$result=mysql_db_query($dbname, $query, $link);
while ( $row=mysql_fetch_array($result) ) { 
$id=$row["id"];
require ("catslist.php");
echo("<span style=\"font-size: 7pt\"><span lang=\"fa\"><font color=\"#6182AE\" size=\"1\"><b>NEXT</b></font></span><br>");
echo("<span style=\"font-size: 7pt\"><span lang=\"fa\"><font color=\"#6182AE\" size=\"1\"><b>» </b></font></span><font color=\"#C0C0C0\" size=\"1\"><a href"."=\"show.php?id=".$row["id"]."\">".$row["title"]."</a></font></span><br><br>");
}
mysql_close ($link);
$tblname="post";
$link = mysql_connect($host, $user, $password);
$query="select min(id) FROM $tblname WHERE wshow=1 and id > $idd " or die(mysql_error());
$result=mysql_db_query($dbname, $query, $link);
while ( $row=mysql_fetch_array($result) ) { 
$id=$row["id"];
echo("<span style=\"font-size: 7pt\"><span lang=\"fa\"><font color=\"#6182AE\" size=\"1\"><b>PREVIOUS</b></font></span><br>");
echo("<span style=\"font-size: 7pt\"><span lang=\"fa\"><font color=\"#6182AE\" size=\"1\"><b>» </b></font></span><font color=\"#C0C0C0\" size=\"1\"><a href"."=\"show.php?id=".$row["id"]."\">".$row["title"]."</a></font></span><br>");
}
mysql_close ($link);
echo ("<br><br>");
?></body></html>

mehrzadsoft

And i have just empty out put

leatherback

The query formulation is wrong:
use:
$query="select max(id) FROM $tblname WHERE wshow=1 and id < $idd ");
$result=mysql_db_query($dbname, $query, $link) or die(mysql_error());

mehrzadsoft

Dear Leatherback,
these codes did not work too!

I just have empty out put.

leatherback

Have you echoed out the actual query, so you can check what is passed to the database, and verified that this is what you expect?

echo $query;
after you have defined each query

and show us what that gives

mehrzadsoft

this id=72 (just test)

for NEXT:
select max(id) FROM weblog_post WHERE wshow=1 and id < 72

For PREVIOUS:
select min(id) FROM weblog_post WHERE wshow=1 and id > 72

leatherback

Hi,

This should work. So if there is no errors, then your database must be empty.

mehrzadsoft

O' thanks God.

I finally found the right way.

thanks to you, all my friends here because of your assistance. 😉

codes:

$query = "SELECT * FROM $tblname where wshow=1 and id > $idd order by id asc limit 1" or die(mysql_error());

$query = "SELECT * FROM $tblname where wshow=1 and id > $idd order by id asc limit 1" or die(mysql_error());

leatherback

Good for you. Enjoy coding

mehrzadsoft

Thanks for your comments and hints.