Caching problem

Alles, was PHP betrifft, kann hier besprochen werden.

Caching problem

Postby grayFalcon » 12. May 2009 09:36

Hello every!

I have a problem probably caused by caching. I have a mysql table and two php pages:
1: List of rows in the table and a small form for submitting another row
2: Confirmation that the row has been submitted and link back to page 1

Now when I submit a new row (=> go to page 2) and click on the link back to page 1, the new row appears in the list UNLESS I do this more than twice in one minute. In this case the newly submitted rows don't appear in the list on page 1. In other words: The list is only updated once per minute.

I have pragma no-cache, expires="Sat, 1 Jan 2000 08:00:00 GMT" and cache-control="no-cache" in my headers, force-reloading doesn't help. In the console, I can run a select * from table and I see the new row is there, while I keep force-reloading in the firefox window and getting the list of rows without the new row.

A tail -f access.log tells me that the request is being sent to apache every time I force-reload, and I get a status code 200.

reset query cache; in the mysql console doesn't help either. All I can do is wait one minute, and then I get the updated list of rows.

By the way, I grepped for "cache" in all apache config files, not a single hit.

Any ideas what can be causing this weird behavior? I'm really at the end of my wits...

Thanks a lot in advance,
-grayFalcon
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08

Re: Caching problem

Postby Nobbie » 12. May 2009 10:43

Can you show us your PHP code? As well the "insert" of the new row as the browse of the table. And please REAL code (copy&paste), not "likely".
Nobbie
 
Posts: 8762
Joined: 09. March 2008 13:04

Re: Caching problem

Postby grayFalcon » 12. May 2009 11:03

Hello!

The insert code is:
Code: Select all
$db = mysql_connect('localhost', '***', '***');
mysql_select_db('eingang', $db);
$res = mysql_query("insert into meldung(time, sender, receipient, notes) values(".
    "STR_TO_DATE('".$_POST["date"]." ".$_POST["time"]."', '%d.%m.%Y %H:%i'), '".
    mysql_real_escape_string($_POST['from'], $db)."',".
    $receipient[2].",'".mysql_real_escape_string($_POST['comments'])."')", $db);
mysql_close($db);


The rows display is:
Code: Select all
$db = mysql_connect('localhost', '***', '***');
mysql_select_db('eingang', $db);
$ppl_q = mysql_query("select m.sender, r.name, r.mail, m.notes, DATE_FORMAT(m.time, '%d.%c.%Y, %H:%i') from meldung as m, receipient as r where time < NOW() and time > DATE_SUB(NOW(), INTERVAL 1 DAY) and r.id = m.receipient order by -m.time;", $db);
while($meldung = mysql_fetch_array($ppl_q)) {
    echo "<tr>";
    echo "<td class='mel'>";
    echo $meldung[4];
    echo "</td>";
// And so on... Yeah, I know, but it's supposed to be quick and dirty
    echo "</tr>";
}
mysql_close($db);


I don't know if this may be relevant, but on both pages I am opening and closing the same connection one more time at the very beginning of the page. As already mentioned above: I know I could do it in one connection, but you know, the quick and dirty thing...

Thanks a lot in advance,
-grayFalcon
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08

Re: Caching problem

Postby Nobbie » 12. May 2009 21:05

>I don't know if this may be relevant, but on both pages I am opening and closing the same connection one more time at the very beginning of the page.

??

Where can I find that in your (REAL!?) code?
Nobbie
 
Posts: 8762
Joined: 09. March 2008 13:04

Re: Caching problem

Postby grayFalcon » 13. May 2009 07:42

OK, I just copy-pasted the php code for inserting the row and browsing the table before. I've now uploaded the full code on pastebin:

  • File 1: http://pastebin.com/m4c96070f
    • Get list of people from the database & close the connection
    • Show fields for entering new row data (including a selection with the list of people fetched earlier from the database)
    • Get list of rows in database and display them & close the connection
  • File 2: http://pastebin.com/m49254825
    • Get list of people from database & close the connection
    • Send notification mail to the person selected on the previous page
    • Insert new row into database & close the connection

Thanks a lot again,
-grayFalcon
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08

Re: Caching problem

Postby Wiedmann » 13. May 2009 08:02

OK, I just copy-pasted the php code for inserting the row and browsing the table before.
...
I have pragma no-cache, expires="Sat, 1 Jan 2000 08:00:00 GMT" and cache-control="no-cache" in my headers,

In short: no. You don't have these headers in a valid and working way (it only seems so). BTW: If you already have a PHP script, you should do this with a real header (with the header() function) and not with a meta tag.

force-reloading doesn't help.

You mean shift-f5 (or ctrl-f5) with "force-reloading"? That should really work.

In the console, I can run a select * from table and I see the new row is there,

But you have not tried to use the same query you are using in the script? I guess this query is the problem.


BTW:
Ever verified the html output from these scripts with a html validator? (with a wrong html, the browser behavior is not foreseeable)
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Re: Caching problem

Postby grayFalcon » 13. May 2009 09:12

Argh. OK, I found the error, and it was really dumb. I was filtering in my query by
Code: Select all
time < NOW()
instead of
Code: Select all
time <= NOW
, and since the accuracy with which I was entering the time into the database was minutes, I always had to wait for the clock to advance to the next minute before the query returned the last row. I'm an idiot :(

Thanks for your great help!

Greets,
-grayFalcon
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08

Re: Caching problem

Postby Nobbie » 13. May 2009 10:30

What i dont understand:

why do query for "time <= NOW()", but do not fill the appropriate time-Field from NOW()-Function (instead by cryptical $_POST['date'] and $_POST['time'] and some MySQL Date-Functions). Or, more precisely: why dont you simply specify the time-field as TIMESTAMP in the Create Table statement,what would cause MySQL to fill in the current timestamp automatically on INSERT?
Nobbie
 
Posts: 8762
Joined: 09. March 2008 13:04

Re: Caching problem

Postby grayFalcon » 13. May 2009 14:31

The problem is that the user can theoretically change the time in the input. So I can't just insert NOW() as time into the table, I have to take the time submitted from the form.
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08

Re: Caching problem

Postby Nobbie » 13. May 2009 20:40

grayFalcon wrote:The problem is that the user can theoretically change the time in the input. So I can't just insert NOW() as time into the table, I have to take the time submitted from the form.


If so, i dont understand the idea of quering for "time <= NOW()", as it does not reflect the NEWEST entries (as you expected in your first posting) but the entries with the newest date/time input of the form (whenever they have been added).

Curious.
Nobbie
 
Posts: 8762
Joined: 09. March 2008 13:04

Re: Caching problem

Postby grayFalcon » 13. May 2009 21:40

Nobbie wrote:
grayFalcon wrote:The problem is that the user can theoretically change the time in the input. So I can't just insert NOW() as time into the table, I have to take the time submitted from the form.


If so, i dont understand the idea of quering for "time <= NOW()", as it does not reflect the NEWEST entries (as you expected in your first posting) but the entries with the newest date/time input of the form (whenever they have been added).

Curious.


Hey, that's actually quite a good point.

The time in the input box is automatically set to the current time, and there's no point in setting it to some time in the future (though it sometimes makes sense to enter a time in the past) in the context in which this app is used. But I don't restrict user input to disallow inputs that point to a time in the future.

So, yeah, a new entry with a time > NOW() should never happen, but making sure it doesn't is something I need to do. Thanks for pointing it out!

Oh, and by the way: I need the NOW() so I can conveniently query between NOW() and DATE_SUB(NOW(), INTERVAL 1 DAY), since I want to show all entries from the past 24 hours.
grayFalcon
 
Posts: 7
Joined: 12. May 2009 09:08


Return to PHP

Who is online

Users browsing this forum: No registered users and 2 guests