1

I have the following 3 tables in my database.

Table: Images
Columns: Image_ID, Title, Description, Author, Date, Path, Rating

Table: Tags
Columns: Tag_ID, Title

Table: TagsConnection
Columns: Image_ID, Tag_ID

In order to get all the images that correspond to a certain tag, I execute this code.

<?php

    $tagName = $_SERVER['QUERY_STRING'];
    $realTagName = substr($tagName, 1);
    $realestTagName = str_replace('%20', ' ', $realTagName);

    $sql = "SELECT * FROM tags WHERE tag='$realestTagName'";
    $result = mysqli_query($conn, $sql);
    $getResult = mysqli_fetch_assoc($result);
    $tagId = $getResult['id'];

    $sql2 = "SELECT * FROM tagsconnection WHERE tagid='$tagId'";
    $result2 = mysqli_query($conn, $sql2);

    while ($row = mysqli_fetch_assoc($result2)) {
        $imageId = $row['imageid'];
        $sql3 = "SELECT * FROM image WHERE id='$imageId'";
        $result3 = mysqli_query($conn, $sql3);
        $getResult3 = mysqli_fetch_assoc($result3);
        echo    '<div class="imageContainer">
                <h1>'.$getResult3['name'].'</h1>
                <a href="imageInfo.php?='.$getResult3["path"].'">
                <img class="uploadedImg" src="uploads/'.$getResult3["path"] .'" alt="Random image" />
                </a>
                </div>';
    }


?>

I have this bad feeling that I'm not doing it the way it should be done, so I decided to ask around here and get a few tips and pointers if possible.

Peter Atanasov
  • 79
  • 6
  • Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples – Alex Howansky Jul 14 '17 at 17:26
  • 1
    As a rule of thumb: Query in loop can almost always be extracted to a `join` before the loop. – colburton Jul 14 '17 at 17:29
  • You asked, "Am I using too many MySQL queries in order to get the all the images that have a certain tag?" YES. If you use more than 1 it's too many. Get ALL of what you need when you need it from the database. – xQbert Jul 14 '17 at 18:29

1 Answers1

0

Use a join, here's a basic example of how it may look

    SELECT * FROM tags
JOIN tagsconnection ON tagsconnection.tagid=tags.id
JOIN image ON image.id=tagsconnection.imageId
WHERE tag=$realestTagName

If you not familiar with joins, here's a good tutorial https://www.w3schools.com/sql/sql_join.asp

Kevin P
  • 601
  • 3
  • 9
  • ...and here's a link about joins explained with a Venn diagram, if it might also help: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Paul T. Jul 14 '17 at 18:08