0

I try to parse a xml file and store each node to a mysql db. The problem is that it store the data in the first loop and returns error in the second time. If I refresh the page it stores the rest data as well.

I try to understand why does it behave in a such way

<?php

try{
    $records = simplexml_load_file("file.xml");
} catch (Exception $e){
    echo "xml problem";
}


#replace loclhost,user, pass, dbname
$db = mysqli_connect("localhost", "root", "", "project") or die("could not connect:" . mysqli_connect_error());

foreach($records->record as $rec){
    $species_location_x = $rec->species_lang;
    $species_location_y = $rec->species_long;
    $species_abundance = (string)$rec->abundance;
    $species_text = (string)$rec->text;
    $species_scene_photo = (string)$rec->space_photo;
    $species_photo_loc = (string)$rec->specimen_photo;

    $site_name = (string)$rec->site->sitename;
    $site_location = (string)$rec->site->sitelocation;
    $site_description = (string)$rec->site->sitedescription;

    $user_name = (string)$rec->user->lastname;
    $user_phone = (string)$rec->user->namephone;
    $user_email = (string)$rec->user->useremail;

    $species_name = (string)$rec->species->species_name;


#print_r($species_location_x." ".$species_location_y." ".$species_abundance." ".$species_text." ".$species_scene_photo." ".$species_photo_loc);
#print_r($site_name." ".$site_location." ".$site_description);
#print_r($user_name." ".$user_phone." ".$user_email);
#print_r($species_name);

    $multiq = "INSERT INTO users (user_name, user_phone, user_email)
               VALUES ('$user_name', '$user_phone', '$user_email');" ;

    $multiq .= "INSERT INTO records (user_name, record_location, record_abundance, record_text, record_scene_photo_location, record_specimen_photo_location )
                VALUES ( '$user_name',
                  GeomFromText('point($species_location_x $species_location_y)'),
                   '$species_abundance',
                   '$species_text',
                   '$species_scene_photo',
                   '$species_photo_loc');" ;

     $multiq .= "INSERT INTO species (species_name, records_id )
                 VALUES ( '$species_name', (select record_id from records where user_name = '$user_name' and record_abundance = '$species_abundance' and record_text = '$species_text'));" ;

     $multiq .= "INSERT INTO sites (site_name, site_location, site_description, species_id)
                 VALUES ('$site_name', '$site_location', '$site_description', (select species_id from species where species_name='$species_name'));";


    if(mysqli_multi_query($db, $multiq)){
        echo "records added successfully\n";
        }
        else{
    echo "problem";
    }
}
mysqli_close($db);
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
b10n1k
  • 567
  • 5
  • 21
  • 2
    *"the php code is here"* - So, why didn't you post it here ^ – Funk Forty Niner Jan 23 '15 at 17:46
  • 2
    The error message would be helpful. – Siguza Jan 23 '15 at 17:46
  • it is a lit biig and i avoid to post it here – b10n1k Jan 23 '15 at 17:51
  • @Sigura the first time it prints "records added successfully" and in the second time "problem". – b10n1k Jan 23 '15 at 17:53
  • *"it is a lit biig and i avoid to post it here"* - That's not big. You should see what some of the others post. *This is nuthin'* ;-) – Funk Forty Niner Jan 23 '15 at 17:55
  • So check what the error is exactly, mysqli offers various ways to get detailed error messages. And injecting texts in your database like that, you are probably sql-injecting yourself. – jeroen Jan 23 '15 at 18:22
  • I am not sure it was the proper way but the problem was solved. I put the connection inside the loop. I know that this might be a bad solution and my code needs a lot of improvement. I suppose the mysqli_multi_query was closing the connection. If any one knows I would appreciate to learn more. Thanks anyone for the help. – b10n1k Jan 23 '15 at 18:41

0 Answers0