Form get POST data, cross reference and insert into mysql

I’ve got a basic PHP script which takes information POSTed from a form and inserts the values into a mySQL database.

Here’s the script:

<?php
$con=mysqli_connect("server.com","username","psswd","dbname");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$reference = mysqli_real_escape_string($con, $_POST['reference']);
$sensor = mysqli_real_escape_string($con, $_POST['sensor']);

$sql="INSERT INTO data_table (reference, sensor)
VALUES ('$reference', '$sensor'')";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);
?>

That basically gets two fields: reference and sensor.

What I would like to do is get the information from the form then check the ‘reference’ number against a different table which would look like this:

[table=“width: 500, class: grid”]
[tr]
[td]reference[/td]
[td]coolthing[/td]
[/tr]
[tr]
[td]001[/td]
[td]333[/td]
[/tr]
[tr]
[td]002[/td]
[td]445[/td]
[/tr]
[tr]
[td]003[/td]
[td]545[/td]
[/tr]
[/table]

So it checks the ‘reference’ against that table and then pulls the ‘coolthing’ value as a variable and finally inserts all three bits of info into the MySQL database so you end up with ‘reference’, ‘sensor’ and ‘coolthing’ all being entered.

How might I be able to do this?

It should be rather simple to do a query before your INSERT query to get that coolthing value. Also Look for extra ’ in your POSTed code above in the VALUES section.

That was my initial thought but wont the $results get confused? Could you suggest some code?


<?php
$con=mysqli_connect("server.com","username","psswd","dbname");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$reference = mysqli_real_escape_string($con, $_POST['reference']);
$sensor = mysqli_real_escape_string($con, $_POST['sensor']);

$findcool = "SELECT coolthing from other_table where reference = '" . $reference . "'";
if (mysqli_query($con, $findcool)) {
  $coolthing = // however you retrieve the data using mysqli, sorry, not sure of syntax
  }

$sql="INSERT INTO data_table (reference, sensor,coolthing)
VALUES ('$reference', '$sensor','$coolthing')";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);
?>

There’s no reason anything should get confused as long as you use different variables as necessary. Once you’ve run the first query, you’ve got $coolthing and you don’t really care about anything else.

It’s also possible to do it in a single INSERT query that uses a SELECT subquery:


INSERT INTO data_table (reference, sensor, coolthing)
SELECT reference, $sensor, coolthing FROM reference_table
WHERE reference = $reference

Couldn’t get that working.

tried

<?php 
$con=mysqli_connect("server.com","username","psswd","dbname");
// Check connection 
if (mysqli_connect_errno()) { 
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
} 

// escape variables for security 
$reference = mysqli_real_escape_string($con, $_GET['reference']);
$sensor = mysqli_real_escape_string($con, $_GET['sensor']);

$findcool = mysqli_query($con,"SELECT coolthing from other_table where reference = '" . $reference . "'"); 
if (mysqli_query($con, $findcool)){  
  $coolthing =  $findcool ;
  } 
  

  echo "coolthing:";
  echo $coolthing;
  echo "</br>";
  

$sql="INSERT INTO data_table (reference, sensor,coolthing) 
VALUES ('$reference', '$sensor','$coolthing')"; 

if (!mysqli_query($con,$sql)) { 
  die('Error: ' . mysqli_error($con)); 
} 
echo "1 record added"; 

mysqli_close($con); 
?>

but get the error :

coolthing:
Error: Unknown column ‘coolthing’ in ‘field list’

Fretburner could you explain how this would be integrated into my original script?

But you said in POST #1 that you have a column named coolthing. Is the table name really “other_table”?

Just change the SQL (adjusting the table names etc to suit):


$con = mysqli_connect("server.com","username","psswd","dbname");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$reference = mysqli_real_escape_string($con, $_POST['reference']);
$sensor = mysqli_real_escape_string($con, $_POST['sensor']);

$sql = "INSERT INTO data_table (reference, sensor, coolthing)
SELECT reference, $sensor, coolthing FROM reference_table
WHERE reference = $reference";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);

Is there no need for the ‘VALUES’ line?

I get the error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ coolthing FROM reference_table WHERE reference =’ at line 2

No, that part is replaced by the fields from the SELECT subquery.

replaced the connection details which fixed that error but now getting

Error: Unknown column ‘coolthing’ in ‘field list’

again

As Drummin pointed out, are these the actual names from your database?

here’s a screenshot of phpmyadmin

Essentially I have 2 columns named: reference and coolthing

OK, and what about the table that you’re inserting the data into? It’s called ‘data_table’, and has at least three columns, called ‘reference’, ‘sensor’ and ‘coolthing’?

Ahh how stupid of me, that’s sorted and all works fine.

1 final thing I would quite like to be able to do is to set coolthing as a variable so that I end up inserting, reference, sensor, coolthing and (sensor + coolthing) being inserted into the table.

perhaps setting coolthing as a variable $coolthing and then adding $sensor and $coolthing together?

Check out the MySQL function CONCAT which can be used to join column data together within the query.

My guess would be that’s because you’re not fetching the first result from the query - all we’ve done is called the query, but not retrieved the row that it hopefully returns. I don’t use mysqli so I didn’t know the syntax hoping you’d add it in. However doing it in one query is nicer.

They’re both integers so I can just add them together, my issue is how to insert that number which is made up of the others.