?
Solved

<PHP MySQL] The used SELECT statements have a different number of columns

Posted on 2011-09-16
8
Medium Priority
?
1,026 Views
Last Modified: 2012-06-27
I completed a project that I have been working on for a while, and when I got done, I found out that the database I had to connect to changed from the one they provided to build the application.  The main issue is that one of the tables doesn't have all of the identifiers they said it would, so the identifier I was use to run my searches was no included in that table.

So I have been trying to combine the search query so that when it searches the main tables it uses the original identifier, but when it searches one of the tables it uses a different identifier that is in both the main table and the second one.

This is probably very confusing, so here is my select statement (and this will hopefully help clear things up...)

SELECT * 
FROM foo
UNION SELECT secondaryid
FROM foo_locations
UNION SELECT chain, storeid, address1, address2, city, state, zip
FROM foo_locations
WHERE record_id LIKE '%$trimmed%' OR serial LIKE '%$trimmed%' OR sku LIKE '%$trimmed%' OR router_mac LIKE '%$trimmed%' OR router_sn LIKE '%$trimmed%' OR ata_mac LIKE '%$trimmed%' OR ata_sn LIKE '%$trimmed%' OR aircard_vendor LIKE '%$trimmed%' OR aircard_mdn LIKE '%$trimmed%' OR aircard_esn_hex LIKE '%$trimmed%' OR aircard_esn_dec LIKE '%$trimmed%' OR secondaryid LIKE '%$trimmed%' OR chain LIKE '%$trimmed%' OR storeid LIKE '%$trimmed%' OR address1 LIKE '%$trimmed%' OR address2 LIKE '%$trimmed%' OR city LIKE '%$trimmed%' OR state LIKE '%$trimmed%' OR zip LIKE '%$trimmed%' OR  tech LIKE '%$trimmed%' OR note LIKE '%$trimmed%'
ORDER BY record_id, secondaryid ASC

Open in new window


And here is the database structure of the two tables:

[b]foo:[/b]
record_id
serial
sku
date
router_sn
ata_mac
ata_sn
aircard_mdn
aircard_esn_hex
aircard_esn_dec
secondaryid

Open in new window


and

[b]foo_locations:[/b]
secondaryid
chain
storeid
address1
address2
city
zip

Open in new window


Basically the issues started happening when I had to find secondaryid for both tables and was getting an error that said it was ambiguous.  So I looked for solutions and got to this impasse and figured I should ask someone who knows what they are doing.

Anyway, if you need a better explanation or more information, let me know and thanks in advance~
0
Comment
Question by:prileyosborne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 13

Expert Comment

by:themrrobert
ID: 36550714
In the orderby clause, you must specify which database's "secondaryid" since both of the mysql tables in the union have a secondaryid field.

This can be done with

SELECT * 
FROM foo
UNION SELECT secondaryid
FROM foo_locations
UNION SELECT chain, storeid, address1, address2, city, state, zip
FROM foo_locations
WHERE record_id LIKE '%$trimmed%' OR serial LIKE '%$trimmed%' OR sku LIKE '%$trimmed%' OR router_mac LIKE '%$trimmed%' OR router_sn LIKE '%$trimmed%' OR ata_mac LIKE '%$trimmed%' OR ata_sn LIKE '%$trimmed%' OR aircard_vendor LIKE '%$trimmed%' OR aircard_mdn LIKE '%$trimmed%' OR aircard_esn_hex LIKE '%$trimmed%' OR aircard_esn_dec LIKE '%$trimmed%' OR secondaryid LIKE '%$trimmed%' OR chain LIKE '%$trimmed%' OR storeid LIKE '%$trimmed%' OR address1 LIKE '%$trimmed%' OR address2 LIKE '%$trimmed%' OR city LIKE '%$trimmed%' OR state LIKE '%$trimmed%' OR zip LIKE '%$trimmed%' OR  tech LIKE '%$trimmed%' OR note LIKE '%$trimmed%'
ORDER BY record_id, `foo`.secondaryid ASC

Open in new window

0
 

Author Comment

by:prileyosborne
ID: 36550804
Ok, I updated it to what you said, and I still get the following error:

Database Access Failed: The used SELECT statements have a different number of columns

Open in new window


Also, do I need to make that same distinction in my WHERE statement?  So find the secondaryid statement and make it foo.secondaryid?

Thanks
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 36552066
First, I would like to say, "Congratulations!" because you have just gotten what is called a "change order" and those are usually handled at the highest hourly rates.

Next, the "ambiguous" part probably refers to the column named "secondaryid" and so I think I might want to remove the SELECT * and replace it with SELECT giving the explicit column names you want to SELECT FROM foo.

you can also name the SELECT results keys with different names from the column names, something like this...

SELECT foo.thing AS fthing, bar.thing AS bthing FROM foo, bar WHERE foo.id = bar.id LIMIT 21

... and in the results set you will find arrays containing keys named fthing and bthing.  Even though the column names "collide" the query will still work.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:prileyosborne
ID: 36552289
Super cool! I will be sure to ask for a raise! :)

As for your suggestion, below is what I changed the code to, but now I am getting a new error. Here is the code:

SELECT record_id, serial, sku, router_mac, router_sn, ata_mac, ata_sn, aircard_mdn, aircard_esn_hex, aircard_esn_dec, foo_location.secondaryid AS clsid, chain, storeid, address1, address2, city, state, zip
FROM foo, foo_locations
WHERE record_id LIKE '%$trimmed%' OR serial LIKE '%$trimmed%' OR sku LIKE '%$trimmed%'
 OR router_mac LIKE '%$trimmed%' OR router_sn LIKE '%$trimmed%' OR ata_mac LIKE 
'%$trimmed%' OR ata_sn LIKE '%$trimmed%' OR aircard_vendor LIKE '%$trimmed%' OR 
aircard_mdn LIKE '%$trimmed%' OR aircard_esn_hex LIKE '%$trimmed%' OR aircard_esn_dec
 LIKE '%$trimmed%' OR clsid LIKE '%$trimmed%' OR chain LIKE '%$trimmed%' OR storeid LIKE 
'%$trimmed%' OR address1 LIKE '%$trimmed%' OR address2 LIKE '%$trimmed%' OR city LIKE 
'%$trimmed%' OR state LIKE '%$trimmed%' OR zip LIKE '%$trimmed%' OR  tech LIKE 
'%$trimmed%' OR note LIKE '%$trimmed%'
ORDER BY record_id, coinstar_locations.mpid ASC

Open in new window


And here is the error:
Database Access Failed: Unknown column 'clsid' in 'where clause'

Open in new window


Is there something else I need to do or is there even a version problem with my MySQL. I am currently running MySQL 5.0.92 and PHP 5.3.6.  My guess is just that I am not quite getting it though...
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 36552475
When you write SELECT thing and want to name the thing differently you need to make it more like SELECT thing as new_thing_name...  Does that make sense?

No problem with the version of MySQL.  The latest is almost always the best, but you should be on firm ground with what you have.
0
 

Author Comment

by:prileyosborne
ID: 36562298
Ok, perfect, that worked!  One last quick question.  I am printing my results with mysql_fetch_row and assigning my variables data based on the row number. So:

$row = mysql_fetch_row($result);
$var_01 		= $row[0];
$var_01 		= $row[1];
$var_01 		= $row[2];
$var_01 		= $row[3];
$var_01 	        = $row[4];
$var_01 	        = $row[5];
$var_01 		= $row[6];
$var_01 		= $row[7];
$var_01 		= $row[8];

etc...

Open in new window


When I am combining results from multiple tables like this, how do I order them?  Or should I replace the numbers with the column names?  Thanks so much!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36563019
I would use names instead of numbered indexes.  Try accessing the results set with mysql_fetch_assoc().  When you use that function the order does not matter because everything is named with the column names you used in the SELECT query.  Coincidentally, you will find that the order in the array will be the same as the order of column names in the SELECT query, but you do not have to depend on that with named elements in an associative array.
0
 

Author Closing Comment

by:prileyosborne
ID: 36563065
Thank you so much. That was very helpful!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question