Solved

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

Posted on 2011-09-16
8
893 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 110

Accepted Solution

by:
Ray Paseur earned 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 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 110

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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 look for a specific file type in a local or remote server directory using PHP.
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 …

749 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