Jump to content

xml / mysql / foreign keys / many to many tables?


Recommended Posts

Happy Thursday !   I will try to ruin it for you. 

I have an xml file with products in it and i use xpath to get out what i need from the file.

Nested in each product are all the elements =  $variables below.   i need the pictures of which there may be upto 20 in each product to go into a seperate mysql table because its relational. but have the same id as the property table. 

i have managed to get the first picture to behave this way by using Last_insert_id .   What i cant do is get the remaining pictures->picture->filename  (nested) ; to insert into the table with the corresponding property id (which i auto increment) 

the filename holds the text url (not a blob)  which i store and will echo once i start doing my select statements. 


Hope this is clear enough....  thx


$xml = simplexml_load_file('propertiesshort.xml') or die("can not find it");


foreach($xml->branches->branch->properties->property as $row){

 $instructedDate = (string)$row->instructedDate;
$price_text = (string)$row->numeric_price;
$bedrooms = (int)$row->bedrooms;
$priority =(string)$row->priority;
$advert_heading = (string)$row->advert_heading;
$brochure = (string)$row->brochure;

foreach($xml->branches->branch->properties->property->pictures as $rows ){
$pictures = (string)$rows->picture->filename;

$sql = "INSERT INTO properties( instructedDate, price_text, bedrooms, priority,  advert_heading, brochure) VALUES ('". $instructedDate ."','". $price_text ."','". $bedrooms ."','". $priority ."','". $advert_heading ."','". $brochure ."')";


$sql2 = "INSERT INTO pictures(property_id, filename) VALUES (LAST_INSERT_ID(),'". $pictures ."')";







Edited by glendango
Link to comment
Share on other sites

The "property_id" cannot be the primary key (auto_incremented) in the images table as there will be many records with the same property_id. Sould be something like

     property_id int auto_increment   (PK)
     property_name varchar(100)
     prop_image_id int auto_increment (PK)
     property_id int                  (FK)
     image_name varchar(255)

Also, do not insert the LAST_INSERT_ID() into the image records - you want the insert id of the property record.

INSERT proprties record
store last_insert_id

foreach (image)
       INSERT with stored insert_id


Link to comment
Share on other sites

maybe that was lost in translation.. yes i have a primary key for every image. =    picture_id   in my case.  So i have 3 columns in the pictures table. 

So,  how to get many pictures belonging to each product into the table with the same property_id  .  Any chance you could could edit my example..cant follow your foreach example. 

Link to comment
Share on other sites

The pseudocode is as simple as it gets

INSERT your property record
$prop_id = $conn->lastInsertId();       // store id of new property

    insert into pictures(property_id,  filename) VALUES ( $prop_id, '$filename' )";

The main point is you cannot use the LAST_INSERT_ID() SQL function when inserting images. The first will be OK as the last record inserted was the property record. After that that the LAST_INSRT_ID() is the id of the previous image inserted.

Do you realise that the code you posted does not execute any queries?

You should be using prepared statements and not putting values directly into the queries.

Link to comment
Share on other sites

'Do you realise that the code you posted does not execute any queries?'

ha yes,,i print onto screen .. otherwise i would be up to about 5000 unique id's if kept sending it to db while i sort this code out. 


i cant get my head round your example! where does it go in relation to my code. come on...iam so close. 

Link to comment
Share on other sites

this is how it looks now but i dont get the last id i get (0) in the pictures and they still dont loop so i get 1 picture.


$sql = "INSERT INTO properties( instructedDate, price_text, bedrooms, priority,  advert_heading, brochure) VALUES ('". $instructedDate ."','". $price_text ."','". $bedrooms ."','". $priority ."','". $advert_heading ."','". $brochure ."')";

$prop_id = mysqli_insert_id($conn);

foreach ($xml->branches->branch->properties->property->pictures->picture as $rows)
$pictures = (string)$rows->filename;
//$sql2 = "INSERT INTO pictures(property_id, filename) VALUES (LAST_INSERT_ID(),'". $pictures ."')";

$sql2 = "INSERT INTO pictures( filename) VALUES ('". $prop_id ."' , '". $pictures ."')";

echo"<br><br>", print_r($sql2);echo"<br><br>";

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.