How to invoke data_array into a foreach loop that deal with database

Question

Problem: How can I invoke array into a foreach loop where I invoke data from database?

Currently I have an array:

Array
(
    [0] => Array
        (
            [time] => 2016-07-01 19:00:00
            [name_station] => RC Bilogora
            [longitude] => 17.162
            [latitude] => 45.882
            [type_station] => 0
            [temperature] => 28.8
            [humidity] => 62
            [pressure] => 1015.7
            [pressure_tendency] => -0.5
            [wind_direction] => SW
            [wind_velocity] => 1.6
            [precipitation] => preteĹľno vedro
            [icon] => 2
        )

    [1] => Array
        (
            [time] => 2016-07-01 19:00:00
            [name_station] => Bjelovar
            [longitude] => 16.869
            [latitude] => 45.910
            [type_station] => 1
            [temperature] => 28.6
            [humidity] => 54
            [pressure] => 1015.8
            [pressure_tendency] => -0.5
            [wind_direction] => NW
            [wind_velocity] => 1.4
            [precipitation] => lahor
            [icon] => -
        )

    [2] => Array
        (
            [time] => 2016-07-01 19:00:00
            [name_station] => Crikvenica
            [longitude] => 14.689
            [latitude] => 45.173
            [type_station] => 1
            [temperature] => 28.0
            [humidity] => 62
            [pressure] => 1015.8
            [pressure_tendency] => -0.9
            [wind_direction] => E
            [wind_velocity] => 1.8
            [precipitation] => povjetarac
            [icon] => -
        )
...

It is stored under $xml_data_array;

From that data I need to get $station_name and $time and compare it to a database values $station_name_db and $time_db.

This part of code communicates with database:

   try {
                $sql_vwstationmeasurment_select = "SELECT * FROM vwstationmeasurment";
                $stmt_vwstationmeasurment_select = $dbh->prepare($sql_vwstationmeasurment_select);
                $stmt_vwstationmeasurment_select->execute();
                $result_vwstationmeasurment_select = $stmt_vwstationmeasurment_select->fetchAll(PDO::FETCH_ASSOC);

                $result_vwstationmeasurment_select_array = array ();
                foreach($result_vwstationmeasurment_select as $row) {
                        $station_name_db = $row['station_name'];
                        $time_db = $row['time'];

                        // how to invoke $station_name and $time here?
                        if ($station_name_db != $station_name OR empty($station_name_db)){
                            // do something
                        } elseif($time_db != $time){
                            // do something
                        }
                                $result_vwstationmeasurment_select_array[] =($row);
                }

                // save transaction
                $dbh->commit();

                                // close database connection
                $dbh = null;

       } catch (PDOException $e) {
                // cancel the transaciton if something went wrong and write msg about it
                $dbh->rollBack();
                print "Error!: " . $e->getMessage() . "<br/>";
                die();
       }

I dont know how to invoke data from $xml_data_array ($station_name and $time) into it? I would appriciate any kind of help.

it's not really clear what you're trying to do. What is the connection between db-data and array? Do you have an ID f.e.? — Jeff, Jul 02 '16 at 17:08
Please look at the above code which communicates with db, there is a comment there. So I dont know how to invoke $station_name and $time from $xml_data_array in that foreach loop, I only know how to invoke it with another foreach loop inside the first one, but then my data is inserted lots of times. Please also take a look at http://stackoverflow.com/questions/38161353/inserting-and-updating-data-into-a-database-under-certain-terms?noredirect=1#comment63751348_38161353 — vayacondios2015, Jul 02 '16 at 18:18
I still don't know what you _actually_ want to achieve, what connection there could be between array and db data (any id??),... But here's the simple answer to the simple question you have: `$station_name = $xml_data_array[0]['name_station'];` — Jeff, Jul 03 '16 at 12:36
There are no IDs in $xml_data_array. This code $station_name= ... that you have provided returns only first station in an array (RC Bilogora). I want to grab all the "station data" from the $xml_data_array (I dont know how to do that) and compare it with "station data" from my database. For example...if RC_Bilogora is equal to RC_Bilogora dont do anything, if it isnt INSERT data for another station that doesnt exist in a database. — vayacondios2015, Jul 03 '16 at 13:26

score 0 · Answer 1 · edited May 23 '17 at 11:52

After some communication to find out what you want to achieve, here's my answer, which would be too long for a comment.

You basically want to check if any item in your array in not yet stored in your db.

To do so I'd

// loop through that array
foreach($xml_data_array as $i => $station) {
    // check in database if you've got that station already
    $sql = "Select ID [or any other fieldname, please don't all with *] from vwstationmeasurment where station_name= ?"
    // prepare that statement
    $stmt = $dbh->prepare($sql_vwstationmeasurment_select);
    // bind params:
    $stmt->bind_param('s', $station['name_station']);
    // execute
    $stmt->execute();
    // get row-count as "found"
    $stmt->store_result();
    $found = $stmt->num_rows;

    // check if we have NOT got a match
    if(!$found) {
       // insert that item into database
    }

}

Depending on how many items you expect on either side it might be quicker the other way round:

Fetch all stored stations, loop through that and unset any matching item in array, then insert the rest.

Another possibility would be using Insert IGNORE - more info here: How to 'insert if not exists' in MySQL? But for that the field station_name would have to be defined as unique.

How to invoke data_array into a foreach loop that deal with database

1 Answers1