searching (WHERE) in a UNION query

[coldfission]

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

[paul2463]

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

[coldfission]

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.

[Wildbug]

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.

[paul2463]

thanks wildbug, you are right maybe I should have explained it a bit better about the mysql_num_rows() and my example of it.

 

 

[coldfission]

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'

[Wildbug]

 

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.

[coldfission]

OK thanks for the help man. I'll read up on those things. Sounds like it will work

 

thanks

[coldfission]

How do i do this with one session? FOUND_ROWS() seems like it can only be done with MySQL command prompt. I'm using PHP. How would i save this variable using only one session or entry thingy?

 

thanks

[bubblegum.anarchy]

$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}";

[fenway]

Why don't you just run a COUNT()?

[Wildbug]

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.

[coldfission]

$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

[Wildbug]

Are you putting SQL_CALC_FOUND_ROWS in the first two queries after the SELECT?

[coldfission]

wow i can't believe i missed that

[Wildbug]

It's nice of you to lob me some easy ones sometimes. 

[fenway]

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.

[coldfission]

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

[fenway]

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.