help a newbie? mySQL find & replace

[kyleknapp]

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

[sn4rf3r]

Take this:

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

and make a comma separated list od the id's which you want to update

$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);
}

hope this helps.

[kyleknapp]

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

[kyleknapp]

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

[sn4rf3r]

if mysql_num_rows($query) {

should be

if (mysql_num_rows($query)) {

you forgot to enclose the if in ()

[kyleknapp]

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

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

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

Thanks,
~kyle

[kyleknapp]

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

[sn4rf3r]

print_r the array to make sure you have results

[kyleknapp]

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....

[kyleknapp]

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

[sn4rf3r]

theres no loop, you are updating all records which have a userid in the comma separated list and match youre where/and statements. Try wrapping each value in quotes (if the columns are int you dont need to do this)
you could also try this

if (mysql_num_rows($query)) {
$ary = array();
foreach  (mysql_fetch_assoc($query) as $key => $val) {
$ary[] = $val;
}
$ary = implode(',',$ary);
}

Its doing the same thing in a different way. if that still fails, post the relevant code.

[kyleknapp]

let me see if I understand. In your previously suggested code, the
mysql_fetch_assoc() function is supposed to retrieve ALL the matching userid's, right?

and then the implode() function is supposed to lump them all together into a single variable ($ary) which, if I view it should look something like 21,45,71,101,131,137,168,229,343,375,380,386,429,439,467,576,618,664,822,830,858,919,920 then the `userid` IN (".$ary.")" clause of the UPDATE command would find any userid that matches anything in the "imploded" field.

Makes sense to me, so why didn't it work? my echo $ary, "<br>"; command should have caused something resembling 21,45,71,101,131,137,168,229,343,375,380,386,429,439,467,576,618,664,822,830,858,919,920 to appear on the screen, but only "21" appeared.

I actually have gotten it to work, though it's probably terribly clumsy (certainly not as elegant as your "foreach" function). My apparently functional hack job is as follows:

echo "selecting userids for 68104<br>";

$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "99999"';
echo "querying for selected userids<br>";
$query = mysql_query($sql);
echo "setting attribute<br>";
echo $rows = mysql_num_rows($query), " rows selected<br>";

$i = 0;
while ($i < $rows) {
$i = $i+1;
if (mysql_num_rows($query)) {
$ary = implode(',', mysql_fetch_assoc($query));
echo $i, "   userid: ", $ary, "<br>";
$update = "UPDATE  `phplist_user_user_attribute`  SET  `value`  =  'on' WHERE  `attributeid`  ='16' AND  `userid` IN (".$ary.")";
mysql_query($update);
}} 

echo "done<br>";

yields the following display:

selecting userids for 68104
querying for selected userids
setting attribute
23 rows selected
1   userid: 21
2   userid: 45
3   userid: 71
4   userid: 101
5   userid: 131
6   userid: 137
7   userid: 168
8   userid: 229
9   userid: 343
10   userid: 375
11   userid: 380
12   userid: 386
13   userid: 429
14   userid: 439
15   userid: 467
16   userid: 576
17   userid: 618
18   userid: 664
19   userid: 822
20   userid: 830
21   userid: 858
22   userid: 919
23   userid: 920
done

and appears to be setting the "targeted" field as desired. What d'ya think?

~kyle

[sn4rf3r]

you dont need to embed your if statement inside of your while loop, should be the other way around. Also you are creating multiple update queries when you only need one.

Try this:

if (mysql_num_rows($query)) {
  while ($row = mysql_fetch_array($query)) {
    $ary[] = $row;
}

echo '<pre>';
print_r($ary)
echo '</pre>';
$ary = implode(',',$ary);
echo 'imploded values ' . $ary;

$update = "update phplist_user_user_attribute set value = 'on' where attributeid = 16 and userid in (".$ary.")";
die($update);
}

See what that shows?

[kyleknapp]

thanks, I'll take a look at that.

~kyle

[fanaticweb]

find and replace in mysql is very easy:

update `table` set fieldname=replace(fieldname,'old,'new');

[peter_budo]

What is point to bump post started 2 years ago, with last respond some 18 months back?