This question already has an answer here:
- Running multiple queries in php 2 answers
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>
