user2519032 user2519032 - 4 months ago 8
SQL Question

Insert data into separate tables related by foreign keys

I have a database with two tables:

posts: id(primary key, autoincrement), title_bg, title_en, body_bg, body_en, status, created, updated

postimage: id(primary key, auto increment), post_id, name

When I'm not using a foreign key, the form with multiple elements is working fine. It fills all the details for the post into the posts table and the multiple images are uploading into the postimage table, but they're not related, so the post_id field shows 0 value.

When I set the foreign key on phpMyAdmin with this query:

ALTER TABLE `postimage` ADD FOREIGN KEY ( `post_id` ) REFERENCES `database_name`.`posts` ( `id` ) ON DELETE RESTRICT ON UPDATE RESTRICT ;


and when I create a new post, all the values are saved into the posts table, except the images into the second table. The postimage table is empty.

Here's my code:

<?php
if(isset($_POST['submit'])) {
$title_bg = $_POST['title_bg'];
$title_en = $_POST['title_en'];
$body_bg = $_POST['body_bg'];
$body_en = $_POST['body_en'];

if(isset($_FILES['image'])) {
foreach($_FILES['image']['name'] as $key => $name) {
$image_tmp = $_FILES['image']['tmp_name'][$key];

move_uploaded_file($image_tmp, '../uploads/' . $name);

$query = "INSERT INTO postimage(name) ";
$query .= "VALUES('$name')";

$upload_images = mysqli_query($connection, $query);
}
}

$status = $_POST['status'];


$query = "INSERT INTO posts(title_bg, title_en, body_bg, body_en, status, created) ";
$query .= "VALUES('$title_bg', '$title_en', '$body_bg', '$body_en', '$status', now())";

$create_post = mysqli_query($connection, $query);

header("Location: posts.php");
}
?>
<form action="" method="post" enctype="multipart/form-data">
<div class="form-item">
<label for="title_bg">Post title BG</label>
<input type="text" name="title_bg">
</div>

<div class="form-item">
<label for="title_en">Post title EN</label>
<input type="text" name="title_en">
</div>
<div class="form-item">
<label for="body_bg">Post body BG</label>
<textarea id="editor" name="body_bg" rows="10" cols="30"></textarea>
</div>

<div class="form-item">
<label for="body_en">Post body EN</label>
<textarea id="editor2" name="body_en" rows="10" cols="30"></textarea>
</div>

<div class="form-item">
<label for="image">Image</label>
<input type="file" name="image[]" multiple>
</div>

<div class="form-item">
<label for="status">Post status</label>
<select name="status">
<option value="published">published</option>
<option value="draft">draft</option>
</select>
</div>

<div class="form-item">
<input type="submit" class="form-submit" name="submit" value="Submit">
</div>
</form>


I've also created a two new tables as a test:

teachers: id, name, content_area, room

students: id, name, homeroom_teacher

When I set the foreign key on students field homeroom_teacher and insert the data manually from phpMyAdmin, they become related and the id on students table becomes clickable and it shows the relation with the teacher. So manually it's working great and the problem is in the PHP code.

What query do I need to change, so to make the connection with post id from the posts table and post_id from the postimage table?

I know that I'm missing the id from the $_FILES query, but I don't know how to get it, because it's already automatic auto increment field.

Thanks.

Answer
<?php

if(isset($_POST['status'])) {
  $status = $_POST['status'];
}

if(isset($_POST['submit'])) {
    $title_bg = $_POST['title_bg'];
    $title_en = $_POST['title_en']; 
    $body_bg = $_POST['body_bg'];
    $body_en = $_POST['body_en'];

$connection =  new mysqli("localhost", "USER_XY", "PASSWD","DB"); 

if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  die ("<h1>can't use Database !</h1>");
  exit();
}
/* change character set to utf8 */
if (!$connection->set_charset("utf8")) {
printf("Error while loading 'character set utf8' : %s\n", $connection->error);
die();
} 

/**
 * First save the Post
 **/
    $query = "INSERT INTO posts(title_bg, title_en, body_bg, body_en, status, created) ";
    $query .= "VALUES('$title_bg', '$title_en', '$body_bg', '$body_en', '$status', now())";

      $result=$connection->query($query);
      // verify results
      if(!$result) {
        $message  = "ERROR SAVING POST : ".$connection->error . "\n";
        $connection->close();
        echo ($message);
        return false;
      }   

/**
 * get the last inster id of the Post
 **/        
    $post_id = $connection->insert_id;
    echo "Post id=".$post_id ."<br>\n";
    if(isset($_FILES['image'])) {
        foreach($_FILES['image']['name'] as $key => $name) {
            $image_tmp = $_FILES['image']['tmp_name'][$key];

            move_uploaded_file($image_tmp, './uploads/' . $name);
/**
 * now insert the image with the post_id
 **/                
    $query = "INSERT INTO `postimage` (`id`, `post_id`, `name`) ";
    $query .= "VALUES (NULL, '".$post_id."', '".$name."');";

                      $result=$connection->query($query);
      // verify results
      if(!$result) {
        $message  = "ERROR INSERT IMAGE : ".$connection->error . "\n";
        $connection->close();
        echo ($message);
        return false;
      } 
        }    
    }
    header("Location: upload_posts.php");  
}
?>
<form action="upload_posts.php" method="post" enctype="multipart/form-data">
    <div class="form-item">
        <label for="title_bg">Post title BG</label>
        <input type="text" name="title_bg">
    </div>

    <div class="form-item">
        <label for="title_en">Post title EN</label>
        <input type="text" name="title_en">
    </div>    
    <div class="form-item">
        <label for="body_bg">Post body BG</label>
        <textarea id="editor" name="body_bg" rows="10" cols="30"></textarea>
    </div>    

    <div class="form-item">
        <label for="body_en">Post body EN</label>
        <textarea id="editor2" name="body_en" rows="10" cols="30"></textarea>
    </div>    

    <div class="form-item">
        <label for="image">Image</label>
        <input type="file" name="image[]" multiple>
    </div>

    <div class="form-item">
        <label for="status">Post status</label>
        <select name="status">
            <option value="published">published</option>
            <option value="draft">draft</option>
        </select>
    </div>

    <div class="form-item">
        <input type="submit" class="form-submit" name="submit" value="Submit">
    </div>    
</form>
Comments