Questions on SQL Join

I got this query...



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.

 

 

 

Comment/Reply (w/o sign-up)

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:



    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:


---> 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/item.asp?ItemID=11122

 

 

 

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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.

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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.

Comment/Reply (w/o sign-up)

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:



this one is without the join



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

Comment/Reply (w/o sign-up)

i think the main idea is here:



which part is that can be modified by user ?
is it


your query


$query = "UPDATE $users_table SET $member_group_col = $new_group_name "; <- doesn't need to change this part

depending which table is reside in.



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

Comment/Reply (w/o sign-up)