Two query at the same time

Question

Hello first of all what i am doing in , i am coding a website for advertise . Now what do i need is a help to display a lots of data from two tables of database . What i have done so far u can check at My project you have to login use (Username : test , password : 123456a) to login , so there is everything is okay except an image image are the same on every ads and i do not find the way to make it right . So i have a "posts" table with an information about ads and an "images" table with a path of an image this is how its looks like :

and this is my code :

<?php

                    $userid = $_SESSION["userid"];
                    $sql = "SELECT * FROM posts WHERE userid='$userid' ";
                    $res = mysqli_query($connect,$sql);
                     while ($row = mysqli_fetch_assoc($res)) {
                         ?>
                        <div id="ads">
                            <div id="titlepic">
                            <a href=""><?php echo $row["title"]; ?></a><br>
                            <a href=""><img src="<?php echo $Photo[0]; ?>" height="100px;"></a>
                            </div>
                            <div id="managead">
                            <a href="">Edit</a><br style="margin-bottom: 5px;">
                            <a href="">Delete</a><br style="margin-bottom: 5px;">
                            <a href="">Renew</a>
                            </div>
                            <div id="dates">
                                <b>Date Added:</b> <?php echo date('m/d/Y', $row["dateadded"]); ?><br>
                                <b>Renew Date:</b> <?php if($row["renewdate"] > 0){ echo date('m/d/Y', $row["renewdate"]); } ?><br>
                                <b>Location:</b> <?php echo $row["location"]; ?><br>
                                <b>Price:</b> <?php echo $row["price"]; ?><br>
                            </div>
                        </div>
                        <hr width="100%">
                        <?php

so the question is how to extract and images from other table at the same time or how tu run two query at the same time and get an information from them

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 10 '16 at 15:25
i though i have to escape strings only when inserting a data into database not when selecting ? — yahoo5000, May 10 '16 at 15:36
No, because I can add an `INSERT`, `UPDATE`, or `DELETE` query at the end of your `SELECT` — Jay Blanchard, May 10 '16 at 15:39
you mean do this way ? $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute(array('name' => $name)); — yahoo5000, May 10 '16 at 15:53

Jeff Puckett · Accepted Answer · 2016-05-10T15:41:24.930

1

your SQL statement needs a JOIN in order to include data from two tables in one query.

$sql = "
  SELECT *
  FROM posts p
  JOIN images i
    ON p.id = i.postid
  WHERE p.userid='$userid'
";

this result set will be populated with all columns from both tables. now you can access path1 via:

<?php echo $row["path1"]; ?>

while this will work for all of your images, such as $row["path2"], $row["path3"], etc, keep in mind this is a bad design for a many-to-many relationship, so it should be normalized to include a linking table which would hold all of your images.

edited May 10 '16 at 15:41

answered May 10 '16 at 15:25

Jeff Puckett

37,464
17
118
167

this will probably produce 'ambiguous column' SQL-error – heximal May 10 '16 at 15:27
@heximal the only name collision in this example is the `id` field which isn't being explicitly accessed here. the SQL statement itself qualifies the reference with the `p` and `i` aliases. – Jeff Puckett May 10 '16 at 15:29
@yahoo5000 yes, this will work for all of your images, such as `$row["path2"]`, `$row["path3"]`, etc but this is not [normalized](https://en.wikipedia.org/wiki/Database_normalization) so you should search about [designing linking tables](http://www.plus2net.com/sql_tutorial/sql_linking_table.php) – Jeff Puckett May 10 '16 at 15:37
i read about this in PHP and mysql book , and i though i did well ? – yahoo5000 May 10 '16 at 15:43
@yahoo5000 it's a good start just to get it working, but we should never stop learning better ways to do things :) – Jeff Puckett May 10 '16 at 15:44

Two query at the same time

1 Answers1