MySQL 2 table search and output?

[safra]

Hi,

can't get this to work:

2 tables:

table x: about individual projects
table y: about who are hosting these projects (most hosts have several projects going)

The search must be done for table x

3 of the search options (col 1,4 and 5) are set in table x but 2 of them are set in table y (col 2 and 3) because this is information about the hosts.

Column 'host' in table y is the same as column 'ref' in table x.

How can I get all search results and print them out in rows like this:

[col 1 table x] [col 4 table x] [col 5 table x] [col 2 table y] [col 3 table y]

hope anyone can give some hints on how to approach this.

Thanks,
Ron

 

[DumTech]

If I understand what you want correctly, your select statement should look something like this:

select x.col1,x.col4,x.col5,y.col2,y.col3 from x,y where y.host=x.ref Matt
matt@paperlove.org
If I can help, I will.

 

[safra]

Thanks for the quick reply Matt,

As I am just starting with MySQL I am not sure if that is what I need.

What will your query return?

What I need is first set the 'where' condition. I use a technique Anikin explained to me. Go through all possible search options of the form. If it needs to be included in the 'where' condition push it into an array and use implode array in the final 'where' condition in the mysql query.

I don't think your suggestion will completely work for that.

example search:
find all entries for:
x.col1 = "projectA"
x.col2 > 100.000
x.col5 = "red"
y.col2 = "companyX"
y.col3 = "product1"

Then on the results page each row that was found should show the values for each column. (values for all these columns should always been shown regardless whether the column was included in the 'where' condition or not.)

Thanks,
Ron

 

[DumTech]

The WHERE clause I gave simply joins the two tables. That SELECT would just return each row of x and its corresponding row from y. Definitely not what you want!

I've yet to do anything with PHP/MySQL involving a search like that though, so I guess we'll have to wait on Anikin to come around again...
Matt
matt@paperlove.org
If I can help, I will.

 

[safra]

Hi again Matt,

Yes, I don't think I can use that as 'ref' in x is not unique.

Rows in x can have the same value for 'ref'.

Thanks anyway,
Ron

 

[DumTech]

ref wouldn't have to be unique; it just determines what row from x corresponds to a given row from y, or put another way, what column to join on.

In other words, if you want your results to contain both project and host information for that project, you'll have to include in the query a way for MySQL to tell which row from the hosts table goes with a given row from the projects table.

ref  projectname  data  data    host  data
me   projectA     ???   ???     me    ???
|<--- from table x -------->|  |<-from y->|
[\code]

Since ref=me and host=me, the host data is the right host data for the given project.

At any rate, whatever the search values from your form wind up being, I imagine you'll need that where clause also.

Good luck! Matt
matt@paperlove.org
If I can help, I will.

 

[safra]

So in the example above the query will look something like this:

select x.col1,x.col4,x.col5,y.col2,y.col3 from x,y where y.host=x.ref and x.col1 = "projectA" and x.col2 > 100.000 and x.col5 = "red" and y.col2 = "companyX" and y.col3 = "product1"

I will try that,

Thanks

 

[safra]

Thanks Matt!

Think I got it working with this:

SELECT x.col1,x.col4,x.col5,y.col2,y.col3 FROM x JOIN y WHERE y.host=x.ref and x.col1 = "projectA" and x.col2 > 100.000 and x.col5 = "red" and y.col2 = "companyX" and y.col3 = "product1"

Ron