0

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.

Relm
  • 7,923
  • 18
  • 66
  • 113
  • 1
    When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). – tadman Oct 14 '14 at 18:11
  • 1
    You should probably not be running sql queries in your loop. Using a `JOIN` is normally a lot more efficient. – jeroen Oct 14 '14 at 18:12
  • @jeroen I would love join, can you give an example based on my question please. – Relm Oct 14 '14 at 18:26
  • you would need to post the first query as well. – jeroen Oct 14 '14 at 18:28

2 Answers2

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

Community
  • 1
  • 1
John Ruddell
  • 25,283
  • 6
  • 57
  • 86
  • This has turned a harmless bit of code into a SQL injection bug. – tadman Oct 14 '14 at 18:10
  • @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 Oct 14 '14 at 18:11
  • It'd be worth taking the two minutes to parameterize this, though, making it actually safe for production use. – tadman Oct 14 '14 at 18:12
  • Now it looks awesome. Might trim out the original as that is, potentially, career destroyingly bad. – tadman Oct 14 '14 at 18:15
  • I really can't believe I've been using this quotations marks without knowing if I swap them around they wouldn't work. This is something else. – Relm Oct 14 '14 at 18:28
  • 1
    @Relm you really shouldnt use the single quotes though. use the prepared statement i put in there... – John Ruddell Oct 14 '14 at 18:30
  • @JohnRuddell For the photoId, should I still use prepared statements even though I know what type of characters I'm expecting from DB? I mean I expect a number, so I can just preg_replace everything else. – Relm Oct 14 '14 at 18:38
  • 1
    @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 – John Ruddell Oct 14 '14 at 18:39
  • 1
    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. – tadman Oct 14 '14 at 18:41
  • Well, looking into it, somehow this ends up as user input, though in this case this was some auto-incremented value, but in the case where its a name of a user, that way its coming from my db, but its still not safe, I would use prepared statements. Another thing, if the argument might be that non-prepared statements might be deprecated in some future, one might just use prepared statements, even though Im not sure about their performances regarding speed. – Relm Oct 14 '14 at 18:46
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.

Community
  • 1
  • 1
Dimitri
  • 453
  • 3
  • 11
  • You should not be using string interpolation in the first place, so using `'`' to be on the safe side is usually a good idea. – tadman Oct 14 '14 at 18:10