MYSQL PHP add table values as rows

Question

I am trying to join 2 MySQL tables, but I can't manage to help myself using the examples online.

The table dat_eb_field_values countains 3 rows: registrant_id, field_id and field_value.

My second table: dat_eb_registrants contains normal rows such as user_id, first_name, last_name ect.

I am trying to get those 2 tables together so that the row: field_value can be used just like the other values from dat_eb_registrants.

Currently I am using the following method, but since the query's are not connected, I can't sort the output on field_value from the table dat_eb_field_values.

$result = mysql_query("SELECT user_id,first_name,last_name,email FROM `dat_eb_registrants`") 
while ($row = mysql_fetch_row($result)) {

    $result1 = mysql_query("SELECT field_value FROM field_values WHERE (field_id = 53) AND (user_id = $row[0])"); $r1 = mysql_fetch_row($result1); echo $r1[0];
    echo $row[2];
    $result2 = mysql_query("SELECT field_value FROM dat_eb_field_values WHERE (field_id = 54) AND (user_id = $row[0])"); $r2 = mysql_fetch_row($result2); echo $r2[0];
}

end so on....

Community · Accepted Answer · 2017-05-23 12:00:06Z

1

Maybe just use a join? Try the following query:

SELECT users.user_id, users.user_name, users.user_email, field_values.field_value 
FROM users LEFT JOIN field_values ON users.user_id=field_values.user_id

You can also append a WHERE clause on the back of that too, such as:

WHERE field_values.field_id='53'

If you need some more explainations on JOIN, refer to this great answer: https://stackoverflow.com/a/5874895/586859

When you run this, you can just loop through your results as normal, but all of your values should be contained in one spot.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Jan 3, 2013 at 15:05

burmat

2,5481 gold badge23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Astrid Over a year ago

i mean that the table goes on... the id's i gave were just a few of the entire table...

burmat Over a year ago

That's why I put the WHERE clause following the initial query - just to inform that it will work perfectly fine if you need to include it in a specific instance.

Astrid Over a year ago

ok, so i tried that, but it gives an error saying: 'Could not query: not unique table/alias: 'field_values'' any idea what went wrong?

burmat Over a year ago

Maybe it has something to do with you changing the names of your tables in your original question. Make sure you change the names of the tables in the query I provided and check the spelling. The concept works and if you need to add another join (for a third table to query), just append it using the same method and update the table names.

Astrid Over a year ago

ok ,you might have just saved part of my life :) but how can i now echo these because formerly i used '$row[0], $row[1], ect.' for the data in users, and for the data in field_values, i made new query's. is there a way to have the '$row[3]' data continue using the data from field_values? thanks.

|

John Woo · Accepted Answer · 2013-01-03 15:03:54Z

0

How about:

select * from users join field_values using( user_id ) where field_id in (53,54);

edited Jan 3, 2013 at 15:03

John Woo

265k70 gold badges509 silver badges500 bronze badges

answered Jan 3, 2013 at 15:02

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

2 Comments

Astrid Over a year ago

the tables go on, my example is just a small part of the entire table, so 53 and 54 are simple 2 out of 1232 records..

ethrbunny Over a year ago

Either way - it's an example of how to join and select a subset of it.

Samuel Cook · Accepted Answer · 2013-01-03 15:04:21Z

0

JOIN can help you here:

SELECT u.user_id,u.user_name,u.user_email,f.field_value FROM users u LEFT JOIN field_values f ON f.field_id=u.user_id

This is assuming that field_id matches user_id

answered Jan 3, 2013 at 15:04

Samuel Cook

16.9k7 gold badges56 silver badges65 bronze badges

Comments

fd8s0 · Accepted Answer · 2013-01-03 15:06:19Z

0

I think what you want is something like this

select u.*, v1.field_value as field_53, v2.field_value as field_54 
from users u 
join values as v1 on v1.user_id = u.user_id AND v1.field_id = 53 
join values as v2 on v2.user_id = u.user_id AND v2.field_id = 54;

Though it's a double join and it's quite horrid I believe...

answered Jan 3, 2013 at 15:06

fd8s0

1,9251 gold badge17 silver badges29 bronze badges

Comments

Rob Forrest · Accepted Answer · 2013-01-03 15:43:38Z

0

As everyone has said, the INNER JOIN is undoubtedly what you're after and its looking like a double join.

To speed up whatever result you go for make sure that you index the columns upon which you are joining. This can change a 30sec query into a .3sec one.

answered Jan 3, 2013 at 15:43

Rob Forrest

7,4809 gold badges55 silver badges69 bronze badges

Collectives™ on Stack Overflow

MYSQL PHP add table values as rows

5 Answers 5

6 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related