sql - PostgreSQL Query and PHP Assistance


Directly under this small intro here you'll see the layout of the database tables that I'm working with and then you'll see the details of my question. Please provide as much guidance as possible. I am still learning PHP and SQL and I really do appreciate your help as I get the hang of this.

Table One ('bue') -- chp_cd rgn_no bgu_cd work_state

Table Two ('chapterassociation') -- chp_cd rgn_no bgu_cd work_state

Database Type: PostgreSQL

I'm trying to do the following with these two tables, and I think it's a JOIN that I have to do but I'm not all that familiar with it and I'm trying to learn. I've created a query thus far to select a set of data from these tables so that the query isn't run on the entire database. Now with the data selected, I'm trying to do the following...

First and foremost, 'work_state' of table one ('bue') should be checked against 'work_state' of table two ('chapterassociation'). Once a match is found, 'bgu_cd' of table one ('bue') should be matched against 'bgu_cd' of table two ('chapterassociation'). When both matches are found, it will always point to a unique row within the second table ('chapterassociation'). Using that unique row within the second table ('chapterassociation'), the values of 'rgn_no' and 'chp_cd' should be UPDATED within the first table ('bue') to match the values within the second table ('chapterassociation').

I know this is probably asking a lot, but if someone could help me to construct a query to do this, it'd be wonderful! I really do want to learn, as I don't wish to be ignorant to this forever. Though I'm not sure if I completely understand how the JOIN and comparison here would work.

If I'm correct, I'll have to put this into seperate queries which will then be in PHP. So for example, it'll probably be a few IF ELSE statements that end with the final result of the final query, which updates the values from table two to table one.




A JOIN will do both level of matching for you...

    ON  bue.work_state = chapterassociation.work_state
    AND bue.bgu_cd     = chapterassociation.bgu_cd

The actual algorithm is determined by PostreSQL. It could be a merge, use hashes, etc, and depends on indexes and other statistics about the data. But you don't need to worry about that directly, SQL abstracts that away for you.

Then you just need a mechanism to write the data from one table to the other...

  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
WHERE bue.work_state = chapterassociation.work_state
  AND bue.bgu_cd     = chapterassociation.bgu_cd

People are also looking for solutions to the problem: forms - php / sql - login is failing


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.