Member Avatar for Martin_42

Hi all

I'm trying to put together a room hire site, to be used by the lettings clerks, for our local Quaker meeting house and I've hit a couple of problems.

I have an HTML form that, using data from a MySQL database and some JSON and JavaScript, when a certain checkboxes are checked inserts the hourly rate and, if required, the deposit payable on the room chosen. This works fine.

However, the value that is displayed isn't being picked up and inserted into the database table.

The code for retrieving the data is:

$roomRateSQL = "SELECT * FROM tblRoomRates";
$result = $lettings->query($roomRateSQL);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
    // echo " RoomID: " . $row["roomID"] . " - Std Rate: " . $row["standardRate"] . " - Std Dep Rate: " . $row["standardDepositRate"] . " - Anon Rate: " . $row["reducedRate"] . " - Anon Dep Rate: " . $row["reducedDepositRate"] . "<br>";

    // data you want to send to json
    $data[] = array(
    'roomID' => $row['roomID'],
    'standardRate' => $row['standardRate'],
    'standardDepositRate' => $row['standardDepositRate'],
    'reducedRate' => $row['reducedRate'],
    'reducedDepositRate' => $row['reducedDepositRate']
    );

    //}

    }
} else {
    echo "0 results";
}

$json_data = json_encode($data);

Part of the JavaScript the uses the code to insert the values into the form is:

<script type="text/javascript">
var Rooms = JSON.parse('<?php print_r($json_data); ?>');
var frm = document.forms["bookingForm"];

function check() {

    // Meeting Room
    if (frm.meetingRoom.checked)
    {
    if (frm.anonGroup.checked)
        {
            frm.meetingRoomCost.value = Rooms[0].reducedRate;
            if (frm.depositRequired.checked)
        {
        frm.meetingRoomDeposit.value = Rooms[0].reducedDepositRate;
    } else {
        frm.meetingRoomDeposit.value = '';
        }
    } else {
            frm.meetingRoomCost.value = Rooms[0].standardRate;
            if (frm.depositRequired.checked)
            {
                frm.meetingRoomDeposit.value = Rooms[0].standardDepositRate;
            } else {
            frm.meetingRoomDeposit.value = '';
            }
        }
    } else {
    frm.meetingRoomCost.value = '';
    }
...
</script>

This code is replicated for another three rooms all pointing to relevant input boxes.

The code that is parsed to JSON is:

var Rooms = JSON.parse('[{"roomID":"1","standardRate":"15","standardDepositRate":"80","reducedRate":"8","reducedDepositRate":"40"},{"roomID":"2","standardRate":"8","standardDepositRate":"35","reducedRate":"4","reducedDepositRate":"20"},{"roomID":"3","standardRate":"8","standardDepositRate":"35","reducedRate":"4","reducedDepositRate":"20"},{"roomID":"4","standardRate":"12","standardDepositRate":"75","reducedRate":"6","reducedDepositRate":"30"}]');

The HTML inputs that collect the values are:

<input type="hidden" id="meetingRoomCost" name="roomRate">
<input type="hidden" id="meetingRoomDeposit" name="depositRate">

This, too, is replicated for another three rooms all with different and relevant IDs.

and the code to insert the values into the database are:

$bookingNumber = $_POST['bookingNumber'];
$roomID = $_POST['roomID'];
$roomRate = $_POST['roomRate'];
$depositRate = $_POST['depositRate'];

$RoomsBooked = "INSERT INTO tblRoomsBooked (bookingNumber, roomID, roomRate, depositRate) VALUES ('$bookingNumber', '$roomID', '$roomRate', '$depositRate')";

The bookingNumber and roomID are collected from other parts of the form.

The error that occurs when the form is submitted is:

Error: INSERT INTO tblRoomsBooked (bookingNumber, roomID, roomRate, depositRate) VALUES ('COT/0620/01', '1', '', ''):-Incorrect integer value: '' for column 'roomRate' at row 1

As you can see, the bookingNumber and roomID are fine but the roomRate and depositRate are missing.

Can you help resolve this please?

M

  • 1 Contributor
  • 1 Reply
  • 323 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Martin_42

Dani AI

Generated

Good find by — the visible bug (an input ending up empty) is a classic client-side/value-type mismatch when the server expects an integer. Empty strings are not valid integer literals under modern MySQL strict modes, so the INSERT failed. The short-term fix (use a numeric default from the client) works, but a couple of defensive measures make the app more robust and harder to break in future.

Server-side validation/coercion (always do this even if the client sets a value):

// treat an explicit empty string as "no value" and coerce safely
$roomRate    = isset($_POST['roomRate']) && $_POST['roomRate'] !== '' ? (int) $_POST['roomRate'] : 0;
$depositRate = isset($_POST['depositRate']) && $_POST['depositRate'] !== '' ? (int) $_POST['depositRate'] : null;

Note: use strict comparison to '' because empty('0') would treat the valid zero as empty.

Use parameterized queries rather than interpolating values. Example with PDO (handles NULL cleanly):

$stmt = $pdo->prepare(
  "INSERT INTO tblRoomsBooked (bookingNumber, roomID, roomRate, depositRate) VALUES (:bn, :rid, :rr, :dr)"
);
$stmt->execute([
  ':bn' => $bookingNumber,
  ':rid'=> (int)$roomID,
  ':rr' => $roomRate,
  ':dr' => $depositRate  // null will be inserted as NULL
]);

Extra hardening and debugging tips:

  • Consider a DB default (DEFAULT 0) or allow NULL for deposit columns so missing values won’t blow up an INSERT.
  • Confirm hidden inputs are inside the form and not disabled (disabled fields are not submitted).
  • Use browser devtools (Network tab or console.log) to inspect the exact POST payload before submit.
  • For multiple rooms, use array-style names (e.g. roomRate[1]) so each room’s values arrive distinctly.

This keeps the client UX simple for the clerks while making the server tolerant and secure against malformed submissions.

Member Avatar for Martin_42

I've found it and I feel such a fool.

The JavaScript needed to have 0 instead of an empty string so that, if a room was selected but no deposit was required it would automatically insert a zero and not a null.

For instance, the lines frm.meetingRoomDeposit.value = ''; would be frm.meetingRoomDeposit.value = '0';.

I had tried putting zeros in the script instead of '' but I put zeros in every ''. I only needed to zeros where a deposit was mentioned.

Hope that makes sense.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.