Hey guys, I've got a pretty standard table setup...

I have a bookings table called "tod_bookings" ... and a company information table called "tod_company_information".

The "tod_bookings" contains a booking_id, and a company_id.

The company_id obviously is a foreign key that comes from the "tod_company_information" table... thus creating a relationship.

Problem is... I need to search both these tables at once... and I don't know how to do it!!

My friend gave me this peice of code...

$query="select tod_bookings.booking_id tod_bookings.company_id from table1,table2 $filter_string ASC";

It doesn't work, I dunno..... it doesn't look right.. but is it along the right tracks?

I need to do WHERE clauses on the query... thats why I'm using:

$filter = array();

if(($_POST['tyre_id'])) {
$filter[] = "tyre_id='".$_POST['tyre_id']."'"; }

if($_POST['company_contact_name']) {
$filter[] = "company_contact_name='".$_POST['company_contact_name']."'"; }

if($_POST['company_name']) {
$filter[] = "company_name='".$_POST['company_name']."'"; }

if(count($filter) > 0) {
$filter_string = "WHERE ". implode(' AND ', $filter);
} else {
$filter_string = "";
}

I would really appreciate any help...

Thanks in advance

crE :)

There are multiple ways to go about this. What exactly are you trying to do? Give the fields that are involved in the query also please.

I'll take a screeny of the form, it'll make things easier...

Ignore the errors: http://www.eeeep.com/stuff/tt-serchproblem.jpg

crE

Maybe I'm not getting it. Ok, you want to search by company, contact name or tyre and you want what results to be returned? Use the tablename.fieldname.

Ok I'll try and explain it easier....

I want to search for a booking using 3 fields.... I'm going to need to search the company_name... the company_contact_name.... and the tyre_id......

The booking record contains the tyre_id ONLY... along with the ID of the company and that will need to be searched.

The ID of the company creates the relationship.. so therefore in turn I will need to search the company information relevant to the ID in the booking, for the details "company_name" and "company_contact_name".

I'm confused on how to make a query that does that :S?

This is what I have at the moment .. which is obviously wrong...

$query="select * from tod_bookings $filter_string tod_company_information $filter_string2 from table1,table2 ASC";

Any ideas?
crE

I'm still confused but not as much anymore.

So, you want to return the records that match the fields specified

You are trying to return all fields from both tables...so you want something like...

SELECT tod_bookings.*, tod_company_information.* FROM tod_bookings, tod_company_information $filterstring

is that right? Because that's what it seems, but from what you're describing, you want soemthing like

SELECT * FROM tod_bookings INNER JOIN tod_company_information ON tod_bookings.company_id = tod_company_information.id $filterstring ORDER BY booking_id ASC

I hope that can point you in a better direction but it's still pretty hard to decipher what you're trying to do.

Ok I did a diagram :D:

http://www.eeeep.com/misc/tt-diagram-serch2.jpg

crE

That's something I can work with. OK, lemme try:

SELECT b.*, c.* FROM tod_company_information c, tod_bookings b INNER JOIN tod_bookings ON c.company_id = b.company_id $filterstring ORDER BY b.booking_id ASC

also, change $filterstring to say like c.company_name = $_POST and c.company_contact_name = $_POST and b.tyre_id = $_POST, but put in the values for the post variables.

What you are trying to do is a little tricky though because if you just search by company name then there might not be a booking id for that. But, tell me how it goes.

omg dude you're the man :D

It's sorted now, you wouldn't believe how long i've been trying to finds that out!!!

All I need to do now is figure out why my urlencode syntax doesnt semm to be working lol

thanks a lot man !

crE

Hi ,

You can use Inner Joins to merge two tables. Inner Join is used when you would like to select one or more rows from two or more related tables if both tables are having same column and data.

For example

Table Name : Users

UserID GroupID Name

1 1 Mason
2 1 Beric
3 2 Slavic
4 5 Tammy
5 3 Dave

Table Name : Groups

GroupID GroupName

1 Cool Guys
2 Banned Members
3 Sleepy Peppers


Then you can use as

[SQL Statement]


SELECT Users.UserID, Users.Name, Groups.GroupName
FROM Users INNER JOIN Groups ON Users.GroupID = Groups.GroupID

Now it will give output as

UserID Name GroupName

1 Mason Cool Guys
2 Beric Cool Guys
3 Slavic Banned Members
5 Dave Sleepy Peppers


Hope this info will provide you enough for your task.

Regards,

Bright
:)

Thanks brightadmin, I appreciate your help, and you unlucky1. That's gonna make things a hell of a lot easier in the future!!

Anyway better get back to work, PC World, weyhey! Don't wanna get fired.

Thanks again.

crE