I’m having a brain meltdown.
In my application, I want to do something as simple as concat_ws a first and last name (and/or two parts of what will become a serial number).
I can handle the concat so that I can see the combined field…
BUT.
How do I write that new field back to the DB table…?
Thanks!!!
you’re welcome 
Thanks for all the help… I got it working… You helped me see the problem quite differently, and I’m much obliged.
David
Oh, if I have to change keys, change serial numbers en-masse (I have only about 2-300 records at the moment).
migrate?
Brazil? What about England!!
I thought for a moment that I might be getting this but then my brain got all soggy. I actually think I see what you’re doing. It’s always hard to think differently…
I will ponder your example, and figure out how to change my tables where needed.
Thanks a ton, and I guess that I wont mind if Brazil wins this one, as long as they don’t get nuked for doing so…
Thanks a ton Rudy. I’ll let you know how I make out…
David…
ps. just one more thought… If I do need to migrate my table keys/data, is there a preferred utility/method you have to do that?
no
since the edition is an auto_increment, all editions are unique
this is also why you don’t need to carry (redundantly) the negative number into the prints table
i did not understand what you meant by this –
select me if
table1 (negative_id and edition_id and print_id) equals table 2 (negative_id and edition_id and print_id).
what are table1 and table2 in that context?
I have two more questions…
“negative_serial_no can be omitted (it’s a redundant relationship), but if you feel you need it, make it INTEGER to match the PK of the negatives table”.
I could have a negative with number 123 and another with 124.
They each could have an edition number of 1.
If I do not have a negative number in this table, how would I differentiate them?
How do I do that with combining them and passing them as some variable.? And that’s the part I’m stuck on…
no problem, the brazil game hasn’t started yet
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 )
;
there are three negatives
the first negative has one edition, the second negative has two, and the third negative don’t be having no editions yet 
the (only) edition for the 10001 negative has two prints, while the second edition for 10002 has one print
I’ve been working too many nights on this whole project. So as I said when I started this thread, my brain feels like mush. I get that I’m over-complicating things.
If I could possibly ask you to give me an example of a query that pulls this together, I will be extremely grateful…
I REALLY appreciate all this help.
I will read Rudy’s note carefully and make the suggested changes.
The question remains though, how to make queries based on multiple keys.
For example:
to identify a specific print, how do I craft a query that says:
select me if
table1 (negative_id and edition_id and print_id) equals table 2 (negative_id and edition_id and print_id).
All I can promise you all is that when I learn something, I will help others.
David
in the editions table, you would specify a value for the editions_id
in the prints table, you would specify a value for the prints_id
by seeing which negative it is related to via the negative_serial_no
it’s really all quite simple
okay, you’re using auto_increments, that’s fine
you do not need concatenation – or, if you do, i don’t as yet see the need
let’s go through the tables one by one and check the keys
this is correct –
CREATE TABLE negatives (
negative_serial_no int(25) NOT NULL AUTO_INCREMENT,
…
PRIMARY KEY (negative_serial_no)
this is fine except INT(25) should just be INTEGER (the number in brackets is not the number of digits it will hold, but rather the number of digits to display if you use ZEROFILL, and i haven’t seen many apps where a 25-digit number needs to be displayed, especially since INTEGER can hold only up to 10 digits)
you should probably replace all those shoot date and time columns with a single DATETIME column
now the editions –
CREATE TABLE editions (
negative_serial_no int(11) DEFAULT NULL,
edition_serial_no int(10) NOT NULL AUTO_INCREMENT,
…
PRIMARY KEY (edition_serial_no),
KEY Editions_original_serial_no_idx (negative_serial_no)
this is also correct, although most people would put the auto_increment first in the table
also, you’ve indexed the foreign key to the parent negatives table, which is good
now the prints –
CREATE TABLE prints (
print_key int(10) unsigned NOT NULL AUTO_INCREMENT,
negative_serial_no varchar(25) COLLATE utf8_bin DEFAULT NULL,
edition_serial_no double DEFAULT NULL,
…
PRIMARY KEY (print_key),
KEY Prints_edition_serial_no_idx (edition_serial_no),
KEY Prints_customer_serial_no_idx (contact_serial_no)
the PK is correct, but should probably not be UNSIGNED, in order to stay consistent with the other keys (or else they should be UNSIGNED too – many people prefer UNSIGNED for auto_increments because it doubles the number of avaliable numbers from 2 billion to 4 billion)
negative_serial_no can be omitted (it’s a redundant relationship), but if you feel you need it, make it INTEGER to match the PK of the negatives table
edition_serial_no should be INTEGER to match the PK of the editions table
i notice that the negatives table references a collection using a VARCHAR column, you should probably revise that as well
you don’t need to concatenate, just use three separate columns
Remarks are correct, always when you connect two tables with primary and secondary key, conforming branches(columns) must have the same type and size.
this is very interesting…
I understand now about the redundancy of the negative-serial…
To answer your question, as I showed before, I have a table for negatives, another for editions, and another for prints…
In the editions and the prints tables, what would be the criteria for selecting a specific record.
How does the editions table know that this record is related to a specific negative. And the same with getting a specific print…
Here - and I do appreciate the help!!
Negatives:
CREATE TABLE negatives (
negative_serial_no int(25) NOT NULL AUTO_INCREMENT,
print_title varchar(50) COLLATE utf8_bin NOT NULL COMMENT ‘Photo Title’,
shootLocation varchar(25) COLLATE utf8_bin NOT NULL,
shootCountry varchar(25) COLLATE utf8_bin NOT NULL,
shootMonth int(10) DEFAULT NULL,
shootDay int(10) DEFAULT NULL,
shootYear int(10) DEFAULT NULL,
shootHr int(10) DEFAULT NULL,
shootMin int(10) DEFAULT NULL,
shootAMPM varchar(10) COLLATE utf8_bin DEFAULT NULL,
image_thumb longblob,
image_big blob,
cameraBody varchar(25) COLLATE utf8_bin DEFAULT NULL,
lens varchar(25) COLLATE utf8_bin DEFAULT NULL,
exposure_time varchar(25) COLLATE utf8_bin DEFAULT NULL,
speed varchar(25) COLLATE utf8_bin DEFAULT NULL,
fstop varchar(25) COLLATE utf8_bin DEFAULT NULL,
filter varchar(25) COLLATE utf8_bin DEFAULT NULL,
tripod varchar(25) COLLATE utf8_bin DEFAULT NULL,
film varchar(25) COLLATE utf8_bin DEFAULT NULL,
expHrs int(10) DEFAULT NULL,
expMin int(10) DEFAULT NULL,
expSec int(10) DEFAULT NULL,
notes longtext COLLATE utf8_bin,
fileYear int(11) DEFAULT NULL,
fileSheet int(11) DEFAULT NULL,
fileNegNumber int(11) DEFAULT NULL,
developer varchar(25) COLLATE utf8_bin DEFAULT NULL,
iso varchar(25) COLLATE utf8_bin DEFAULT NULL,
studio_id varchar(25) COLLATE utf8_bin DEFAULT NULL,
timeStamp timestamp NULL DEFAULT NULL,
collection_serial_no varchar(11) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (negative_serial_no)
) ENGINE=InnoDB AUTO_INCREMENT=1314 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Editions:
CREATE TABLE editions (
negative_serial_no int(11) DEFAULT NULL,
edition_serial_no int(10) NOT NULL AUTO_INCREMENT,
edition_size int(11) NOT NULL DEFAULT ‘25’ COMMENT ‘No. of prints allowed in the Edition’,
dollar_list_price int(10) DEFAULT NULL,
euro_list_price int(10) DEFAULT NULL,
contrast varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_dilution varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_time varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_temperature_a_f varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_toning varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_toning_time varchar(25) COLLATE utf8_bin DEFAULT NULL,
developer_toning_temp varchar(25) COLLATE utf8_bin DEFAULT NULL,
editionYear int(10) DEFAULT NULL,
editionMonth int(10) DEFAULT NULL,
editionDay int(10) DEFAULT NULL,
image_size_units varchar(25) COLLATE utf8_bin DEFAULT NULL,
width int(10) DEFAULT NULL,
height int(10) DEFAULT NULL,
date_edition_modified timestamp NULL DEFAULT NULL,
edition_id varchar(25) COLLATE utf8_bin DEFAULT NULL,
notes longtext COLLATE utf8_bin,
print_title varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (edition_serial_no),
KEY Editions_original_serial_no_idx (negative_serial_no)
) ENGINE=InnoDB AUTO_INCREMENT=700 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Prints:
CREATE TABLE prints (
print_key int(10) unsigned NOT NULL AUTO_INCREMENT,
negative_serial_no varchar(25) COLLATE utf8_bin DEFAULT NULL,
edition_serial_no double DEFAULT NULL,
print_serial_no int(10) DEFAULT NULL,
print_serialOld varchar(25) COLLATE utf8_bin DEFAULT NULL,
date_printed date DEFAULT NULL,
paper_type varchar(50) COLLATE utf8_bin DEFAULT NULL,
status varchar(25) COLLATE utf8_bin DEFAULT NULL,
date_of_disposition date DEFAULT NULL,
image_size varchar(25) COLLATE utf8_bin DEFAULT NULL,
image_width varchar(10) COLLATE utf8_bin DEFAULT NULL,
image_height varchar(10) COLLATE utf8_bin DEFAULT NULL,
frame_type varchar(15) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘Examples: Wood, Metal’,
frame_width int(10) DEFAULT NULL,
frame_height int(10) DEFAULT NULL,
frame_size_units varchar(10) COLLATE utf8_bin DEFAULT ‘’ COMMENT ‘mm, cm, inches’,
studio_location_id smallint(6) DEFAULT ‘0’,
studio_location varchar(50) COLLATE utf8_bin DEFAULT NULL,
customer_id varchar(25) COLLATE utf8_bin DEFAULT NULL,
contact_serial_no varchar(25) COLLATE utf8_bin DEFAULT NULL,
sold_price_dollar double DEFAULT NULL,
sold_price_euro double DEFAULT NULL,
print_titleOld varchar(25) COLLATE utf8_bin DEFAULT NULL,
edition_id varchar(25) COLLATE utf8_bin DEFAULT NULL,
notes longtext COLLATE utf8_bin,
glass_type varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (print_key),
KEY Prints_edition_serial_no_idx (edition_serial_no),
KEY Prints_customer_serial_no_idx (contact_serial_no)
) ENGINE=InnoDB AUTO_INCREMENT=1850 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
that looks fine, i don’t see where you need to do any concatenation at all
could you do a SHOW CREATE TABLE for these tables?
Thanks for responding. I have a key field that grows at each point in the application. (Please let me know if there’s a better way of doing this.
Key 1 = photograph.
Key 2 = photograph + edition (size - there can be different sizes of the same photograph)
Key 3 = photograph + edition + print number (a unique number for a specific prints).
I hope this is clear…
David
could you give an example of what you’re trying to do?
you want to write concatenated first/last names back to the database as a single column value? why? i mean, why do you need to store it?