can you run a SQL dump of the table structures and data and I'll rebuild it on my dev server to see what is happening

either attach it to this thread or email it. my forum user name at the second site in my signature will get here

The entire file is here, and is called newcar.php:

PHP Code:

<?PHP
//Server connect -------------------------------------
include 'www/samband/kikarin/xxxxxxx.php';

$bilardays = mysql_db_query("$datab", "SELECT * FROM lysingar where lysingbolkid=1");
while ($row = mysql_fetch_array ($bilardays)) { 
$lysing_id = "".$row["id"];
$numberdays = "".$row["dagar"];
$yvirskrift = "".$row["yvirskrift"];
$bolkid = "".$row["lysingbolkid"];


$bilar = mysql_db_query("$datab", "SELECT * FROM lysingar WHERE DATE_SUB(CURDATE(),INTERVAL $numberdays DAY) >= date AND lysingbolkid=1  ORDER by date desc");
while ($row = mysql_fetch_array ($bilar)) { 
$lysing_id2 = "".$row["id"];
$yvirskrift2 = "".$row["yvirskrift"];
$bolkid2 = "".$row["lysingbolkid"];
print("$yvirskrift2<br>");
}
}
?>

This bit, was made by Mgraphic (you can see it in the other thread about time interval something something..) - I belive i may have used his bit of code wrong... misplaced it or forgot something - don't belive anything is wrong with his code:

DATE_SUB(CURDATE(),INTERVAL $numberdays DAY) >= date AND lysingbolkid=1

Planing to go public with my website september 1th.... hope to get this problem fixed by then

This is the table structure:
Text with bold is atribut-names - the other is types
id int(255)
navn text
budstad varchar(255)
bygd varchar(255)
tlf int(255)
mail varchar(255)
dagar date
pris int(255)
km int(255)
year int(255)
merki varchar(255)
motor varchar(255)
vekt int(255)
litur varchar(255)
brennievni text
yvirskrift varchar(255)
tekstur varchar(255)
mynd varchar(255)
date date
count int(255)
lysingbolkid int(10)
ip varchar(255)
active tinyint(10)

And let me remind the readers that the whole point of the script is to select records that has not passed the expiration date

not much help really, need some real data to go with it

A dump is much simpler as it can be imported into a DB in seconds rather me having to spend maybe an hour recreating what I think your data is.

the columns called date, year & count may be giving problems. They are not reserved words but are functions in SQL so could cause glitches. I have even had problems with a field called mail in Access.

Originally Posted by chrishirst

not much help really, need some real data to go with it

A dump is much simpler as it can be imported into a DB in seconds rather me having to spend maybe an hour recreating what I think your data is.

the columns called date, year & count may be giving problems. They are not reserved words but are functions in SQL so could cause glitches. I have even had problems with a field called mail in Access.

Was not sure what u ment by DUMP.... but guess this it it:

-- 
-- Struktur-dump for tabellen `lysingar`
-- 

CREATE TABLE `lysingar` (
  `id` int(255) NOT NULL auto_increment,
  `navn` text NOT NULL,
  `budstad` varchar(255) NOT NULL default '',
  `bygd` varchar(255) NOT NULL default '',
  `tlf` int(255) NOT NULL default '0',
  `mail` varchar(255) NOT NULL default '',
  `dagar` date NOT NULL default '0000-00-00',
  `pris` int(255) NOT NULL default '0',
  `km` int(255) NOT NULL default '0',
  `year` int(255) NOT NULL default '0',
  `merki` varchar(255) NOT NULL default '',
  `motor` varchar(255) NOT NULL default '',
  `vekt` int(255) NOT NULL default '0',
  `litur` varchar(255) NOT NULL default '',
  `brennievni` text NOT NULL,
  `yvirskrift` varchar(255) NOT NULL default '',
  `tekstur` varchar(255) NOT NULL default '',
  `mynd` varchar(255) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `count` int(255) NOT NULL default '0',
  `lysingbolkid` int(10) NOT NULL default '0',
  `ip` varchar(255) NOT NULL default '0',
  `active` tinyint(10) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='lysing info' AUTO_INCREMENT=122 ;

-- 
-- Data dump for tabellen `lysingar`
-- 

INSERT INTO `lysingar` VALUES (114, '', '', '', 0, '', '2006-08-28', 0, 0, 0, '', '', 0, '', '', '', '', '', '2006-08-28', 0, 1, '217.172.89.235', 0);
INSERT INTO `lysingar` VALUES (115, '', '', '', 0, '', '2006-08-28', 0, 0, 0, '', '', 0, '', '', '', '', '', '2006-08-28', 0, 1, '217.172.89.235', 0);
INSERT INTO `lysingar` VALUES (116, '', '', '', 0, '', '2006-09-02', 0, 0, 0, '', '', 0, '', 'Bensin', 'Max 43 tekn (millimrúm telja við)', 'Lýsingar-tekstur: Max 600 tekn. \r\nSvarar til uml. 10 reglur Ã* hesum kassanum. (millumrúm telja við)\r\nAllir upplýsingar tú vilt hava Ã* lýsingini skulu vera her.\r\n\r\n', '', '2006-08-28', 0, 1, '217.172.89.235', 0);

Ok with only 3 records makes testing a bit limited but;

PHP Code:

SELECT * FROM lysingar WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) >= date AND lysingbolkid=1 ORDER by date desc LIMIT 10 


does exactly what I would expect

changing the value of the date column returns records in the range of dates set by INTERVAL and changing LIMIT from 1 - 3 returns the appropriate record count.

I am working somewhat blind because I don't have a clue which is the expiration date. I will assume that it is column 'date'

so;

PHP Code:

SELECT dagar,date,lysingbolkid FROM lysingar WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) <= date AND lysingbolkid=1 ORDER by date desc LIMIT 10 


would probably do that
that equates to
if 'date' is more than today - 2 days don't show them (today being thursday records before tuesday would not be shown).
It would also only extract the dagar,date & lysingbolkid columns. (Whatever they do)


is that correct. If not please explain exactly what you are trying to achieve. Tell us what relevant fields (columns) are for what, we do not need translations of the column names, simply the function is enough.

Well actualy "dagar" is the exeration date, while "date" is current date...

So you can see that one record expires 2006-09-02, and therefor should be printed now, while the other two should not print.

What i need the script to do is to print records when they have not passed expiration date (im making an add thing where people can set how many days they want the post on the website and i'v set 30 days as a maximum, and Mgraphic code made it possible to calculate the number of days from current date into the future, so we get the expire-date.... wich is then but into the atribut "dagar")


I would say the relivant attributs therfor are "dagar" "date".

select id,dagar,'date' from lysingar where dagar > curdate() limit 10 (in natural order)

select id,dagar,'date' from lysingar where dagar > curdate() order by dagar asc limit 10 (in latest expiry first)

select id,dagar,'date' from lysingar where dagar > curdate() order by dagar desc limit 10 (oldest expiry first)

Add in your filters for the advert owner ID (lysingbolkid I'd guess) and extend the field list and that should be it.

NOTE: I've used "backticks" (single quotes) around the date field name to distinguish as a fieldname.

Well it seems to work now... thanks a lot.

The reason that this maybe got more complicated than nessacery is that i pasted the wrong bit of code into my script.... The following bit belongs in another script, where i have my top-links thing:
DATE_SUB(CURDATE(),INTERVAL 2 DAY) .... so this has never had anyting to do with the thing we are talking about in this thread.

Thanks a lot