fatblokeonbike wrote:
. I'm seeing double with this, and I just KNOW it's got to be simple -
The table "images" contains columns:
id | reference_number | image | width | height
That's all it contains, nothing else.
id is auto-increment
reference_number is usually set anew by the operator to input new data, but sometimes he returns to an earlier one to change data.
Each new reference_number will not always be higher than the previous one.
The reference_number being worked will not always be the highest in its sequence.
There are always eight images per reference_number, numbered 01 to 08, with their different widths and heights.
All types are integer.
As the operator inputs new data for the reference_number he's just newly set, or returned to, I want his monitor to display, on-the-run, all the data he's input for the reference_number he's working (even if the reference_number is newly set, he's just input image 01 and there are no images 02 to 08 yet) and order it by increasing image number.
Things like:
SELECT id, image, width, height, max(id) AS top FROM images WHERE reference_number=top ORDER BY image
one of a seeming hundred variants that I've tried, plus others with LIMIT and HAVING, just give MySQL indigestion.
(Perhaps I'm over-egging the pudding with the description, but it's helping me make sure I have it right.)
I really need a holiday...
Any thoughts? (No, not where I should go for the holiday!)
T.I.A.
If I understand you correctly, you need the id of the last insert/update so you can pull all the rows with the same reference_number. You can get the last inserted/updated id with the LAST_INSERT_ID function, use that to recover the reference_number, then get the desired rows.
Try this:
SELECT @ref:=reference_number FROM images WHERE id=LAST_INSERT_ID();
SELECT * FROM images WHERE [EMAIL PROTECTED] ORDER BY image;
Or, you could do it in a single select with a join:
SELECT im1.id, im1.image, im1.width, im1.height FROM images AS im1, images AS im2 WHERE im1.reference_number = im2.reference_number AND im2.id = LAST_INSERT_ID() ORDER BY i1.image;
See <http://www.mysql.com/doc/en/Miscellaneous_functions.html> for more on LAST_INSERT_ID().
Hope that helps. If that's not what you meant, give an example showing how the output should look to help us see what you want.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]