fenway

Yikes... why do you have 5 left joins for the same base query?

Not sure if you can use env vars from linux like that... did you try using --tmpdir? But 127 is still record-table is crashed. Bah! Did you say earlier that you could run some select statements at some point?

Try: select o.offerid , o.name, o.incent, o.type, o.reporting, o.category , IFNULL( e.pay_value, o.pay_value ) AS pay_value from offers as o left join exceptions as e using (pay_value,offerid) where o.status = 'On' order by o.pay_value $orderx LIMIT $from, $max_results

What not try the simple case to prove it? Use a basic string on both server.

Strictly speaking, whenever you CONCAT, I would probably recommend CONCAT_WS(), and use a separator that is unlikely to appear in your data. Otherwise, if you have teams like: team1 = me team2 = another and team1 = mean team2 = other then CONCAT() with return the same string for both! Case insensitivity can also wreak havoc. I would recommend: SELECT team1, team2, COUNT(*) AS count FROM matchups GROUP BY CONCAT_WS( '~',team1,team2) Just to be safe. You'll get burned eventually.

Actually, I use it to be explicit -- CURDATE() when I'm just comparing the date, vs. NOW() when I mean the full date & time. Just a convention of mine, but it helps.

If you have an integers table, it should be easy to generate a table on-the-fly with the date range in question.

It's NEVER easier to see the whole code... please don't do that again. Check mysql_error() after the query, but before mysql_fetch_array()... also, try lower UM.

You'll have to combine the data somehow... UNION provides ways to sort data both before & after the union step.

It's far from ideal to join on a non-integer value -- particularly on a variable value, like price. select o.pay_value as orig, e.pay_value as new from offers as o left join exceptions as e using ( pay_value ) where o.status = 'On' order by o.pay_value ASC

Sorry, I missed what was going on here... I thought you were comparing a DATE field to NOW(). FYI, it's easier to use: SELECT d FROM `dateTable` WHERE d = CURDATE();

Oops, my bad; try this: select distinct ca.user_id from car_auctions AS ca inner join user_master AS um using (user_id) inner join user_details AS ud on ( ud.user_id = um.user_id AND ud.user_status=2 ) where ca.auction_closed=1 and seller_fees_charged_status<>1 and winning_bidder<>'' AND seller_fees<>0 and (seller_fees>0 OR UM.user_discount=100)

I don't know what you mean... you're simply taking a bunch of values from a select statement and using them to update another table, right?

So you want to show both prices?

You're lucky it worked in mysql 4 -- you got very lucky that comma and JOIN had the same precedence. It's bad to mix them; in fact, NEVER use comma and you'll never go astray. You can cheat: .... from ( " . TABLE_PRODUCTS . " p, recently_view as rv, " . TABLE_PRODUCTS_DESCRIPTION . " pd ) left join " . TABLE_MANUFACTURERS . " m ..... Or, better yet, fix it: $products_query_raw = "select m.manufacturers_name, mrv.rec_v_id, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, pd.products_description from " . TABLE_PRODUCTS . " p using ( products_id ) inner join recently_view as rv using ( products_id ) inner join " . TABLE_PRODUCTS_DESCRIPTION . " pd using ( products_id ) left join " . TABLE_MANUFACTURERS . " m using ( manufacturers_id ) where p.products_id in (" . $prod_viewed . ") and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by rv.rec_v_id";

First, don't use NULL for the id field... just leave it out. Second, to do that, you'll need to have a column list -- which you should always have anyway. Third, the DECIMAL format specifies the TOTAL number of digits, and then how many are decimals -- so (3,2) means you can store #.## -- not enough. You want (5,2). So: INSERT INTO OPTICAL_FRAMES ( Brand char, model char, Image, Description, Retail, Sale_Price, Material char, col_nam char) VALUES ('Smith and Jones New York','511 C1',NULL,'Hand Made Acetate with Spring Hinges','136.00','27.28','Acetate','Dark Orange'); Isn't it good that MySQL 5 has strict mode on?

how about some table structure?

union would only be a good fit if you had the same number of columns

why not simply write a multi-table update?

select distinct ca.user_id from car_auctions AS ca inner join user_master AS um using (user_id) inner join (SELECT ud.user_id from user_details ud inner join user_master u using (user_id) where ud.user_status=2) as sub using ( user_id ) where ca.auction_closed=1 and seller_fees_charged_status<>1 and winning_bidder<>'' AND seller_fees<>0 and (seller_fees>0 OR UM.user_discount=100) Untested... and missing table prefixes in some places.

You can use a random number generated from php....

You need to use LEFT JOINs for this: SELECT q.quote, qp.paraphrase, s.*, u.username FROM quotes q LEFT JOIN quotes_paraphrases qp ON ( q.id=qp.quote_id ) LEFT JOIN sources s ON ( q.source_id=s.id ) LEFT JOIN users u ON ( q.user_id=u.id ) WHERE q.enabled=1

What is your tmpdir again? Is it is the default, or is it set in the my.cnf file? Why don't you set it to something unique and new for mysql and then try it again? Can you drop any of the indexes directly? i.e. with ALTER TABLE DROP INDEX?

I know exactly what you want -- cross-tabs.

Did you try running as root?