Makeing multiple MySQL queries in a single query.

[Daz]

Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.

My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.

Would anyone know if there is something I can do to get around this
problem?

Any input would be appreciated.

Thanks.

Daz

[cseymour]

Re: Makeing multiple MySQL queries in a single query.

Hi Daz,
Are the data being return from the 2 queries similar? Have you looked
into using a UNION?

For example:

select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
from address_add
Union
select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
from boardmembers_br d


Just a thought.

Chris


Daz wrote:

Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.
>
My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.
>
Would anyone know if there is something I can do to get around this
problem?
>
Any input would be appreciated.
>
Thanks.
>
Daz

[Daz]

Re: Makeing multiple MySQL queries in a single query.


cseymour wrote:

Hi Daz,
Are the data being return from the 2 queries similar? Have you looked
into using a UNION?
>
For example:
>
select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
from address_add
Union
select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
from boardmembers_br d
>
>
Just a thought.
>
Chris
>
>
Daz wrote:

Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.

My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.

Would anyone know if there is something I can do to get around this
problem?

Any input would be appreciated.

Thanks.

Daz

Hi Chris.

Thanks for your input. Unfortunately, UNION will not work for what I
need it for. I need to get the data from several different tables for
different users. I was forced into breaking everything up into separate
tables due to the MySQL restrictions and in the interest of keeping the
data optimized and the database query response times fast. As a result,
the number of colums is different for each of the 26 tables (ranging
from 6 columns, to 530 columns). It could just be a flaw in my design,
however, it would be fantastic to be able to put lots of SELECT queries
together into a single query, and get all of the results back in the
form of an array of arrays.

Thanks again.

[cseymour]

Re: Makeing multiple MySQL queries in a single query.

Hi Daz,
Couldn't you create a PHP function that would fire all your queries
then return the results as an array, kind of like createing the "array
of arrays" you had mentioned?

Just a thought.

Good luck. I'll keep chewing on this one for a bit.

Chris
Daz wrote:

cseymour wrote:

Hi Daz,
Are the data being return from the 2 queries similar? Have you looked
into using a UNION?

For example:

select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
from address_add
Union
select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
from boardmembers_br d


Just a thought.

Chris


Daz wrote:

Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.
>
My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.
>
Would anyone know if there is something I can do to get around this
problem?
>
Any input would be appreciated.
>
Thanks.
>
Daz

>
Hi Chris.
>
Thanks for your input. Unfortunately, UNION will not work for what I
need it for. I need to get the data from several different tables for
different users. I was forced into breaking everything up into separate
tables due to the MySQL restrictions and in the interest of keeping the
data optimized and the database query response times fast. As a result,
the number of colums is different for each of the 26 tables (ranging
from 6 columns, to 530 columns). It could just be a flaw in my design,
however, it would be fantastic to be able to put lots of SELECT queries
together into a single query, and get all of the results back in the
form of an array of arrays.
>
Thanks again.

[davie]

Re: Makeing multiple MySQL queries in a single query.

If you cannot join two tables it must be a flaw in your design,

Normalization is the process of removing redundant data from relational
tables by decomposing (splitting) a relational table into smaller
tables. In order to be correct, decomposition must be lossless. That
is, the new tables can be recombined by a natural join to recreate the
original table without creating any spurious or redundant data. .
I suggest you check out

http://www.utexas.edu/its/windows/database/datamodeling/index.html

cseymour wrote:

Hi Daz,
Couldn't you create a PHP function that would fire all your queries
then return the results as an array, kind of like createing the "array
of arrays" you had mentioned?
>
Just a thought.
>
Good luck. I'll keep chewing on this one for a bit.
>
Chris
Daz wrote:

cseymour wrote:

Hi Daz,
Are the data being return from the 2 queries similar? Have you looked
into using a UNION?
>
For example:
>
select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
from address_add
Union
select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
from boardmembers_br d
>
>
Just a thought.
>
Chris
>
>
Daz wrote:
Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.

My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.

Would anyone know if there is something I can do to get around this
problem?

Any input would be appreciated.

Thanks.

Daz

Hi Chris.

Thanks for your input. Unfortunately, UNION will not work for what I
need it for. I need to get the data from several different tables for
different users. I was forced into breaking everything up into separate
tables due to the MySQL restrictions and in the interest of keeping the
data optimized and the database query response times fast. As a result,
the number of colums is different for each of the 26 tables (ranging
from 6 columns, to 530 columns). It could just be a flaw in my design,
however, it would be fantastic to be able to put lots of SELECT queries
together into a single query, and get all of the results back in the
form of an array of arrays.

Thanks again.

[Daz]

Re: Makeing multiple MySQL queries in a single query.


cseymour wrote:

Hi Daz,
Couldn't you create a PHP function that would fire all your queries
then return the results as an array, kind of like createing the "array
of arrays" you had mentioned?
>
Just a thought.
>
Good luck. I'll keep chewing on this one for a bit.
>
Chris
Daz wrote:

cseymour wrote:

Hi Daz,
Are the data being return from the 2 queries similar? Have you looked
into using a UNION?
>
For example:
>
select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
from address_add
Union
select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
from boardmembers_br d
>
>
Just a thought.
>
Chris
>
>
Daz wrote:
Hi. I am trying to select data from two separate MySQL tables, where I
cannot use join, but when I put the two select queries into a single
query, I get an error telling me to check my syntax. Both of the
queries work fine when I use them to query the MySQL server directly.

My guess is that the MySQL extension only expects a single resource
back from the database, but get's several, or that it just checks the
statement first, and decides it's not valid. However, my guesses and/or
assumptions don't really make much of a difference as to the workings
of the PHP MySQL extension.

Would anyone know if there is something I can do to get around this
problem?

Any input would be appreciated.

Thanks.

Daz

Hi Chris.

Thanks for your input. Unfortunately, UNION will not work for what I
need it for. I need to get the data from several different tables for
different users. I was forced into breaking everything up into separate
tables due to the MySQL restrictions and in the interest of keeping the
data optimized and the database query response times fast. As a result,
the number of colums is different for each of the 26 tables (ranging
from 6 columns, to 530 columns). It could just be a flaw in my design,
however, it would be fantastic to be able to put lots of SELECT queries
together into a single query, and get all of the results back in the
form of an array of arrays.

Thanks again.

Hi Chris,

Thanks for your input. My objective is to reduce the number of separate
queries made to the database at any one time. I think the only way that
this is ever going to be possible, is if they change the way that the
MySQL extension for PHP works. I think it's fairly safe to say that
there aren't many people quite pedantic about such small details as I
am, although I am sure there are a few in their minority.

Thanks for sharing your thoughts.

All the best.

Daz

[Daz]

Re: Makeing multiple MySQL queries in a single query.


davie wrote:

If you cannot join two tables it must be a flaw in your design,
>
Normalization is the process of removing redundant data from relational
tables by decomposing (splitting) a relational table into smaller
tables. In order to be correct, decomposition must be lossless. That
is, the new tables can be recombined by a natural join to recreate the
original table without creating any spurious or redundant data. .
I suggest you check out
http://www.utexas.edu/its/windows/da...ing/index.html

The URL will be a useful asset to the documentation I currently know
of. Thank you very much. It is undoubtably a flaw in my design. I had
given it a lot of thought previously, and I had two ideas. My ISP lets
me use a small amount of server space for free, along with some other
resources such as the MySQL database. I went with the idea that I
believed would be lower on resources. It would appear that this is the
wrong way. Anyhow, this is getting way off topic, so I will no doubt
post in the appropriate group at some point, and hope for a good
discussion. Thanks for your input, it's very much appreciated.

Best wishes.

Daz

[Colin Fine]

Re: Makeing multiple MySQL queries in a single query.

Daz wrote:

cseymour wrote:

>Hi Daz,
>Couldn't you create a PHP function that would fire all your queries
>then return the results as an array, kind of like createing the "array
>of arrays" you had mentioned?
>>
>Just a thought.
>>
>Good luck. I'll keep chewing on this one for a bit.
>>
>Chris
>Daz wrote:

>>cseymour wrote:
>>>Hi Daz,
>>>Are the data being return from the 2 queries similar? Have you looked
>>>into using a UNION?
>>>>
>>>For example:
>>>>
>>> select ID_add 'Field1', ID_tpl 'Field2', null 'Field3', null 'Field4'
>>>from address_add
>>> Union
>>> select null 'Field1', null 'Field2', ID_brd 'Field3', ID_per 'Field4'
>>>from boardmembers_br d
>>>>
>>>>
>>>Just a thought.
>>>>
>>>Chris
>>>>
>>>>
>>>Daz wrote:
>>>>Hi. I am trying to select data from two separate MySQL tables, where I
>>>>cannot use join, but when I put the two select queries into a single
>>>>query, I get an error telling me to check my syntax. Both of the
>>>>queries work fine when I use them to query the MySQL server directly.
>>>>>
>>>>My guess is that the MySQL extension only expects a single resource
>>>>back from the database, but get's several, or that it just checks the
>>>>statement first, and decides it's not valid. However, my guesses and/or
>>>>assumptio ns don't really make much of a difference as to the workings
>>>>of the PHP MySQL extension.
>>>>>
>>>>Would anyone know if there is something I can do to get around this
>>>>problem?
>>>>>
>>>>Any input would be appreciated.
>>>>>
>>>>Thanks.
>>>>>
>>>>Daz
>>Hi Chris.
>>>
>>Thanks for your input. Unfortunately, UNION will not work for what I
>>need it for. I need to get the data from several different tables for
>>different users. I was forced into breaking everything up into separate
>>tables due to the MySQL restrictions and in the interest of keeping the
>>data optimized and the database query response times fast. As a result,
>>the number of colums is different for each of the 26 tables (ranging
>>from 6 columns, to 530 columns). It could just be a flaw in my design,
>>however, it would be fantastic to be able to put lots of SELECT queries
>>together into a single query, and get all of the results back in the
>>form of an array of arrays.
>>>
>>Thanks again.

Hi Chris,
>
Thanks for your input. My objective is to reduce the number of separate
queries made to the database at any one time. I think the only way that
this is ever going to be possible, is if they change the way that the
MySQL extension for PHP works. I think it's fairly safe to say that
there aren't many people quite pedantic about such small details as I
am, although I am sure there are a few in their minority.
>
Thanks for sharing your thoughts.
>
All the best.
>
Daz
>

On the contrary, many people who work with databases are (have to be)
both pedantic about small details, and concerned with efficiency.
If you are needing to 'change the way that the MySQL extension works',
you are either going outside the range of uses the designers foresaw
(possible) or (more likely in my opinion) you have not designed your
database optimtally.

Since you haven't given any examples of what you mean by 'two select
queries in a single query', it's hard to be sure what you do mean.

Colin