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 | 
INSERT INTO scan (user_id, ...) SELECT user_id... FROM Users WHERE user_id = :userId. In addition, you're very open to SQL injection attacks. Look up parameterized queries. Parameterized queries will also help you avoid those unnecessary quotes which make it very difficult to debug. Here's a decent reference for PDO - phpdelusions.net/pdo