Jump to content

searching (WHERE) in a UNION query


coldfission

Recommended Posts

SELECT orders.orders_id AS ID, last_modified AS modified, customers_company AS organization, customers_name AS name, customers_street_address AS address, customers_city AS city, customers_postcode AS zip, customers_state AS state, customers_country AS country, customers_telephone AS phone, customers_email_address AS email, payment_method, value AS total, 'tackwholesale.com' AS source FROM orders
LEFT JOIN orders_total
ON orders.orders_id=orders_total.orders_id
WHERE orders_status=3 AND class='ot_total'
UNION
SELECT ID, modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, address, city, state, zip, country, phone, email, payment_method, total, 'tack-wholesale.com' AS source FROM tack_orders
UNION
SELECT NULL AS ID, NULL AS modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, CONCAT(street, ' ', street_con) AS address, city, zip, state, country, phone, email, NULL AS payment_method, NULL AS total, 'Catalog Request' AS source FROM cat_req_static

 

I need to search this result using a WHERE clause. I tried putting this line after all of the above code:

 

WHERE ID LIKE '%'

 

But, this returns "Unknown column 'ID' in 'where clause'" How do i do this?

 

Also, I need to count how many total records are returned by this query for my page numbering system. How would I COUNT all of this?

 

thanks

Link to comment
Share on other sites

what are you trying to find with the WHERE clause???

 

$query = "SELECT * FROM table";
$result = mysql_query($query) or die ("Error in query" . mysql_error());

$count = mysql_num_rows($result); //the number of rows returned by the query

Link to comment
Share on other sites

Yes, i understand that this is possible, but i do not want to query the whole database

 

sorry i should have said that i was using the LIMIT on the whole query for pages, but I want to just COUNT the whole query without limitting it to see how many pages there will be. It's a little confusing, but running the whole query without limitting takes a while, and COUNT is much faster.

 

and the WHERE clause is my search string from a search page. WHERE ID LIKE '%' is just an example of something that doesn't work and i would like to get working.

Link to comment
Share on other sites

I think paul2463 was suggesting that you use mysql_num_rows() to find the number of rows returned.  His example involved the whole database, but I don't believe he was implying that you use the whole database.  Just use mysql_num_rows() with whatever your own query/result is.

 

As far as the WHERE condition, you'll need to put it in each of the SELECTs, not at the end of the UNIONs.  And what are you hoping to accomplish with '%' anyway?  That's a special character, not especially useful alone.

Link to comment
Share on other sites

OK here is an example of the first page:

 

SELECT orders.orders_id AS ID, last_modified AS modified, customers_company AS organization, customers_name AS name, customers_street_address AS address, customers_city AS city, customers_postcode AS zip, customers_state AS state, customers_country AS country, customers_telephone AS phone, customers_email_address AS email, payment_method, value AS total, 'tackwholesale.com' AS source FROM orders
LEFT JOIN orders_total
ON orders.orders_id=orders_total.orders_id
WHERE orders_status=3 AND class='ot_total'
UNION
SELECT ID, modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, address, city, state, zip, country, phone, email, payment_method, total, 'tack-wholesale.com' AS source FROM tack_orders
UNION
SELECT NULL AS ID, NULL AS modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, CONCAT(street, ' ', street_con) AS address, city, zip, state, country, phone, email, NULL AS payment_method, NULL AS total, 'Catalog Request' AS source FROM cat_req_static
LIMIT 0,30

 

if i do a mysql_num_rows() on that i will get a result of 30.

 

If i run this query without the LIMIT, it will take about 10 seconds. That is rediculous when you are browsing a query page by page.

If i had a way to just COUNT (much faster) this without the LIMIT, it would tell me how many records there are total, so i can calculate how many pages there will be.

 

I was giving WHERE ID LIKE '%' as an example that would return all of the records, but i'll give a different example

WHERE ID LIKE 'bob'

Link to comment
Share on other sites

 

if i do a mysql_num_rows() on that i will get a result of 30.

 

If i run this query without the LIMIT, it will take about 10 seconds. That is rediculous when you are browsing a query page by page.

If i had a way to just COUNT (much faster) this without the LIMIT, it would tell me how many records there are total, so i can calculate how many pages there will be.

 

I was giving WHERE ID LIKE '%' as an example that would return all of the records, but i'll give a different example

WHERE ID LIKE 'bob'

 

Okay, I see what you're doing now.  (1) you'll need to put the where clause in each of the selects.  (2)  you probably want to read up on SQL_CALC_FOUND_ROWS() and FOUND_ROWS() in order to return the total amount of rows that would have been returned without the LIMIT clause.  Using those, however, is more complicated with UNIONs.

Link to comment
Share on other sites

$found_rows = mysql_query($query = "SELECT found_rows()") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);
$found_rows = mysql_result($found_rows, 0);
print "Found rows: {$found_rows}";

 

I used that and it works for most of my queries that i am using. It will not work for 2 out of 3 of my individual (non UNION) queries.

 

Doesn't Work For:

SELECT ID, modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, address, city, state, zip, country, phone, email, payment_method, total, 'tack-wholesale.com' AS source FROM tack_orders ORDER BY modified ASC LIMIT 0, 35

 

or

 

SELECT NULL AS ID, NULL AS modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, street AS address, city, zip, state, country, phone, email, NULL AS payment_method, NULL AS total, 'Catalog Request' AS source FROM cat_req_static ORDER BY modified ASC LIMIT 0, 35

 

$found_rows always comes back as 35 for both of those

 

Does work for:

SELECT SQL_CALC_FOUND_ROWS orders.orders_id AS ID, last_modified AS modified, customers_company AS organization, customers_name AS name, customers_street_address AS address, customers_city AS city, customers_state AS state, customers_postcode AS zip, customers_country AS country, customers_telephone AS phone, customers_email_address AS email, payment_method, value AS total, 'tackwholesale.com' AS source FROM orders LEFT JOIN orders_total ON orders.orders_id=orders_total.orders_id WHERE orders_status=3 AND class='ot_total' ORDER BY modified ASC LIMIT 0, 35

 

Why are the first two not returning the actual number of records found without the LIMIT?

 

thanks

Link to comment
Share on other sites

Because he's using a LIMIT (but it's not in that query) and wants to know how many rows would be returned were the LIMIT not used.

He could still run 2 separate queries... I've never been convinced that calc_rows is faster.

Link to comment
Share on other sites

OK i think i've come down to my last glitch.

 

The modified column in the first table is in datetime format, and the rest are all in varchar format. To use a UNION, all have to be in the same data type i think. That is why the modified column was not showing up when i ran the UNION query. I can not change the modified column from datetime format, and i can not change the other columns to datetime. How would i do this?

 

thanks

Link to comment
Share on other sites

OK i think i've come down to my last glitch.

 

The modified column in the first table is in datetime format, and the rest are all in varchar format. To use a UNION, all have to be in the same data type i think. That is why the modified column was not showing up when i ran the UNION query. I can not change the modified column from datetime format, and i can not change the other columns to datetime. How would i do this?

 

thanks

You can CAST() one to the other.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.