MySQL recursive WHERE?

Question

I have a database which looks like this:

Token (PRIMARY)     School        Skip
--------------------------------------
f2342f              School 1      0
434fbc              School 1      0
33332c              School 1      0

My PHP code sends out a Push Notification to each Token if the School is matched up with a temp database. Then it sets skip to 1, so that it doesn't try sending another notification for a day.

The problem is though that I can't have the script set multiple skip values at once. When I do, it seems to work fine for the first token, but then won't set the skip value for the others, and dies.

Here's my PHP:

// Run comparison SQL query
        $compare = mysql_resultTo2DAssocArray(mysql_query("SELECT Temp.School, Temp.Status, Snow.Skip, GROUP_CONCAT(Snow.Token SEPARATOR '\', \'') Tokens FROM Temp JOIN Snow USING (School) WHERE Skip = 0 GROUP BY Temp.School"), $con);
        $amount = count($compare);

        // Send Push Notifications
        for ($i = 0; $i < $amount; $i++) {
            $message = $compare[$i][School] . " - " . $compare[$i][Status];
            $tokens = $compare[$i][Tokens];
            pwCall( 'createMessage', array(
                'application' => PW_APPLICATION,
                'username' => PW_LOGIN,
                'password' => PW_PASSWORD,
                'notifications' => array(
                    array(
                        'send_date' => 'now',
                        'content' => $message,
                        'ios_badges' => 1,
                        'ios_sound' => 'bells.caf',
                        'data' => 'daily',
                        'devices' => array($tokens),
                        )
                    )
                )
            );
            if (!mysql_query("UPDATE Snow SET Skip='1' WHERE Token='$tokens[$i]'", $con)) {

                echo "<pre>";
                print_r(str_replace("'", '', $tokens));
                echo "</pre>";

                die('Error: ' . mysql_error());
            }
        }

With the output being:

f2342f, 434fbc, 33332c Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'f2342f', '434' at line 1

So to me it seems that I need to have it set the skip recursively, unless I'm missing something important with my code.

$compare:

Array
(
    [0] => Array
        (
            [School] => School 1
            [Status] => Closed 
            [Skip] => 0
            [Tokens] => f2342f', '434fbc', '33332c
        )

)

Why does Tokens have that strange quote pattern? Anyway, it's not an array, so indexing it looks like what's breaking your query. Was it intended to be used as WHERE Token IN ('$tokens') or something? — Ry-
– Ry- ♦, Commented Nov 18, 2012 at 15:25
Using GROUP_CONCAT that way is definitely not the most attractive or straight-forward pattern. Smells a little like premature optimization... — grossvogel
– grossvogel, Commented Nov 18, 2012 at 15:32
Also, if you're really not quoting your array keys (e.g. $compare[$i][Tokens] instead of $compare[$i]['Tokens'], you should start. PHP will issue a notice-level error every time: Use of undefined constant Tokens - assumend 'Tokens'... — grossvogel
– grossvogel, Commented Nov 18, 2012 at 15:35

grossvogel · Accepted Answer · 2012-11-18 15:38:09Z

2

After you do this:

$tokens = $compare[$i]['Tokens'];

$tokens should be something like this:

f2342f', '434fbc', '33332c

Meaning your query should be changed to

"UPDATE Snow SET Skip='1' WHERE Token IN('$tokens')"

edited Nov 18, 2012 at 15:38

answered Nov 18, 2012 at 15:27

grossvogel

6,7921 gold badge28 silver badges37 bronze badges

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

Comments

Martin Vrkljan · Accepted Answer · 2012-11-18 15:24:34Z

1

The problem seems to be in how you treat $tokens in your UPDATE query.

You're trying to access the tokens in it as if it were array, but the error message clearly shows you that $tokens is a string - otherwise print_r would tell you it's an array.

You need to explode the $tokens string before making an update query for each token.

$tokensArray = explode (', ', $tokens);

EDIT:

@minitech gave you a good comment on your question; if the tokens were meant to be used in an IN statement (WHERE token IN ($tokens)) you don't need to explode them into an array.

answered Nov 18, 2012 at 15:24

Martin Vrkljan

86910 silver badges20 bronze badges

1 Comment

Charlie Over a year ago

I ended up using explode for sending out the push notifications, as I was sending out a string that looked like an array but really wasn't, which caused a lot of problems.

Collectives™ on Stack Overflow

MySQL recursive WHERE?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related