Multiple Inserts Inner Join PDO

Asked

Viewed 272 times

-3

Hello,

I have in DB the tables categoria and imagens

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

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 inserting up to 5 images.

The title I want you to go to the category table, until here ok, the images should go to the table images, an id for each image should be created in that single Ubmit but with Inner Join (or similar) that makes it possible to insert the id of the created category along with the images.

In the table pictures the columns are like this:

id_imagem | categoria_ID | dir_imagem

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

The doubt is as in a single Ubmit insert the ids in the table images according to the amount of images in the form and the id of the created category, using the same form, fill the category column.

As hard as 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 still not clear to me

I appreciate help

php mysql pdo

  • Why are you denying my doubt? Can I rephrase or add more information

  • It was not I who denied it, but I understand that the question is a little confused. You are mixing more than one question in the question alone. Starting with relationship, which is an SQL problem, then there’s Submit, which is an HTML and PHP problem, and it talks about Ids that we don’t know where it comes from. There is a lot of information in the question, but there is no way to know what is important and what is not, nor what you were able to do and where exactly your doubt is. I would suggest simplifying doubt and solving the problem one step at a time (it’s not a criticism, I’m just pointing out what I saw as a reader).

  • Thanks @Bacco I tried to be clearer and make understand as a single question focused on php code with sql statements

  • The code is important, but more important than that is to explain well the result to be obtained. Try to think of what the first step is to solve your problem, and [Dit] the question to reflect this particular step. Once the step has passed, you can ask a new question with the next step. A tip is to put yourself in the place of those who are reading, and have no idea what you are doing, and will have to understand only by what is in the question :)

1 answer

0

From what I understand, your intention is to insert a record in the category table. Then use the registry code inserted during a loop (or something like that), which will insert the path to the image files in another table.

I’m imagining that your two tables probably have as primary key, a column with automatic numbering.

A quick option to do this is after the INSERT of the category, use the lastInsertId() to recover the ID of the last entered record. By using the category ID, you can use it to perform the 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(), is worth checking out.

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.