473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Oct 4 '06 #1
7 2696
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_brd
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
Oct 4 '06 #2
Daz

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_brd
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.

Oct 4 '06 #3
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_brd
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.
Oct 4 '06 #4
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

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_brd
>
>
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.
Oct 4 '06 #5
Daz

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_brd
>
>
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

Oct 6 '06 #6
Daz

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

Oct 6 '06 #7
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_brd
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
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
Oct 6 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Brian | last post by:
Hello I am using version 4.0.12-nt of MySQL and when I hit the enter key rapidly I can't connect to the database. The result is a message is returned to me from mysql that says I can't connect...
4
by: jy2003 | last post by:
I have read a book, which suggests we should change OR to UNION for better performance, but currently I have too many OR clauses(I have a query with 100 ORs) and it does not sound good to have 100...
11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time...
4
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from...
10
by: smorrey | last post by:
Hello all, this might better be suited for the MySQL newsgroup, but I figured I'ld post here and see if anyone can help me. I'm trying to create a simple transaction handling system where users...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
13
by: Ciaran | last post by:
Hi All, Is it faster to have mySql look up as much data as possible in one complex query or to have php do all the complex processing and submit lots of simple queries to the mysql database? ...
4
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
0
bilibytes
by: bilibytes | last post by:
hi, i am trying to UPDATE multiple rows with mysql. I know how to do it with multiple queries but i think it would be less resource consuming generating mysql query code with php and update all...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.