help a newbie? mySQL find & replace
 

I'm brand new to SQL queries, hoping someone can help me set up this query.

I'm using phpList to manage my mailing list. All user defined attributes appear to be stored in a table called phplist_user_user_attribute. The structure of this table is:

Field, Type
attributeid, int(11)
userid, int(11)
value, varchar(255)

attributeid 7 represents zipcodes
attributeid 16 refers to a boolean field referred to in another table as "Targeted", which I want to use for mailing list selections.

what I need to do is find every record with attributeid of 7 and a value of "68104" (for example), as:

SELECT * FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value`= "68104"

this gives me a list of all userid's in this zip code.
then, for every userid found, I need to set attribute 16 'ON', as

UPDATE  `phplist_user_user_attribute`  SET  `value`  =  '' WHERE  `attributeid`  =16 AND  `userid`  =1;

(the above code sets the flag 'ON' for userid 1, but I can't figure out how to do it for every userid found in the previous query. Can I 'nest' these somehow?
I'm guessing this is pretty simple, I just need some help...

Thanks,
~kyle

Re: help a newbie? mySQL find & replace
 

Take this:
and make a comma separated list od the id's which you want to update
[php]
$sql = 'SELECT id FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value`= "68104"';
$query = mysql_query($sql);
if mysql_num_rows($query) {
$ary = implode(',', mysql_fetch_assoc($query, MYSQL_NUM));
$update = "UPDATE `phplist_user_user_attribute` SET `value` = '' WHERE `attributeid` ='16' AND `userid` IN (".$ary.")";
mysql_query($update);
}
[/php]
hope this helps.

Re: help a newbie? mySQL find & replace
 

Thanks a million. I'm not very skilled in PHP either, but let me see if I can figure this out, and then I'll post back.
~kyle

Re: help a newbie? mySQL find & replace
 

Okay, I'm still doing something wrong. I created a test php file and called it targetzip.php. it's coded as:

<?php

// Connects to your Database

mysql_connect("localhost", "ID", "PASSWORD") or die(mysql_error());

mysql_select_db("DBNAME") or die(mysql_error());



$sql = 'SELECT id FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value`= "68104"';

$query = mysql_query($sql);

if mysql_num_rows($query) {

$ary = implode(',', mysql_fetch_assoc($query, MYSQL_NUM));

$update = "UPDATE  `phplist_user_user_attribute`  SET  `value`  =  'on' WHERE  `attributeid`  ='16' AND  `userid` IN (".$ary.")";

mysql_query($update);

} 



?>

When I execute it I get the following error:

Parse error:  syntax error, unexpected T_STRING, expecting '(' in /www/kyleknapp.com/web/targetzip.php on line 8

can you tell what's wrong?

Thanks,
~kyle

Re: help a newbie? mySQL find & replace
 

should be
[php]
if (mysql_num_rows($query)) {
[/php]
you forgot to enclose the if in ()

Re: help a newbie? mySQL find & replace
 

okay, that's fixed, thanks. But now I get:

Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/kyleknapp.com/web/targetzip.php on line 8

Is there another missing bit of punctuation, or am I completely misunderstanding the syntax?

Thanks,
~kyle

Re: help a newbie? mySQL find & replace
 

Hmmm... I guess "id" in line 6 should be "userid".
And I think the MYSQL_NUM argument in the mysql_fetch_assoc() command is extraneous

Now, after fixing that, I get no errors, but also no results (ie, the value for attributeid=16 does not seem to getting reset.

the updated code is:

$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value`= "68104"';

$query = mysql_query($sql);

if (mysql_num_rows($query)) {

$ary = implode(',', mysql_fetch_assoc($query));

$update = "UPDATE  `phplist_user_user_attribute`  SET  `value`  =  'on' WHERE  `attributeid`  ='16' AND  `userid` IN (".$ary.")";

mysql_query($update);

} 

Anybody see anything obviously wrong with it?

Is there some kind of a "debug" command I can insert that will cause it to display line-by-line what it's doing?

Thanks, I'm learning a lot.

~kyle

Re: help a newbie? mySQL find & replace
 

print_r the array to make sure you have results

Re: help a newbie? mySQL find & replace
 

looks like its only finding (and updating) the first useid, even though the query

SELECT *

FROM `phplist_user_user_attribute`

WHERE `attributeid` =7

AND `value` LIKE '68104'

tells me there are 23

sn4rf3r, can you tell me what the 2nd parameter in the mysql_fetch_assoc() command is for? I removed the one you originally suggested ("MYSQL_NUM"), as it appear to cause the scipt to crash. Does it indicate the number of rows to fetch? and if so, should it be "mysql_num_rows($query)"? (that doesn't seem to work either)


rrrrrrrrrrrrrr....

Re: help a newbie? mySQL find & replace
 

Okay, I'm making progress. The following code

echo "selecting userids for 68104<br>";



$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "68104"';

$query = mysql_query($sql);



echo mysql_num_rows($query), "rows selected<br>";



if (mysql_num_rows($query)) {

$ary = implode(',', mysql_fetch_assoc($query));

echo $ary, "<br>";

$update = "UPDATE  `phplist_user_user_attribute`  SET  `value`  =  'on' WHERE  `attributeid`  ='16' AND  `userid` IN (".$ary.")";

mysql_query($update);

} 

displays:

selecting userids for 68104

23rows selected

userid: 21

the script is selecting 23 records, but not doing anything with any but the first. I don't think I see anything in the code that causes it to "loop" through the selected records. Is there something comparable to a "DO WHILE" or "LOOP" command I should be inserting somewhere?

Thanks,
~kyle