tooba tooba - 6 days ago 6
MySQL Question

update empty attributes with select

$attrib= array('State', 'one', 'two', 'three', 'four', 'five', 'six');
for ($i=0; $i <6; $i++) {
$sql= "insert into table(Attribute, col1, col2)
select '$attrib[$i]', MIN(cast(".$attrib[$i]." as decimal(5,1))), MAX(cast(".$attrib[$i]." as decimal(5,1))) from table2";
$run_sql= mysqli_query($conn, $sql);
}

for ($i=0; $i < 6; $i++) {
$sql= "insert into table(col3, col4)
select MIN(cast(".$attrib[$i]." as decimal(5,1))), MAX(cast(".$attrib[$i]." as decimal(5,1))) from table2 where Class=1";
$run_sql= mysqli_query($conn, $sql);
}


The problem in here is that the second for loop starts insertion into col3 and col4 where the insertion of col1 and col2 ends. So the starting all the values of col3 and col4 are empty i want to update those empty values instead of starting insertion where the values of col1 and col2 ends.

Answer

If you had a Primary Key for you table table2, it would be better, but since your subqueries return only one row, the you still can combine these two queries in one query like this:

$sql = "insert into table(Attribute, col1, col2, col3, col4)
select t2_1.".$attrib[$i].", MIN(cast(t2_1.".$attrib[$i]." as decimal(5,1))), MAX(cast(t2_1.".$attrib[$i]." as decimal(5,1))), MIN(cast(t2_2.".$attrib[$i]." as decimal(5,1))),  MAX(cast(t2_2.".$attrib[$i]." as decimal(5,1)))
from table2 as t2_1, table2 as t2_2
where t2_2.Class = 1";

I'm joining these two table(which both are the same table2) and I'm giving them alias names t2_1 and t2_2(you can change these two names with any other name which make more sense to you) and I'm selecting fields that I want from each of them.

So your code would have only one loop and look like this:

$attrib = array('State', 'one', 'two', 'three', 'four', 'five', 'six');              
for ($i = 0; $i <= count($attrib); $i++) { 
    $sql = "insert into table(Attribute, col1, col2, col3, col4)
    select t2_1.".$attrib[$i].", MIN(cast(t2_1.".$attrib[$i]." as decimal(5,1))), MAX(cast(t2_1.".$attrib[$i]." as decimal(5,1))), MIN(cast(t2_2.".$attrib[$i]." as decimal(5,1))),  MAX(cast(t2_2.".$attrib[$i]." as decimal(5,1)))
    from table2 as t2_1, table2 as t2_2
    where t2_2.Class = 1";
    $run_sql= mysqli_query($conn, $sql);
}

Also note that in you for, instead of using a hardcode number 6 it's better to your count() to get the length of the array(as I'm using in this code) and since you have 7 records in your array, I think your for loop condition should be <= instead of <

Comments