I have this project that works semi-fine in my laptop, it sends 3 parameteres to the DB, UserName , Expense , UserID , using PDO. it displays and inputs both UserID and Expense, but not UserName, it isn't being sent to the DB and shows NULL.
This is the code of each file: Html:
<form action="insertExpenses.php" method="POST">
<div class="row mb-3">
<label for="inputName" class="col-sm-2 col-form-label">إسمك</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="UserName" name="UserName">
</div>
</div>
<div class="row mb-3">
<label for="inputEmail3" class="col-sm-2 col-form-label">رقم الهوية</label>
<div class="col-sm-10">
<input type="number" class="form-control" id="UserID" name="UserID">
</div>
</div>
<div class="row mb-3">
<label for="ExpenseInput" class="col-sm-2 col-form-label">قيمة النفقة</label>
<div class="col-sm-10">
<input type="number" class="form-control" id="Expense" name="Expense">
</div>
</div>
<fieldset class="row mb-3">
<legend class="col-form-label col-sm-2 pt-0">الخيارات</legend>
<div class="col-sm-10">
<div class="form-check">
<input class="form-check-input" type="radio" name="gridRadio" id="gridRadio" value="option1" checked>
<label class="form-check-label" for="gridRadio">
إضافة نفقة
</label>
</div>
<div class="form-check">
<input class="form-check-input" type="radio" name="gridRadio" id="gridRadio" value="option2">
<label class="form-check-label" for="gridRadio">
تعديل نفقة
</label>
</div class="form-check">
<input class="form-check-input" type="radio" name="gridRadio" id="gridRadio" value="option3" >
<label class="form-check-label" for="gridRadio">
حذف نفقة
</label>
</div>
</fieldset>
<div class="text-center">
<button type="submit" class="btn btn-primary">Submit</button>
<button type="reset" class="btn btn-secondary">Reset</button>
</div>
</form><!-- End Horizontal Form -->
and this is the PHP files:
Connection.php:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "T14DB"; /*important to have all param the same*/
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully<br>";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
insertExpense.php:
<?php
include "connection.php"; //connect to the database and return $conn
try {
$temp = $_POST["gridRadio"];
$userID = $_POST['UserID'];
if ($temp == "option1") { // insert or add expense
echo "<br>Entered insert block<br>";
$query = "INSERT INTO t14db.userfinances (`UserName`, `Expense`, `UserID`)
VALUES (:UserName, :Expense, :UserID)";
$stmt = $conn->prepare($query);
$stmt->bindParam(':UserName', $_POST["UserName"]);
$stmt->bindParam(':Expense', $_POST["Expense"]);
$stmt->bindParam(':UserID', $_POST["UserID"]);
if ($stmt->execute()) {
displayUserData($conn, $userID);
} else {
echo "Error executing the query.";
}
} elseif ($temp == "option2") {//edit or update expense
displayUserData($conn, $userID);
$query = "update t14db.userfinances set `UserName`=:Username, Expense=:Expense where UserID=:UserID";
$stmt = $conn->Prepare($query);
$stmt->bindParam(':`UserID`', $_POST["UserID"]);
$stmt->bindParam(':`UserName`', $_POST["UserName"]);
$stmt->bindParam(':`Expense`', $_POST["Expense"]);
if ($stmt->execute()) {
echo "Data Changed successfully, here it is: <br>";
$userID = $_POST['UserID'];
$query2 = "SELECT `ID`, `UserName`, `Expense` from userfincaces where `UserID` = 'UserID'";
$stmt = $conn->prepare($query2);
$stmt->execute();
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "ID: " . $row['UserID'] . "<br>";
echo "NAME: " . $row['`UserName`'] . "<br>";
echo "Expense: " . $row['Expense'] . "<br>";
} else
echo "The ID is not found in the table";
}
} elseif ($temp == "option3") {//delete expense or record
displayUserData($conn, $userID);
$query = "DELETE FROM t14db.userfinances WHERE UserID=:UserID";//Now Delete
$stmt = $conn->prepare($query);
$stmt->bindParam(':UserID', $userID);
if ($stmt->execute()) {
echo "record Deleted Successfully";
}
}
} catch (Exception $ex) {
echo $ex->getMessage();
}
function displayUserData($conn, $userID)
{
echo "<br>Entered DisplayUserData<br>";
$query = "SELECT `UserID`, `UserName`, `Expense` FROM userfinances WHERE `UserID` = :UserID";
$stmt = $conn->prepare($query);
$stmt->bindParam(':UserID', $userID, PDO::PARAM_STR);
$stmt->execute();
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "--------------------------------------------<br>";
echo "ID: " . $row['UserID'] . "<br>";
echo "NAME: " . $row['UserName'] . "<br>";
echo "Expense: " . $row['Expense'] . "<br>";
echo "--------------------------------------------";
} else {
echo "--------------------------------------------";
echo "The ID is not found in the table";
echo "--------------------------------------------";
}
}
$conn = null;
The DB export file is:
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 11, 2024 at 12:00 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `t14db`
--
-- --------------------------------------------------------
--
-- Table structure for table `userfinances`
--
CREATE TABLE `userfinances` (
`UserName` varchar(50) DEFAULT NULL,
`Expense` int(11) NOT NULL,
`UserID` int(11) NOT NULL,
`Income` int(11) NOT NULL,
`Debt` int(11) NOT NULL,
`Qs` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
--
-- Indexes for table `userfinances`
--
ALTER TABLE `userfinances`
ADD PRIMARY KEY (`UserID`);
COMMIT;
The output in my laptop is as follows:
Connected successfully
Entered insert block
Entered DisplayUserData
--------------------------------------------
ID: 3
NAME:
Expense: 950
--------------------------------------------
The issues are:
NAME not reaching DB so it isn't being displayed by display functionChecked the parameters names, made sure the column names are the same in the php file and DB, tested almost every possible issue copilot has suggested and it made no progress.
How to debug it?
The issue was a typoo, a new lesson to publish, never push 20 commits at once without testing before each commit. most of the work i have made on the project was at that branch, and didn't test most of the work. The result is 13 commits fixing the typoos, and few more hours to finish the rest of the 2 typoos i found.
typoo 1):
if ($stmt->execute()) {
displayUserData($conn, $userID);
} else {
echo "Error executing the query.";
}
it is UserID in the HTML file not userID:
<input type="number" class="form-control" id="UserID" name="UserID">
Correct php code:
if ($stmt->execute()) {
displayUserData($conn, $UserID);
} else {
echo "Error executing the query.";
}
typoo 2):
$userID = $_POST['UserID'];
$query2 = "SELECT `ID`, `UserName`, `Expense` from userfincaces where `UserID` = 'UserID'";
$stmt = $conn->prepare($query2);
$stmt->execute();
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "ID: " . $row['UserID'] . "<br>";
echo "NAME: " . $row['`UserName`'] . "<br>";
echo "Expense: " . $row['Expense'] . "<br>";
} else
echo "The ID is not found in the table";
}
The query had the column name incorrect, ID->UserID.
typoo 3:
$stmt->bindParam(':`UserID`', $_POST["UserID"]);
$stmt->bindParam(':`UserName`', $_POST["UserName"]);
$stmt->bindParam(':`Expense`', $_POST["Expense"]);
whenever the is a : it is an HTML placeholder, so the correct code is:
$stmt = $conn->Prepare($query);
$stmt->bindParam(':UserID', $_POST["UserID"]);
$stmt->bindParam(':UserName', $_POST["UserName"]);
$stmt->bindParam(':Expense', $_POST["Expense"]);
A total of 1.2 hours+13 commits to fix the typoos of 20 commits that wasn't tested before deployment to the repo. great lesson of patience and accuracy.
Replying to the comments:
from @nbk that i should get an error from my query bc of not supplying a value to the rest of clumns is incorrect, it will be 0 or left NULL if NULL is ok.
@droopsnot is focusing more on DB design concepts rather than the issues with the code. As per the professor request, the form should have 3 inputs and does 3 Functional-Requirments. Hence seeking the 25points of the project rather than arguing about the DB or Table design concepts with him.
@droopsnot the username never came from the HTML form cuase it didn't have the proper placeholder spelling as mentioned earlier.
@aynber correct, the backtics are for the DB columns not HTML ids or names
@aynber again, correct there was a typoo there and fixing it contributed to the solution
@aynber, the issue was in the query and placeholder spelling not the ` or " or '. especially that the line of code he is referring too is for echoing the data, and i had the DB opened in phpmyadmin not showing data in the column UserName, so echo isn't the issue its the insert lines of codes.
Hope this helps the rest of the community.