Inner Join - return by id column name of another table

Question

Inner Join - return by id column name of another table

Navigation

#1 by (0 votes)

2

I have two tables in the DB:

category table:

id_categoria | nome_categoria
   1             camisetas
   2             estrelas

post table:

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 .htaccess rule

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

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

What inner join statement use in this case? Or rather how do I add the inner join in that select?

Thank you for your help

UPDATING

For the select I'm doing this:

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
        }

    }

To insert it like this:

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, as for type most only letters (without numbers and characters) within each part of url /

Is this a way to go?

php mysql

asked by anonymous 11.03.2016 / 01:57

1 answer

Dynamic allocation problem - Targeting failure Regarding jQuery functions, is it risky to use them in inline elements?

score 0 · Accepted Answer

I think what you're looking for would look something like this:

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

Well, I'm your example:

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

You understand that $categoria executes the conditional of your query, this is extremely vulnerable to sql injection attacks.

I recommend giving a search on and how to improve it.

In any case, the query I presented to you makes the JOIN of the post table with the categoria , the id_categoria da categoria with categoria_id da post , just change the conditional of the WHERE clause of your query to the search condition desired in the post table.