php - Solution to database insert and delete -

i want 1 suggestion or way can handle situation

i having module in used crawl website , current movies running in nearby cinema halls. have 2 tables 1) 1 movies , other cinemas reffering movies inserted in movie table first.

for have set file on every morning cron job. in code first delete data in both tables , insert new data . loose rating scores given end user fr particula movie.

to overcome situation , thought of solution

i created new query

insert jos_movie (movie_name, language, cast,movie_release,director,rating,rating_count,movie_ids) select * (select 'test','null','yahoo','dec 21, 2012','himmat',250,230,'43677') tmp not exists (     select movie_name jos_movie movie_name = 'test') 

similary created same approach cinema table also.

by check , not overwrite movie in table . there problme in approach. if cinema owner did removed shows particular movie eg 'test' . above query wont delete . remain there .

sorry subject line not able think subject line issue.

so how can achieve result existing movie wont update if in table , gets deleted if not in crawl result array of script.

here table results

this movie table result

this cinema table

here code using same.

$con=mysql_connect('localhost','test','test'); mysql_select_db('test',$con);  // use curl rss feed php string variable. $ch = curl_init(); curl_setopt($ch, curlopt_url,'myrsslink.xml'); curl_setopt($ch, curlopt_header, false); curl_setopt($ch, curlopt_returntransfer, true); $xml = curl_exec($ch); curl_close($ch);  $arrdata = array(); // create array of item elements xml feed. $news_items = element_set('item', $xml); $del_movie = "delete jos_movie"; mysql_query($del_movie);  $del_cinema = "delete jos_cinema"; mysql_query($del_cinema);  foreach($news_items $item) {     $title = value_in('title', $item);     $url = value_in('link', $item);     $cast = value_in('description', $item);     //curl_setopt($ch, curlopt_url,$url);     //curl_setopt($ch, curlopt_header, false);     //curl_setopt($ch, curlopt_returntransfer, true);     //$html = curl_exec($ch);     $arrtitle = explode('-',$title);     $html = file_get_html($url);     $htmlshowtime = '';      // find span tags class=gb1 movitimes movitmngbox     foreach($html->find('ul[style=line-height:2em;]') $e)         $htmlshowtime = $e->plaintext;      $movie_name = $arrtitle[0];      $apikey = '30f44b6ef9472d414e50d2acaa058b60';     $url = sprintf('http://api.themoviedb.org/2.1/movie.search/en/xml/%s/"%s"',$apikey,rawurlencode(trim($movie_name)));      //$xml = simplexml_load_file("http://api.themoviedb.org/2.1/movie.search/en/xml/accd3ddbbae37c0315fb5c8e19b815a5/"$movie_name"");     $xml = simplexml_load_file($url);     $movies = $xml->movies->movie;    foreach ($movies $movie){         $arrmovie_id = $movie->id;     }     $arrstr = explode(':',$htmlshowtime);     $release = substr($arrstr[3],0,strlen($arrstr[3])-8);     $director = substr($arrstr[5],0,strlen($arrstr[5])-11);       $sql_movie = "insert jos_movie(movie_name,language,cast,movie_release,director,rating,rating_count,movie_ids)values('$movie_name','null','$cast','$release','$director',250,230,'$arrmovie_id')";     //echo $sql.'<br>';    // echo $sql_movie;      mysql_query($sql_movie);      $sqlcount = 'select max(id) jos_movie' or die("cannot select db");     $data = mysql_query($sqlcount);     echo $data;     print_r($data);     $result = mysql_fetch_array($data);     $id = $result[0];     echo '<br>'.$id.'<br>';       //$id = mysql_insert_id();     //echo $id;          // find span tags class=gb1     foreach($html->find('div.movitmngbox') $e){         $tagtitle =  $e->find('a',0);         $tagtime  = $e->find('div.movitimes',0);         $name = $tagtitle->title;         $time = $tagtime->innertext;      $trimname = '';     $temname = strtolower(str_replace(' ','',$name));      if(strpos($temname,'indraaudi1') !== false)       $trimname = 'indra audi 1' ,  $cinemaid = '1' , $long='32.726602' , $lat='74.857026';     elseif(strpos($temname,'indraaudi2') !== false)      $trimname = 'indra audi 2' , $cinemaid = '2'and $long='32.726602' , $lat='74.857026';     elseif(strpos($temname,'indraaudi3') !== false)       $trimname = 'indra audi 3'and $cinemaid = '3' , $long='32.726602' , $lat='74.857026';     elseif(strpos($temname,'apsra') !== false)       $trimname = 'apsra' , $cinemaid = '4' , $long='32.700314' , $lat='74.858023';     else{         $trimname = trim(substr($name,18,strlen($name))) , $cinemaid = '5' , $long='32.7300' , $lat='74.8700' ;     }          //echo $tagtime->innertext.'<br/>';         $sql = "insert jos_cinema(cinema_name,show_time,movie_id,cinemaid,logitude,latitude)values('$trimname','$time',$id,$cinemaid,$long,$lat)";         //echo $sql.'<br/>';         mysql_query($sql);         //$arrtem = array($tagtitle->title,$tagtime->innertext);      }  }//end rss feed loop  ?> 

please not inserting default value of rating movie.

thanks

first of create db structure:-

1. create id uniquely define each movie in tables.

2. normalize table structure. means keep movie details , active movie lists in separate tables(active_movies). follow below link more info normalization in mysql

3. instead of updating complete row again , again same movie, update active movie table.

4. keep backup of data means movies, can in future, show list , details of movies released in year or including particular cast, or rating above 4 etc.

a db structure make code lot simpler , implementable. few more helpful links http://searchbusinessintelligence.techtarget.in/tutorial/database-normalization-in-mysql-four-quick-and-easy-steps