This question already has an answer here:
I need help trying to make the below php code work. The code searches for User on the Users table. When doing the search I would like to insert the timestamp of the search in another table called scan. The purpose of this is to create a report with the table scan as I want to see every time a User has been searched.
However, everything works except for the second query. Can anyone help me identify what's wrong with the php code below and how can I make it work? Again, I would like to make the second query (INSERT INTO) work and to insert the searched data into the scan table.
<?php
// initalize the variables
$osha = "";
$firstname = "";
$lastname = "";
$company = "";
$trade = "";
// php code to search data in mysql database and set it in input text
if(isset($_POST['search']))
{
// connect to mysql
$dbc = mysqli_connect("127.0.0.1", "root", "root","demodb");
// id to search
$user_id = mysqli_real_escape_string($dbc, $_POST['user_id']);
$query = "SELECT * FROM Users WHERE user_id = '$user_id' LIMIT 1";
$rs = mysqli_query($dbc, $query);
if (mysqli_num_rows($rs) == 1)
{
$row = mysqli_fetch_array($rs);
$osha = $row['osha'];
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$company = $row['company'];
$trade = $row['trade'];
$query = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) VALUES (" .
"'" . $user_id . "', '" .
"'" . mysqli_real_escape_string($dbc, $osha ) . "', '" .
"'" . mysqli_real_escape_string($dbc, $firstname) . "', '" .
"'" . mysqli_real_escape_string($dbc, $lastname ) . "', '" .
"'" . mysqli_real_escape_string($dbc, $company ) . "', '" .
"'" . mysqli_real_escape_string($dbc, $trade ) . "')";
mysqli_query($dbc, $query);
}
else
{
echo "Undefined ID";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title> PHP FIND DATA </title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<form action="barcode.php" method="post">
Id:<input type="text" name="user_id"><br><br>
Osha #:<input type="text" name="osha" value="<?= htmlspecialchars($osha) ?>"><br><br>
First Name:<input type="text" name="firstname" value="<?= htmlspecialchars($firstname) ?>"><br>
<br>
Last Name:<input type="text" name="lastname" value="<?= htmlspecialchars($lastname) ?>"><br><br>
Company:<input type="text" name="company" value="<?= htmlspecialchars($company) ?>"><br><br>
Trade:<input type="text" name="trade" value="<?= htmlspecialchars($trade) ?>"><br><br>
<input type="submit" name="search" value="Find">
</form>
</body>
</html>
ADDING Tables definitions
Users Table
| Users | CREATE TABLE `Users` (
`user_id` int(6) unsigned NOT NULL AUTO_INCREMENT,
`osha` int(50) DEFAULT NULL,
`firstname` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
`company` varchar(50) DEFAULT NULL,
`trade` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`picture` varchar(50) DEFAULT NULL,
`reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=98819 DEFAULT CHARSET=latin1 |
scan Table
| scan | CREATE TABLE `scan` (
`user_id` int(6) unsigned NOT NULL DEFAULT '0',
`osha` int(50) DEFAULT NULL,
`firstname` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
`company` varchar(50) DEFAULT NULL,
`trade` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`picture` varchar(50) DEFAULT NULL,
`reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
</div>
You are inserting less data than you declare fields to insert. There are no values for fields 'email' and 'picture'. At least this could be reason of your problem.
Your query results to a 2 single quote on the value.
Try this one.
$query = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) VALUES (" .
"'" . $user_id . "', " .
"'" . mysqli_real_escape_string($dbc, $osha ) . "', " .
"'" . mysqli_real_escape_string($dbc, $firstname) . "', " .
"'" . mysqli_real_escape_string($dbc, $lastname ) . "', " .
"'" . mysqli_real_escape_string($dbc, $company ) . "', " .
"'" . mysqli_real_escape_string($dbc, $trade ) . "')";
mysqli_query($dbc, $query);
I would also suggest to using mysql parameter for good practices
Untested but in looking at this you have a lot of extra, unneeded quotes at the end of each mysqli_real_escape_string line:
<?php
// initalize the variables
$osha = "";
$firstname = "";
$lastname = "";
$company = "";
$trade = "";
// php code to search data in mysql database and set it in input text
if(isset($_POST['search']))
{
// connect to mysql
$dbc = mysqli_connect("127.0.0.1", "root", "root","demodb");
// id to search
$user_id = mysqli_real_escape_string($dbc, $_POST['user_id']);
$query = "SELECT * FROM Users WHERE user_id = '$user_id' LIMIT 1";
$rs = mysqli_query($dbc, $query);
if (mysqli_num_rows($rs) == 1)
{
$row = mysqli_fetch_array($rs);
$osha = $row['osha'];
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$company = $row['company'];
$trade = $row['trade'];
$query = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) VALUES (" .
"'" . $user_id . "'," .
"'" . mysqli_real_escape_string($dbc, $osha ) . "', " .
"'" . mysqli_real_escape_string($dbc, $firstname) . "', " .
"'" . mysqli_real_escape_string($dbc, $lastname ) . "', " .
"'" . mysqli_real_escape_string($dbc, $company ) . "', " .
"'" . mysqli_real_escape_string($dbc, $trade ) . "')";
mysqli_query($dbc, $query);
}
else
{
echo "Undefined ID";
}
}
?>