-1

I have a MYSQL database query nested in a foreach statement like this:

foreach ($_POST['articles'] as $article => $number) {
$sql_update2 = "INSERT INTO testmerrill.Device_Article_Connection (device_id, article_id)
VALUES ((MAX (device_id) FROM testmerrill.Devices),'{$_POST['articles'][$article]}))'";
query_db1($sql_update2);
}

The above query runs after this one:

$sql_update1 = "INSERT INTO testmerrill.Devices (device_name, device_manufacturer)
VALUES ('{$device_name}', '{$device_manufacturer}')";
query_db1($sql_update1);

This page is the create page of a CRUD program I am writing. I have two tables I am working with here: Devices and Device_Article_Connection. What I am trying to do is insert into my Device_Article_Connection table multiple select statement options the user posted on submit, calling a query for as many options the user chose to include as associated with a new 'device'. I know my current solution is probably not a very elegent way to do it, but thats where I'm at at the moment. The difficulty is that I do not know the id of the new device the user just created, which I need in order to associate it with the articles the user chose to associate with it. I am trying to find the device id using the MAX function (because the last id the user just added should be the largest), but I can't seem to get that to work, there is some syntax error that I have not been able to pinpoint.

I am thankful for any suggestions.

Patrick Q
  • 6,373
  • 2
  • 25
  • 34
Jordan Merrill
  • 3
  • 2
  • Are you using [mysqli](https://www.php.net/manual/en/book.mysqli.php)? – Patrick Q Nov 14 '19 at 13:13
  • instead of query_db1() try `$conn = mysqli_connect(host,user,pass,dbname) `(at the start of the code then `mysqli_query($conn, $sql_update)` – Jack Wright Nov 14 '19 at 13:18
  • The query_db1() function works fine, it is just part of the company's larger database logic – Jordan Merrill Nov 14 '19 at 13:22
  • In case you think your update answers my question, it doesn't. mysqli is a specific extension for connecting to MySQL. Same for [PDO](https://www.php.net/manual/en/book.pdo.php). The answer to your question likely depends on what you're using to connect to your MySQL db. – Patrick Q Nov 14 '19 at 13:22
  • My bad Patrick, my eyes must be bad! Yes, I am using a functional approach (mysqli) – Jordan Merrill Nov 14 '19 at 13:24
  • try `(SELECT MAX(device_id))` – Jack Wright Nov 14 '19 at 13:24
  • You'll want to use [`insert_id`](https://www.php.net/manual/en/mysqli.insert-id.php), in combination with [transactions](https://www.php.net/manual/en/mysqli.begin-transaction.php) and a try/catch block. So start a try, start a transaction, insert the device, get the insert id, insert the child records using the device insert id, commit the transaction, end the try, open catch, rollback within catch, close catch. – Patrick Q Nov 14 '19 at 13:29
  • `$_POST['articles'][$article]` this snippet of code still shows the rest of your code as in a string (query_db1()) - try maybe: `$postArticle = $_POST['articles'][$article]` - then in your insert query use `$postArticle` instead? – Jack Wright Nov 14 '19 at 13:30
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Nov 14 '19 at 18:17

1 Answers1

1

If you use mysqli for a database connection, you could use insert_id to solve this problem:

$conn = new mysqli($server, $user, $pass, $db);
$q = $conn->prepare("INSERT INTO testmerrill.Devices (device_name, device_manufacturer) VALUES (?, ?)"); // Prepare the query
$q->bind_param("ss", $device_name, $device_manufacturer); // Bind variables to prevent SQL injection
$q->execute();
$last_id = $q->insert_id; //Represents the last inserted id
René Beneš
  • 448
  • 1
  • 13
  • 29