ppppp ppppp - 1 year ago 45
HTML Question

Insert data with MAX(id) and values of status at the same time

I was trying with this code but it didnt work. it's always get the MAX(eq_no) as 0

$sql1 =mysqli_query($con, "SELECT MAX(eq_no) AS val FROM tech_add_equip");

$sql2 = "INSERT INTO time (eq_no,status_no) VALUES ('$val', 4 );";
if (!mysqli_query($con,$sql2)) {
die('Error: ' . mysqli_error($con)); };

Finally, after i try with this code, it insert in the right number of MAX(eq_no) but i still cant insert the values of status_no

INSERT INTO time (eq_no) SELECT MAX(eq_no) AS vale FROM tech_add_equip

Could you suggest me what did i missing in the code?

Thank you for your helping

Answer Source

One row returned from SELECT a,b,c statement in sub query is equivalent to set of values that is otherwise hardcoded as ('a-value','b-value','c-value')*. You can hardcode a value within select as well:

INSERT INTO time (eq_no, status_no)
    SELECT MAX(eq_no), 4
    FROM tech_add_equip

No need for aliases within select - order of columns matters.

*) One row result can be used for IN() clause. Another row would become set of values after comma - can't be uset for IN(), but it works ok for INSERT

('row1-a-value', 'row1-b-value'), ('row2-a-value', 'row2-b-value')