What is going on with the variable? Subquery not being executed?

Question

I think I may be onto something. But I'm still having a problem. What am I missing here to define the Task1?. Some info you may need is I am using MySqli PDO.

$sql = "INSERT INTO Tasks 
(FirstName, LastName, 
ListName, Phone, 
HomeAddress, 
Task1) VALUES ('$FirstName', 
'$LastName', '$ListName', 
'$Phone', 
'$HomeAddress', 
(SELECT Task1 FROM 
 AutoPilotTaskLists WHERE 
 ListName = 
'$ListName'))";
 $result = $conn->query($sql);

 if ($conn->query($sql) === 
TRUE) {
echo "Here is the 
information";
$Task1<br />
";

This outputs:

Notice: Undefined variable: Task1 in /filepath/ on line 57

EDIT: Just to provide more info on the progress of discovering the solution.

PHP Version is 7.3.33. The following is the entire code (after some edits from the original above). This doesn't work and throws an error saying: Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::get_result() in /Filepath/testpage.php:34 Stack trace: #0 {main} thrown in /Filepath/testpage.php on line 34

<?php
$servername = "servername";
$username = "username";
$password = "password!";
$dbname = "DBname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER["REQUEST_METHOD"] == "POST") {
// collect value of input field
$id = $_POST['id'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$ListName = $_POST['ListName'];
$Phone = $_POST['Phone'];
$HomeAddress = $_POST['HomeAddress'];

}


$stmt = $conn->prepare('SELECT Task1 FROM AutoPilotTaskLists WHERE 
ListName 
= ?');
$stmt->bind_param("s", $ListName);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row) {
$Task1 = $row['Task1'];
$stmt = $conn->prepare("INSERT INTO Tasks 
    (FirstName, LastName, ListName, Phone, HomeAddress, Task1) 
    VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param("ssssss", $FirstName, $LastName, $ListName, 
$Phone, 
$HomeAddress, $Task1);
if ($stmt->execute()) {
    echo "Here is the information $Task1<br />";
}
}
$conn->close();

?>

Don't call `$conn->query($sql)` twice. That will insert the row a second time. Use `if ($result)` — Barmar, Sep 23 '22 at 23:08
If you want to get the value of `Task1`, you need to execute a separate `SELECT` query and fetch the result. There's no way to get intermediate results from an `INSERT` query. — Barmar, Sep 23 '22 at 23:09
Your script is vulnerable to [SQL Injection Attack](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even if [you are escaping variables, its not safe](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string%5D)! You should always use [prepared statements and parameterized queries](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either MYSQLI or PDO instead of concatenating user provided values into the query. — Barmar, Sep 23 '22 at 23:09
This is all inside of a PHP script, thus the echo. But on the safety issues was planning on taking care of that later. The idea I had was to use a subquery to select the value of task1 and use the main query to insert that and variables provided by a form by the user into the tasks table. I think there's a syntax problem or something though because it doesn't submit the task1 value and says it is an undefined variable. — Jason Evans, Sep 24 '22 at 00:43

score 1 · Answer 1 · answered Sep 23 '22 at 23:14

1

You have to do a separate SELECT query to get the value of Task1.

$stmt = $conn->prepare('SELECT Task1 FROM AutoPilotTaskLists WHERE ListName = ?');
$stmt->bind_param("s", $ListName);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row) {
    $Task1 = $row['Task1'];
    $stmt = $conn->prepare("INSERT INTO Tasks 
        (FirstName, LastName, ListName, Phone, HomeAddress, Task1) 
        VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssss", $FirstName, $LastName, $ListName, $Phone, $HomeAddress, $Task1);
    if ($stmt->execute()) {
        echo "Here is the information $Task1<br />";
    }
}

answered Sep 23 '22 at 23:14

Barmar

741,623
53
500
612

So other than the safety issues it looks like you defined the variable of task1 with $Task1 = $row['Task1']. Then did the insert query. Would this be correct? Would the second sql query use that value or forget it? – Jason Evans Sep 24 '22 at 00:54
You need to use the variable somehow. – Barmar Sep 24 '22 at 02:22
Ok, so just doing a copy and paste I get this: Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::get_result() in /path/testpage.php:34 Stack trace: #0 {main} thrown in /path/testpage.php on line 34 – Jason Evans Sep 24 '22 at 04:02
$result = $stmt->get_result(); This is line 34 – Jason Evans Sep 24 '22 at 04:03
What PHP version are you using? That function requires the mysqlnd driver, which has been installed by default since 5.4. – Barmar Sep 24 '22 at 04:06
7.3.33 is the version. – Jason Evans Sep 24 '22 at 04:09
Then it should work. See https://www.php.net/manual/en/mysqli-stmt.get-result.php – Barmar Sep 24 '22 at 04:11
I don't know what to tell you but it doesn't. I have edited the original post to give information on the exact code I have. – Jason Evans Sep 24 '22 at 04:33
You could use the older method of `$stmt->bind_result()` followed by `$stmt->fetch()` – Barmar Sep 25 '22 at 20:39

Jason Evans · Accepted Answer · 2022-09-26T04:01:16.057

0

I have figured out that my first code DID submit task1 from one table to the other. I have got it working. For those that want to see what worked:

$sql = "INSERT INTO Tasks (FirstName, LastName, ListName, Phone, 
HomeAddress, Task1) VALUES ('$FirstName', '$LastName', '$ListName', 
'$Phone', '$HomeAddress', (SELECT Task1 FROM AutoPilotTaskLists WHERE 
ListName = '$ListName'))";

$result = $conn->query("SELECT Task1 FROM AutoPilotTaskLists WHERE ListName = '$ListName'");
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
$Task1 = $row["Task1"];
}}

if ($conn->query($sql) === TRUE) {
echo "Here is the information";
echo "<h2>YOUR INPUT:</h2>";
echo "$FirstName<br />
$LastName<br />
$ListName<br />
$Phone<br />
$HomeAddress<br />
$Task1<br />
";
}

edited Sep 26 '22 at 04:01

answered Sep 25 '22 at 03:40

Jason Evans

23
5

You still never set `$Task1`, that's why you get the error message. – Barmar Sep 25 '22 at 20:38
I just updated this answer to include the solution to that as well. THANKS! – Jason Evans Sep 26 '22 at 04:01
Your code is still wide open to SQL-injection because you don't use prepared statements. – Barmar Sep 26 '22 at 14:58
I took care of that after I posted this. Thanks. – Jason Evans Sep 26 '22 at 18:57

What is going on with the variable? Subquery not being executed?

2 Answers2