•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 426,401 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,306 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 3827 | Replies: 15 | Solved
 |
•
•
Join Date: Aug 2006
Posts: 15
Reputation: 
Rep Power: 3
Solved Threads: 0
Newbie Poster
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:
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:
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
(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
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, Typeattributeid 7 represents zipcodes
attributeid, int(11)
userid, int(11)
value, varchar(255)
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;
I'm guessing this is pretty simple, I just need some help...
Thanks,
~kyle
Last edited by kyleknapp : Oct 10th, 2006 at 4:10 pm.
•
•
Join Date: Sep 2006
Location: NYC
Posts: 133
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster
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.
•
•
•
•
Originally Posted by kyleknapp 
SELECT * FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value`= "68104"
[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.
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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
~kyle
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
Okay, I'm still doing something wrong. I created a test php file and called it targetzip.php. it's coded as:
When I execute it I get the following error:
can you tell what's wrong?
Thanks,
~kyle
<?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 8Thanks,
~kyle
•
•
Join Date: Sep 2006
Location: NYC
Posts: 133
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster
•
•
•
•
Originally Posted by kyleknapp
[php]
if mysql_num_rows($query) {
[/php]
should be
[php]
if (mysql_num_rows($query)) {
[/php]
you forgot to enclose the if in ()
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
okay, that's fixed, thanks. But now I get:
Is there another missing bit of punctuation, or am I completely misunderstanding the syntax?
Thanks,
~kyle
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
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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:
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
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);
} 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
•
•
Join Date: Sep 2006
Location: NYC
Posts: 133
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster
print_r the array to make sure you have results
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
looks like its only finding (and updating) the first useid, even though the query
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....
SELECT * FROM `phplist_user_user_attribute` WHERE `attributeid` =7 AND `value` LIKE '68104'
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....
•
•
Join Date: Aug 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
Okay, I'm making progress. The following code
displays:
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
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
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Previous Thread: How do I edit a file in MySql please?
- Next Thread: Image Database
