gin

It's not 3 tables, each with a different set. I'm already JOINing the 3 to get just 1 set. Each set is a different row. Unless I'm misunderstanding you?

Thanks for the suggestions, freelance84, but rearranging the data in that manner doesn't quite work. While I'm only showing item_id & price, there are also 3 other fields, like so: $set1[0][item_id] = 'admin'; $set1[0][price] = 500; $set1[0][item_name] = 'Administrative charges'; $set1[0][section] = 'Administration'; $set1[0][sec_id] = 1; I supposed I could pull out the id & price and sort it like you suggest, but then I'd just have to match it back to the rest of the data. Seems a bit circular? As it is, there'll be more loops to get section subtotals and to format for output, though really, I'll probably try cram everything into one convoluted loop anyhow. One thing at a time As for the original data arrays, they're being pulled from 3 different MySQL tables. As in data from 3 tables to get 1 set. And although I say I want to "combine 2 sets of data", in reality it's "2 or more". If it's possible to do any of this in the SQL code, it's beyond my can-barely-get-joins abilities.

I have 2 sets of data I pulled from MySQL. Below is a small sample of the data. $set1[0][item_id] = 'admin'; $set1[0][price] = 500; $set1[1][item_id] = 'courier'; $set1[1][price] = 200; $set2[0][item_id] = 'courier'; $set2[0][price] = 300; $set2[1][item_id] = 'flight'; $set2[1][price] = 100; I want to add the two sets of data together such that the prices of identical items are added together, like so: $set3[0][item_id] = 'admin'; $set3[0][price] = 500; $set3[1][item_id] = 'courier'; $set3[1][price] = 500; // new price $set3[2][item_id] = 'flight'; $set3[2][price] = 100; Any kind of array merging or appending will not work due to the varying line numbers. Right now I'm resorting to comparing the two arrays with for loops, as below. Sadly my code only allows me to add the prices, but not insert new lines of data. $set3 = $set1; foreach ($set3 as $i => $idata) { foreach ($set2 as $jdata) { if ($idata['item_id'] == $jdata['item_id']) $set3[$i]['price'] += $jdata['price']; } } Please advise. Any help much appreciated!

Hi, the second account username is 'iridys'. Do I have to do anything for proof? The emails are quite similar. Thank you.

Dear mods, I realize I had at some point created a second user login to the forums. Can I have the second merged into the first (this one) or have the second one deleted?

That solution isn't complete because I needed the rate that corresponded to the max_date as well. Anyway, correct me if I'm wrong (since most of your discussion went over my head, lol)... This solution from luca200, while it works, is bad because it involves a correlated subquery which eats resources SELECT * FROM test_item LEFT JOIN test_rate ON test_item.rate = test_rate.id WHERE date = (SELECT MAX(date) from test_rate WHERE test_rate.id = test_item.rate) OR date IS NULL ORDER BY test_item.id, test_rate.date DESC; But this one by aschk is better, because it uses a derived table(s?) instead. SELECT i.id, i.rate, r.max_date as 'date', r.rate FROM test_item i LEFT JOIN ( SELECT t.id, x.max_date, t.rate FROM test_rate t JOIN ( SELECT id, MAX(date) as 'max_date' FROM test_rate GROUP BY id ) x ON x.max_date = t.`date` AND x.id = t.id ) r ON r.id = i.rate; Thanks you guys for all your help! Note to self: go read up on subqueries vs joins and derived tables.

Okay, still trying to figure this out. Given this: SELECT * FROM test_rate; +-------+------------+------+ | id | date | rate | +-------+------------+------+ | rate1 | 2008-03-01 | 500 | | rate1 | 2008-03-05 | 200 | | rate2 | 2008-03-03 | 300 | | rate2 | 2008-03-19 | 50 | +-------+------------+------+ How do I get this? +-------+------------+------+ | id | date | rate | +-------+------------+------+ | rate1 | 2008-03-05 | 200 | | rate2 | 2008-03-19 | 50 | +-------+------------+------+

Well if I understand it correctly, a WHERE constitutes an implicit JOIN when two tables are involved. That said, I'm afraid I'm not getting what you (aschk & fenway) are trying to hint at. I'm guessing, though, that you mean me to first generate a table of rates that I want, but I'm not sure how, even ignoring the item table. luca200's code does work (thanks very much!) but I'd very much like to understand why. Could you explain?

Still not entirely working. The second row shouldn't appear ;_; SELECT * FROM test_item LEFT JOIN test_rate ON test_item.rate = test_rate.id WHERE date IN (SELECT MAX(date) from test_rate GROUP BY test_rate.id) OR date IS NULL ORDER BY test_item.id, test_rate.date DESC; +-------+-------+-------+------------+------+ | id | rate | id | date | rate | +-------+-------+-------+------------+------+ | item1 | rate1 | rate1 | 2008-03-06 | 150 | | item1 | rate1 | rate1 | 2008-03-05 | 200 | | item2 | | NULL | NULL | NULL | | item3 | rate2 | rate2 | 2008-03-05 | 300 | +-------+-------+-------+------------+------+

Use PHP (or whatever). When you change status, do a calculation right after.

Well, here's my next attempt with subqueries, but it doesn't really solve the problem since I get that error below. I understand why I get it, but I'm not so big on understanding how to get around it. SELECT * FROM test_item LEFT JOIN test_rate ON test_item.rate = test_rate.id WHERE date = (SELECT MAX(date) from test_rate GROUP BY test_rate.id) OR date IS NULL ORDER BY test_item.id, test_rate.date DESC; >> ERROR 1242 (21000): Subquery returns more than 1 row

The trouble is, I don't want the max rate. I want the rate where the date is max.

MySQL client version: 5.0.37 I have a table of items that sometimes references a table of rates. There may be multiples of a rate, depending on the date which the rate takes effect. CREATE TABLE `test_item` ( `id` varchar(20) NOT NULL, `rate` varchar(15) NOT NULL ); -- Dumping data for table `test_item` INSERT INTO `test_item` (`id`, `rate`) VALUES ('item1', 'rate1'), ('item2', ''), ('item3', 'rate2'); +-------+-------+ | id | rate | +-------+-------+ | item1 | rate1 | | item2 | | | item3 | rate2 | +-------+-------+ CREATE TABLE `test_rate` ( `id` varchar(15) NOT NULL, `date` date NOT NULL, `rate` mediumint(7) NOT NULL ); -- Dumping data for table `test_rate` INSERT INTO `test_rate` (`id`, `date`, `rate`) VALUES ('rate1', '2008-03-01', 100), ('rate1', '2008-03-05', 200), ('rate2', '2008-03-05', 300); +-------+------------+------+ | id | date | rate | +-------+------------+------+ | rate1 | 2008-03-01 | 100 | | rate1 | 2008-03-05 | 200 | | rate2 | 2008-03-05 | 300 | +-------+------------+------+ I want a list of all items and its most recent rate, like so: +-------+-------+-------+------------+------+ | id | rate | id | date | rate | +-------+-------+-------+------------+------+ | item1 | rate1 | rate1 | 2008-03-05 | 200 | | item2 | | NULL | NULL | NULL | | item3 | rate2 | rate2 | 2008-03-05 | 300 | +-------+-------+-------+------------+------+ The closest I can get: SELECT * FROM test_item LEFT JOIN test_rate ON test_item.rate = test_rate.id GROUP BY test_item.id ORDER BY test_item.id, test_rate.date DESC; +-------+-------+-------+------------+------+ | id | rate | id | date | rate | +-------+-------+-------+------------+------+ | item1 | rate1 | rate1 | 2008-03-01 | 100 | <-- Note the rate | item2 | | NULL | NULL | NULL | | item3 | rate2 | rate2 | 2008-03-05 | 300 | +-------+-------+-------+------------+------+ Removing the GROUP BY shows that the results are correctly sorted by date in the order I want, but the GROUP BY seems to take the first entry regardless of sorting rules, so I'm stumped. Any help much much appreciated!

You can explode, I suppose. Though I still think basename would work. Have you actually tried it?

Actually, if it's a MySQL db, you can work with just a month or day from a date field by SELECTing MONTH(date) or DAY(date).

This should be in your form: <input type="hidden" name="hiddenTotal" /> This should be in your javascript: document.getElementById('hiddenTotal').value=value;

As for the loop, look at the first 11, what is the difference? Just one number right? <?php for ($i=1; $i<=11; $i++) { if ($d <= strtotime($date1)) $fc1= ${'m'.$i.'fc'}; // a variable variable else $fc1="$fontcolor2"; } ?> Even 7 times is better than 77, right? And if you place this loop in another loop, you can get away with just one nested loop.

Seriously, the PHP manual is your friend. You should download it and start looking up stuff. www.php.net/docs.php The beginner tutorials on this site are also good. I learnt a lot from them. mktime() without any arguments will return the timestamp of the current datetime. You can use if (strtotime($date1) <= mktime()) color="$m1fc"; else color="#000000"; Comparing string compares the info as if they were a sentence, it doesn't know it's a date. It starts at the first letter/number. For 01/01/08, the first numerical is 0. For 12/31/07, the first numerical is 1. Therefore 01/01/08 is less than 12/31/07. It won't even bother to check the other numbers. Similarly, when comparing STRINGS, 123 is less than 55. But when comparing NUMBERS, 55 is less than 123. The idea of mktime() & strtotime() is to take a date (in a string format) and turn it into a number.

You know, I'm not understanding what exactly you're trying to accomplish here.... Anyway, you've got 1 variable (one!) in your form. How does your form pass the information to $numbers_received & $numsA? You're getting the error because those two variables don't have any information at all. Incidentally, $value up there would hold "dx2201" not "2201" like I suspect you're hoping. All that code up there does is copy the info from one array to another in a very difficult fashion. Wouldn't it be easier to transfer your information in separated arrays? Something that would result in: $dx = array (2201, 108); $sx = array (9);

I'm not sure if I'm understanding correctly, but if all you want is the filename you can use basename().

date() outputs a string, so you will be comparing two strings. 01/01/08 will definitely be less than 12/31/07. Try it and see. Sorry, I didn't mean strtotime(), I meant mktime(). It changes the date to a Unix timestamp. If in the future you do pick up CSS, please remember this conversation so you can laugh at yourself Also, I suggest loops. You shouldn't need to repeat the same code 77 times

Since you're looking for fully functioning code, this is not the right place. Try php.resourceindex.com

What's the error? I can't see anything wrong with that piece of code.

First you should check if the form is correcting info correctly. Do an echo $code before writing to your db, see if it shows what you expect. If not, start doing an echo every step back to see what went wrong.

Try use <form action="">, or use POST instead of GET