How to insert the values manually as well as selecting from one table, into another table at a same time?

Question

Herein, I am doing this and instead of getting the MAX value for CustomerID, i am getting 0.

My CustomerData Table :

     Columns

---

    1   CustomerID      
    2   Name         
    3   PhoneNumber 
    4   Address      

My OrderTable1 Table

 Columns

---

1   OrderID    
2   TableID 
3   OrderDate   
4   OrderStatus
5   OrderDesc   
6   CustomerID

INSERT INTO OrderTable1 (OrderDate,OrderStatus,OrderDesc,CustomerID)
VALUES ('$LenthOfService','$Quantity','$ItemName','(SELECT MAX(CustomerID) FROM CustomerData)');

Answer 1

You can use the INSERT INTO .. SELECT statement. For the input variable values, we can SELECT them alongside maximum value of CustomerID from the CustomerData table:

INSERT INTO OrderTable1 (OrderDate,OrderStatus,OrderDesc,CustomerID)
SELECT MAX('$LenthOfService') AS OrderDate,
       MAX('$Quantity') AS OrderStatus,
       MAX('$ItemName') AS OrderDesc,
        MAX(CustomerID) AS CustomerID
FROM CustomerData

In order to be ANSI SQL compliant, we will need to use Max() function around the variable values. More details at: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Also, it seems that you are not using Parametrized queries. In that case, your code will be vulnerable to SQL injection related attacks. Please learn to use Prepared Statements