running two queries in php

Question

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 |

Just as an FYI, if all you're doing is *search and insert if found*, then you can do this in a single insert statement. See here - https://stackoverflow.com/q/5391344/296555. Ex. `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 - https://phpdelusions.net/pdo — waterloomatt, Jun 01 '18 at 11:56
See @Alexandr Khomutetsky's answer. You are trying to insert into a table with 8 columns but only passing in 6 values. There might be other issues too. Please use error reporting to find out these types of issues - https://stackoverflow.com/q/22662488/296555 — waterloomatt, Jun 01 '18 at 12:02

score 2 · Answer 1 · answered Jun 01 '18 at 11:55

2

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.

answered Jun 01 '18 at 11:55

Oleksandr Khomutetsky

66
5

I did try this. However, it seems that it was a combination of both having too many quotes and inserting less data. Thanks for the help. – Unxcellent Jun 01 '18 at 13:49

score 2 · Answer 2 · answered Jun 01 '18 at 11:56

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

score 0 · Answer 3 · answered Jun 01 '18 at 11:57

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";
        }
    }    
    ?>

running two queries in php

3 Answers3