New to INNER join

Question

I've never used Inner join everything is something new to learn I guess.

I will explain my problem: I've 2 tables into mysql, 1 called category and the second is post, all category names/rows are ofc into the table category and the posts related to that category are in post I make an example of a category name and a post.

Category name I use for ex: Test

A post related to the category Test have the ID count 1 so the link will be

/category.php?nameID=Test&id=1 this url just shows the content from row ID 1 of the table which is called post now when I replace Test in the url to ex. Hello /category.php?nameID=Hello&id=1 I still receive the content of ID 1 from the table post which I don't want because the post count ID 1 isn't related to the category of Hello I want to receive an error like This post doesn't exist in this category.

Here below is mime code of &id=: Have now this error Unknown column 'Test' in 'where clause'

<?php
    if (isset($_GET['id']))
{
        $naamID = mysql_real_escape_string($_GET['nameID']);
        $id     = mysql_real_escape_string($_GET['id']);
        $idnext = $id + 1;
        $goo    = mysql_query("SELECT * FROM category, post WHERE category.name = post.cat = ".$naamID."") or die(mysql_error());
        $gnn    = mysql_query("SELECT * FROM post WHERE id= $id ") or die(mysql_error());
        $gnnn   = mysql_fetch_assoc($gnn);
        $gooo   = mysql_fetch_assoc($goo);
        if($gooo['youtube'])
        {
                $fullurl1 = $gooo['youtube'];
                $videoid1=substr($fullurl1,-11);       
        ?>
                <h1> <?php echo htmlspecialchars($gooo["title"]); ?> </h1><br />
                <p>
                <a href="/editpost.php?id=<?php echo htmlspecialchars($gooo['id']);?>"><i>Edit this post</i></a><br />
                <iframe width="560" height="315" src="//www.youtube.com/embed/<?php echo htmlspecialchars($videoid1);?>" frameborder="0" allowfullscreen></iframe><br />
                </p>
        <?php }if($gooo['pic']){ ?>
                <p>
                <h1><?php echo htmlspecialchars($gooo["title"]); ?></h1><br />
                <a href="/editpost.php?id=<?php echo htmlspecialchars($gooo['id']);?>"><i>Edit this post</i></a><br />
                <img src="<?php echo htmlspecialchars($gooo["pic"]);?>" style="max-height: auto; max-width: 600px;"/><br>
                </p>
        <?php }
// END OF SHOWING CONTENT PAGE
} else {?>

[use prepared statements and parametrized queries](http://stackoverflow.com/a/60496/2094094) — Alexander, Apr 07 '14 at 16:36
this is invalid `category.name = post.cat = ".$naamID.""` Should be `category.name = post.cat AND post.cat = '.$naamID.'`. (notice the single quotes) — crthompson, Apr 07 '14 at 16:43
Also, `SELECT * FROM category, post WHERE category.name = post.cat` is the same as `SELECT * FROM category inner join post ON category.name = post.cat` — crthompson, Apr 07 '14 at 16:45
@GolezTrol, the `TEST` comes from the data, not the query. The interpolated text is being taken for a column, not a value. — crthompson, Apr 07 '14 at 16:47

serakfalcon · Accepted Answer · 2014-04-07T17:02:37.580

2

There are multiple problems with this code, first, as alexander is pointing at, your data is not escaped.

You are passing Test directly into the query, which means that the query is not treating it as a string (it is expecting a column name), you can fix that by adding quotes around the variable ('$id'). Even though you've escaped it, the query needs quotes to identify it as a string.

If you wanted an inner join, the syntax would look like:

"SELECT * FROM category INNER JOIN post ON category.name = post.cat WHERE post.cat = '" .$naamID "'"

This essentially tells the DB, I want all the columns from category AND post, treat category.name as the same thing as post.cat, and only retrieve rows where post.cat = whatever. (note, if you put category.name instead it would do the same thing, since the inner join merges the two).

edited Apr 07 '14 at 17:02

answered Apr 07 '14 at 16:49

serakfalcon

3,501
1
22
33

Thank you! Finaly after putting many hours to solve this, I'm getting now a blanco page, Is there a way to get that? Again thank you – user3490572 Apr 07 '14 at 17:17
I'm not sure what you mean, but code that I would look at for the page being blank is the or die() after the mysql queries. It's possible that the die() command is being evaluated, but there is no sql error, leaving you with a blank page. – serakfalcon Apr 09 '14 at 15:32

New to INNER join

1 Answers1