How to use result of one query in the next

[jbearnolimits]

I'm trying to get the result of Task1 in table AutoPilotTaskLists where the column is ListName. I then want to use that result in another query to insert it along with other information submitted by a form into the table Tasks. My problem is if I write two queries it "forgets" the result of Task1 when it runs the next query. How do I get it to remember Task1 and put it into the other table?

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'];
}

$sql = "SELECT Task1 FROM AutoPilotTaskLists WHERE ListName = '$ListName'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // output data of each row
 while($row = $result->fetch_assoc()) {
 echo "" . $row["Task1"]. " <br>";
 }
} else {
 echo "0 results";
}

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

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 />
";
} else

{
 echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

It gets the value of Task1 and echo's it from the first query so I know it can get the value. And the second query works to insert all of the information into the second table, except for Task1. It throws the following error:

Notice: Undefined variable: Task1

[jbearnolimits]

I think I may be onto something. But I'm still having a problem. Here is what I'm now using.

$sql = "SELECT Task1 FROM AutoPilotTaskLists WHERE ListName = '$ListName'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // output data of each row
 while($row = $result->fetch_assoc()) {
  echo "" . $row["Task1"]. " <br>";
 }
} else {
 echo "0 results";
}

$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";
echo "<h2>YOUR INPUT:</h2>";
echo "$FirstName<br />
$LastName<br />
$ListName<br />
$Phone<br />
$HomeAddress<br />
$Task1<br />
";

This outputs:

This is the Task1 Info stored in the DB.
Here is the information
YOUR INPUT:

Notice: Undefined variable: Task1 in /filepath/ on line 57
This is the first name
This is the Last name
This is the list name
this is the phone number

So, what am I missing here to define the Task1?

[phranque]

It gets the value of Task1 and echo's it from the first query so I know it can get the value. And the second query works to insert all of the information into the second table, except for Task1. It throws the following error:

Notice: Undefined variable: Task1

what you have echoed is $row["Task1"] which isn't the same as $Task1

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

similarly, the SELECT statement within the INSERT statement does nothing to evaluate $Task1 when the query is executed.

echo "$FirstName<br />
$LastName<br />
$ListName<br />
$Phone<br />
$HomeAddress<br />
$Task1<br />
";

note that all variables in this statement but $Task1 were valuated earlier from $_POST variable values rather than a db query.

[jbearnolimits]

Yes, that is correct. It echos what is in the table column named Task1.

Yes, you are correct that the Select statement does nothing. This is the problem I am asking for help with as I know I need to get the value via a subquery. I just don't know how. If I write it as the tutorials I have seen it tells me I have an error in syntax even though I copied and pasted the syntax. If I do it like this it tells me task1 is undefined. So how am I supposed to write it?

Yes, the other variables came from a form and not a database.

[jbearnolimits]

I have new information. And by the way, I'm using MySqli PDO for the syntax.

So I discovered that the code I will share below DOES INDEED submit everything to the table "Tasks."

BUT Even though it is submitting the task1 variable it still gives me this Notice: Undefined variable: Task1 in /Path/TestPage.php on line 56

And, it doesn't show the task1 info in the bottom section showing what was input.

Below is the code that is submitting to the database but throwing the notice up and not showing the result in the bottom portion.

<?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'];
}

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

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 />
";
} else

{
 echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

[phranque]

Even though it is submitting the task1 variable ...

no.
it isn't.

the Task1 column is not the same as the Task1 variable.
executing statements on the database doesn't automatically make things happen in your programming language environment.
until you declare or evaluate the $Task1 variable, it is undefined and therefore does not exist.

[jbearnolimits]

I'm not sure I understand what you are trying to say. The information in the table autopilottasks at the row with listname FSBO and the column named Task1 is being sent to the other table called Tasks successfully.

Are you trying to say that even though it's doing that it still isn't able to be used elsewhere on the page? If so I agree.

What do you suggest? Obviously I need to define the task1 variable. But how?

[jbearnolimits]

Got it.

$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"];
}}

[phranque]

note that in this case $Task1 is still undefined if there are no results from the query.

[jbearnolimits]

Did not think about that. I suppose I can do an else statement and have Task1 = null.

[phranque]

... or declare $Task1 before the if statement ...