[RESOLVED] select query from 2 tables

[dcjones]

Hi all.

I have 2 tables, both of which contain a feild which holds an id number.

table = int_bookings
feild name = booking_id, TYPE =int
feild name = course_id, TYPE = int
table int_courses
feild name id, TYPE = int

I want to call records from the "int_bookings" table using the "booking_id" and show all the feilds along with the record from the "int_courses" that has the same "course_id".

This is what I have so far.

SELECT *
FROM int_course_booking, int_courses
WHERE int_course_booking.booking_id = int_courses.id 

I know that this is not correct, because it don't work. Am I far off or is it just some adjustment required.

Many thanks for any help you can give

[ZibingsCoder]

Might be more appropriate to do a JOIN statement, as such:

SELECT * FROM int_course_booking AS icb LEFT JOIN int_courses AS ic ON icb.booking_id=ic.id

[dcjones]

Hi and many thanks for your reply.

Sorry for returning my thanks late.

The code work fine but I need to know how it works so I can learn a little.

Can you please explain what the code does.

SELECT * FROM int_course_booking AS icb LEFT JOIN int_courses AS ic ON icb.booking_id=ic.id

SELECT * FROM int_course_booking

That all feild from the int_course_booking TABLE
This part I understand.

Where does icb come in?

Many thanks again

[rincewind456]

Where does icb come in?

In this case icb is an alias for "int_course_booking" it saves having to type it out every time. The same as ic is an alias for "int_courses".

[dcjones]

Thanks for that, now I see what is happening.

[dcjones]

Hi again,

This is the SQL statement I am now using.

SELECT *
FROM int_course_booking AS icb LEFT JOIN int_courses AS ic ON icb.booking_id=colname

The complete SQL statement looks like this.

$colname_booking = "-1";
if (isset($_GET['booking_id'])) {
  $colname_booking = (get_magic_quotes_gpc()) ? $_GET['booking_id'] : addslashes($_GET['booking_id']);
}
mysql_select_db($database_int_courses, $int_courses);
$query_booking = sprintf("SELECT * FROM int_course_booking AS icb LEFT JOIN int_courses AS ic ON icb.booking_id=%s", GetSQLValueString($colname_booking, "int"));
$booking = mysql_query($query_booking, $int_courses) or die(mysql_error());
$row_booking = mysql_fetch_assoc($booking);
$totalRows_booking = mysql_num_rows($booking);

The page is called by another with the following:

?booking_id= "Which ever record is clicked on in the list". So it could be some thing like

...?booking_id=12987

which calls the booking record 12987 from the "int_course_booking" table. The content of the record is displayed but the data from the "int_courses" table is not.

Any ideas

Many thanks

[dcjones]

Hi all,

Done it. I used the following

$colname_booking = "-1";
if (isset($_GET['booking_id'])) {
  $colname_booking = (get_magic_quotes_gpc()) ? $_GET['booking_id'] : addslashes($_GET['booking_id']);
}
mysql_select_db($database_int_courses, $int_courses);
$query_booking = sprintf(" SELECT int_course_booking.*,  int_courses.* FROM int_course_booking,  int_courses WHERE int_course_booking.course_id=int_courses.id AND int_course_booking.booking_id=%s", GetSQLValueString($colname_booking, "int"));
$booking = mysql_query($query_booking, $int_courses) or die(mysql_error());
$row_booking = mysql_fetch_assoc($booking);
$totalRows_booking = mysql_num_rows($booking);

all it needed was the AND to link it to the "booking_id"

Many thanks for all your help