-1

My question is how to i saves images on the sql server directed to the same post? specifically, should i have a posts table with an id for each post, then an image table with a column for post id? If this is the case, how would i be able to add a row into the post table, obtain its id, then add rows in the image table referring to the post's id? any and all help would be appreciated.

Jesse Jacob '21
  • 1
  • 1
  • You pretty much nailed it as far as I'm concerned. Of course, there are other ways to do it but that's basically how I would set it up. PHP will have a function for you to retrieve the generated id after inserting the post, depending on which type of database you're using (mysql, sqlite, postgres, sql server, etc.). Check the manual pages to find out which one you need to use. – rickdenhaan Apr 14 '20 at 21:46
  • is mysql_insert_id() the right function. thank u so much – Jesse Jacob '21 Apr 14 '20 at 22:05
  • Close, the `mysql_*` functions no longer exist. If you're using MySQL as a database then use the `mysqli_*` functions (`mysqli_insert_id($db)`) or PDO (`$db->lastInsertId()`). – rickdenhaan Apr 16 '20 at 07:19

1 Answers1

0

Here is quick example:

Database structure

post
------
id (AUTO_INCREMENT)
title
content

post_image
------
id (AUTO_INCREMENT)
post_id (INDEX, FOREIGN KEY to post.id)
path

PHP (using mysqli)

$title = mysqli_real_escape_string($_POST['title']);
$content = mysqli_real_escape_string($_POST['content']);

mysqli_query("INSERT INTO post (title, content) VALUES ('{$title}', '{$content}')");

$postId = mysqli_insert_id();

$images = [ // Here you can process images from $_FILES
    "/images/post/{$postId}/image1.png",
    "/images/post/{$postId}/image2.png",
];

foreach ($images as $image) {
    $safePath = mysqli_real_escape_string($image);

    mysqli_query("INSERT INTO post_image (post_id, path) VALUES ({$postId}, '{$safePath}')");
}
Daniel Vítek
  • 185
  • 12
  • Nice, simple explanation! Could you please update it to use prepared statements instead? Escaping is [not enough](https://stackoverflow.com/q/1220182/1941241) to protect agains SQL injection. – rickdenhaan Apr 16 '20 at 07:02