Inner Join - return by name id in column of another table

Question

I have two tables in DB:

table category:

id_categoria | nome_categoria
   1             camisetas
   2             estrelas

table post:

id_post | título | resumo | conteúdo | categoria_id
    1      teste    lorem     ipsum         2
    2      test2    lorem     ipsum         1

If I access the url: www.exemplo.com/camisetas

camisetas is the variable $categoria because of the rule in . htaccess

I want to access all the data in the column categoria_id table posts by name and not by id:

$query = "SELECT * FROM post where ".$categoria." ";

What instruction Inner Join use in this case? Or better how do I add Inner Join to that select?

I appreciate help

---

UPDATING

For the select I’m doing so:

public function dataview($query)
    {

    $query = "SELECT * FROM post 
              JOIN categoria ON categoria.id_categoria = post.categoria_id 
              WHERE categoria.nome_categoria = '".$categoria."'"; 


        $stmt = $this->db->prepare($query);
        $stmt->execute();

        if($stmt->rowCount()>0)
        {
            while($row=$stmt->fetch(PDO::FETCH_ASSOC))
            {

                echo // aqui as linhas que quero
            }
        }
        else
        {
                echo // aqui o que for necessario
        }

    }

For the Insert so:

public function add($titulo, $etc)
{
try {
        $stmt = $this->db->prepare("INSERT INTO post (o_titulo, o_etc)
                                           VALUES(:o_titulo, :o_etc)");
        $stmt->bindParam(":o_titulo",$titulo);
        $stmt->bindParam(":o_etc",$etc);  
        $stmt->execute();
        return true;
    }
    catch(PDOException $e) {
        echo $e->getMessage();    
        return false;
    }

In htaccess I also determined the number of characters and type, mostly letters (no numbers and characters), within each part of the url /

This way it can be seen?

Answer 1

I believe that what you’re looking for would go something like this:

SELECT * FROM post 
       JOIN categoria ON categoria.id_categoria = post.categoria_id 
WHERE id_post = 'id_do seu post'

You see, I’m your example:

$query = "SELECT * FROM post where ".$categoria." ";

Do you understand that $categoria executes the condition of your query, this is extremely vulnerable to sql Injection attacks.

I recommend doing some research on and how to improve this.

Anyway the query I presented to you makes the table JOIN post with the categoria, for id_categoria da categoria with categoria_id da post, just change the WHERE class condition of your query to the desired search condition in the post table.