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

[glendango]

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 ."')";

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

print_r($sql2);

echo"<br><br>";

 

?>

    

Edited October 18, 2018 by glendango

[Barand]

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
 -----------
     property_id int auto_increment   (PK)
     property_name varchar(100)
     etc
 
 
 property_image
 --------------
     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
endforeach

 

[glendango]

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. 

[Barand]

The pseudocode is as simple as it gets
 

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

foreach(pictures){
    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.

[glendango]

'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. 

[glendango]

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 ."')";

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

[Barand]

If you don't execute the property insert query, how the hell do you expect mysqli_insert_id() to return the id of the last inserted property record?

As for you loop containing a single picture that's something I can't help with. Check your data. I can't do that for you.

[glendango]

fair one ...  will see what happens when use the main db.