I would like to be able to show something like the above, but with a bit more meat…
I could use help/advice on how to manage this.
The problem I want to solve specifically is:
"This is image x of y edition. " where x is a serial number, and y is a field integer.
x will come from a prints table, and y will come from an editions table.
I must also validate (when I create a new record) that x is not greater than y
Thanks for any/all help…
Hi Rudy, yep, it’s a PHP-based app.
I have no trouble extracting the data elements.
I just have trouble formatting them…
I can prints.print-serial_no and I can get the editions.edition_size.
I just cannot figure out the simplest (I guess) way to format the output so there is a column that has
“This is” p.pserial “of” e.edition_size
I do over-complicate things- esp. when I’m tired…
okey doke… That’s the problem with a) being old, and b) staying up nights…
I will organize a better post.
Thanks for letting me know…
nobody is gonna know what you’re talking about unless maybe they also read your other thread, dave
i know i was deep into it, and i don’t know what you’re after in this thread
perhaps you might consider showing a few sample rows of data, similar to what i gave you, which will illustrate the scenario you want to work on
i don’t really know what “x is not greater than y” means, either
As Rudy suggested, here is more info…
I am building a set of applications for artists to keep track of their stuff. Most make items that are one off (ie a pot, a painting, a piece of wood or metal. Some, make limited editions of stuff, like 25 copies of the same image.
For this latter group, I have to be able to show where they are in making these limited editions.
Thus, I need to show that a specific item is made/printed, and is sold, or on consignment…
I must also validate to ensure that if a limited edition has a maximum run of 25 items, that that limit is not exceeded - you cant enter record 26 of 25.
My tables and basic record data:
CREATE TABLE negatives
( negative_serial_no INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, print_title VARCHAR(50)
) ENGINE=InnoDB
;
CREATE TABLE editions
( edition_serial_no INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, negative_serial_no INTEGER NOT NULL
, edition_size TINYINT NOT NULL
) ENGINE=InnoDB
;
CREATE TABLE prints
( print_key INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, edition_serial_no INTEGER NOT NULL
, print_serial_no TINYINT
) ENGINE=InnoDB
;
INSERT INTO negatives VALUES
( 10001,'nom nom nom' )
,( 10002,'ur doing it wrong' )
,( 10003,'imaginary bicycle' )
;
INSERT INTO editions VALUES
( 35,10001,25 )
,( 36,10002,10 )
,( 37,10002,10 )
;
INSERT INTO prints VALUES
( 935,35,1 )
,( 936,35,2 )
,( 937,37,1 )
;
I have a field in the editions table that sets the maximum size of the edition.
, edition_size TINYINT NOT NULL
I have a print_serial_number
, print_serial_no TINYINT
I am trying to show in a report:
“This is record prints.print_serial_no of editions.edition_size”
I’m stuck on how to make that simple line…
what are you building the app with, php? because that’s where most of the effort is going to go in this situation
the SQL side of it is really simple, although you will want different queries for different purposes
for a given print, which is what i think you’re asking, you’d us something like this –
SELECT negatives.negative_serial_no
, negatives.print_title
, editions.edition_serial_no
, editions.edition_size
, prints.print_key
, prints.print_serial_no
FROM prints
INNER
JOIN editions
ON editions.edition_serial_no = prints.edition_serial_no
INNER
JOIN negatives
ON negatives.negative_serial_no = editions.negative_serial_no
WHERE prints.print_key = 937
[B][COLOR="Blue"]negative_serial_no print_title edition_serial_no edition_size print_key print_serial_no[/COLOR][/b]
10002 ur doing it wrong 37 10 937 1
this query is driven by the WHERE clause and retrieves related information for print 937, which you can see is the 1st print of the 37 edition which allows 10 prints