Multiple inner join PDO inserts

Question

Multiple inner join PDO inserts

Navigation

#1 by (0 votes)

-3

Hello,

I have in the DB the tables categoria and imagens

Currently the function (in PHP) to insert in the table category is similar to this (only title itself):

public function insere($ttitulo)
{


try
    {
        $stmt = $this->db->prepare("INSERT INTO categoria (titulo) VALUES(:titulo)");
        $stmt->bindparam(":titulo",$ttitulo);                   
        $stmt->execute();
        return true;

    }
    catch(PDOException $e)
    {
        echo $e->getMessage();  
        return false;
    }

}

In html, I have a form with title and possibility of insertion of up to 5 images.

The title I want to go to the category table, so far okay, the images should go to the images table, an id for each image should be created in this single submit but with inner join (or similar) that makes it possible to insert the id of the category created together with the images.

In the images table the columns look like this:

id_imagem | categoria_ID | dir_imagem

Image dir is image itself that will come from the form (I can already insert in the form the url of each image)

The question is how in a single submit insert the ids into the images table according to the amount of images in the form and the created category id, using the same form, fill the column category_ID in the images table.

The most I could try:

$stmt = $this->db->prepare("INSERT INTO categoria (titulo) VALUES(:titulo)");

$stmt = $this->db->prepare("SELECT id_imagens FROM imagens WHERE id_categoria = id_imagens ");

But it's not clear to me yet

Thank you for your help

php mysql pdo

asked by anonymous 07.03.2016 / 22:06

1 answer

How to put a datepicker in an Android application whose page is being developed in PHP Java Connection MySql and FTP

score 0 · Answer 1

As I understand it, your intent is to insert a record into the category table. Then use the registry key inserted during a loop (or something like that), which will insert the path to the image files in another table.

I'm wondering if your two tables probably have a primary key, an auto-numbered column.

A quick option to do this is after INSERT of the category, use lastInsertId () to retrieve the ID of the last inserted record. Having the category ID in hand, you can use it to make INSERT of the images.

$id_categoria = 0; // Variável para armazenar o ID da categoria.

$stmt = $this->db->prepare("INSERT INTO categoria (titulo) VALUES(:titulo)");
$stmt->bindparam(":titulo",$ttitulo);                   

if ($stmt->execute()) {
    $id_categoria = (int) $this->db->lastInsertId();
}

// Com o valor de $id_categoria, basta realizar o INSERT das imagens.
$caminho_imagem = "/caminho/para/a/imagem.jpg";
$sql_imagem = "INSERT INTO imagens (categoria_ID, dir_imagem)  
                   VALUES(:categoria, :dir_imagem)"; 

$stmt = $this->db->prepare($sql_imagem);
$stmt->bindparam(":categoria",$id_categoria, PDO::PARAM_INT);
$stmt->bindparam(":dir_imagem",$caminho_imagem, PDO::PARAM_STR);

if ($stmt->execute()) {
    // Registro inserido com sucesso.
}

In the documentation you have more details about the lastInsertId () , it's worth checking out.