can an include do this?

[jlknauff]

Can I use an include to pull text from a SQL database - but have it pull a random field each time? What I want is to have a footer or header that will display something different each time the page loads. It all has to be server side so the spiders can see it.

[dilligaf]

Yup. Can't help you with the PHP but I use the same technique in ASP on a few pages so I know it's do-able.

[sarahk]

What you want to do is perfectly feasible.

In the query within the header and footer scripts you need to have something like this

$query = "select *
from mytable
order by rand(UNIX_TIMESTAMP())
limit 1";

This will return just one row which you can then output as required.

Sarah

[jlknauff]

I don't suppose you have a working example somewhere do you?

[sarahk]

Probably better that you give your stuff. If you take a look at my latest project http://www.raincheck.co.nz you'll see ad spots on the front page. Those ads are selected from the pool of ads using just that type of query. Bit more complicated in this instance but that's the concept.

Alternatively I use this type of code for selecting a winner from a prize draw. I get the list of entrants and then let the database pick one as the winner.

So, give us a bit more of what you need and I'm sure we can point you in the right direction.

Sarah

[l234244]

If it is php based I have used this little script before which uses a txt file instead of a databse:

Make a php file called "random_content.php" and add the following code:

<?
$random_content="random_content.txt";
$random_content=file("$random_content");
$display=rand(0, sizeof($random_content)-1);
echo $random_content[$display];
?>

Then, make a txt file called "random_content.txt" and add the phrases that you want to be randomly displayed on seperate lines.

Then in your header/footer file add:

<? include("random_content.php"); ?>

[Chemo]

[QUOTE=sarahk]What you want to do is perfectly feasible.

In the query within the header and footer scripts you need to have something like this

$query = "select *
from mytable
order by rand(UNIX_TIMESTAMP())
limit 1";

This will return just one row which you can then output as required.

Sarah[/QUOTE]
Sarah,

That code is not scalable. Let's say that table has 100 rows...well it'll scan each (consuming memory in the process) and will then choose one at random but ONLY after pulling the whole table into memory. Now, let's say that table has 10,000 rows...now the code will consume megabytes of memory and is very inefficient in general. You will have to use a decent where clause with proper indexes for it to even remotely be scalable.

As an example, let's say that you have 1 Gb of RAM on your box. On each page load it consumes 2.5 Mb of RAM to load that table in order to select 1 row at random. That site would be able to support about 250-300 concurrent users before it starts using swap files.

Bobby

[sarahk]

[QUOTE=Chemo]That code is not scalable.[/QUOTE]
Fair comment, and I guess you could generate the random number and put the limit in as "limit $x, 1" but that too might cause problems.

I guess it all comes down to the circumstances. I was just giving one option, not the only answer.

Sarah

[jlknauff]

These options look great guys (and gals ;)) I'll let you know the outcome-I'll be implimenting something soon.

[harley]

Unless you're doing something fancy you don't even need a text file, much less a database..



$rand[1] = "hi";
$rand[2] = "hello";
$rand[3] = "goodbye";
$rand[4] = "later";

srand((double)microtime()*1000000);
$winner = rand(1,4);

echo "The random winner is: " . $winner;



Easily adapted for a DB:



$results = mysql_query("SELECT field FROM table");
$x = 0;
while($result = mysql_fetch_array($results))
{
$rand[$x] = $result['field'];
$x++;
}

srand((double)microtime()*1000000);
$x--;
$winner = rand(0, $x);

echo "The random winner is: " . $winner;

[Atomical]

This topic has less to do with include() and more to do with sql. Yay I'm back.

[AdultSEO]

Hi jlknauff!

I was wondering if you are sure $query = "select * from mytable order by rand() limit 1"; will load the whole table into memory since the limit 1 should limit it to just one result.

Best Regards,
Adult SEO

[reZo]

The way which I would do it, is count the number of rows there are in the table. This is using MySQL as the database. Here is some code you might like to use, or look at. However, in this example, I am using the database "quotes", which you might like to change.



<?php

$rows_query = mysql_query("SELECT * FROM quotes");

$rows = mysql_num_rows($rows_query);

$random = rand(0, $rows - 1);

$mysql_query = mysql_query("SELECT * FROM quotes WHERE quotes_id = '$random'");

if( $rows = mysql_fetch_array($mysql_query) )
{
echo $rows['quote'];
}

?>



Hope this code helps, reZo.

[AdultSEO]

[QUOTE=reZo]The way which I would do it, is count the number of rows there are in the table. This is using MySQL as the database. Here is some code you might like to use, or look at. However, in this example, I am using the database "quotes", which you might like to change.



<?php

$rows_query = mysql_query("SELECT * FROM quotes");

$rows = mysql_num_rows($rows_query);

$random = rand(0, $rows - 1);

$mysql_query = mysql_query("SELECT * FROM quotes WHERE quotes_id = '$random'");

if( $rows = mysql_fetch_array($mysql_query) )
{
echo $rows['quote'];
}

?>



Hope this code helps, reZo.[/QUOTE]

This is slower then using a single random query.


$rows_query = mysql_query("SELECT * FROM quotes ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_array($rows_query);
print $row["quote"];