quick php help required

[roly]

i'm not a programmer so this might seem fairly basic stuff but here goes. i've got a dynamic page that displays random data from a mysql database. it currently displays new data with every page refresh. i'm sure google won't like this, so i want to generate the dynamic part of the page once per day (with cron?) and save it as either a txt file that i can include or use the whole original page as a template.

i know what i want to do i'm just not sure how, any suggestions of the best way of achieving this?

thanks in advance

[V_RocKs]

Best to do exactly what you already suggested.

[Babaganoosh]

Set up a cron job and have it output to a static file.

Or just let the script run once normally when someone visits the page but output the content to a static file and use filemtime() to find when the file was last modified. The first solution seems easier to me but it's your call.

[EddyTheDog]

You seem to have it sorted - The php just needs to get the timestamp from the txt file and if its over 24hrs old create a new one.

[asdasd]

Call Microsoft !!!

[SmokeyTheBear]

im not sure why you think this would help. Google does check your site more than once per day but i dont think google will see much of a difference in having new data every 6 hours or every 24. It will still appear much the same to google.

Might want to set the cron a bit longer like every week or month even

[potter]

Yeah, I don't google is going to care because it's probably not checking your site more than once every 24 hours anyway. So whether you reload dynamic content every page refresh or once every 24 hours it'll likely not make a difference.

But, if you do want to continue to do what you're asking. Add a table/column/something to the database and just make it a simple unix timestamp. Everytime your page is loaded - prior to running the function that replaces the content - check whether or not time() is greater than 24 hours of the timestamp in the DB. If it isn't, you don't do anything - if it is you update the timestamp to the current time and replace the content.

[Babaganoosh]

Yeah, I don't google is going to care because it's probably not checking your site more than once every 24 hours anyway. So whether you reload dynamic content every page refresh or once every 24 hours it'll likely not make a difference.

But, if you do want to continue to do what you're asking. Add a table/column/something to the database and just make it a simple unix timestamp. Everytime your page is loaded - prior to running the function that replaces the content - check whether or not time() is greater than 24 hours of the timestamp in the DB. If it isn't, you don't do anything - if it is you update the timestamp to the current time and replace the content.

Unnecessary db call is unnecessary.

[HomerSimpson]

http://www.awmzone.com/services

[potter]

Unnecessary db call is unnecessary.

Well, as stated already a cron job be the best way.

But please don't try to make it out like a single db query to get a single field value is bad.

[Babaganoosh]

Well, as stated already a cron job be the best way.

But please don't try to make it out like a single db query to get a single field value is bad.

Not trying to make a big deal out of it but if a file is written there's already a timestamp. There's no reason to make an entry in a db and query the db every time we want to see when the file was created. That step is completely redundant.

[fris]

i'm not a programmer so this might seem fairly basic stuff but here goes. i've got a dynamic page that displays random data from a mysql database. it currently displays new data with every page refresh. i'm sure google won't like this, so i want to generate the dynamic part of the page once per day (with cron?) and save it as either a txt file that i can include or use the whole original page as a template.

i know what i want to do i'm just not sure how, any suggestions of the best way of achieving this?

thanks in advance

i did something like this, i had 30 links, which would rotate one spot per day, so everyone got the same exposure.

not sure if that interests you.

[roly]

thanks for all the replys guys, this is roughly the php code i've got

$sql = "select foo from bar where catid = $cat limit 20";
      $result = mysql_query($sql ,$db);

 while ($myrow = mysql_fetch_array($result))
{
echo $myrow['something'];
}

i want to copy the output of that into a text file as discussed so that i can include it in a file, how do i do that? fopen? any idea how i would write it?

[Babaganoosh]

thanks for all the replys guys, this is roughly the php code i've got

$sql = "select foo from bar where catid = $cat limit 20";
      $result = mysql_query($sql ,$db);

 while ($myrow = mysql_fetch_array($result))
{
echo $myrow['something'];
}

i want to copy the output of that into a text file as discussed so that i can include it in a file, how do i do that? fopen? any idea how i would write it?

http://php.net/manual/en/function.fwrite.php

[roly]

im not sure why you think this would help. Google does check your site more than once per day but i dont think google will see much of a difference in having new data every 6 hours or every 24. It will still appear much the same to google.

Might want to set the cron a bit longer like every week or month even

well the bulk of the page is generated on the fly and with almost the whole page content changing with every refresh i think that's likely to look spammy with google. i would rather google think it was a static page that was updated daily.

[roly]

i did something like this, i had 30 links, which would rotate one spot per day, so everyone got the same exposure.

not sure if that interests you.

yes that's the sort of thing.

[roly]

http://php.net/manual/en/function.fwrite.php

that looks like what i'm looking for thanks

[fris]

yes that's the sort of thing.

links.txt

http://www.bing.com|very unpopular search engine
http://www.google.com|search engine
http://www.gfy.com|gfy forum

to show the links

<?

$show = 30;
$linkfile = dirname(__FILE__). "/links.txt";
$links = file($linkfile);
$result = array();
for ($i=0; (($i<$show) && ($i<count($links))); $i++) {
  $link = explode("|", $links[$i]);
  $result[] = '<a href="' . $link[0] . '">' . $link[1] . '</a><br/>';
  echo $result[$i];
}
	
?>

script to run in crontab once every 24 hours

<?

$linkfile = dirname(__FILE__). "/links.txt";
$tmp = file($linkfile);
$result = array();
$result[] = trim($tmp[(count($tmp)-1)]);
unset($tmp[(count($tmp)-1)]);

foreach ($tmp as $idx => $t) {
   $result[] = trim($t);
}

$s = implode("\n", $result);
$fp = fopen($linkfile, "w+");
fwrite($fp, $s);
fclose($fp);

?>

[roly]

thanks fris that's a big help

[roly]

hi

i've got the fwrite to work and my script works ok, so i've got both bits working seperately, but i'm not sure how i call all of this:

$sql = "select foo from bar where catid = $cat limit 20";
      $result = mysql_query($sql ,$db);

 while ($myrow = mysql_fetch_array($result))
{
echo $myrow['something'];
}

into this $scriptoutput:

$myfile= fopen ("/home/domains/domain.com/public_html/mytxt.txt", "w+");
fwrite($myfile, $scriptoutput);
fclose($myfile;

how do i get that first little script to output into $scriptoutput, that's what i'm stuck on, anyone got any advice?

thanks in advance

[Brujah]

$sql = "select foo from bar where catid = $cat limit 20";
$result = mysql_query($sql ,$db);

$scriptoutput = '';
while ($myrow = mysql_fetch_array($result))
{
    $scriptoutput .= '<li>' . $myrow['something'];
}

$myfile= fopen ("/home/domains/domain.com/public_html/mytxt.txt", "w+");
fwrite($myfile, $scriptoutput);
fclose($myfile;

If you're interested in using prepared statements, you can do this:

$mysqli = new mysql( 'localhost', 'username', 'password', 'db' );

$content = '';
if( $stmt = $mysqli->prepare("SELECT foo FROM bar WHERE catid = ? LIMIT 20") ) 
{
  $stmt->bind_param( 'i', $cat );
  $stmt->execute();
  $stmt->bind_result( $foo );
  while ( $stmt->fetch() )
  {
    $content .= '<li>' . $foo;
  }
  $stmt->close();
}

$mysqli->close();

file_put_contents( '/path/to/saved.txt', $content );

set up a cron to execute this every 24 hours
0 3 * * * /usr/bin/php /path/to/cron.php

[roly]

$sql = "select foo from bar where catid = $cat limit 20";
$result = mysql_query($sql ,$db);

$scriptoutput = '';
while ($myrow = mysql_fetch_array($result))
{
    $scriptoutput .= '<li>' . $myrow['something'];
}

$myfile= fopen ("/home/domains/domain.com/public_html/mytxt.txt", "w+");
fwrite($myfile, $scriptoutput);
fclose($myfile;

If you're interested in using prepared statements, you can do this:

$mysqli = new mysql( 'localhost', 'username', 'password', 'db' );

$content = '';
if( $stmt = $mysqli->prepare("SELECT foo FROM bar WHERE catid = ? LIMIT 20") ) 
{
  $stmt->bind_param( 'i', $cat );
  $stmt->execute();
  $stmt->bind_result( $foo );
  while ( $stmt->fetch() )
  {
    $content .= '<li>' . $foo;
  }
  $stmt->close();
}

$mysqli->close();

file_put_contents( '/path/to/saved.txt', $content );

set up a cron to execute this every 24 hours
0 3 * * * /usr/bin/php /path/to/cron.php

Thanks Brujah, that's a big help, very much appreciated.

[Brujah]

oops, that first line in the prepared statement should be:
$mysqli = new mysqli, forgot the i

if you cron using the php /path/to/script.php example, then wherever you want it in your files you can just

<?php echo file_get_contents( '/path/to/saved.txt' ); ?>

[xJerk]

You could do

ORDER BY RAND(CURDATE())

But there are some major performance implications with doing this with a large table (unless the query results are cached).

[roly]

oops, that first line in the prepared statement should be:
$mysqli = new mysqli, forgot the i

if you cron using the php /path/to/script.php example, then wherever you want it in your files you can just

<?php echo file_get_contents( '/path/to/saved.txt' ); ?>

i'm using

<?php include ("/home/domain.com/public_html/saved.txt"); ?>

is that just as efficient?

it's all working perfectly thanks

[roly]

You could do

ORDER BY RAND(CURDATE())

But there are some major performance implications with doing this with a large table (unless the query results are cached).

got it sorted thanks, but that's a new one i've never heard of which i'm sure will have its uses.

[Brujah]

i'm using


is that just as efficient?

it's all working perfectly thanks

I prefer file_get_contents() in this case, and it's probably safer just in case, since it won't attempt to execute code or anything.

[roly]

I prefer file_get_contents() in this case, and it's probably safer just in case, since it won't attempt to execute code or anything.

that's great, thanks again

[potter]

Not trying to make a big deal out of it but if a file is written there's already a timestamp. There's no reason to make an entry in a db and query the db every time we want to see when the file was created. That step is completely redundant.

Your idea breaks down if the file is ever manually updated, or if there isn't actually a static file to update and it's a dynamic page.

I would go as far to say a file's timestamp is akin to user input - not something I would base a script off of.