How do I select data from multiple tables where...

[giraffemedia]

Hi guys

 

i'm having trouble selecting data from two tables I have set up. One of them lists magazine issue numbers, the other contains a comma separated field of magazine issues that an advert has been booked into. They are as follows...

 

Magazines

 

issues_id issue_number

3 59

4 60

5 61

6 62

7 63

8 64

9 65

 

etc..

 

and the booking form details...

 

bf_id bf_number_insertions bf_issues_booked

1234             6         65,66,67,68,69,70

1235             6 64,65,66,67,68,69

1236             7 67,68,69,70,71,72,73

1237             6 61,62,63,68,69,70

1238             13 60,61,62,63,64,65,66,67,68,69,70,71,72

1250             13 60,61,62,63,64,65,66,67,68,69,70,71,72

1251             6 65,66,67,68,69,70

1252             6 65,66,67,68,69,70

1253             11 60,61,62,63,64,65,66,67,68,69,70

 

What I want to do is select everything from a row in the booking form table where an issue number is found within the bf_issues_booked column.

 

Can anyone help please?

 

Regards

 

James

[wildteen88]

Have a look into the FIND_IN_SET MySQL function.

 

EDIT: Example query:

SELECT b.id, m.issue_number

  FROM bookings as b

  LEFT OUTER JOIN magazines as m

    ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0

WHERE m.issue_number = 64

 

Change bookings and magazines to your actual table names

[giraffemedia]

I can't get that to work wildteen.

 

What about...

 

while ($array = mysql_fetch_array($find_issues_result))
{
$data[] = $array[1];
}

//Test to see if working
echo implode(',', $data );

$get_booking_query = "SELECT * FROM booking_form,issues WHERE issues.issue_number IN ('" . implode("','",$data) . "')";

$get_booking_result = mysql_query ($get_booking_query);

if (!$get_booking_result) { 
echo ("Problem because " . mysql_error());
}
?>

<p>The bookings containing these issues were...</p>

<? while ($booking_row = mysql_fetch_array($get_booking_result, MYSQL_ASSOC)) {

$booking_id = $booking_row['bf_id'];

echo $booking_id . '<br /><br />';
}

 

This works, except I get duplicate entries as the query returns rows for every single number found in the string. Is there a way to say if row has already been selected don't select it again?

[giraffemedia]

This works fine...

 

$get_booking_query = "SELECT * FROM booking_form as b LEFT OUTER JOIN issues as m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number = 60";

 

But it will obviously only select all rows where the issue_number = 60. How can I change this so that it selects from more than one issue_number i.e. the WHERE bit has more than one number like an array? Something like...

 

$array = array(60, 61, 62);

$get_booking_query = "SELECT * FROM booking_form as b LEFT OUTER JOIN issues as m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number = $array";

 

The problem is, this will look for all rows that have 61,62 AND 63 in them, not if they have 61 or 62 or 63 or a combination.

 

Can anyone help please?

 

Regards

 

James

 

 

[xstevey_bx]

WHERE m.issue_number IN $array?

 

I did this in one of my news scripts but I dont know if it is correct. $array was just a comma sepperated list

 

$array = 1,2,3,4,5;

[wildteen88]

I tested my query before posting it and it works fine for me, did it return any errors. Looking over the query I did have a slight typo b.id should of been b.bf_id.

 

NOTE: I setup a test database with the data you posted in your first post.

 

I did forget to mention to change the number 64 at the end of the query to what ever issue number you're searching for. If it finds the issue number in the bf_issues_booked column it'll return the bf_id and issue_number. The query can be adapted to return what ever columns you want.

 

EDIT: Ignore me, I didn't see the above two replies when posting this

[giraffemedia]

WHERE m.issue_number IN $array?

 

I did this in one of my news scripts but I dont know if it is correct. $array was just a comma sepperated list

 

$array = 1,2,3,4,5;

 

I tried that and I get the following error...

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1

 

Wildteen, it works fine if I give just the one issue number, but if I want to search for all issues returned from another query, i.e. an array with multiple issue numbers it won't work.

 

I want to do this because I want the user to be able to choose issues within a date range on a form. The issue(s) needed for the query come from the results of this in an array.

 

Regards

 

James

[xstevey_bx]

Woops sorry

 

$array = "1,2,3,4,5";
$get_booking_query = "SELECT * 
                                FROM booking_form as b 
                                LEFT OUTER JOIN issues as m 
                                ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 
                                WHERE m.issue_number 
                                IN ($array)";

[giraffemedia]

Woops sorry

 

 

No problem - I appreciate your help.

 

I've managed to get it working by using DISTINCT bf_id in my mysql query. The problem with using an array for the IN part of the SELECT query is that I get duplicate results as a result is returned for each number found in a row. So if 63, 64 and 65 are in the comma separated string for one row that row will come up 3 times in the results - not what I want.

 

$issues = implode(',', $data );

$get_booking_query = "SELECT DISTINCT bf_id, bf_issues_booked FROM booking_form as b LEFT OUTER JOIN issues as m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number IN ($issues)";

 

Regards

 

James

[giraffemedia]

Hi guys,

 

i've got a new query relating to this post.

 

What I would like to do is add another table to the mysql query using something like INNER JOIN.

 

I have another table called contacts that holds all the information about the people that are making the bookings. In this table the primary key number matches the one found in the bf_company_id column of the booking_form table.

 

How can I add another table to the query...

 

$get_booking_query = "SELECT DISTINCT bf_id, bf_issues_booked, bf_company_id, bf_sales_contact FROM booking_form b LEFT OUTER JOIN issues m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number IN ($issues) ORDER BY $order_by LIMIT $start, $display";

 

... that will allow me to select rows from the contacts table at the same time. I've tried several combinations but cannot seem to get the syntax right. I was thinking of something like this...

 

$get_booking_query = "SELECT DISTINCT bf_id, bf_issues_booked, bf_company_id, bf_sales_contact FROM booking_form b LEFT OUTER JOIN issues m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number IN ($issues) INNER JOIN contacts c ON b.bf_company_id = c.contacts_id WHERE b.bf_company_id = c.contacts_id ORDER BY $order_by LIMIT $start, $display";

 

Does this make sense?

 

I really need to get this sorted asap so if anyone could chip in any advice i'd be very grateful.

 

Regards

 

James

 

 

[DarkWater]

Just a little insight on the original post:

 

That's why you try to normalize databases and NEVER use lists of values in a single column.

 

You could then have a simple join rather than:

$get_booking_query = "SELECT DISTINCT bf_id, bf_issues_booked FROM booking_form as b LEFT OUTER JOIN issues as m ON FIND_IN_SET(m.issue_number , b.bf_issues_booked ) > 0 WHERE m.issue_number IN ($issues)";

 

You may want to honestly fix your database setup before going further to avoid any future problems.

[giraffemedia]

That's why you try to normalize databases and NEVER use lists of values in a single column.

 

You may want to honestly fix your database setup before going further to avoid any future problems.

 

Hi Darkwater,

 

I can see your point, but I honestly don't have a clue about a better way of doing this. Each contact can have multiple booking forms that contain a different number of issues booked on each of them. I'm not sure how else I would go about this and NOT using the comma separated values in the bf_issues_booked column. Can you enlighten me?

 

Regards

 

James

[DarkWater]

Firstly, read up on database normalization.  That's what this is called.  Now, for what you'd do...You'd have a table correlating booking forms that were issued to the other table.  For example (with the data you provided):

 

magazines: issues_id  issue_number

booking_forms: bf_id    bf_number_insertions

booking_forms_issued: bf_id issues_id

 

Then you just do a join.

[giraffemedia]

I understand that Darkwater, and my tables have been designed to be as normalized as possible.

 

I don't understand how your method would store the details in the booking_forms_issued table for each booking form as they are all different.

 

I have the query working fine using the arrangement I have - all I want to do is join another table to the query asking if the bf_company_id in the booking_form table matches the value of the company_id in the contacts table (which it does as i've tested it). If so, I would like to use the contacts table information on the same page, rather than have to perform another query.

 

Regards

 

James

[DarkWater]

It stores the information pertaining to ONE booking form record in the booking_forms table and the booking_forms_issued allows you to create one-to-many relationships from both sides of the booking_forms_issued table.

[giraffemedia]

Sorry for being a bit noobish here but I don't understand the how the issues_booked table will look. If I have the following tables

 

issues

 

issues_id issue_number

3 59

4 60

5 61

6 62

7 63

8 64

9 65

 

etc..

 

booking_form

 

bf_id bf_number_insertions

1234             6

1235             6

1236             7

1237             6

1238             13

1250             13

 

will the issues_booked table need to look like...

 

issue_booked_id   issue_numbers    booking_form_id

3                   59                    1234

4                   60                    1234

5                   61                    1234

6                   59                    1235

7                   60                    1235

8                   59                    1236

9                   62                    1237

 

etc..

 

then when I join the tables I can ask WHERE issue_numbers = 60 and it will work?

 

Regards

 

James

[DarkWater]

SELECT * FROM booking_form AS bf INNER JOIN issues_booked AS ib ON ib.booking_form_id = bf.bf_id WHERE ib.issue_numbers = 60;

 

Something like that.

[giraffemedia]

ok, how would insert these using a form and insert each issue as a new row, and if I wanted to change the issues booked by adding another issue or changing the amount of issues booked into, how would I go about making sure the rows were either updated or deleted. For instance, if I searched for booking_form 124 in the issues_booked_into table and it says that it had been booked into 59, 60, 61, 62 and 63, and I wanted to change them to 59, 60, 63 and 64, how would I delete 61 and 62 and add 64?

 

Regards

 

James

[DarkWater]

array_diff.  I wrote up something similar recently, here's what I did (in terms of my system.  It may need changing in yours, for example, you have no user system for this):

 

1) Assemble all the ones from the form in an array, which I did with checkboxes.

2) Query the database to get all the IDs of games for that specific user.

3) Assemble those IDs into one big array.

4) Check and make sure that the arrays are different, otherwise don't bother. 

5) Then I did something like:

if ($tracked != NULL) {

    $add = array_diff($all, $tracked);

    $delete = array_diff($tracked, $all);

}

else {

    $add = $all;

            $delete = NULL;

}

This assembles them all into arrays of ones that need to be added and deleted.

6) I did a check to see whether $add was null.

7) If it wasn't, did a query by looping through the array and adding them all to an INSERT query using multi-insert syntax.

Same for delete, except I just join(',')'d them and then using IN() to delete them.

 

Pretty simple once you get it done.

[DarkWater]

That was supposed to be 8 followed by a ), not that smiley. >_>  *Shrug*