[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • Last Modified:

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

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
prileyosborne
Asked:
prileyosborne
  • 4
  • 3
2 Solutions
 
themrrobertCommented:
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
 
prileyosborneAuthor Commented:
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
 
Ray PaseurCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
prileyosborneAuthor Commented:
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
 
Ray PaseurCommented:
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
 
prileyosborneAuthor Commented:
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
 
Ray PaseurCommented:
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
 
prileyosborneAuthor Commented:
Thank you so much. That was very helpful!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now