this simple query is broke lol

[Monkuar]

$ids = implode (",", $ibforums->input['checkbox']);
			$time = time();
	$ids2 = implode (",", $ibforums->input['pendingusers']);	


$DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)");

 

weird thing is, it's not bring up any error's or nothing

 

$ids2 spits out

2,30

 

and $ids spits out

 

9,7

 

for this particular project

 

doesn't give me mysql error or nothing, script runs fine.  I have mysql error enabling under $dbquery class so nn to  worry, how to get this to work?  :P

 

Can i even use 2 IN's in 1 query or???

[Pikachu2000]

Are there any matching records in the table? Run a SELECT query with a the same WHERE criteria to make sure.

[AGuyWithAthing]

To find out what you're trying to execute just before/after it put:

 

printf("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)");

 

and see what the query is.

 

From a glance date is a reserved word and if date is a date or datetime field the value should be commented. Try:

 

"UPDATE friends_pending SET pending='0', `date`='$time' WHERE id IN ($ids) AND toid IN ($ids2)"

 

and see if that fixes your issue, other than that I cannot see anything jumping out at me which would cause it to fail.

[Pikachu2000]

Date actually isn't a reserved word.

 

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

 

    *      ACTION

    *      BIT

    *      DATE

    *      ENUM

    *      NO

    *      TEXT

    *      TIME

    *      TIMESTAMP

[kicken]

$DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)");

 

Maybe this is what you want, but just to make sure incase it isn't:

 

That query would be the same as doing

WHERE (id=2 or id=30) AND (toid=9 OR toid=7)

 

When means it will match any of these row combinations:

id=2, toid=9

id=2, toid=7

id=30, toid=9

id=30, toid=7

 

If instead you intend to match only the rows where

id=2, toid=9

id=30, toid=7

 

then you'd need this query condition:

WHERE (id=2 AND toid=9) OR (id=30 AND toid=7)

 

 

[Monkuar]

Are there any matching records in the table? Run a SELECT query with a the same WHERE criteria to make sure.

 

 

my whole table

 

i tried

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE id IN ($ids) AND toid IN ($ids2)");

 

not working

 

and to reply to kicken

i need to use the IN because i have mulitiple values, if i use ur method it will only update 1 row, or iwould have to use a query each time.

 

 

[AGuyWithAthing]

Date actually isn't a reserved word.

 

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

 

    *      ACTION

    *      BIT

    *      DATE

    *      ENUM

    *      NO

    *      TEXT

    *      TIME

    *      TIMESTAMP

 

Not as a direct statement but defined as a function name.

 

@monkuar Could you dump the query to see what you are actually executing?

[Monkuar]

Date actually isn't a reserved word.

 

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

 

    *      ACTION

    *      BIT

    *      DATE

    *      ENUM

    *      NO

    *      TEXT

    *      TIME

    *      TIMESTAMP

 

Not as a direct statement but defined as a function name.

 

@monkuar Could you dump the query to see what you are actually executing?

 

hmmiuno what you mean by dump, im not going to drop my table/etc just for some help XD

 

 

This is what i need to do, set the pending = 0 to all the toid's and id's from the input array that i am grabbing.

 

it works when i just use

 

WHERE id IN ($ids)

 

But if I add the

AND toid IN ($ids2)

 

It won't work,

 

I am simply needing the toid values to be set to pending=0 along with my regular id values  :shrug: :shrug:

 

[AGuyWithAthing]

"Dump" ( Get the raw contents of also associated with var_dump ) as in echo/printf/print/var_dump/print_r the query e.g. put the following in your code just before you execute your query :

 

printf("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)");

 

Just so I can see what query is actually being executed.

[Monkuar]

"Dump" ( Get the raw contents of also associated with var_dump ) as in echo/printf/print/var_dump/print_r the query e.g. put the following in your code just before you execute your query :

 

printf("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)");

 

Just so I can see what query is actually being executed.

 

UPDATE friends_pending SET pending='0', `date`='1324591002' WHERE id IN (7,9) AND toid IN (30,2)

 

this is just amazingly making me stumped, query looks fine lol

[AGuyWithAthing]

IDs 7 and 9 have the TOID of 1 and will never match as the query requires the TOID to match 30 and 2 so the query is correct.

 

Change the TOID of 7 and/or 9 ?

[Monkuar]

IDs 7 and 9 have the TOID of 1 and will never match as the query requires the TOID to match 30 and 2 so the query is correct.

 

Change the TOID of 7 and/or 9 ?

 

I need to somehow change the info of the  id 8 and 10 then

 

 

this possible with the input I am receiving atm?

 

 

this query should be working though i am using toid the same way as Id, just a different name, or that's how I think it's working, this query should work.... this is just making me mad atm

 

all i need to do is..

 

IF toid=XXX change pending = 0....  but i want to run 1 query, if somone selects 20 users on the page and accepts them all as a friend, i am not running 20 queries on my page

 

this stinks atm, im stuck

 

 

[AGuyWithAthing]

You need to pass 8 and 10 in your checkboxes?

 

$ids = implode (",", $ibforums->input['checkbox']);

 

Also, if your using the unix timestamp you don't need the quotes in the query I thought it may have been braking if you were passing datestamps e.g. '2011-11-11 11:11:11' which would of caused it to break.

[Monkuar]

You need to pass 8 and 10 in your checkboxes?

 

$ids = implode (",", $ibforums->input['checkbox']);

 

Also, if your using the unix timestamp you don't need the quotes in the query I thought it may have been braking if you were passing datestamps e.g. '2011-11-11 11:11:11' which would of caused it to break.

 

No no i passed all my arrays and implodes, it's the fact this query isn't using TOID to set the pending=0...there has to be away around this,

 

just imagine switching the toid's with "ID" that's how I want it to work, it should set each pending=0 where toid=XX but it should work using the "IN" function

 

which itis not  :shrug: :shrug:  :wtf: :wtf:

[AGuyWithAthing]

do you mean you want an OR instead of an AND in the query clause where it will either match the toid or the id passed?

 

e.g.

 

$DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) OR toid IN ($ids2)");

 

[Monkuar]

do you mean you want an OR instead of an AND in the query clause where it will either match the toid or the id passed?

 

e.g.

 

$DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) OR toid IN ($ids2)");

nope i need to update both, if it was OR, it would prob just use only the id anyway,

 

im doing this bcz if it the user accepts the friend request, it needs to update the other row to make it so they're friends, which if pending=0 = they are now friends..

 

[kicken]

im doing this bcz if it the user accepts the friend request, it needs to update the other row to make it so they're friends, which if pending=0 = they are now friends..

 

So, your id's are 7,9.  Based on your image of your data those rows both have a toid of 1.

 

Are you trying to update those rows and their corresponding rows (8,10) so pending=0 on all? 

 

I don't think you'll be able to do what you want with two IN statements.  You need to either get the other two row ID's (8,10) or match on both the to and from id's

 

[Monkuar]

im doing this bcz if it the user accepts the friend request, it needs to update the other row to make it so they're friends, which if pending=0 = they are now friends..

 

So, your id's are 7,9.  Based on your image of your data those rows both have a toid of 1.

 

Are you trying to update those rows and their corresponding rows (8,10) so pending=0 on all? 

 

I don't think you'll be able to do what you want with two IN statements.  You need to either get the other two row ID's (8,10) or match on both the to and from id's

 

yea i guess u can't use AND with IN, what a bummer, mysql developers need to fix that..

 

so now i wil use

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (id IN ($ids))");

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (toid IN ($ids2))");

 

works fine now

 

but 1 more problem i will make a new topic

[Pikachu2000]

Are there any matching records in the table? Run a SELECT query with a the same WHERE criteria to make sure.

 

 

my whole table

 

i tried

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE id IN ($ids) AND toid IN ($ids2)");

 

not working

 

and to reply to kicken

i need to use the IN because i have mulitiple values, if i use ur method it will only update 1 row, or iwould have to use a query each time.

 

 

 

It appears you ignored my suggestion to run a SELECT query. I suggested it because I suspected there were no records that matched the WHERE criteria, and according your output I was right; there are no matching records.

[Monkuar]

Are there any matching records in the table? Run a SELECT query with a the same WHERE criteria to make sure.

 

 

my whole table

 

i tried

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE id IN ($ids) AND toid IN ($ids2)");

 

not working

 

and to reply to kicken

i need to use the IN because i have mulitiple values, if i use ur method it will only update 1 row, or iwould have to use a query each time.

 

 

 

It appears you ignored my suggestion to run a SELECT query. I suggested it because I suspected there were no records that matched the WHERE criteria, and according your output I was right; there are no matching records.

 

hey, it's not your fault, the mysql developers don't support the "IN" clause being used with "AND", it wasn't gonna work, I just went ahead and used

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (id IN ($ids))");
$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (toid IN ($ids2))");

 

Another query aint gonna kill my db  :shy: :shy:

[Pikachu2000]

im doing this bcz if it the user accepts the friend request, it needs to update the other row to make it so they're friends, which if pending=0 = they are now friends..

 

So, your id's are 7,9.  Based on your image of your data those rows both have a toid of 1.

 

Are you trying to update those rows and their corresponding rows (8,10) so pending=0 on all? 

 

I don't think you'll be able to do what you want with two IN statements.  You need to either get the other two row ID's (8,10) or match on both the to and from id's

 

yea i guess u can't use AND with IN, what a bummer, mysql developers need to fix that..

 

so now i wil use

 

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (id IN ($ids))");

$DB->query("UPDATE friends_pending SET pending='0', `date`='$time' WHERE (toid IN ($ids2))");

 

works fine now

 

but 1 more problem i will make a new topic

 

Of course you can use IN with AND, but there have to be matching records in order for the query to actually do anything. Now you're using 2 queries that do the same thing as the one query that was suggested here:

 

do you mean you want an OR instead of an AND in the query clause where it will either match the toid or the id passed?

 

e.g.

 

$DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) OR toid IN ($ids2)");

 

[kicken]

hey, it's not your fault, the mysql developers don't support the "IN" clause being used with "AND", it wasn't gonna work, I just went ahead and used

 

Its supported, it's just not what you want.  Your logic was wrong.  It's still wrong too.

 

Say you have a datatable like this:

id | toid | fromid 
----+------+---------
  7 |   1  |   30
  8 |  30  |    1
  9 |   1  |    2
10 |   2  |    1
11 |   2  |    30
12 |  30  |    2
--------------------

 

According to your statement you would only want to update rows 7,8,9,10 to pending=0 given your original values (updating 7,9 and their corresponding rows 8,10).

 

The queries you execute though, will also update rows 11,12 because they match your criteria of toid IN (2,30).  Not what you intended.