too long

Question

I am very new to SQL and to say the least, I am struggling.

My table looks like this:

All I want to do is be able to increment any of these values by one upon the press of a button, like this:

This is how it looks on the website, but nothing is functional at all yet.

I have an understanding of HTML, CSS, and PHP, so if I were to know the correct way to do this with SQL, I should be able to implement it.

Thanks.

Answer 1

Ok, I see people suggesting AJAX ("elsewhere" as well as here), but you are unfamiliar with this. I'm going to suggest a completely non-Javascript solution, sticking with HTML, PHP, and MySQL, as you already know these. I would definitely recommend learning Javascript at some point though.

I've no idea of your level of understanding, so please let me know any bits of the following code you don't follow, and i'll explain in more detail.

<?php

    /* Initialise the database connection */
    $db = new mysqli("localhost", "username", "password", "database");
    if ($db->connect_errno) {
        exit("Failed to connect to the database");
    }

    /* First, check if a "name" field has been submitted via POST, and verify it 
     * matches one of your names.  This second part is important, this
     * will end up in an SQL query and you should NEVER allow any unchecked 
     * values to end up in an SQL query.
     */
    $names = array("Anawhata","Karekare","Muriwai","Piha","Whatipu");

    if(isset($_POST['name']) && in_array($_POST['name'], $names)) {

        $name = $_POST['name'];

        /* Add 1 to the counter of the person whose name was submitted via POST */
        $result = $db->query("UPDATE your_table SET counter = counter+1 WHERE name = $name");
        if(!$result) {
            exit("Failed to update counter for $name.");
        }

    }
?>

<table>

<?php
    /* Get the counters from the database and loop through them */
    $result = $db->query("SELECT name, counter FROM your_table");
    while($row = $result->fetch_assoc()) {
?>

    <tr>
        <td>
            <p><?php echo $row['name'];?></p>

            <form method="POST" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
                <input type="hidden" name="name" value="<?php echo $row['name']; ?>" />
                <input type="submit" name="submit" value="Add One" />
            </form>
        </td>

        <td><?php echo $row['counter']; ?></td>
    </tr>

<?php

    } // End of "while" each database record

?>

</table>