CODEWITHSUNDEEP
phpmysql
Mert METİN
Mert METİN

Reputation: 1288

Delete and Insert from database

I'm a little bit confused why it does not work.

What i want is that control db if for ex 2012-02-21 exist in DB delete it and insert again but it does not work why

my full code is that but else statement doesnt work :S

 $ga->requestAccountData();
$mysql = new mysql();
$mysql->connect();



    // $startDate = date("Y-m-d");
    $startDate = "2012-02-21";

     $dbResult = mysql_query("select * from profiles where profile_Date='".$startDate."'");
     $query =  mysql_num_rows($dbResult);
     if($query > 0)
     {

         mysql_query("delete from profiles where profile_Date='".$startDate."'");
         foreach ($ga->getResults() as $result) {


        $ga->requestReportData($result->getProfileId(),array('eventCategory','eventAction'),array('totalEvents'),$sort_metric=null,$filter='eventAction==InitPlayer',$start_date=$startDate,$end_date=$startDate);
        foreach($ga->getResults() as $result2)
        {
            echo $result;
            echo $result2->geteventCategory()."<br />";
            echo $result2->geteventAction();
            echo $result2->gettotalEvents();
            "<br />";
            "<br />";
            "<br />";

              $mysql->query("insert into profiles values(" . $result->getProfileId() . ",'" . $result . "','".$result2->geteventCategory()."','".$result2->geteventAction()."','".$result2->gettotalEvents()."','".$startDate."')");

         }
        }
     }
     else
     {
         foreach ($ga->getResults() as $result) {


        $ga->requestReportData($result->getProfileId(),array('eventCategory','eventAction'),array('totalEvents'),$sort_metric=null,$filter='eventAction==InitPlayer',$start_date=$startDate,$end_date=$startDate);
        foreach($ga->getResults() as $result2)
        {
            echo $result;
            echo $result2->geteventCategory()."<br />";
            echo $result2->geteventAction();
            echo $result2->gettotalEvents();
            "<br />";
            "<br />";
            "<br />";

              $mysql->query("insert into profiles values(" . $result->getProfileId() . ",'" . $result . "','".$result2->geteventCategory()."','".$result2->geteventAction()."','".$result2->gettotalEvents()."','".$startDate."')");

         }
        }
     }

Upvotes: 0

Views: 214

Answers (3)

apfelbox
apfelbox

Reputation: 2634

You can use MySQL's REPLACE functionality (docs). It basically works like INSERT, with the exception that if a value with the same key already exists in the database, at first the existing value will be removed and afterwards the new value will be inserted as completely new entry. If there is no existing value, it will behave like INSERT.

Watch out for the differences to UPDATE

The difference to UPDATE is hidden in the details:

  • since an existing value will actually be removed, all referenced foreign keys constraints will be removed too.
  • UPDATE will just update the values provided, with INSERT the other columns will get the default value (practical example: a field name timestamp_created with the default value of CURRENT_TIMESTAMP will be unchanged in an UPDATE statement, but will display the current time in a REPLACE statement)

Upvotes: 1

Hanan Ali
Hanan Ali

Reputation: 102

Why you need to first delete and then insert? you can use update query as well. Also what is the data type of the field profile_Date ? Is it set date or date and time?

Upvotes: 1

Stelian Matei
Stelian Matei

Reputation: 11623

You should use mysql_num_rows to count the results selected and not mysql_affected_rows which applies to queries altering data (insert, update, delete etc.)

$result = mysql_query("select * from profiles where profile_Date='".$startDate."'");
$count =  mysql_num_rows($result);
if($count > 0) { 
   ... 
}

http://php.net/manual/en/function.mysql-num-rows.php

Upvotes: 4

Related Questions