[php-sql] delete row with associated id

[mordimi]

hi there, im creating a page for adding ads "last minute" so i created a condition for the expired ad with this code $adesso= time();
if ($adesso>=$row[scadenza]){
echo "<br>annuncio scaduto";
} else echo "ok";


where $row[scadenza] is the value taken from the form with this value $o24= (time()+86400);
$o48= (time()+172800);
o24 is for 24hrs and o48 is for 48 hrs.

so, i want to delete the expired ads, in the condition if-else of the first code ive pasted. the problem is tht i dont know how to associate the relative id of the ad to be deleted :confused:

any suggestion?

ps-sorry for my english i hope you understand

[Vooler]

I think it is quite simple. You already have added the maximum timestamp an ad must remain, saved in 'scadenza', so simply compare with current timestamp.


Delete all expired rows
$adesso = time();
mysql_query("DELETE FROM `table_name` WHERE scadenza >= $adesso");

Or, I did not follow you ?

regards

[mordimi]

u saved my day!
btw i tried and didnt work (maybe i mispelled something),
doing this way if ($adesso>=$row[scadenza]){
mysql_query("DELETE FROM annunci");
it worked like a charm..!
thx a lot, really


I think it is quite simple. You already have added the maximum timestamp an ad must remain, saved in 'scadenza', so simply compare with current timestamp.


Delete all expired rows
$adesso = time();
mysql_query("DELETE FROM `table_name` WHERE scadenza >= $adesso");

Or, I did not follow you ?

regards

[Vooler]

Dont take it offensive please, I really have been laughing at the code, I repeat please do not mind, is just I put myself where you are rightnow in programming few years back I used to do same things.



# [X] THIS IS ABSOLUTELY ILLOGICAL - AND IS WRONG
if ($adesso>=$row[scadenza]){
mysql_query("DELETE FROM annunci");
}

Your code means, when condition is met delete all rows - means empty the table, leave no record either expired ads or unexpired.

Am glad I helped.

regards

[Danltn]

Remember unless you want the constant effect, put quotations around array keys!

$row['scadenza'] NOT $row[scadenza]

[Vooler]

Absolutely right Dan, on some platform (also as per warning level set) php will complain and issue warning, but in some case it wont, and that will result in unexpected results, if a constant already defined with same name as the key.

Perfect
$row['scadenza'] OR $row["scadenza"] NOT $row[scadenza]

In string (DO NOT ENQUOTE ASSOCIATIVE INDEX IF EVELEMENT ACCESSED WITHIN STRING)
echo "ad expiry timestamp is: $row[scadenza]";

Good dig Dan. I appreciate it.

regards

[Danltn]

Indeed PHP should send an E_WARNING Constant not set assumed to be... error.

I'll see if I can find another of my posts explaining the problem in a bit more detail, sorry if it sounds rude - but the guy I was replying to was annoying me.

Edit: Here we go... this was an example where the key was 'num' rather than 'scadenza'.

_________________________________________

"Notice: Use of undefined constant num - assumed 'num'"

This means he's used a string which is not encapsulated with either single or double quotation marks, this means PHP will automatically look for a constant (as it should do), if the user has not previously used a function like define('num', 'blah'); then it will default to use that unencapsulated string as it's base value. Hence 'num' will be returned.

It's roughly 4-7 times slower and returns a warning, before defaulting to $total_pages = $total_pages['num']; anyway. It's vital unless you want the definition feature that you put it in quotes, preferably single unless parsing is required - and even then single is preferable.

It also has the downside if someone had done a operation like define('num', 'blah'); then it would instead look for the index 'blah' (i.e. $total_pages['blah']) because the constant had been changed to 'blah' as it should have been. Therefore the result of this is unlikely to be what you want.

[mordimi]

lol your right, i was confused cause when i did the test, i tried with 2 rows in the db, one with id taken from he $_session (with the expired date) and one with NO id,and not expired,then when i tried my wrong code, only the record with the id was deleted, and i though it was right, but it was only a coincidence cause no other id were on the db..

regarding the quotations, i knwo they must be inserted, im usual to insert them, but now i understand WHY they must be included :)
thanks Danltn.

Dont take it offensive please, I really have been laughing at the code, I repeat please do not mind, is just I put myself where you are rightnow in programming few years back I used to do same things.



# [X] THIS IS ABSOLUTELY ILLOGICAL - AND IS WRONG
if ($adesso>=$row[scadenza]){
mysql_query("DELETE FROM annunci");
}

Your code means, when condition is met delete all rows - means empty the table, leave no record either expired ads or unexpired.

Am glad I helped.

regards

[mordimi]

i tried it now, and its not working, i tried with expired date (scadenza) of 1 minute ( time()+60 ) and the wont delete.


heres the last part of the code


while ($row = mysql_fetch_array($result))
{
print("<br> chi:<strong>".$row['username']."</strong>, annuncio:<strong>".$row['messaggio']."</strong>,da dove: <strong>".$row['luogo']."</strong>,alle: <strong>".$row['data']."</strong>, genere: <strong>".$row['genere']."</strong>, lui: <strong>".$row['lui']."</strong>, lei: <strong>".$row['lei']."</strong>");
$adesso= time()+21600;
mysql_query("DELETE FROM `annunci` WHERE scadenza >= $adesso");
}
mysql_close($con);
?>


(time is with +21600 cause the host is in US and im in italy so the time is different)


edit: okok its time to go to bed for me (its 2.14am) cause im making big mistakes, i have to put +21660 in the time() for 1 minute, and not +60.
good night :)


re-edit: nope its not working, when i adda record, then i add another record wth a different id, on reload the previous record disappears showing ONLY the last record inserted. its like it delete all the records.
ill dream about that for tonight :P

[Vooler]

#When you save record for example you save for next 10 minutes, do this way
$ctime = time() + 600; //10 minutes
mysql_query("INSERT INTO annunci (scadenza) VALUES($ctime)");


#Now when viewing, first run filtering
$adesso = time();
mysql_query("DELETE FROM `annunci` WHERE scadenza >= $adesso");

#show unexpired
$res = mysql_query("SELECT * FROM annunci ");

[mordimi]

i did this way, in the first file:

$minuto1= (time()+21660); //+6hrs and 1min

//conly the row of the form we are talkin about
echo "Scadenza: <select name=scadenza> <option value=".$minuto1.">1 minuto(per test scadenza)</option></select>";
echo "<input type=submit value=INVIA>";

then the insert

$sql="REPLACE INTO annunci (id, lui, lei, genere, data, username, luogo, messaggio, scadenza)
VALUES
('$_REQUEST[id]', '$_REQUEST[lui]', '$_REQUEST[lei]','$_REQUEST[genere]', '$data','$_REQUEST[username]','$_REQUEST[luogo]','$_REQUEST[messaggio]','$_REQUEST[scadenza]')";





then in the insert file

$query= "select * from annunci";
$result = mysql_query($query, $con);

//print all the fields of the form
while ($row = mysql_fetch_array($result))
{
print("<br> chi: <strong>".$row['username']."</strong>, annuncio:<strong>".$row['messaggio']."</strong>,da dove: <strong>".$row['luogo']."</strong>,alle: <strong>".$row['data']."</strong>, genere: <strong>".$row['genere']."</strong>, lui: <strong>".$row['lui']."</strong>, lei: <strong>".$row['lei']."</strong>");

$adesso= (time()+21600); //now is +6hrs ahead
mysql_query("DELETE FROM `annunci` WHERE scadenza >= $adesso");
}
mysql_close($con);
?>


the correct condition is WHERE $adesso >= scadenza

now it works :)