CODEWITHSUNDEEP
phpmysql
Johan Fourie
Johan Fourie

Reputation: 179

Updating mysql database based on logical test

I am trying to update one database based on the data of another.

Basically the road no, the block no, and the building no are in both databases, but the one contains the lats and longs and the other doesn't. So I am trying to update the lats and longs to the other.

Here is what I did.

<?php 

 mysql_connect("localhost", "root", "test") or die(mysql_error()); 
 mysql_select_db("class") or die(mysql_error()); 
 $data = mysql_query("SELECT * FROM testdata WHERE lat2='' And lon2 = ''"); // Selects from the database without lats and longs
 $address = mysql_query("SELECT * FROM address ") // selects from database with lats and longs
 or die(mysql_error()); 


 while($infodata = mysql_fetch_array( $data )) 
 { 
    $infoaddress = (mysql_fetch_array($address));


    $BuildingA = $infoaddress['Building']; // A stands for Address where is the database address
    $RoadNoA = $infoaddress['RoadNo'];
    $BlockA = $infoaddress['Block'];

    $BuildingD = $infodata['Building']; // D stands for testData which is the same database
    $RoadNoD = $infodata['RoadNo'];
    $BlockD = $infodata['Block'];

    $idA = $infoaddress['id']; // ID for address
    $idD = $infodata['id']; // ID for Data

    $lat = $infoaddress['lat']; // get the lats and longs
    $lon = $infoaddress['lon'];

    if ($BuildingA = $BuildingD && $RoadNoA = $RoadNoD && $BlockA = $BlockD)
     { // do the logical test

    mysql_query("UPDATE testdata SET lat2='$lat', lon2='$lon' WHERE id='$idD'"); // update the values

     }
     else 
    mysql_query("UPDATE testdata SET lat2='', lon2='' WHERE id='$idD'"); // update the values
 }

?>

What happens is that it does update the testdatabase, but for some reason, it has the wrong lats and longs, even when I type in a wrong road and block and building no that don't exist in the address database, it updates it with some lats and longs.

It is important that all 3 conditions be true.

Any suggestions?

Upvotes: 0

Views: 48

Answers (2)

Barmar
Barmar

Reputation: 781716

I suspect what you're really looking for is an UPDATE with a JOIN:

UPDATE testdata t
JOIN address a USING (Building, RoadNo, Block)
SET t.lat2 = a.lat, t.lon2 = a.lon
WHERE t.lat2 = '' AND t.lon2 = ''

Upvotes: 1

Krish R
Krish R

Reputation: 22721

Try this, Use == instead of =

 if ($BuildingA == $BuildingD && $RoadNoA == $RoadNoD && $BlockA == $BlockD)

instead of 

if ($BuildingA = $BuildingD && $RoadNoA = $RoadNoD && $BlockA = $BlockD)

Upvotes: 2

Related Questions