We help IT Professionals succeed at work.

[PHP] Creating previous and next buttons that swap variable data

prileyosborne
prileyosborne used Ask the Experts™
on
I am working on a project that has a select menu that is populated dynamically from a MySQL database.  Currently when I select one of the items on the drop down list (they are a series of serial numbers) the content on the page changes to reflect that update.

This is being done by selecting the item and hitting submit.

I also wanted to include some next and previous buttons that would just choose the next serial number in the record and display it (or the previous one depending on whether they clicked previous or next).

Below is how I have the initial code set up (The names of the participants have been modified for their protection)

$query = 'SELECT * FROM dbtable WHERE column = "'.$_POST['kit_id'].'"';
$result = mysql_query($query) or die ('Database Access Failed: '.mysql_error());
		
//setting variables from database with $_ROW                
$row = mysql_fetch_row($result);
$router_sn 		= $row[0];
$router_mac 		= $row[1];
$ata_sn 			= $row[2];
$ata_mac 		= $row[3];

Open in new window


And then here is the code for the drop down:
<p><select name="kit_id" id="kit_id">
     <?php
	 $kit_id = "Select Kit ID";
	       if (isset($_POST['kit_id']))
		{
		       echo '<option value="'.$_POST['kit_id'].'"  selected="selected">'.$_POST['kit_id'].'</option>';
		} else {
			echo '<option value="Select Kit ID" selected="selected">Select Kit ID</option>';
	}
	 db_connect();
        //SERIAL NUMBER DROP DOWN GENERATOR
       //Gather form data for Serial Number drop down or show the error if you are unable to access the database
                $serial_query = 'SELECT column FROM dbtable ORDER BY column DESC';
                $serial_result = mysql_query($serial_query) or die ('Database Access Failed: '.mysql_error());
                
                //Gather the number of rows from the database.
                $serial_rows = mysql_num_rows($serial_result);
                
                //create serial number drop down with this While loop statement
                while ($serial_rows = mysql_fetch_array($serial_result)) {
                  $serial_number = $serial_rows['column'];
		echo '<option value="'.$serial_number.'">'.$serial_number.'</option>';
                }
              //close database
		mysql_close();
              ?>
  </select></p>

Open in new window


So now I want to either use a previous or next button or text link. Either is fine with me. I just need to basically be able to make the $kit_id variable one more or one less and then reload the page with that new variable value. Or at least I think that is what I need to do...

Any help would be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
Try this to see the design pattern that I've used for this sort of thing.
http://www.laprbass.com/RAY_mysql_example_14.php

The parts you want probably start around line 130.

HTH, ~Ray
<?php // RAY_mysql_example_14.php
error_reporting(E_ALL);
echo "<pre>\n"; // READABILITY FOR DUMPS

// DEMONSTRATE THE USE OF mysql_data_seek() TO GET
// PREVIOUS AND NEXT RECORDS FROM A RESULTS SET

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// LIVE DATABASE CREDENTIALS
require_once('RAY_credentials.php');


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// CREATING THE TEST DATA TABLE
$sql = "CREATE TEMPORARY TABLE myTable (
        nid   int           NOT NULL AUTO_INCREMENT,
        nom   varchar(4)    NOT NULL DEFAULT ''      COMMENT 'Just some test data strings',
        PRIMARY KEY(nid)  )";
$res = mysql_query($sql);

// LOADING SOME DATA INTO THE TEST DATA TABLE
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Joe' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Ray' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Tom' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Sue' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Pat' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Tib' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Kat' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Amy' )");
$res = mysql_query("INSERT INTO myTable ( nom ) VALUES ( 'Rob' )");




// CREATE THE SELECT QUERY TO GET AN APPROPRIATE RESULTS SET
$sql = "SELECT nid, nom FROM myTable";

// RUN AND TEST THE QUERY
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}

// DID WE GET ANYTHING IN THE RESULTS SET?
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num) die("QUERY $sql FOUND NOTHING");

// CONVERT NUMBER OF ROWS INTO AN OFFSET RELATIVE TO POSITION ZERO
$num--;




// IF THERE IS NOTHING IN THE QUERY STRING CREATE THE FORM
if (empty($_GET['n']))
{
    echo "<form>CHOOSE NAME";
    while ($row = mysql_fetch_assoc($res))
    {
        echo "\n<input type=\"radio\" name=\"n\" value=\"{$row['nom']}\" />{$row['nom']}";
    }
    echo "\n<input type=\"submit\" /></form>";
    die();
}




// ITERATE OVER THIS RESULTS SET USING A POINTER TO THE ROW NUMBERS
$ptr = 0;
$yes = FALSE;
while ($row = mysql_fetch_assoc($res))
{
	$nom = $row["nom"];
	if ($nom == $_GET['n'])
	{
	    echo "\n$nom FOUND IN THE RESULTS SET AT OFFSET $ptr";
	    $yes = TRUE;
	    break;
	}
	$ptr++;
}
if (!$yes)
{
    echo "\n{$_GET['n']} NOT FOUND";
    die("\n\n<a href=\"{$_SERVER['PHP_SELF']}\">TRY AGAIN</a>");
}




// SINCE WE FOUND THE ROW, SHOW PREVIOUS AND NEXT ROWS
$pre = $ptr - 1;
$nxt = $ptr + 1;

// PREVIOUS ROW - IF THERE IS ONE
if ($pre < 0)
{
    echo "\nTHERE IS NO PREVIOUS ROW";
}
else
{
    // MAN PAGE: http://us2.php.net/manual/en/function.mysql-data-seek.php
    if (!mysql_data_seek($res, $pre))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>DATA SEEK FAIL: ";
        die($errmsg);
    }

    // DATA SEEK WAS SUCCESFUL - RETRIEVE THE ROW
    $pre_row = mysql_fetch_assoc($res);
    $pre_nom = $pre_row["nom"];
    echo "\nPREVIOUS ROW CONTAINS $pre_nom";
}

// FOLLOWING ROW - IF THERE IS ONE
if ($nxt > $num)
{
    echo "\nTHERE IS NO FOLLOWING ROW";
}
else
{
    // MAN PAGE: http://us2.php.net/manual/en/function.mysql-data-seek.php
    if (!mysql_data_seek($res, $nxt))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>DATA SEEK FAIL: ";
        die($errmsg);
    }

    // DATA SEEK WAS SUCCESFUL - RETRIEVE THE ROW
    $nxt_row = mysql_fetch_assoc($res);
    $nxt_nom = $nxt_row["nom"];
    echo "\nFOLLOWING ROW CONTAINS $nxt_nom";
}




// TRY AGAIN?
die("\n\n<a href=\"{$_SERVER['PHP_SELF']}\">TRY AGAIN</a>");

Open in new window

Author

Commented:
Thanks for the quick feedback! I am working on implementing mysql-data-seek into my code.  I had one quick question before i get too far down, would you use submit buttons to trigger the change in the variable or would you use text links?  Thanks again, this is really helpful!
Most Valuable Expert 2011
Top Expert 2016

Commented:
I don't think it matters much which method you use.  Both can be styled with CSS.  I would probably use text links with the key as a URL argument just because it's a few lines easier to code.

Best regards, ~Ray

Author

Commented:
Ok, so the problem I am running into with this is what I want to do is take $kit_id and add 1 or take 1 away from it (since the kit number is just an auto incrementing number in the database) and then have the page refresh with all of the variables updated to that new kit id number.  If that makes sense.  I am afraid my knowledge of PHP is starting to reach its limit and i cannot seem to get what the change should be.

Anyway, thanks for all your help, but I am afraid I am still stuck.
Most Valuable Expert 2011
Top Expert 2016
Commented:
You may be at the threshold of a learning curve, so I will recommend a good learning resource.  
http://www.sitepoint.com/books/phpmysql4/

I want to do is take $kit_id and add 1 or take 1 away from it (since the kit number is just an auto incrementing number...
The problem with that strategy is that even though you have an auto_increment index, you do not know whether there are integer values that are $kit_id+1 or $kit_id-1.  It's quite possible for a row to be deleted.

The general design pattern I would follow for this would be to use the previous/next code as shown in the sample I gave you earlier.  You would get the the $kit_id values from the rows that MySQL selected, and when you put them into the link, the URL would look something like this:

$link = "<a href='show_a_kit.php?n=$kit_id'>SHOW $kit_id</a>";

When the client clicks that link, it will trigger an HTTP GET request to the script located at show_a_kit.php.  The script will find the $kit_id in $_GET["n"].

Does that make sense for your needs?

Author

Commented:
Totally!  Thanks so much Ray.  I am working on implementing it now. i will make sure to reply if I have any more questions. Thanks again!
Most Valuable Expert 2011
Top Expert 2016

Commented:
10-4.  We will be here!  Seriously, buy that book.  It has been a part of my professional library since edition One.  Great examples, very readable and a code library you can download and copy for your own use.

Author

Commented:
Ray did a fantastic job of not only teaching me about new SQL calls, but also giving me direction in how to learn it myself rather than just solving it for me and leaving me none the wiser.  Plus, whoever wrote the book he recommended should give him a cut since I will most surely buy it.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks for the points and for your kind words!  Best of luck with the project, ~Ray