PHP display each column of a row of Mysql results in session variables - next row on refresh

Question

okay,

going crazy here - i wanted to display each row of a array (from results of Mysql query) one at a time. i had them in while loop and all rows showed at once. i was then advised to ditch the while loop and use sessions.

i have spent hours trying to get this to work - using comments on this site and PHP tutorials, but am very confused and need help.

scenario:

eg. Mysql query: select col1, col2, col3, col4 from MyTable. (MyTable has about 10 rows)

display

col1 (row1)

col2 (row1)

col3 (row1)

col4 (row1)

refresh browser (or pref - click Submit button)

display

col1 (row2)

col2 (row2)

col3 (row2)

col4 (row2)

refresh/submit .... etc etc until all rows have been displayed.

i believe i have to use $_SESSION variables - i tried - in each refresh - to set the variables like this

$_SESSION['column1']= $rownum;

$_SESSION['column2']= $rownum;

$_SESSION['column3']= $rownum;

$_SESSION['column4']= $rownum;

but i don't get the columns displaying correctly.

please help me resolve this - and if possible with code example - including the session start and refresh or whatever it may need.

Many Thanks in advance

Answer 1

There's couple ways how you can do this.

First is being mysql offset (not getting all rows but needed part (you might have 10000 rows there - all at once will be problem)).

First example can be used if you have quite some records or you want to prepare for future as it can handle large record groups.

Second example can be used if you have small group of records or you do sort of caching of database pre-script execution.

Example #1:

MySql -

SELECT col1, col2, col3, col4 FROM MyTable LIMIT 4 OFFSET 0 

php -

session_start();

$position = isset($_SESSION['display_position']) ? $_SESSION['display_position'] : 0;
$limit    = 4;

if(empty($position))
    $_SESSION['display_position'] = 0;

$sql    = 'SELECT col1, col2, col3, col4 FROM MyTable LIMIT '.$limit.' OFFSET '.$position;
$result = mysql_query($sql);

// We don't want to fetch non existen resource
if($result)
{
    while($rows = mysql_fetch_array($result))
    {
        echo $rows;
    }   
}
else
{
    echo 'No more records';
    exit;
}

// Update session for next run
$_SESSION['display_position'] = $position + $limit;

Other would be for example array looping and storing position in $_SESSION.

Example #2:

Mysql -

SELECT col1, col2, col3, col4 FROM MyTable

php -

session_start();

$records = array('row1', 'row2', 'row3', 'row4', 'row5', 'row6', 'row7', 'row8', 'row9', 'row10');

$position = isset($_SESSION['display_position']) ? $_SESSION['display_position'] : 0;
$limit    = 4;

if(empty($position))
    $_SESSION['display_position'] = 0;

if(count($records) < $position)
{
    echo 'No more records';
    exit;
}

for($i = $position; $i < $position + $limit; $i++)
{
    // Display rows
    if(isset($records[$i]))
        echo $records[$i];
}

// Update session for next run
$_SESSION['display_position'] = $position + $limit;

Note: Adjust $limit variable to suit your needs.

Your specific use case (updated):

session_start();

$records = array(
    array(
        'col1' => 'col1valuerow1',
        'col2' => 'col2valuerow1',
        'col3' => 'col3valuerow1',
        'col4' => 'col4valuerow1'
    ),
    array(
        'col1' => 'col1valuerow2',
        'col2' => 'col2valuerow2',
        'col3' => 'col3valuerow2',
        'col4' => 'col4valuerow2'
    ), 
    array(
        'col1' => 'col1valuerow3',
        'col2' => 'col2valuerow3',
        'col3' => 'col3valuerow3',
        'col4' => 'col4valuerow3'
    ), 
    array(
        'col1' => 'col1valuerow4',
        'col2' => 'col2valuerow4',
        'col3' => 'col3valuerow4',
        'col4' => 'col4valuerow4'
    ), 
    array(
        'col1' => 'col1valuerow5',
        'col2' => 'col2valuerow5',
        'col3' => 'col3valuerow5',
        'col4' => 'col4valuerow5'
    ), 
    array(
        'col1' => 'col1valuerow6',
        'col2' => 'col2valuerow6',
        'col3' => 'col3valuerow6',
        'col4' => 'col4valuerow6'
    ), 
    array(
        'col1' => 'col1valuerow7',
        'col2' => 'col2valuerow7',
        'col3' => 'col3valuerow7',
        'col4' => 'col4valuerow7'
    ), 
    array(
        'col1' => 'col1valuerow8',
        'col2' => 'col2valuerow8',
        'col3' => 'col3valuerow8',
        'col4' => 'col4valuerow8'
    ), 
    array(
        'col1' => 'col1valuerow9',
        'col2' => 'col2valuerow9',
        'col3' => 'col3valuerow9',
        'col4' => 'col4valuerow9'
    ),
    array(
        'col1' => 'col1valuerow10',
        'col2' => 'col2valuerow10',
        'col3' => 'col3valuerow10',
        'col4' => 'col4valuerow10'
    )
);

$position = isset($_SESSION['display_position']) ? $_SESSION['display_position'] : 0;
$limit    = 1;

if(empty($position))
    $_SESSION['display_position'] = 0;

if(count($records) < $position)
{
    echo 'No more records';
    exit;
}

for($i = $position; $i < $position + $limit; $i++)
{
    // Display rows
    if(isset($records[$i])){
        echo $records[$i]['col1'] . '<br/>';
        echo $records[$i]['col2'] . '<br/>';
        echo $records[$i]['col3'] . '<br/>';
        echo $records[$i]['col4'] . '<br/>';
    }
}

// Update session for next run
$_SESSION['display_position'] = $position + $limit;

Answer 2

Add a primary key column to your table if you don't already have one. You can then use the following:

<?php

session_start();

if(isset($_SESSION["id"])){
    $_SESSION["id"] = $_SESSION["id"] + 1;
}
else{
    $_SESSION["id"] = 1;
}

$id = $_SESSION["id"];

//Your mysql connection 

$results = mysql_query("SELECT * 
                        FROM urtable 
                        WHERE id = $id");

while($row = mysql_fetch_array($results)){
    echo $row["urcol1"];
    echo "<br>";
    echo $row["urcol2"];
    echo "<br><br>";
}

?>

Answer 3

add session_start(); before using sessions

<?php

session_start();

$_SESSION['pop'] = 12;
echo $_SESSION['pop'];

?>