aligning multiple query results

[kiqyou_vf]

I'm trying to pull data from 2 different tables and do a loop to
retrieve more than one row. I'm having problems with aligning the
information. Can someone lead me in the right direction? I've done some
looking myself and found something called GROUP BY? Is that what I need
to use? Thanks in advance.

[Jerry Stuckle]

Re: aligning multiple query results

kiqyou_vf wrote:[color=blue]
> I'm trying to pull data from 2 different tables and do a loop to
> retrieve more than one row. I'm having problems with aligning the
> information. Can someone lead me in the right direction? I've done some
> looking myself and found something called GROUP BY? Is that what I need
> to use? Thanks in advance.
>[/color]

Sorry, my mind reading isn't working this morning.

It's impossible to tell without knowing the structure of your tables,
the code you're using and the output you expect.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[David Haynes]

Re: aligning multiple query results

Jerry Stuckle wrote:[color=blue]
> kiqyou_vf wrote:[color=green]
>> I'm trying to pull data from 2 different tables and do a loop to
>> retrieve more than one row. I'm having problems with aligning the
>> information. Can someone lead me in the right direction? I've done some
>> looking myself and found something called GROUP BY? Is that what I need
>> to use? Thanks in advance.
>>[/color]
>
> Sorry, my mind reading isn't working this morning.
>
> It's impossible to tell without knowing the structure of your tables,
> the code you're using and the output you expect.
>[/color]
Sounds more like a UNION problem, but Jerry is right. We need more
information to make reasonable comments.

-david-

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[David Haynes]

Re: aligning multiple query results

kiqyou_vf wrote:[color=blue]
> I have a page that displays multiple search results. On each search
> result I want unique information from two or more tables to be
> displayed. All tables have a "client_id" column. I need the main query
> to display results based on the $_GET['keywords'] and a second table to
> display results based on the "client_id" of the main query. I don't
> have any code as of yet to show you. I have done some research on UNION
> and found this on mysql.com:
>
> "Selected columns listed in corresponding positions of each SELECT
> statement should have the same type. (For example, the first column
> selected by the first statement should have the same type as the first
> column selected by the other statements.) The column names used in the
> first SELECT statement are used as the column names for the results
> returned."
>
> Is this going to be a problem since the names and information are going
> to be different on each of my tables.
>[/color]
It sounds more like you want an inner join not a union (unless the
tables are from different data sources). It's hard to advise without
some concrete details, but it *sounds* like you have a table 'a' with a
client_id and some other values and a table 'b' with a client_id and
some other values and you want to show values from a and b.

So, the select would be something like:
select
a.column1,
a.column4,
b.column7
from
a,
b
where
a.client_id = b.client_id <-- inner join
and (any other conditions)

-david-

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays search results. With each entry returned, I
want to be able to display rows from 2 or more tables.

table.qcbd has a keywords column that I want to search and
table.qcbd_home has a description that I want to include in the search
results. So if a search for "cups" returned 8 results, I would want
each result to have information from table.qcbd AND table.qcbd_home .
I'm not exactly sure how to go about doing this. I've tried some really
messy coding, putting a query INSIDE of the while loop, lol- that
didn't work.

This is not the code, but I'll describe what I want to do like this:
SELECT * FROM qcbd, qcbd_ghours WHERE keywords = $_GET['search']

I would then do a loop and extract all of the returned rows from both
tables. The problem I'm running into is that the returned rows dont
line up.

Hope this clears things up. As for the code, I don't really have
anything of use to show you. Thanks!

[David Haynes]

Re: aligning multiple query results

kiqyou_vf wrote:[color=blue]
> I have a page that displays search results. With each entry returned, I
> want to be able to display rows from 2 or more tables.
>
> table.qcbd has a keywords column that I want to search and
> table.qcbd_home has a description that I want to include in the search
> results. So if a search for "cups" returned 8 results, I would want
> each result to have information from table.qcbd AND table.qcbd_home .
> I'm not exactly sure how to go about doing this. I've tried some really
> messy coding, putting a query INSIDE of the while loop, lol- that
> didn't work.
>
> This is not the code, but I'll describe what I want to do like this:
> SELECT * FROM qcbd, qcbd_ghours WHERE keywords = $_GET['search']
>
> I would then do a loop and extract all of the returned rows from both
> tables. The problem I'm running into is that the returned rows dont
> line up.
>
> Hope this clears things up. As for the code, I don't really have
> anything of use to show you. Thanks!
>[/color]
Do table.qcbd and table.qcbd_home have a column of data that is a)
unique row in each table and b) defines some relationship between the
two tables? If so, you need to join the two tables based upon that
column. If not, the two tables are probably going to be joined with a
sub-select.

To join two tables you need to have an element in common to join them
on. The result may be one or more rows depending upon the nature of the
data in the joining columns.

It sounds like you want something like:

select qcdb_hours
from gcdb_home
where ??? in (
select ????
from qcdb
where keywords = '$_GET['search']'
)

At any rate, this seems to be more of a SQL problem rather than a PHP
problem. I would suggest you move your query over to one of the mysql
news groups.

-david-

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results

Hey, thanks a lot for pointing me in the right direction. I'll head on
over to a mysql group. Sorry about multiple posts, Google has been
acting up for me. THANKS!

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results


David Haynes wrote:[color=blue]
> Jerry Stuckle wrote:[color=green]
> > kiqyou_vf wrote:[color=darkred]
> >> I'm trying to pull data from 2 different tables and do a loop to
> >> retrieve more than one row. I'm having problems with aligning the
> >> information. Can someone lead me in the right direction? I've done some
> >> looking myself and found something called GROUP BY? Is that what I need
> >> to use? Thanks in advance.
> >>[/color]
> >
> > Sorry, my mind reading isn't working this morning.
> >
> > It's impossible to tell without knowing the structure of your tables,
> > the code you're using and the output you expect.
> >[/color]
> Sounds more like a UNION problem, but Jerry is right. We need more
> information to make reasonable comments.
>
> -david-[/color]


I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.

[kiqyou_vf]

Re: aligning multiple query results

I have a page that displays multiple search results. On each search
result I want unique information from two or more tables to be
displayed. All tables have a "client_id" column. I need the main query
to display results based on the $_GET['keywords'] and a second table to
display results based on the "client_id" of the main query. I don't
have any code as of yet to show you. I have done some research on UNION
and found this on mysql.com:

"Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column
selected by the first statement should have the same type as the first
column selected by the other statements.) The column names used in the
first SELECT statement are used as the column names for the results
returned."

Is this going to be a problem since the names and information are going
to be different on each of my tables.