Page 1 of 1
PHP for TV Quotes
Posted: Tue Jul 24, 2007 9:50 am
by adamsblueguitar
Hello! I'm the owner of
http://OfficeQuotes.net , and I'm in need of some PHP code (I think).
My site contains every quote ever said on the show The Office.
The quotes are not stored in a database, they're all just static text.
I need something that will allow me to store all of the quotes separately, tag them with who said it, the episode, etc...
I've been googling for a while, but cannot find anything suitable.
Does anyone know of something that could help me out?
Thanks in advance,
Adam
Posted: Tue Jul 24, 2007 10:09 am
by Chalks
php and mysql will definately make that easy.
look for php functions:
mysql_connect()
mysql_close()
mysql_query()
mysql_fetch_array()
also look for mysql commands:
SELECT
INSERT
You can use mysql to create a database that has a table named "office" in it. In that table each row could contain:
quote id number
quote
speaker
episode
air date
and more
This tutorial is fairly short, and will give you a pretty easy introduction to mysql databases using php.
Posted: Tue Jul 24, 2007 10:27 am
by adamsblueguitar
Thanks, Chalks!
Building this by hand would be quite a bit of work for me, but if there were no other alternative I would definitely give it a try. I know basic SQL commands, but that's as far as I've gone.
Before I begin, do you think there are any scripts out there, already in place? Or anything that is premade?
Posted: Tue Jul 24, 2007 10:29 am
by Chalks
I'm sure there are scripts premade for that kind of thing. I would recommend coding it yourself though. It's not as hard as it seems, and the experience is good.
I mean, I only started php/mysql a week ago, and I'm pretty sure I could code what you're looking for without any difficulties at all.
So, try it! and if you get stuck, someone here will be happy to help.

Posted: Tue Jul 24, 2007 10:40 am
by adamsblueguitar
Alright, I'll give it a shot!
Thanks for your help, Chalks, and I'm sure I'll be back here with more questions. =)
Posted: Tue Jul 24, 2007 1:38 pm
by adamsblueguitar
Question #1.
I've created the database, I'm creating my first table inside.
These are quotes from a TV show that I'm doing. Sometimes, the "collective quote" has more than one speaker. Example:
Code: Select all
Michael: Hey, Pam, how would you, like to be our cheerleader today? You know, some, ah, pigtails? A little, ah, halter top, you could tie that up. And you know, something a little, just, youthful, for a change. Just this once?
Pam: I don't think so Michael. Besides, I can't cheer against my fiance.
Jim: I'll do it. Wear a little flouncey skirt if you want, and...
Michael: Yeah, I bet you would. Just try not to be too gay on the court. And by gay I mean, um, you know, not in a homosexual way at all. I mean the uh, you know, like the bad-at-sports way. I think that goes without saying.
I want that whole thing as an entry in the database.
I would like to somehow tag that with each speaker who spoke. Michael, Pam, and Jim.
If I had a "speaker" field in side my "quotes" table, how do I add multiple entries to that field?
try a freelancer
Posted: Tue Jul 24, 2007 7:02 pm
by yacahuma
Why dont you try a site like
http://www.getafreelancer.com/
I am sure you can get someone for less than $100 to do this for you.
db sample
Posted: Tue Jul 24, 2007 8:49 pm
by yacahuma
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Yo need a db like this
[syntax="sql"]
-- ----------------------------
-- Table structure for actor
-- ----------------------------
CREATE TABLE `actor` (
`actorID` smallint(5) unsigned NOT NULL auto_increment,
`actorName` varchar(100) default NULL,
PRIMARY KEY (`actorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for actor_quote
-- ----------------------------
CREATE TABLE `actor_quote` (
`quoteID` int(10) unsigned NOT NULL,
`actorID` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`quoteID`,`actorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for quote
-- ----------------------------
CREATE TABLE `quote` (
`quoteID` int(10) unsigned NOT NULL auto_increment,
`quoteText` text,
PRIMARY KEY (`quoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `actor` VALUES ('1', 'pam');
INSERT INTO `actor` VALUES ('2', 'micheal');
INSERT INTO `actor` VALUES ('3', 'jim');
INSERT INTO `actor_quote` VALUES ('1', '1');
INSERT INTO `actor_quote` VALUES ('1', '2');
INSERT INTO `actor_quote` VALUES ('1', '3');
INSERT INTO `quote` VALUES ('1', 'icheal: hey ddxxxxxx Pam: xxxxxxxxx Jim: xxxxxxx');
that way a quote could have one or more actors
I am not a sql expert but if you are going to look by name.
The best way will be to look by actorID
Code: Select all
SELECT DISTINCT
`quote`.`quoteID`,
`quote`.`quoteText`
FROM
`quote`
Inner Join `actor_quote` ON `quote`.`quoteID` = `actor_quote`.`quoteID`
Inner Join `actor` ON `actor_quote`.`actorID` = `actor`.`actorID`
WHERE
`actor`.`actorName` IN ('Pam','Jim')
use php adodb for all you database stuff
if you dont want to create all the pages for administration i suggest you use eather phpmyadmin of navicat, (very easy to use admin program) and enter the data there and then just create the code for the selection[/syntax]
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Tue Jul 24, 2007 10:19 pm
by Chalks
whew, that's a lot, yacahuma. I'm new to mysql myself, and I'm a bit overwhelmed there (though now that I look a little closer, it makes sense). I would probably just make one table, and if I had more than one person talking I would insert under speaker "michael,pam,jim" then when I pull it out, I would just test for commas. If there are commas, then explode() it. If not... then there was only one speaker.
Posted: Tue Jul 24, 2007 10:33 pm
by John Cartwright
yacahuma: you really only need two tables, as your actor_quote table is unncessary
Code: Select all
SELECT * FROM `quotes`
INNER JOIN `actors` ON `quote`.`actor_id` ON `actors`.`id`
would do just fine
Posted: Tue Jul 24, 2007 10:53 pm
by Benjamin
adamsblueguitar, if you need some help with this I'd be willing to volunteer. Just send me a PM and let me know.
actor_quote is needed
Posted: Wed Jul 25, 2007 4:55 am
by yacahuma
jcart, the actor_quote table is not necesary if you have the name of the person as the key. I would not do that since he will have to repeat the name for every quote.
Posted: Wed Jul 25, 2007 5:01 am
by onion2k
Jcart wrote:yacahuma: you really only need two tables, as your actor_quote table is unncessary
Code: Select all
SELECT * FROM `quotes`
INNER JOIN `actors` ON `quote`.`actor_id` ON `actors`.`id`
would do just fine
Each quote can have multiple actors ... it's a one-to-many relationship ... therefore it need a cross reference table in the middle.
Re: try a freelancer
Posted: Wed Jul 25, 2007 5:02 am
by onion2k
Before going there, why not go to the Volunteers forum on here and see if someone will help out for free.