How to run an sql query inside a while loop of another query

Question

The following works with no problem at all, when the photoId is directly on the statement and not a variable.

$img_query = mysqli_query($con, 'SELECT * FROM imgs WHERE photoid = "103"') or die(mysqli_error($con));

but the following just won't work with no error, what might be causing this not to select.

$imageid = '103';
$img_query = mysqli_query($con, 'SELECT * FROM imgs WHERE photoid = "$imageid"') or die(mysqli_error($con));
$img_row = mysqli_fetch_array($img_query);
    echo $img_row['img'];

This is inside a while loop.

while($row = mysqli_fetch_array($somequery)){
 $imageid = $row['photoid'];
    $img_query = mysqli_query($con, 'SELECT * FROM imgs WHERE photoid = "$imageid"') or die(mysqli_error($con));
    $img_row = mysqli_fetch_array($img_query);
        echo $img_row['img'];
}

Thanks.

When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation to accomplish this because you will create severe SQL injection bugs. — tadman
– tadman, Commented Oct 14, 2014 at 18:11
You should probably not be running sql queries in your loop. Using a JOIN is normally a lot more efficient. — jeroen
– jeroen, Commented Oct 14, 2014 at 18:12
@jeroen I would love join, can you give an example based on my question please. — Relm
– Relm, Commented Oct 14, 2014 at 18:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

in php a ' and a " are very different and the query syntax is double quote around the query and single quote around variables.. although I would recommend you look at using parameters on your query instead of just putting a variable directly into the query

Per my recommendation you should change your query to this:

$imageid = '103';
$query = $con->prepare("SELECT * FROM imgs WHERE photoid = ?");
$query->bind_param('sssd', $imageid);
$query->execute();

this is just the nuts and bolts of it... if you want more information about the connection.. error handling and everything else read the DOCS

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 14, 2014 at 18:08

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

tadman Over a year ago

This has turned a harmless bit of code into a SQL injection bug.

John Ruddell Over a year ago

@tadman well from what the OP is using the variable for it wouldn't be user input... but if you notice the end of my post i recommend the OP look into using parameters

John Ruddell Over a year ago

@Relm you really shouldnt use the single quotes though. use the prepared statement i put in there...

John Ruddell Over a year ago

@Relm well it really depends on if you are taking user input or not... i would stick with the parameterized queries because they are safe and are where all sql queries are headed and should be done but its up to you... if there is any user input at all for any query then you MUST use this method

tadman Over a year ago

Being disciplined about using parameterized queries means the chance of an escaping bug slipping through is really low. Manually escaping some things is always risky and should be done only as an absolute last resort even if you're "sure" the data is fine. Where that data originates could change in the future and you might be caught wide open.

|

Community · Accepted Answer · 2017-05-23 12:27:21Z

1

there is a big difference between ' and " in php

Differences

change your query to be

$img_query = mysqli_query($con, "SELECT * FROM imgs WHERE photoid = '$imageid'") or die(mysqli_error($con));

and it should work.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Oct 14, 2014 at 18:08

Dimitri

4533 silver badges11 bronze badges

1 Comment

tadman Over a year ago

You should not be using string interpolation in the first place, so using '' to be on the safe side is usually a good idea.

Collectives™ on Stack Overflow

How to run an sql query inside a while loop of another query

2 Answers 2

Per my recommendation you should change your query to this:

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Per my recommendation you should change your query to this:

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related