View Full Version : Rotating data with PHP

03-01-2007, 05:20 AM
I need to adjust the script below to limit it to 10 rows of output and then make those rows rotate on the page every page view. I'm not sure how to do this. if someone could point me to a good tutorial or tell me the command that would do this I could then figure it out.

// the script starts here
$i = 0;
$db = mysql_connect("localhost", "*******", "*********");
mysql_select_db("*********", $db);
$result2 = mysql_query("select name, link,photo from featured_djs order_appear");
while($data1 = mysql_fetch_array($result2)) {
$dj_name[i] = $data1["name"];
$dj_link[i] = $data1["link"];
$dj_photo[i] = $data1["photo"];

<div><span class=\"leftfloat\"><img src='$dj_photo[i]'width=40 height=40></span><br /><br />&nbsp; <span class=\"leftfloat\">$dj_name[i] </span> <span class=\"rightfloat\"><a href=\"$dj_link[i]\" class=\"\">Listen</a></span>
<br />
<br />";

if($i > 3) {

03-01-2007, 12:34 PM
Add "LIMIT 10" to query to restrict to 10 records.

"..make those rows rotate on the page every page view" This is ambiguous. Can you explain what you want the records to do each page load a little more clearly?

03-01-2007, 12:56 PM
ok.....lets say I have 50 rows in the database. If I am only showing ten at a time then what I would want is that every time that a person refreshes the page that new data is shown but in a rotating fashion so that the previouse data was not show until all the 50 rows had been shown

03-01-2007, 01:37 PM
Basically this is the same gig as pagination except you're not outputting the page links, just the records.

Here's an adapted pagination script which should work. Something to get you started.

if (!isset($_SESSION)) session_start();


//set page to 1 if !isset, else post increment it
if ( !isset($_SESSION['page']) ) $_SESSION['page'] = 1;
else $_SESSION['page']++;

//grab a copy of page session and use that value from this point
$page = $_SESSION['page'];

//applicable db table for qry
$db_tbl = 'tableName';

//$db_table primary key column name
$id = 'columnName';

//column to order results by
$col_order = 'columnName';

//connect to svr and select db
$db_conn = mysql_connect("localhost", "*******", "*********");
$db = mysql_select_db("*********", $db_conn);

//query total rows
$sql_t_rows = "SELECT $id FROM $db_tbl";
$qry_t_rows = mysql_query($sql_t_rows);
$resultset_total = mysql_num_rows($qry_t_rows);

//max records per page
$p_max = 10;

//total pages
$p_total = ceil ( $resultset_total / $p_max );

//reset $_SESSION['page'] to 0 if this is the last page
if ($page == $p_total) $_SESSION['page'] = 0;

//page offset
$offset = ( $p_max * $page ) - $p_max;

// handle additional where clause items here
$where = '';

// page limited result set
$page_sql = "SELECT * FROM $db_tbl $where ORDER BY $col_order LIMIT $offset, $p_max";
$page_qry = mysql_query( $page_sql );
$page_row = mysql_fetch_assoc( $page_qry );

Only trouble with this logic is, on the last iteration through the 10 record resultsets, you'll get the remainder after dividing by 10. To avoid the last page load displaying less than 10 rows, make sure you have a record total that can be divided by 10.

03-01-2007, 03:24 PM
In the user's session, store the current offset (default to 0).

Then use that to query the database.

Something like this.

if (!isset($_SESSION['offset'])) {
$_SESSION['offset'] = 0;
} else {
$_SESSION['offset'] += 10;

$result = query("SELECT * FROM table LIMIT {$_SESSION['offset']}, 10");

/* display rows */