How can I optimize this code? Sending xml data to mysql

[mb1]

Hi,

 

I hope you can help. I am querying 2 xml files (ultimately there will be more than just 2), parsing them and then sending them to my sql db.

 

The way I have it currently, I am doing everything twice and 99% of the code is similar. There has to be a way to combine them someone with a loop.

 

Could you help?

 

Thank you,

 

Marine

 

<?php
  // set the XML file name as a PHP string
  $file = "http://api.foursquare.com/v1/venue?vid=1064544" ;
  // load the XML file
  $xml = @simplexml_load_file($file) or die ("no file loaded") ;

  // assign the listName element to a string
        $name   = $xml->name;
        $id = $xml->id;
        $checkins       = $xml->stats->checkins;
        $herenow = $xml->stats->herenow;
        $address = $xml->address;
        $crossstreet = $xml->crossstreet;
        $city = $xml->city;
        $userid = $xml->stats->mayor->user->id;
        $firstname = $xml->stats->mayor->user->firstname;
        $lastname = $xml->stats->mayor->user->lastname;
        $photo = $xml->stats->mayor->user->photo;
        $tips = $xml->tips->tip->text;
        $tipuserfirst = $xml->tips->tip->user->firstname;
        $tipuserlast = $xml->tips->tip->user->lastname;
        $tipuserid = $xml->tips->tip->user->id;
    echo '<div class="venue"><a href="http://foursquare.com/venue/'.$id.'" target="_blank"><h2 class="name">'.$name."</h2></a>";
        echo '<div class="address">'.$address.' '.$crossstreet.' '.$city.'</div>';
        echo '<div class="herenow">'.$herenow.' people here right now.</div>';
        echo '<div class="checkins">'.$checkins.' total checkins. </div>';
        echo '<div class="mayor"><a href="http://foursquare.com/user/-'.$userid.'">'.$firstname.' '.$lastname.'</a></div>';
        echo '</div>';
       
        require_once('connectvars.php');
        $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die( "Unable to select database");
       
        $query ="INSERT INTO `mb_foursquare`.`venues` (`id`, `v_id`, `v_name`, `v_address`, `v_mayor`, `v_mayor_url`) VALUES (NULL, '$id', '$name', '$address', '$firstname', '$userid')";
       
        mysqli_query($dbc, $query);            
        echo 'that worked!';   
        mysqli_close($dbc);

?>



<?php
  // set the XML file name as a PHP string
  $file = "http://api.foursquare.com/v1/venue?vid=1633541" ;
  // load the XML file
  $xml = @simplexml_load_file($file) or die ("no file loaded") ;

  // assign the listName element to a string
        $name   = $xml->name;
        $id = $xml->id;
        $checkins       = $xml->stats->checkins;
        $herenow = $xml->stats->herenow;
        $address = $xml->address;
        $crossstreet = $xml->crossstreet;
        $city = $xml->city;
        $userid = $xml->stats->mayor->user->id;
        $firstname = $xml->stats->mayor->user->firstname;
        $lastname = $xml->stats->mayor->user->lastname;
        $photo = $xml->stats->mayor->user->photo;
        $tips = $xml->tips->tip->text;
        $tipuserfirst = $xml->tips->tip->user->firstname;
        $tipuserlast = $xml->tips->tip->user->lastname;
        $tipuserid = $xml->tips->tip->user->id;
    echo '<div class="venue"><a href="http://foursquare.com/venue/'.$id.'" target="_blank"><h2 class="name">'.$name."</h2></a>";
        echo '<div class="address">'.$address.' '.$crossstreet.' '.$city.'</div>';
        echo '<div class="herenow">'.$herenow.' people here right now.</div>';
        echo '<div class="checkins">'.$checkins.' total checkins. </div>';
        echo '<div class="mayor"><a href="http://foursquare.com/user/-'.$userid.'">'.$firstname.' '.$lastname.'</a></div>';
        echo '</div>';
       
        require_once('connectvars.php');
        $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die( "Unable to select database");
       
        $query ="INSERT INTO `mb_foursquare`.`venues` (`id`, `v_id`, `v_name`, `v_address`, `v_mayor`, `v_mayor_url`) VALUES (NULL, '$id', '$name', '$address', '$firstname', '$userid')";
       
        mysqli_query($dbc, $query);            
        echo 'that worked!';   
        mysqli_close($dbc);

?>

[PFMaBiSmAd]

Perhaps if you would be so kind to state or point out what exactly is different?

[PFMaBiSmAd]

And the answer is the URL and specifically just the vid= number.

 

You would simply put the values into an array, iterate over the array using a foreach() loop and either just put the body of the code inside of the loop, or you could put the body of the code into a user function definition and call the function inside of the loop.

 

You should put the code that gets your database details and makes the mysqli connection before the loop and either put the mysqli_close() after the end of the loop or just let php close the connection when the script ends.

[mb1]

Thank you for your reply.

 

I understand about putting the core of the code in a function. That makes sense. I can't believe I did not think of that...!!

Regarding the loop and the array, do you mind being more specific, I am not sure how this would look like.

 

Thank you,

 

Marine

[PFMaBiSmAd]

<?php
$array = array();
$array[] = "1633541";
$array[] = "1064544";

require_once('connectvars.php');
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die( "Unable to select database");

foreach($array as $value){
// load the XML file
$file = "http://api.foursquare.com/v1/venue?vid=$value";
$xml = @simplexml_load_file($file) or die ("no file loaded");
// assign the listName element to a string
$name = $xml->name;
$id = $xml->id;
$checkins = $xml->stats->checkins;
$herenow = $xml->stats->herenow;
$address = $xml->address;
$crossstreet = $xml->crossstreet;
$city = $xml->city;
$userid = $xml->stats->mayor->user->id;
$firstname = $xml->stats->mayor->user->firstname;
$lastname = $xml->stats->mayor->user->lastname;
$photo = $xml->stats->mayor->user->photo;
$tips = $xml->tips->tip->text;
$tipuserfirst = $xml->tips->tip->user->firstname;
$tipuserlast = $xml->tips->tip->user->lastname;
$tipuserid = $xml->tips->tip->user->id;
echo '<div class="venue"><a href="http://foursquare.com/venue/'.$id.'" target="_blank"><h2 class="name">'.$name."</h2></a>";
echo '<div class="address">'.$address.' '.$crossstreet.' '.$city.'</div>';
echo '<div class="herenow">'.$herenow.' people here right now.</div>';
echo '<div class="checkins">'.$checkins.' total checkins. </div>';
echo '<div class="mayor"><a href="http://foursquare.com/user/-'.$userid.'">'.$firstname.' '.$lastname.'</a></div>';
echo '</div>';
$query ="INSERT INTO `mb_foursquare`.`venues` (`id`, `v_id`,`v_name`,`v_address`,`v_mayor`,`v_mayor_url`) VALUES (NULL,'$id','$name','$address','$firstname','$userid')";
mysqli_query($dbc, $query);
} // end of loop
echo 'that worked!';
?>

[mb1]

Oh that super neat. Awesome. Thank you so much!!!