Compare two queries in PHP-MySQL based on date and time and paste the selected data into a table

Question

I have Table1and Table2in the MySQL database. I want to select animalsand gardensdata from Table1, then I would like to choose new_animalsand new_gardensfrom Table2what time has expired during the last week. Then, causing mysql_fetch_array ($row1)the data from Table1if both of the field animalsand gardensnot previously recorded in Table2, insert date, animalsand gardensa new_date, new_animalsand new_gardensa Table2.

Please take a look at the table layout and view the code below. Note that it Table1returns 4 rows from $select_query1, and since the data is not written to Table2; It must be inserted in Table2. The code below works fine if the if condition, but does not work with the if condition. Could you tell me your corrected version of the if condition in this problem?

$select_query1 = mysql_query("SELECT date, animals, gardens FROM Table1");
$select_query2 = mysql_query("SELECT new_animals, new_gardens FROM Table2  
                                          WHERE new_date between date_sub(now(),INTERVAL 1 WEEK) and now()");
$row1 = mysql_fetch_array($select_query1); // This returns 4 rows
$row2 = mysql_fetch_array($select_query2); // This returs 4 rows
    while ($row1) {
        if ($row2['new_animals'] !== $row1['animals'] && $row2['new_gardens'] !== $row1['gardens']) {
            $animals = $row1['animals'];
            $gardens = $row1['gardens'];
            $date = $row1['date'];
            $insert_query = mysql_query("INSERT INTO Table2 (new_date, new_animals, new_gardens) 
            VALUES ('$date', '$animals', '$gardens')");
    }
}

Answer 1

Your operator is ifincorrect. You compare the rows from table 1 only with the first row from table2. You need to compare the rows from table 1 with all the rows of table2.

try it

$select_query1 = mysql_query("SELECT date, animals, gardens FROM Table1");

while ($row1 = mysql_fetch_array($select_query1)) {

    $select_query2 = mysql_query("SELECT new_animals, new_gardens FROM Table2WHERE new_date between date_sub(now(),INTERVAL 1 WEEK) and now()");
    $count=0;
    while($row2 = mysql_fetch_array($select_query2))    
    {
        if ($row2['new_animals'] == $row1['animals'] && $row2['new_gardens'] == $row1['gardens']) {$count=1;}
    }

    if($count==0)
    {
        $animals = $row1['animals'];
        $gardens = $row1['gardens'];
        $date = $row1['date'];
        $insert_query = mysql_query("INSERT INTO Table2 (new_date, new_animals, new_gardens)
        VALUES ('$date', '$animals', '$gardens')");
    }

}

Answer 2

 $select_query1 = mysql_query("SELECT date, animals, gardens FROM Table1");
  $select_query2 = mysql_query("SELECT new_animals, new_gardens FROM Table2  
                                      WHERE new_date between date_sub(now(),INTERVAL 1 WEEK) and now()");

$row1 = mysql_fetch_array($select_query1); // This returns 4 rows
$row2 = mysql_fetch_array($select_query2); // This returs 4 rows

$animalsArray = array();
   while ($row2) {
  $animalsArray[ $row2['new_animals']."_".$row2['new_gardens'] ]=$row2["new_animals"];
   }

$insertValues = "";
while($row1){
   if( $animalsArray[ $row1['animals']."_".$row1['gardens'] ] == "" ){
        $animals = $row1['animals'];
        $gardens = $row1['gardens'];
        $date = $row1['date'];
      $insertValues .= "('$date', '$animals', '$gardens'),";
   }
}

$insertValues = substring( insertValues,0,-1); //delete last comma
$insert_query = "INSERT INTO Table2 (new_date, new_animals, new_gardens)VALUES ".$insertValues." ;
 mysql_query($insert_query);

this line     $animalsArray[ $row2['new_animals']."_".$row2['new_gardens'] ] = $row2["new_animals"];

en array this = $animalsArray [donkey_alabama] = 'donkey';

    if( $animalsArray[ $row1['animals']."_".$row1['gardens'] ] == "" ) {} , , ,

insertValues ​​- ()

PD: mysqli_