Is there a way to cycle through multiple records in a database using PHP forms?

Question

I'm trying to create a php page where I can cycle through a database table line-by-line. I have created a page that allows me to do this but I can't seem to be able to combine the forms so that clicking the button to go to the next/previous form also submits the data entry form.

I've made a very simplified version of the code that does not have a database attached to show you what I've been doing:

<?php
session_start();

// Create an array to cycle through
$a = array('1', '2', '3', '4');

if(isset($_POST['village']))
{
    $_SESSION['counter']++;
}
elseif(isset($_POST['village1']))
{
    $_SESSION['counter'] = $_SESSION['counter']-1;
}
elseif(isset($_POST['testVar']))
{
    $_SESSION['testVar'] = $_POST['testVar'];
}
else
{
    $_SESSION['counter'] = 0;
}
?>

<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1" http-equiv="Content Type" charset="utf-8"/>
    <title>TEST</title>
</head>
    <body>
        <!-- Previous value form -->
        <form name="prevValue_form" method="post" action="" enctype="multipart/form-data">
            <div style="float: left">
                <button class="btn btn-success" name="prev" id="prev" value="prev" style="font-size: 20px;"><</a>
            </div>
            <input type="text" name="village1" value="testing" />
        </form>

        <!-- Next value form -->
        <form name="nextValue_form" method="post" action="" enctype="multipart/form-data">          
            <div style="float: left">
                <button class="btn btn-primary" name="next" id="next" value="next" style="font-size: 20px;">></button>
            </div>
            <input type="text" name="village" value="testing" />
        </form>

        <!-- data input section -->
        <div id="main" accept-charset="UTF-8" class="form">
            <form name="dataEntry_form" method="post" action="" enctype="multipart/form-data">
                <!-- data input variable -->
                tester: <input name="testVar" value="" />

                <!-- Values to display if code is working -->
                <br clear="all" /><br />
                count: <?php echo $a[$_SESSION['counter']]; ?>
                <br clear="all" />
                test variable: <?php echo $_SESSION['testVar']; ?>

                <!-- submit the data entry form -->
                <p><input name="submit" type="submit" value="Submit" /></p>
            </form>
        </div>


    </body>

</html>

<script>

    $("#nextValue_form button").click(function (ev) {
        ev.preventDefault()
        if ($(this).attr("value") == "next") {
            $("#test_form").submit();
            $("#test_form2").submit();
        }
    });

    $("#prevValue_form button").click(function (ev) {
        ev.preventDefault()
        if ($(this).attr("value") == "prev") {
            $("#test_form").submit();
            $("#test_form1").submit();
        }
    });

</script>

Like I said above, the page works. However, I'm moving some people over from a bunch of Access databases to SQL Server and they don't like that the inputs don't automatically submit when they click the button to go to the next record. I'd imagine this is possible to accomplish, but I'm more of a database person than a PHP person and I've been having trouble with it.

Thanks for any help

score 1 · Accepted Answer · answered Mar 30 '19 at 06:15

Using this post as a guide, it might be worth it to just do one form with 2 buttons and fetch only the row you need instead of an array of rows and storing things into a session. I might only store the current id into session, but that is about it.

First I would create some basic functions to reduce duplication:

<?php
# General PDO query function. Needs to have the connection injected
function query($con, $sql, $bind = null)
{
    $query = $con->prepare($sql);
    $query->execute($bind);
    return $query->fetch(\PDO::FETCH_ASSOC);
}
# Create a next function that uses the current id to find the next record
function toNextRecord($id, $con)
{
    return query($con, 'select * from tablename where id = (select min(id) from components where id > ?)',[$id]);
}
# Use the current id to find the previous record
function toPrevRecord($id, $con)
{
    return query($con, 'select * from tablename where id = (select max(id) from components where id < ?)',[$id]);
}
# Check for a post
if(!empty($_POST['action']) && $_POST['action'] == 'walk_table') {
    # Fetch previous or next, depending on button press
    $row    =   (strtolower($_POST['button']) == 'next')?  toNextRecord($_POST['id'], $con) : toPrevRecord($_POST['id'], $con);
}
else
    # Set the default to whatever record id to start on
    $row    =   ['id' => 1];
?>

Make one form with an action (just makes it easier to differentiate actions) and the current id. Add the two buttons in. When clicked, only the one clicked will register in the post.

<form method="post" action="#">
    <input type="hidden" name="action" value="walk_table" />
    <input type="hidden" name="id" value="<?php echo $row['id'] ?>" />
    <input type="submit" name="button" value="Prev" />
    <input type="submit" name="button" value="Next" />
</form>

Thanks for this. Apologies that it took me a while to accept it - it took me a while to get it to work. I really need to find a local college or something with courses on PHP. It has somehow become a large portion of my job and I'm not very comfortable with it. The functions are really cool and I'll look into these more - I couldn't get them to work and just nested the button code in the if/else button click area which still worked but isn't as clean. Sadly, I don't have PDO on this server so I had to switch everything to plain PHP. — jdavid05, Apr 01 '19 at 12:23
Well PDO is class library for querying your database. What ever you use to pull from your database, you would just substitute. I use PDO to fetch from the database, you probably use MySQLi. Either way, substitute the query stuff for the interface you use and it will work. — Rasclatt, Apr 01 '19 at 15:52
That's what I ended up doing. Thanks for this. I use sqlsrv (SQL Server) but I just made some substitutes and everything worked great. — jdavid05, Apr 01 '19 at 18:50

Is there a way to cycle through multiple records in a database using PHP forms?

1 Answers1