SQL generated table in PHP, insert row into SQL only if user inputs correct ID

Question

I have generated a table from a query and at the end of each row in the table there is a 'book' button. Once this button is selected, a modal asking the user to enter their ID is shown. Once the user enters their ID, a button is clicked and the ID is checked against the database.

What I would like to do is when the button is clicked within the modal have the selected row data and the members ID inserted into the database table. I'm getting confused on how I can get the selected row data after the modal is shown.

An image of php table:

Code for index.php

<table class="table table-bordered">
                    <tr>
                        <th width="10%">Class</th>
                        <th width="35%">Date</th>
                        <th width="40%">Time</th>
                        <th width="10%">Location</th>
                        <th width="5%">Book</th>
                    </tr>
                    <?php
                    while ($row = mysqli_fetch_array($sql)){
                        $classdescription = $row["class_description"];
                        $date = $row["date"];
                        $start = $row["startTime"];
                        $area = $row["area_description"];
                        ?>
                    <tr>
                        <td><?php echo $classdescription?></td>
                        <td><?php echo $date?></td>
                        <td><?php echo $start?></td>
                        <td><?php echo $area?></td>
                        <td><input type="button" data-toggle="modal" data-target="#myModal" value="Book" name="book"/>
                        </td>'

                    </tr>
                    <?php
                    }
                    ?>
                </table>
            </div>
        </div>

        <!-- Book Modal -->
        <div id="myModal" class="modal fade" role="dialog">
            <div class="modal-dialog">
        <!-- Book Modal content-->
            <form id = "bookingform" action="login.php" method="post">
                Username: <input type="username" id="username" placeholder="username"></br>

    <button id="book">Book</button>
    </form>

code for login.php

<?php

$user = $_POST['user'];

require "connectivity.php";

$date = date("Y-m-d");

$query = "SELECT member_forename FROM member WHERE name='$user'";
$result = mysqli_query($con, $query);

if(mysqli_num_rows($result) == 1) {
    $row = mysqli_fetch_assoc($result);
}
elseif (mysqli_num_rows($result) == 0) {
    echo "Username not recogised";
} 
    $sql = "INSERT INTO booking (booking_id, booking_date, customer_ID, bschedule_date, bschedule_class_id) VALUES ('15', '$date', '3', '2017-12-32')";
if(mysqli_query($con, $sql)) {
    echo "booking made";
}
?>

login.js

$(document).ready(function(){
$("#login_btn").click(function(){
    var user = $("#username").val();

    var data = "user=" + user;
    $.ajax({
        method: "post",
        url: "login.php?",
        data: data,
        success: function(data){
            $("#login_error").html(data);
        }
    });
});

Your code is vulnerable to SQL injection attacks. You should use [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) or [PDO](http://php.net/manual/en/pdo.prepared-statements.php) prepared statements as described in [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 13 '17 at 13:36
You can do this using Javascript/jQuery, while showing model/Book add "selected" class to selected row, then pick that row by "selected" class — Sanjay, Apr 13 '17 at 14:17

score 0 · Answer 1 · answered Apr 13 '17 at 14:14

This example is based on an example made on the site https://www.w3schools.com I use PG_QUERYS but you can switch to MYSQL. The rest is what you want. Any doubt is just ask. getuser.php

<!DOCTYPE html>
<html>
<head>
<style>
table {
    width: 100%;
    border-collapse: collapse;
}

table, td, th {
    border: 1px solid black;
    padding: 5px;
}

th {text-align: left;}
</style>
</head>
<body>

<?php
$q = intval($_GET['q']);

$dbconn = pg_connect('localhost','peter','abc123','my_db');
if (!$con) {
    die('Could not connect');
}



$query = "SELECT * FROM user WHERE id = $1";
$result = pg_prepare($dbconn, "my_query", $query);

$data = array($q);
$result = pg_execute($dbconn, "my_query", $data);


echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = pg_fetch_row($result)) {
    echo "<tr>";
    echo "<td>" . $row[0] . "</td>";
    echo "<td>" . $row[1] . "</td>";
    echo "<td>" . $row[2] . "</td>";
    echo "<td>" . $row[3] . "</td>";
    echo "<td>" . $row[4] . "</td>";
    echo "</tr>";
}
echo "</table>";
pg_close($dbconn);
?>
</body>
</html>

The Html

<html>
<head>
<script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else {
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("txtHint").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET","getuser.php?q="+str,true);
        xmlhttp.send();
    }
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
  <option value="">Select a person:</option>
  <option value="1">Peter Griffin</option>
  <option value="2">Lois Griffin</option>
  <option value="3">Joseph Swanson</option>
  <option value="4">Glenn Quagmire</option>
  </select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

</body>
</html>

This is a simple example, for you to understand the basics. If you want more help start programming, and if you have any difficulties ask the community.

In the example above, when a user selects a person in the dropdown list above, a function called "showUser()" is executed.

The function is triggered by the onchange event.

Code explanation:

First, check if person is selected. If no person is selected (str == ""), clear the content of txtHint and exit the function. If a person is selected, do the following:

1) Create an XMLHttpRequest object

2) Create the function to be executed when the server response is ready

3) Send the request off to a file on the server

Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The page on the server called by the JavaScript above is a PHP file called "getuser.php".

The source code in "getuser.php" runs a preaper execute on a PostgreSQL database, and returns the result in an HTML table.

Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:

1) PHP opens a connection to a PostgreSQL server

2) The correct person is found

3) An HTML table is created, filled with data, and sent back to the "txtHint" placeholder.

score 0 · Answer 2 · answered Apr 13 '17 at 14:35

What you want is to have the ID of the selected row in the modal, so that you can include it in the form and submit it to the login script. As I see it there are several approaches how you can achieve that.

One is to dynamically generate an individual modal for each row that includes its ID. Here you would move the modal code into the while loop and create a new modal for each row with an individual ID etc. The row ID should be included as hidden input in the form.

Another solution would be to use java script to keep track of which row has been selected. For that you need to a) call a js function for each "book" button in the table that sets the row ID on a global JS var. b) do the form submit with Java script and ensure the row ID is set first in the same script

SQL generated table in PHP, insert row into SQL only if user inputs correct ID

2 Answers2