Questions on SQL Join

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.

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, say

Column1 = 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

right, you lost me their, have you used SMF?

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

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.

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

ok.. diffrent tables, same db, same prefix.

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.

Using Join

Assume I have this database tables

triwall_data
batch varchar
box int
lot varchar

and

barcode2batch
batch varchar
barcode varchar

All 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 join

select * 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

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 part

depending 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 Beureum
The Best Place in the City

I got this query...
CODE

<?php
error_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.