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

score 1 · Accepted Answer · edited May 23 '17 at 12:00

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 '17 at 12:00

Community

1
1

answered Jan 03 '13 at 15:05

burmat

2,548
1
23
28

i mean that the table goes on... the id's i gave were just a few of the entire table... – Laurent Jan 03 '13 at 15:23
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. – burmat Jan 03 '13 at 15:26
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? – Laurent Jan 03 '13 at 15:34
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. – burmat Jan 03 '13 at 15:38
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. – Laurent Jan 03 '13 at 15:56
If works exactly the same. For example, if you are using a `while` loop to loop through each row (your query result), just use `$row[i]` where `i` = your column number, or use `$row['column_name']` where `column_name` = the name of the column. In my answer, this can appear as: `$row['user_id']`, `$row['user_name']`, `$row['field_value']` etc.. If you use a `join`, the the column will be added to your result in the order it is placed in your query. In my answer, your search result would dump the following columns respectively: 'user_id', 'user_name', 'user_email', 'field_value'. – burmat Jan 03 '13 at 16:02

score 0 · Answer 2 · edited Jan 03 '13 at 15:03

0

How about:

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

edited Jan 03 '13 at 15:03

John Woo

258,903
69
498
492

answered Jan 03 '13 at 15:02

ethrbunny

10,379
9
69
131

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.. – Laurent Jan 03 '13 at 15:25
Either way - it's an example of how to join and select a subset of it. – ethrbunny Jan 03 '13 at 15:25

score 0 · Answer 3 · answered Jan 03 '13 at 15:04

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 03 '13 at 15:04

Samuel Cook

16,620
7
50
62

score 0 · Answer 4 · answered Jan 03 '13 at 15:06

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

score 0 · Answer 5 · answered Jan 03 '13 at 15:43

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.

MYSQL PHP add table values as rows

5 Answers5