SELECT Statement with array in WHERE clause

Question

I am trying to track pageviews on my site whether a user is logged in or not and so I'm capturing the sessionId in my log. I only want to show tracking results for session ids that have at some point logged in and so my flow is as follows:

$pagestats = $wpdb->get_results("SELECT * FROM wp_user_stats WHERE uid=".$_GET['viewstats']);
$sessionArray = array();

foreach($pagestats as $checkSession)
{
    if( !(in_array($checkSession->sessionId, $sessionArray)))
    {
        $sessionArray[] = $checkSession->sessionId;
        }
    }

Next, I am trying to gather all of the stats concerning any session Ids generated by this particular user ...

$sessions = join(',',$sessionArray);
$pagestats = $wpdb->get_results("SELECT * FROM wp_user_stats WHERE `sessionId` IN($sessions)") or die(mysql_error());

This is the part that throws the error. The error is:

Unknown column 'sjhntmqhltknks8pbhr750voe7' in 'where clause'

I don't understand why it's trying to find a column that matches the session Id instead of a result within that column.

coyotebush · Accepted Answer · 2012-08-20 23:41:55Z

2

The session IDs probably aren't getting quoted so the query looks like WHERE sessionID IN(abc, def, ...).

One way to fix that would be to change a line in the first loop:

$sessionArray[] = "'".$checkSession->sessionId."'";

Or you could create a second array with the quoted values.

answered Aug 20, 2012 at 23:41

coyotebush

6536 silver badges10 bronze badges

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

2 Comments

pestilence669 Over a year ago

for the love of God, please use mysql_real_escape()

coyotebush Over a year ago

Definitely... but that's a separate issue.

JamesArmes · Accepted Answer · 2012-08-20 23:45:20Z

2

The problem is that the session id is not numeric and needs to be wrapped in quotes. Something like the following would add proper quote to your $sessions string:

$sessions = "'" . implode("', '", $sessionArray) . "'";

You should also make sure you are escaping any user supplied input (e.g. $_GET['viewstats']) before using them to help avoid SQL injection attacks. It wouldn't hurt to escape $checkSession->sessionId as you are adding it to the $sessionArray array either.

If you are using a framework (it looks like you may be using WordPress) read the manual for the database component as it may provide functions to handle some of this for you.

answered Aug 20, 2012 at 23:45

JamesArmes

1,3735 gold badges18 silver badges24 bronze badges

Comments

codingbiz · Accepted Answer · 2012-08-20 23:45:13Z

1

I think you need to put quote around your session values

$sessions = "'".join("','",$sessionArray)."'";

Right now it's like WHERE IN (a,b,c) instead of WHERE IN ('a','b','c').

answered Aug 20, 2012 at 23:45

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Comments

user1558223 · Accepted Answer · 2012-08-20 23:38:15Z

0

$pagestats = $wpdb->get_results("SELECT * FROM wp_user_stats WHERE `sessionId`=$sessions") or die(mysql_error());

answered Aug 20, 2012 at 23:38

user1558223

1971 silver badge9 bronze badges

Comments

techn1cs · Accepted Answer · 2012-08-21 00:41:11Z

0

This isn't an answer to the specific issue, but isn't the second SELECT statement unnecessary? If it is all stored in one table (unless there is a typo...) then SELECT * FROM wp_user_stats WHERE uid=$_GET['view_stats'] would retrieve all sessions for that user. Perhaps you need it done for multiple users? Even at that, you could simply do a GROUP BY clause.

Maybe I'm missing something though -- if so, sorry.

answered Aug 21, 2012 at 0:41

techn1cs

463 bronze badges

Collectives™ on Stack Overflow

SELECT Statement with array in WHERE clause

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related