union/select statement & number of columns

Hello all, I receive an error of the following: The used SELECT
statements have a different number of columns. Any help, pointers,
tutorials are appreciated.

Here are the two tables structure I am trying to pull from:
Table 1
mysql> describe orders;
+------------+--------------+------+-----+---------+-------- --------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------- --------+
| id | int(255) | NO | PRI | | auto_increment |
| ordernum | int(10) | NO | | | |
| date | varchar(60) | NO | | | |
| time | varchar(20) | NO | | | |
| group | varchar(20) | NO | | | |
| purpose | varchar(255) | NO | | | |
| tracking | varchar(120) | NO | | | |
| contact | varchar(255) | NO | | | |
| eta | varchar(50) | NO | | | |
| department | varchar(125) | NO | | | |
| notes | varchar(255) | NO | | | |
+------------+--------------+------+-----+---------+-------- --------+
11 rows in set (0.01 sec)

Table 2
mysql> describe order_items;
+-------------+---------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+------ ----------+
| id | int(11) | NO | PRI | | auto_increment |
| ordernum | int(124) | NO | | | |
| quantity | int(124) | NO | | | |
| description | varchar(124) | NO | | | |
| price | decimal(10,0) | NO | | | |
| partnum | varchar(255) | NO | | | |
| vendor | varchar(255) | NO | | | |
+-------------+---------------+------+-----+---------+------ ----------+
7 rows in set (0.00 sec)

And here is the statement I am using (PHP):
$query = "( SELECT * FROM `orders` WHERE ( `ordernum` LIKE "$var\" OR
`purpose` LIKE \"$var\" OR `tracking` LIKE \"$var\" OR `contact` LIKE
\"$var\" OR `date` LIKE \"$var\" OR `time` LIKE \"$var\" OR `eta` LIKE
\"$var\" OR `department` LIKE \"$var\" OR `notes` LIKE \"$var\" ) AND
`group` = \"$group\" ) UNION ( SELECT * FROM `order_items` WHERE (
`ordernum` LIKE \"$var\" OR `price` LIKE \"$var\" OR `partnum` LIKE
\"$var\" OR `vendor` LIKE \"$var\" OR `quantity` LIKE \"$var\" OR
`description` LIKE \"$var\" ) ORDER BY `ordernum` )";

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: union/select statement & number of columns

--_2fe3379d-a316-40f8-8c79-07fbb6cc55f0_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Unions required that the the queries have the same number of columns and th=
e same data type for each column
=20
bastien> To: php-db@lists.php.net> Date: Fri, 19 Oct 2007 09:24:38 -0600> F=
rom: jason.gerfen@scl.utah.edu> Subject: [PHP-DB] union/select statement & =
number of columns> > Hello all, I receive an error of the following: The us=
ed SELECT> statements have a different number of columns. Any help, pointer=
s,> tutorials are appreciated.> > Here are the two tables structure I am tr=
ying to pull from:> Table 1> mysql> describe orders;> +------------+-------=
-------+------+-----+---------+----------------+> | Field | Type | Null | K=
ey | Default | Extra |> +------------+--------------+------+-----+---------=
+----------------+> | id | int(255) | NO | PRI | | auto_increment |> | orde=
rnum | int(10) | NO | | | |> | date | varchar(60) | NO | | | |> | time | va=
rchar(20) | NO | | | |> | group | varchar(20) | NO | | | |> | purpose | var=
char(255) | NO | | | |> | tracking | varchar(120) | NO | | | |> | contact |=
varchar(255) | NO | | | |> | eta | varchar(50) | NO | | | |> | department =
| varchar(125) | NO | | | |> | notes | varchar(255) | NO | | | |> +--------=
----+--------------+------+-----+---------+----------------+ > 11 rows in se=
t (0.01 sec)> > Table 2> mysql> describe order_items;> +-------------+-----=
----------+------+-----+---------+----------------+> | Field | Type | Null =
| Key | Default | Extra |> +-------------+---------------+------+-----+----=
-----+----------------+> | id | int(11) | NO | PRI | | auto_increment |> | =
ordernum | int(124) | NO | | | |> | quantity | int(124) | NO | | | |> | des=
cription | varchar(124) | NO | | | |> | price | decimal(10,0) | NO | | | |>=
| partnum | varchar(255) | NO | | | |> | vendor | varchar(255) | NO | | | =
|> +-------------+---------------+------+-----+---------+------ ----------+>=
7 rows in set (0.00 sec)> > And here is the statement I am using (PHP):> $=
query =3D "( SELECT * FROM `orders` WHERE ( `ordernum` LIKE "$var\" OR> `pu=
rpose` LIKE \"$var\" OR `tracking` LIKE \"$var\" OR `contact` LIKE> \"$var\=
" OR `date` LIKE \"$var\" OR `time` LIKE \"$var\" OR `eta` LIKE> \"$var\" O=
R `department` LIKE \"$var\" OR `notes` LIKE \"$var\" ) AND> `group` =3D \"=
$group\" ) UNION ( SELECT * FROM `order_items` WHERE (> `ordernum` LIKE \"$=
var\" OR `price` LIKE \"$var\" OR `partnum` LIKE> \"$var\" OR `vendor` LIKE=
\"$var\" OR `quantity` LIKE \"$var\" OR> `description` LIKE \"$var\" ) ORD=
ER BY `ordernum` )";> > -- > PHP Database Mailing List (http://www.php.net/=
)> To unsubscribe, visit: http://www.php.net/unsub.php>=20
____________________________________________________________ _____
Express yourself with free Messenger emoticons. Get them today!
http://www.freemessengeremoticons.ca/?icid=3DEMENCA122=

--_2fe3379d-a316-40f8-8c79-07fbb6cc55f0_--

Re: union/select statement & number of columns

------=_Part_9710_4279408.1192822175833
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

For starters, here's a simple example using Oracle:

select tname, tabtype from tab
union
select table_name, null from user_tables;

I replaced NULL in the second query for the missing column.

In a different observation, it would be better, I think, to name the
individual columns rather than select *. Otherwise, any new columns added
to either table (with, or without using UNIONs), may break the application
if it's not prepared to handled unknown columns.

On 10/19/07, Jas wrote:
>
> Hello all, I receive an error of the following: The used SELECT
> statements have a different number of columns. Any help, pointers,
> tutorials are appreciated.
>
> Here are the two tables structure I am trying to pull from:
> Table 1
> mysql> describe orders;
> +------------+--------------+------+-----+---------+-------- --------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+--------------+------+-----+---------+-------- --------+
> | id | int(255) | NO | PRI | | auto_increment |
> | ordernum | int(10) | NO | | | |
> | date | varchar(60) | NO | | | |
> | time | varchar(20) | NO | | | |
> | group | varchar(20) | NO | | | |
> | purpose | varchar(255) | NO | | | |
> | tracking | varchar(120) | NO | | | |
> | contact | varchar(255) | NO | | | |
> | eta | varchar(50) | NO | | | |
> | department | varchar(125) | NO | | | |
> | notes | varchar(255) | NO | | | |
> +------------+--------------+------+-----+---------+-------- --------+
> 11 rows in set (0.01 sec)
>
> Table 2
> mysql> describe order_items;
> +-------------+---------------+------+-----+---------+------ ----------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+---------------+------+-----+---------+------ ----------+
> | id | int(11) | NO | PRI | | auto_increment |
> | ordernum | int(124) | NO | | | |
> | quantity | int(124) | NO | | | |
> | description | varchar(124) | NO | | | |
> | price | decimal(10,0) | NO | | | |
> | partnum | varchar(255) | NO | | | |
> | vendor | varchar(255) | NO | | | |
> +-------------+---------------+------+-----+---------+------ ----------+
> 7 rows in set (0.00 sec)
>
> And here is the statement I am using (PHP):
> $query = "( SELECT * FROM `orders` WHERE ( `ordernum` LIKE "$var\" OR
> `purpose` LIKE \"$var\" OR `tracking` LIKE \"$var\" OR `contact` LIKE
> \"$var\" OR `date` LIKE \"$var\" OR `time` LIKE \"$var\" OR `eta` LIKE
> \"$var\" OR `department` LIKE \"$var\" OR `notes` LIKE \"$var\" ) AND
> `group` = \"$group\" ) UNION ( SELECT * FROM `order_items` WHERE (
> `ordernum` LIKE \"$var\" OR `price` LIKE \"$var\" OR `partnum` LIKE
> \"$var\" OR `vendor` LIKE \"$var\" OR `quantity` LIKE \"$var\" OR
> `description` LIKE \"$var\" ) ORDER BY `ordernum` )";
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_9710_4279408.1192822175833--

RE: union/select statement & number of columns

--_5b9a18a3-ae80-4fef-b478-37b75d0cee36_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Unions required that the the queries have the same number of columns and th=
e same data type for each column
=20
bastien> To: php-db@lists.php.net> Date: Fri, 19 Oct 2007 09:24:38 -0600> F=
rom: jason.gerfen@scl.utah.edu> Subject: [PHP-DB] union/select statement & =
number of columns> > Hello all, I receive an error of the following: The us=
ed SELECT> statements have a different number of columns. Any help, pointer=
s,> tutorials are appreciated.> > Here are the two tables structure I am tr=
ying to pull from:> Table 1> mysql> describe orders;> +------------+-------=
-------+------+-----+---------+----------------+> | Field | Type | Null | K=
ey | Default | Extra |> +------------+--------------+------+-----+---------=
+----------------+> | id | int(255) | NO | PRI | | auto_increment |> | orde=
rnum | int(10) | NO | | | |> | date | varchar(60) | NO | | | |> | time | va=
rchar(20) | NO | | | |> | group | varchar(20) | NO | | | |> | purpose | var=
char(255) | NO | | | |> | tracking | varchar(120) | NO | | | |> | contact |=
varchar(255) | NO | | | |> | eta | varchar(50) | NO | | | |> | department =
| varchar(125) | NO | | | |> | notes | varchar(255) | NO | | | |> +--------=
----+--------------+------+-----+---------+----------------+ > 11 rows in se=
t (0.01 sec)> > Table 2> mysql> describe order_items;> +-------------+-----=
----------+------+-----+---------+----------------+> | Field | Type | Null =
| Key | Default | Extra |> +-------------+---------------+------+-----+----=
-----+----------------+> | id | int(11) | NO | PRI | | auto_increment |> | =
ordernum | int(124) | NO | | | |> | quantity | int(124) | NO | | | |> | des=
cription | varchar(124) | NO | | | |> | price | decimal(10,0) | NO | | | |>=
| partnum | varchar(255) | NO | | | |> | vendor | varchar(255) | NO | | | =
|> +-------------+---------------+------+-----+---------+------ ----------+>=
7 rows in set (0.00 sec)> > And here is the statement I am using (PHP):> $=
query =3D "( SELECT * FROM `orders` WHERE ( `ordernum` LIKE "$var\" OR> `pu=
rpose` LIKE \"$var\" OR `tracking` LIKE \"$var\" OR `contact` LIKE> \"$var\=
" OR `date` LIKE \"$var\" OR `time` LIKE \"$var\" OR `eta` LIKE> \"$var\" O=
R `department` LIKE \"$var\" OR `notes` LIKE \"$var\" ) AND> `group` =3D \"=
$group\" ) UNION ( SELECT * FROM `order_items` WHERE (> `ordernum` LIKE \"$=
var\" OR `price` LIKE \"$var\" OR `partnum` LIKE> \"$var\" OR `vendor` LIKE=
\"$var\" OR `quantity` LIKE \"$var\" OR> `description` LIKE \"$var\" ) ORD=
ER BY `ordernum` )";> > -- > PHP Database Mailing List (http://www.php.net/=
)> To unsubscribe, visit: http://www.php.net/unsub.php>=20
____________________________________________________________ _____
R U Ready for Windows Live Messenger Beta 8.5? Try it today!
http://entertainment.sympatico.msn.ca/WindowsLiveMessenger=

--_5b9a18a3-ae80-4fef-b478-37b75d0cee36_--