[SOLVED] Select query causes output displayed to duplicate

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

[SOLVED] Select query causes output displayed to duplicate

Post by Janco »

Hi,

The following query causes the output displayed to duplicate:

Code: Select all

$query="select uni_bw_usr.user_number,uni_bw_usr.user_name,uni_bw_usr.user_level,uni_bw_usr.user_entry_menu,uni_bw_usr.user_branch_cd,bw_templates.user_number as template from uni_bw_usr,bw_templates where uni_bw_usr.user_level = bw_templates.user_level and uni_bw_usr.user_entry_menu = bw_templates.user_entry_menu and uni_bw_usr.user_op1 = bw_templates.user_op1 and uni_bw_usr.user_op2 = bw_templates.user_op2 and uni_bw_usr.user_op3 = bw_templates.user_op3 and uni_bw_usr.user_op4 = bw_templates.user_op4 and uni_bw_usr.user_op5 = bw_templates.user_op5 and uni_bw_usr.user_op6 = bw_templates.user_op6 and uni_bw_usr.user_op7 = bw_templates.user_op7 and uni_bw_usr.user_op8 = bw_templates.user_op8 and uni_bw_usr.user_op9 = bw_templates.user_op9 and uni_bw_usr.user_op10 = bw_templates.user_op10 and uni_bw_usr.user_op11 = bw_templates.user_op11 and uni_bw_usr.user_op12 = bw_templates.user_op12 and uni_bw_usr.user_op13 = bw_templates.user_op13 and uni_bw_usr.user_op14 = bw_templates.user_op14 and uni_bw_usr.user_op15 = bw_templates.user_op15 and uni_bw_usr.user_op16 = bw_templates.user_op16 and uni_bw_usr.user_op17 = bw_templates.user_op17 and uni_bw_usr.user_op18 = bw_templates.user_op18 and uni_bw_usr.user_op19 = bw_templates.user_op19 and uni_bw_usr.user_op20 = bw_templates.user_op20 and uni_bw_usr.user_op21 = bw_templates.user_op21 and uni_bw_usr.user_op22 = bw_templates.user_op22 and uni_bw_usr.user_op23 = bw_templates.user_op23 and uni_bw_usr.user_op24 = bw_templates.user_op24 and uni_bw_usr.user_op25 = bw_templates.user_op26 and uni_bw_usr.user_op26 = bw_templates.user_op26 and uni_bw_usr.user_name like '%$name%' order by uni_bw_usr.user_branch_cd"
This problem is resolved with the use of

Code: Select all

$query="select distinct .........
but I have another page with exactly the same code with a different table but the output is not duplicated there. I've checked to ensure that there aren't any duplicate records in the database table.

I've run the query from the PHP Page and within MySQL and the result remains the same on both.
Why would this query cause the rows displayed to duplicate if there aren't any duplications in the DB or if it has not been placed in a loop?
Last edited by Janco on Mon Jun 02, 2008 3:04 am, edited 1 time in total.
dml
Forum Contributor
Posts: 133
Joined: Sat Jan 26, 2008 2:20 pm

Re: Select query causes output displayed to duplicate

Post by dml »

What do uni_bw_usr and bw_templates represent, what is the relationship between them (one to one? one to many? many to many?), and how is that relationship represented and enforced in the database (does one table point to the id of another table? is there any kind of foreign key or uniqueness constraint?)
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: Select query causes output displayed to duplicate

Post by Janco »

uni_bw_usr and bw_templates contain the exact same columns.

I first have to sketch the scenario, maybe that will clear things up.

This DB is used to keep user information, user roles, user options etc.

We use a Cobolt base Point of Sale Software and in the software the above mentioned is defined by certain flags in certain fields.

What we have done is to create template for each role which is stored in the templates tables so that we don't have to specify the flags every time we create users for a store, there are 78 different fields that we need to specify so you can see the need for templates.

The uni_bw_usr table is the user table and contains the exact same columns as that of the templates table. Here users are stored per branch and the query is basically there to show the template which was used to create the user by comparing data in the fields specified.

I have not specified all 78 fields only a couple - I was too lazy to specify 78 x 2 and my typing is not that fast.

Me thinks.. that the relationship will be many to many???

As I said in the original post, the resolution is to use the

Code: Select all

SELECT DISTINCT
which works but I just wanted to know why the DB will duplicate the output in the one page or on the table and not in another.
dml
Forum Contributor
Posts: 133
Joined: Sat Jan 26, 2008 2:20 pm

Re: Select query causes output displayed to duplicate

Post by dml »

Sounds complicated. If I understand correctly, many users can be created from one template, and each user can only be created from one template, so it's many users to one template. Normally the way to keep track of that would be to have a template_id column in the user table. If you're trying to find the template by matching the fields, there's going to be problems if the user or template data changes (and if the user data doesn't change, then why store it separately from the template?), or if there are two templates with the same columns (which may be what's happening here).
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: Select query causes output displayed to duplicate

Post by Janco »

Okay, you're right about the many users to one template....it's quite logical.....don't know why I didn't think of it that way.
or if there are two templates with the same columns (which may be what's happening here
The reason why I match the fields is a temporary measure because what the aim is to add a column named template_used to the user table and then store the template name used when the user was created in that column. At this point, because I did not design the DB, when we want to find out what access the user has we have to run a query like the one in the original post.

What bugs me, in your reply you might have hit the nail on the head where it shows all matching rows, meaning that for every match it will display the row and causing duplicates, but why is it only happening on the one table and where the same code is used with different tables it doesn't.

My next question is that if I run the query statement and take the result and write it into the the added column, will it result in duplicate rows being added, on the other hand it shouldn't if I use the

Code: Select all

select distinct
then write it to the column?

I'll run the test and see what happens but I think as far as this post goes it can be set to SOLVED because the answer was in the original post with the use of select distinct and I don't want to waste anyone else's time.
Post Reply