Solved

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

Posted on 2011-09-16
8
788 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
  • 4
  • 3
8 Comments
 
LVL 13

Expert Comment

by:themrrobert
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
 

Author Comment

by:prileyosborne
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
Thank you so much. That was very helpful!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now