I am working on a web page to insert data into postgresql database using php-pdo. I am able to successfully insert data into the tables but following notices and one error is raised.
Here are my questions?
1) What wrong I am doing that is causing these errors & warnings ?
2) How to correct errors & warnings?
Here are errors:
ERRORS
Notice: Undefined index: l_id in C:\xampp\htdocs\ubicomp\index3.php on line 97
Notice: Undefined index: time in C:\xampp\htdocs\ubicomp\index3.php on line 97
Notice: Undefined index: m_gps in C:\xampp\htdocs\ubicomp\index3.php on line 97
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "" LINE 2: VALUES ('','01','','') ^
Here is my code:
CODE
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP-PostgreSQL</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm" style="background-color: #FFFFFF">
<h2 style="color:navy;">PHP - Postgres</h2>
<?php
$servername = "localhost";
$username = "postgres";
$password = "test123";
$dbname = "testDB";
class TableRows extends RecursiveIteratorIterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current() {
return "<td style='width:150px;border:1px solid grey;'>" . parent::current(). "</td>";
}
function beginChildren() {
echo "<tr>";
}
function endChildren() {
echo "</tr>" . "\n";
}
}
?>
<div class="col-sm" style="background-color: #FFFFFF">
<?php
echo "<h3>Table1</h3>";
echo "<table class='table table-hover table-bordered table-reponsive'>";
echo "<thead class='table-dark'>";
echo "<tr><th>l_id</th><th>p_id<th>time</th><th>m_gps</th></tr>";
try {
$conn = new PDO("pgsql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT l_id, p_id, time, m_gps FROM table1");
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
echo $v;
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
//$conn = null;
echo "</thead'>";
echo "</table>";
?>
<div id="form">
<form action="" method="post">
<input type="text" name="l_id" id="l_id" required="required" placeholder="Enter l_id"/>
<input type="text" name="p_id" id="p_id" required="required" placeholder="Enter p_id"/>
<input type="text" name="time" id="time" required="required" placeholder="Enter time"/>
<input type="text" name="m_gps" id="m_gps" required="required" placeholder="Enter M_gps"/>
<br/><br />
<input type="submit" value=" Insert " name="submit"/><br />
</form>
<hr/>
</div>
<?php
if(isset($_POST["submit"])){
try {
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
$sql = "INSERT INTO table1 (l_id, p_id, time, m_gps)
VALUES ('".$_POST["l_id"]."','".$_POST["p_id"]."','".$_POST["time"]."','".$_POST["m_gps"]."')";
if ($conn->query($sql)) {
echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
}
else{
echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
?>
</div>
<div class="col-sm" style="background-color: #FFFFFF">
<?php
echo "<h3>Table2</h3>";
echo "<table class='table table-hover table-bordered table-reponsive'>";
echo "<thead class='table-dark'>";
echo "<tr><th>a_id</th><th>p_id</th><th>gps_koordinat<th>temp</th></tr>";
try {
$conn = new PDO("pgsql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT a_id, p_id, gps_koordinaat, temp FROM table2");
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
echo $v;
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
//$conn = null;
echo "</thead'>";
echo "</table>";
?>
<div id="form">
<form action="" method="post">
<input type="text" name="a_id" id="a_id" required="required" placeholder="Enter a_id"/>
<input type="text" name="p_id" id="p_id" required="required" placeholder="Enter p_id"/>
<input type="text" name="gps_koordinaat" id="gps_koordinaat" required="required" placeholder="Enter gps_koordinaat"/>
<input type="text" name="temp" id="temp" required="required" placeholder="Enter temp"/>
<br/><br />
<input type="submit" value=" Insert " name="submit"/><br />
</form>
<hr/>
</div>
<?php
if(isset($_POST["submit"])){
try {
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
$sql = "INSERT INTO table2 (a_id, p_id, gps_koordinaat, temp)
VALUES ('".$_POST["a_id"]."','".$_POST["p_id"]."','".$_POST["gps_koordinaat"]."','".$_POST["temp"]."')";
if ($conn->query($sql)) {
echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
}
else{
echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
$conn = null;
?>
</div>
</div>
</div>
</div>
</body>
</html>
Here are the queries for the tables.
**Table1**
CREATE TABLE public.table1
(
l_id integer NOT NULL,
p_id integer,
time text COLLATE pg_catalog."default",
m_gps text COLLATE pg_catalog."default",
CONSTRAINT l_pkey PRIMARY KEY (l_id),
CONSTRAINT table1_p_id_fkey FOREIGN KEY (p_id)
REFERENCES public.table_p (p_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.table1
OWNER to postgres;
Table2:
CREATE TABLE public.table2
(
a_id integer NOT NULL,
p_id integer,
gps_koordinaat text COLLATE pg_catalog."default",
temp real,
CONSTRAINT a_pkey PRIMARY KEY (a_id),
CONSTRAINT table2_p_id_fkey FOREIGN KEY (p_id)
REFERENCES public.table_p (p_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.table2
OWNER to postgres;
