[SOLVED] Help needed with mysql query and arrays

[giraffemedia]

Hi

 

I've got a mysql query using three joined tables and I can get the data from two of them fine, but I have a problem with the third one. table3 contains lot's of rows that have a foreign key that corresponds with the primary key from table2.

 

There are multiple entries for each table2 primary key in table3 and I would like to use the data as one long comma separated string of text as it contains issue numbers I would like the user to be able to see.

 

How can I get this information as an array whilst still getting table1 and table2 as normal? I have drawn some table below to show what I am trying to achieve...

 

 

table1 - contacts

contacts_id

     

contact_name

1

     

Homer

2

     

Marge

3

     

Maggie

4

     

Bart

5

     

Lisa

6

     

Barney

7

     

Ned

 

table2 - bookings

bf_id

     

bf_contacts_id

1

     

2

2

     

2

3

     

1

4

     

3

5

     

2

6

     

3

7

     

4

 

table3 - issues_booked

issue_id

     

issue_number

     

bf_id_number

1

     

60

     

2

2

     

61

     

2

3

     

62

     

2

4

     

58

     

1

5

     

59

     

1

6

     

60

     

1

7

     

61

     

1

8

     

62

     

1

9

     

45

     

3

10

     

46

     

3

11

     

47

     

3

12

     

48

     

3

13

     

49

     

3

14

     

50

     

3

15

     

70

     

4

16

     

71

     

4

17

     

72

     

4

 

I'd like it to look like the following...

 

Homer 45, 46, 47, 48 49, 50

Marge 60, 61, 62

 

etc etc

 

Can anyone help?

 

Regards

 

James

[scottybwoy]

I think this can be done, just using an SQL query, but I'm not sure of the syntax, your Joins are the right way about it though.  It would certainly be quicker.

[giraffemedia]

Anyone have any idea of what the syntax would be?

[giraffemedia]

Anyone?

[giraffemedia]

Would it be best to sort the issues into an array then a string first and then get the rest of the rows and match up the array results with the rows from the other tables?

 

Regards

 

James

[giraffemedia]

It's alive.....it's alive!!

 

I've managed to get it working by using a query to make an array for the issues booked and changing my main query to:

 

$get_booking_query = "SELECT DISTINCT bf_id, bf_company_id, section, contactonefirstname, pre_payment FROM booking_form AS b LEFT JOIN contacts AS c ON b.bf_company_id = c.contacts_id LEFT JOIN issues_booked AS i ON i.ib_booking_form_number = b.bf_id WHERE bf_id IN('".implode("', '", $issues)."') ORDER BY bf_id";