PHP->sql UPDATE row with data from 2 other rows merged together within the same table

Table = location Fields = address, street, suburb

address fields are initially empty, street and suburb are populated. I have tried to update the address field with the street and suburb data but its not working. only allows me to update with one field. With the code below the address field would be updated with 0's... But if i remove one of the variables from the update then it will update correctly with ju开发者_如何学Cst either the street or suburb.

The reason why im doing this is because the way the data is given to me is in that format and would take too long for me to manually do it.

while ($row=$result->fetch_assoc()) {

    $street=$row['street'];
        $suburb=$row['suburb'];

        $mysqli->query("UPDATE address SET address = $street+$suburb");
        echo($street);
}
}

Don't forget to put a WHERE clause in your UPDATE query, unless you want your whole table to have the same adress column. Try this

while ($row=$result->fetch_assoc()) {

        $street=$row['street'];
        $suburb=$row['suburb'];
        $address=$street." ".$suburb;
        $id=$row['id'];

        $mysqli->query("UPDATE address SET address = `".address."` WHERE id=".$id);

}

Try

 $mysqli->query("UPDATE address SET address ='". $street." ".$suburb."' WHERE street = '$street' AND subrub = '$subrub'");

+ is not a concatenation operator in php. So try using .

Also, if you dont have the WHERE clause, then all the rows will get updated!!!

Try with:

while ($row=$result->fetch_assoc()) {

    $street=$row['street'];
        $suburb=$row['suburb'];
    $address = $street.$suburb;

        $mysqli->query("UPDATE address SET address = '$address'");
        echo($street);
}
}

You don't have to fetch results to PHP and then run an UPDATE for every row. It seems you are now sending one SELECT and numerous (thousands or millions, depends on the size of your table) of UPDATE statements to the database.

You can update the whole table (all rows!) with one UPDATE statement:

"UPDATE location SET address = CONCAT(street, ' ', suburb) ;" 

If (later on) some of your rows have already data in the address column, you can update only rows with empty address:

"UPDATE location
 SET address = CONCAT(street, ' ', suburb) 
 WHERE address = ''
    OR address IS NULL ;"  

You should also check that the size of address field is bigger or at least equal to the maximum of CHAR_LENGTH(street) + CHAR_LENGTH(suburb) + 1