php and mysql query trouble - query isn't working

Question

I'm trying to create a table of users that are part of a group but I can't seem to get it working. The list is stored in a column so that each userid # is separated by a "~". For example, if users 1,2 and 3 are attending the column would show "1~2~3". This is what I use explode for.

I'm getting the following error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in" which is for the line where $friend is defined.

$attendingUsers = mysql_query("Select acceptedInvites from events where eventID = ".$_GET['eventID']." ");
    while($friend = mysql_fetch_array($attendingUsers)){
                $users = $friend['acceptedInvites'];
                $userExplode = explode("~",$users);
            for($i=0; $i<count($userExplode);$i++){
                echo $userExplode[$i]; //displays the userid number properly so I know this is working
                $friendInfo = mysql_query("select (userid,username) from users where userid = '". $userExplode[$i]."' ");;
                $friend = mysql_fetch_array($friendInfo);
                echo '<table><tr><td><a href="profile.php?userid=' . $friend['userid'] . '">' . $friend['username'] . '</a></td>';
                }
            }

I'm starting to think it's something to do with $friendInfo because when I echo it nothing is displayed (usually would say array).

Nice sql injection hole. Hope you like the truck someone'll drive through it and park in the middle of your server. — Marc B
– Marc B, Commented Feb 9, 2012 at 3:18

Michael Berkowski · Accepted Answer · 2012-02-09 03:22:50Z

There are a few problems here. The primary problem you're experiencing is that you have no error handling and a syntax error in your query. There should be no parentheses around the select list columns:

$friendInfo = mysql_query("select userid, username from users where userid = '". $userExplode[$i]."' ");
//-------------------------------^^^^^^^^^^^^^^^^^^

Some basic error handling will surface these errors:

$friendInfo = mysql_query("select userid, username from users where userid = '". $userExplode[$i]."' ");
if (!$friendInfo) {
  // error!
  echo mysql_error();
}
else {
  $friend = mysql_fetch_array(....);
}

You must escape the input parameters against SQL injection, rather than use them directly in the query. That is most easily done with mysql_real_escape_string().

$attendingUsers = mysql_query("Select acceptedInvites from events where eventID = ". mysql_real_escape_string($_GET['eventID'])." ");

You can improve this algorithm a bit by replacing the for loop with a query that uses an IN() clause. Instead of looping over all the friends, do one query by imploding the array into a comma-separated list:

$userExplode = explode("~", $users);
// Implode them together with commas
// Don't forget to call mysql_real_escape_string() on these if necessary
$friendlist = implode(",", $userExplode);
// Actually, you could just do $friendlist = str_replace("~", ",", $users)
// and avoid doing either explode() or implode()...

// Then query with an IN () clause...
$friendInfo = mysql_query("select userid, username from users where userid IN ($friendlist)");

Now rather than performing the query in a loop, you only need to fetch in a loop. This is far more efficient than querying again and again.

Wow, such a simple mistake. I can't believe I didn't notice that, I guess it's time to take a break for the day. Thanks for the help!

Collectives™ on Stack Overflow

php and mysql query trouble - query isn't working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related