Input tag value being displayed but not being posted to MySQL database

[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

[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.