Greetings. I seek your advisory for the following 2-table simple SQL problem. (i believe, simple, that is). Super-details (eg. Table3), i might add later (Eg. producer, writer, credits), but i need to get the thing moving, first. I've tried to use Std MySQL doc terminology, where applicable (Eg. table1.col1 , table2.col2). Please, don't hesitate to tell me what YOU need / wish to know about my task. Thank you!
Table1, Master:
CREATE TABLE `cp_master` (
`master_pk` int(11) NOT NULL AUTO_INCREMENT COMMENT 'master table primary key',
`master_date` date NOT NULL,
`master_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`master_pk`),
UNIQUE KEY `master_date` (`master_date`,`master_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
Table2, Details:
CREATE TABLE `cp_details` (
`details_pk` int(11) NOT NULL AUTO_INCREMENT,
`details_fk` int(11) NOT NULL COMMENT 'insert foreign key',
`details_date` date NOT NULL,
`details_toonname` tinytext NOT NULL,
`details_toonepi` tinytext NOT NULL,
`details_toontime` time NOT NULL,
PRIMARY KEY (`details_pk`),
KEY `details_fk` (`details_fk`,`details_date`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COMMENT='Cartoon Planet: Details'
This is a private/ personal DB created for Mgt of DVR media (indeed, @cartoonNetwork’s Cartoon Planet. yikes!).
Using phpMyAdmin to create / admin the thing, i find these queries are quite redundant. Having been much more active in the DB Admin game, years ago in my studies, i'm a bit rusty in terms of
“How might I minimize my query, while maintaining equal results?”
E.G.
INSERT INTO `cartoonplanet`.`cp_details`
(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`)
VALUES
(NULL, '11', '2013-04-05',
'Courage the Cowardly Dog', 'Klub Katz', '00:27:30');
My thoughts:
First, the timestamp on the master. It's superfluous, from any perspective, truly. I don't /really/ need to know at what time X was entered to Y, etc., but knowing i've usually used TimeStamps for indexing, etc., i stuck it there. Whatever: likely NOT at issue (and would have been of more use, had I placed it instead, in the Table2.)
Second
I'm looking at the query, thinking: i've got a PK (Eg. 11, in the code above), and an index (Eg. 2013-04-05). This is what bothers me. I don't really need both, in my Table2 INSERT Query.
At the crux
In addition to your valued advisory, RE optimization (Eg. you should have placed A at B, and used C instead of D), i wonder: supposing I code a proper interface for this DB, outside of the PMA-zone
as ultimately desirable, what is most important, in terms of the data entry, are
table2.`details_date`
and, particular to that bit of data, the
table2.`details_toontime`
, which reflects the counter on the DVR (Eg. 00:30:00 , where 30 -eq 30 mins).
Summary
How might I better submit such a query; prep the table for more efficient PHP; realizing there are multiple perspectives from which to attack this problem, please, how ever you wish to view it, i suspect you understand my general plight.
I've coded PHP / MySQL queries using LEFT JOIN, and INSERT's using SELECT statements. So, i'm not
quite at
beginner mode here. I'm seeking some feedback, having no one to bounce the topic-off-of, so to speak, other than ye in the cyberverse.
When I look at the query, I think:
how am I going to manipulate/ USE this data, from a PHP / interface point-of-view. Should I be placing the table2.ForeignKey-Ref (table2.`details_fk`) AND the date-as-index (table2.`details_date`) in EVERY submit?
I sense i'm encountering a “Which Came First: The Chicken-or-the-Egg scenario”, where my application is going to likely want to See/ rather, Display the dates, not the Keys.
E.G.
SQL = "INSERT INTO table2 VALUES '','SELECT...','SELECT...','ToonTitle','ToonEpisode','ToonTime'";
But, i'm having difficulty
wrapping my head around-it, so to speak (ie. it's been so long since i've done such coding, from a completely home-baked recipe).
Your feedback will be appreciated.
EDIT: SQL Gathered while working so far (gathering into a new Netbeans project, for some sanity)...
Insert a new table1.master row:
$sql = "INSERT INTO `cartoonplanet`.`cp_master` " .
"(`master_pk`, `master_date`, `master_timestamp`) " .
"VALUES (NULL, \'20XX-XX-XX\', CURRENT_TIMESTAMP);";
Insert a new table2.details row:
$sql = "INSERT INTO `cartoonplanet`.`cp_details` " .
"(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`) ".
"VALUES (NULL, \'11\', \'2013-04-05\', \'Courage the Cowardly Dog\', \'Klub Katz\', \'00:27:30\');";
Find a Toon by Name:
$sql = "SELECT * FROM `cartoonplanet`.`cp_details`\n" .
"WHERE `details_toonname` LIKE \'TOON_NAME\' ORDER BY `cp_details`.`details_pk` " .
"ASC LIMIT 0, 30 ";
Show what ToonEpi(sodes) are particular to the date(Index):
$sql = 'SELECT *'
. ' FROM `cp_details`'
. ' WHERE `details_date` = \'XXXX-XX-XX\';';
best regards!
user is Author of NoviceNotes.Net
Edited 7 time(s). Last edit at 04/29/2013 11:54AM by stanley tweedle.