Ryan1405241476 0 Report post Posted February 11, 2005 I got this query... <?phperror_reporting(E_ALL); //Tell all the things$host="this_host";$user="me";$pass="secret_word";$users_table="users_table_for_smf";$member_group_col="column_name_for_member_group_in_smf";$new_group_name="whatever";$this_group="the_old_group";$custom_field="custom_field_in_users_profile";$this_value="the_value_for_custom_field";mysql_connect($host,$user,$pass) or die ("Can't connect, was told : <br>". mysql_error());mysql_select_db('smf') or die ("Can't open database, was told : <br>".mysql_error());$query = "UPDATE $users_table SET $member_group_col = $new_group_name ";$query .= "WHERE $member_group_col = $this_group AND ";$query .= "$custom_field = $this_value ";mysql_query($query);print("Updated records: %d\n", mysql_affected_rows());?> but i need this modified cause custom profile field data is stored in smf_themes and the membergroup data is store in smf_members with MEMBER ID as the common field, I am hoping someone here can rewrite this for me to work cause I don't know php, i had to get someone to write me this qury but they don't know how toy use the SQL Join.here is what i want this for: i need a php script to query the db, the smf users table and checks to entires in a users profile a custom field i made and the member group and if both match preset values then i want it to change the member group. i would just want this to be a php file that i can just randomly decide to goto, enter the url in firefox or any browser then it will run. Share this post Link to post Share on other sites
miCRoSCoPiC^eaRthLinG 0 Report post Posted February 11, 2005 Hey Ryan, I didn't get a really clear picture from the post, but it seems like you are trying to get a collection of different fields from a set of disjoint tables and depending on the result you intend to modify the column value of a certain table - that's almost what you term as Inner/Outer Join in SQL depending on the tables that you are combining the values from.. There are so many kinds of JOINS (Inner, Outer, Full, Cross, etc.) that if I began to to talk about them the posts would fill up all the space available on any board But one factor that is common to almost all the joins is that you have to explicitly specify, WHICH COLUMN is being fetched from WHICH TABLE/DB, i.e., let me show you by modifying a little bit of one of your querries: $query = "UPDATE $users_table SET $member_group_col = $new_group_name "; $query .= "WHERE $member_group_col = $this_group AND "; $query .= "$custom_field = $this_value "; Say, your $member_group_col resides in one table and $custom_field resides in another and the tables are titled say, A and B, your query would transform into: $query = "UPDATE $users_table SET A." . " $member_group_col = $new_group_name "; $query .= "WHERE A." . "$member_group_col = $this_group AND "; $query .= "B." . "$custom_field = $this_value "; ---> i.e., for joins, columns from different tables (that's what join is for) aren't referred to as simply column names, but they have to be refered as, tablename.columnname in EVERY instance they occur in your query. This is a MUST. Or else, you can you aliasing, wherby you can specify a name, sayColumn1 = A.Column1 and Column2 = B.Column2 --> you can probably see, that these two disjoint columns can be refered in your queries as just Column1 and Column2... There's an excellent book on all this named "MySQL CookBook that teaches you all these trick 10 times over in 1000 different ways. It's written by Paul Dubois and published by O'Reilly. You should grab it rightaway. Try eBay -and you might be able to get a second hand one at about 1/10th the price.. I find it an extremely good reference at these confusing points, coz that's where it opens up a handful of really nifty tricks to solve your prob... Have fun.. Hope I could help you, even if marginally - coz even I'm relatively new to SQL - I've been messing around with it for at the most an year or so...my main background is in C++ & Java on Linux.. A useful link on JOINS: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause Share this post Link to post Share on other sites
Ryan1405241476 0 Report post Posted February 11, 2005 right, you lost me their, have you used SMF? Share this post Link to post Share on other sites
miCRoSCoPiC^eaRthLinG 0 Report post Posted February 12, 2005 Lol.. Ok.. here's the deal. I'm going to grab a copy of SMF and try it out and see if I encounter similar problem. Or else I can try to simulate the exact db/table names that you've set up and try again.. In that case you need to paste here the db and tables names that you were using and a little more details about the table colums.. Share this post Link to post Share on other sites
Ryan1405241476 0 Report post Posted February 12, 2005 just look at the stucture to see what type of join i need, cus i want it to if you are in group id 4 lets say and the value of this profile field (stored in themems rater than users) is say yes then i want it to change the group to say groupid 5. Share this post Link to post Share on other sites
miCRoSCoPiC^eaRthLinG 0 Report post Posted February 12, 2005 While I'm downloading SMF, I'll try make the syntax of the JOINS a little simpler for you: Lets say we have two databases DB1 and DB2 And couple of tables in each - but what we have in common are two tables called say, tabxyz - which server different purpose but have the same name in both the databases.. Now, if say, you have changed the active database to DB1. When you do a simple select, say SELECT * FROM tabxyz - it will fetch you the columns from DB1. What IF, you wanted to match one of the columns from the tabxyz of DB2 ?? In such a case you can: 1. Either switch over and make DB2 your active database - which is a very inconvenient process, coz then you just keep switching between the DBs to everytime you need to fetch values from the other db... OR 2. Use the explicit dbname.tablename declaration to pull values out..In this case, even while your active database is DB1, you can do a select using the following syntax: SELECT * FROM DB2.tabxyz Now, lets consider two columns, residing in these tabxyz-s of the two databases... in DB1.tabzyx there's a column called ColA and in DB2.tabxyz, the column name is ColB. You watch to match the values of these two columns and IF they match, you write some value into yet a third column in say, DB2.tabxyz named ColC.. what you do is set up a couple of aliases to refer to ColA and ColB to make life easier for yourself. Thus: SELECT ColA from DB1.tabxyz AS ColumnA SELECT ColB from DB1.tabxyz AS ColumnB UPDATE DB2.tabxyz SET ColC='SomeValue' WHERE ( ColumnA = ColumnB ) Do you get it now ? ColumnA and ColumnB act as aliases (you can say, almost variables) pointing to ColA and ColB of the corresponding tables.. Thus, when you match the values, you avoid a lot of unnecessary headache of calling up each db and tables - instead you have two very handy aliases ready to be used... My whole point of writing all this is - that this kind of aliasing syntax is very common in JOINS and you'll encounter them in almost any code that involves JOINS... The process of JOIN itself is what you'd make out from it's name - joining data split over multiple tables and dbs and producing the required output.. Just keep this syntax in mind and it'll help you a lot in understanding joins.. More on the actual joins later have fun... Share this post Link to post Share on other sites
Ryan1405241476 0 Report post Posted February 14, 2005 ok.. diffrent tables, same db, same prefix. Share this post Link to post Share on other sites
Ryan1405241476 0 Report post Posted February 22, 2005 Thanks for your help but I am still lost, I am going to gte a php scripter to work on this and any other php scripts i will need for my site though. Share this post Link to post Share on other sites
vhortex 1 Report post Posted April 23, 2005 Using JoinAssume I have this database tablestriwall_databatch varchar box int lot varcharandbarcode2batchbatch varcharbarcode varcharAll I want to do is get all the records from triwall_data and barcode2batch where the batches matches I will then call this: select * from `qa_tracing`.`triwall_data` inner join barcode2batch on triwall_data.batch = barcode2batch.batch limit 0,10 this one is without the joinselect * from `qa_tracing`.`triwall_data`, barcode2batch where triwall_data.batch = barcode2batch.batch limit 0,10 hope this helps...update functions almost the same..NOTE: the sample is in assumption that you only want 10 records as is the limit code Share this post Link to post Share on other sites
oncombeureum 0 Report post Posted April 27, 2005 i think the main idea is here: $host="this_host";$user="me";$pass="secret_word";$users_table="users_table_for_smf";$member_group_col="column_name_for_member_group_in_smf";$new_group_name="whatever";$this_group="the_old_group";$custom_field="custom_field_in_users_profile";$this_value="the_value_for_custom_field"; which part is that can be modified by user ?is it$custom_field="custom_field_in_users_profile";$this_value="the_value_for_custom_field"; your query$query = "UPDATE $users_table SET $member_group_col = $new_group_name ";$query .= "WHERE $member_group_col = $this_group AND ";$query .= "$custom_field = $this_value "; $query = "UPDATE $users_table SET $member_group_col = $new_group_name "; <- doesn't need to change this partdepending which table is reside in.here is what i want this for: i need a php script to query the db, the smf users table and checks to entires in a users profile a custom field i made and the member group and if both match preset values then i want it to change the member group. i would just want this to be a php file that i can just randomly decide to goto, enter the url in firefox or any browser then it will run.what u need is an ID from smf user table that will connect to db and get the field u req.the query will look something like this.$query .= "WHERE $member_group_col = $this_group AND ";$query .= "$custom_field = $this_value ";continued.Oncom BeureumThe Best Place in the City Share this post Link to post Share on other sites
CarolinaBlues 0 Report post Posted November 8, 2005 I got this query...CODE<?phperror_reporting(E_ALL); //Tell all the things$host="this_host";$user="me";$pass="secret_word";$users_table="users_table_for_smf";$member_group_col="column_name_for_member_group_in_smf";$new_group_name="whatever";$this_group="the_old_group";$custom_field="custom_field_in_users_profile";$this_value="the_value_for_custom_field";mysql_connect($host,$user,$pass) or die ("Can't connect, was told : <br>". mysql_error());mysql_select_db('smf') or die ("Can't open database, was told : <br>".mysql_error());$query = "UPDATE $users_table SET $member_group_col = $new_group_name ";$query .= "WHERE $member_group_col = $this_group AND ";$query .= "$custom_field = $this_value ";mysql_query($query);print("Updated records: %d\n", mysql_affected_rows());?>but i need this modified cause custom profile field data is stored in smf_themes and the membergroup data is store in smf_members with MEMBER ID as the common field, I am hoping someone here can rewrite this for me to work cause I don't know php, i had to get someone to write me this qury but they don't know how toy use the SQL Join.here is what i want this for: i need a php script to query the db, the smf users table and checks to entires in a users profile a custom field i made and the member group and if both match preset values then i want it to change the member group. i would just want this to be a php file that i can just randomly decide to goto, enter the url in firefox or any browser then it will run. Mine was so different from yours. Its all confusing and different coding. Share this post Link to post Share on other sites