[SOLVED] Data Types?

[justspiffy]

Hi there. I am just learning mysql, and am making my first database. I have finished creating all of the tables, but I am not sure what data type to use for them. There is ID, NAME, and NUM. The problem I am running into I think is because the NAME and NUM both have letters, numbers and symbols like ! and - . I am not sure how to get around this. I will post what I have created here.

Thanks for any help!!

Shawna

 

-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- http://www.phpmyadmin.net
-- 
-- Host: localhost:3306
-- Generation Time: Aug 07, 2008 at 03:47 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3
-- 
-- Database: `mornington_sql`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `chan`
-- 

CREATE TABLE `chan` (
  `ID` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  `num` varbinary(20) default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=518 DEFAULT CHARSET=utf8 AUTO_INCREMENT=518 ;

-- 
-- Dumping data for table `chan`
-- 

INSERT INTO `chan` (`ID`, `name`, `num`) VALUES (1, 'TV Ontario', 2 TVO),
(2, 'Global Toronto', 3 GLBTo),
(3, 'OMNI 1', 4 OMNI1),
(4, 'PBS Buffalo', 5 WNED),
(5, 'CBC Toronto', 6 CBCTo),
(6, 'City TV Toronto', 7 CTYTo),
(7, 'A Channel Wingham', 8 CKNX),
(8, 'Crossroads', 9 CTS),
(9, 'A Channel London', 10 CFPL),
(10, 'E!ONT', 11 E!ONT),
(11, 'SRC Toronto', 12 SRCTo),
(12, 'CTV Kitchener', 13 CKCO),
(13, 'RDI', 14 RDI),
(14, 'CTV NewsNet', 15 CTVNN),
(15, 'The Shopping Channel', 16 TSC),
(16, 'ABC Buffalo', 17 ABC 7),
(17, 'CBS Buffalo', 18 CBS 4),
(18, 'FOX Buffalo', 19 FOX 29),
(19, 'NBC Buffalo', 20 NBC2),
(20, 'HNN', 21 HNN),
(21, 'A Channel Barrie', 22 A-BAR),
(22, 'OMNI 2', 23 OMNI2),
(23, 'Sun TV', 24 SUNTV),
(24, 'TV Tropolis', 25 TV Tropolis),
(25, 'TSN, 26 TSN),
(26, 'Treehouse', 27 TREE),
(27, 'Tele Toon Retro', 28 TOONR),
(28, 'YTV', 29 YTV-E),
(29, 'NFL Network', 30 NFL-N),
(30, 'TVA Montreal', 31 TVAML),
(31, 'Weather Channel', 32 WEA-T),
(32, 'CBC Newsworld', 33 CBCNW),
(33, 'BBC World', 34 BBC W),
(34, 'CPAC', 35 CPAC),
(35, 'CNN', 36 CNN),
(36, 'Fox News Network', 37 FOXNC),
(37, 'Business News Network', 38 BNN),
(38, 'Ontario Legislative', 39 OLA),
(39, 'TQS Montreal', 40 TQSML),
(40, 'Country Music Television', 41 CMT),
(41, 'Aboriginal People TV Network', 42 APTN),
(42, 'Vision TV', 43 VISION),
(43, 'Much Music', 44 MUCH),
(44, 'Musicmax', 45 Musicmax),
(45, 'Casino Gaming TV', 46 GTV),
(46, 'Tele Quebec', 47 TELEQ),
(47, 'Canadian Learning TV', 48 CLTV),
(48, 'NTV', 101 NTV),
(49, 'ASN', 102 ASN),
(50, 'Global Halifax', 103 GLBHx),
(51, 'CBC Fredericton', 104 CBCFn),
(52, 'SRC Moncton', 105 SRCMn),
(53, 'CBC Toronto HD', 106 CBCHD),
(54, 'CTV Toronto', 107 CTVTo),
(55, 'CTV Toronto HD', 108 CTVHD),
(56, 'City TV Toronto HD', 109 CTYHD),
(57, 'Global Toronto HD', 110 GLBHD),
(58, 'ABC Buffalo HD', 111 ABCHD),
(59, 'CBS Buffalo HD', 112 CBSHD),
(60, 'FOX Buffalo HD', 113 FOXHD),
(61, 'NBC Buffalo HD', 114 NBCHD),
(62, 'PBS Buffalo HD', 115 PBS-H),
(63, 'Omni 1 HD', 116 OM1HD),
(64, 'Omni 2 HD', 117 OM2HD),
(65, 'Sun HD', 118 SUNHD),
(66, 'SRC Toronto HD', 119 SRCHD),
(67, 'CBC Winnipeg', 120 CBCWg),
(68, 'Global Winnipeg', 121 GLBWg),
(69, 'SRC Winnipeg', 122 SRCWg),
(70, 'CTV Calgary', 123 CTVCy),
(71, 'CBC Vancouver', 124 CBCVr),
(72, 'City TV Vancouver', 125 CTYVr),
(73, 'CTV Vancouver', 126 CTYVr),
(74, 'E! British Columbia', 127 E!BC),
(75, 'Global Vancouver', 128 GLBVr),
(76, 'SRC Vancouver', 129 SRCVr),
(77, 'ABC Seattle', 130 ABCSe),
(78, 'CBS Seattle', 131 CBSSe),
(79, 'Fox Seattle', 132 FOXSe),
(80, 'NBC Seattle', 133 NBCSe),
(81, 'PBS Seattle', 134 PBSSe),
(82, 'Family Channel', 135 FMLY),
(83, 'Playhouse Disney', 136 PHDIS),
(84, 'Family Channel VOD', 137 FCVOD),
(85, 'Discovery Kids', 138 D-KDS),
(86, 'BBC Kids', 139 BBC-K),
(87, 'Tele Toon English', 140 TOONE),
(88, 'Tele Toon French', 141 TOONF),
(89, 'A & E', 142 A&E),
(90, 'A & E HD', 143 A&EHD),
(91, 'Discovery Channel', 144 DISC),
(92, 'The Learning Channel', 145 TLC),
(93, 'History Channel', 146 HIST),
(94, 'HGTV', 147 HGTV),
(95, 'Food Network', 148 FOOD),
(96, 'Slice', 149 SLICE),
(97, 'Comedy Network', 150 CMDY),
(98, 'Comedy Network 2', 151 CMDY2),
(99, 'Golf Channel', 152 GC),
(100, 'HDNET', 153 HDNET),
(101, 'Sportsnet Ontario', 154 SNETO),
(102, 'TSN-Alternative Service', 155 TSN-A),
(103, 'Outdoor Life Network', 156 OLN),
(104, 'CP 24', 157 CP24),
(105, 'Bravo!', 158 BRAVO),
(106, 'Star TV', 159 STAR),
(107, 'Space', 160 SPACE),
(108, 'Spike TV', 161 SPIKE),
(109, 'MTV Canada', 162 MTV),
(110, 'Much More Music', 163 MMM),
(111, 'Gameshow Network', 164 GSN),
(112, 'CNBC', 165 CNBC),
(113, 'Documentary', 300 D),
(114, 'Drive-In Classics', 301 DRVIn),
(115, 'IFC', 302 IFC),
(116, 'Lonestar', 303 LStr),
(117, 'Movie-ola', 304 M-OLA),
(118, 'Peachtree', 305 PT),
(119, 'Showcase Action', 306 ACTN),
(120, 'Showcase Diva', 307 DIVA),
(121, 'Showcase East', 308 SHO-E),
(122, 'Showcase West', 309 SHO-W),
(123, 'Silver Screen Classics', 310 SLVC),
(124, 'Turner Classic Movies', 311 TCM),
(125, 'Art TV(French)', 360 ARTV),
(126, 'Biography', 361 BIO),
(127, 'Book TV', 362 BIO),
(128, 'Court TV Canada', 363 BOOK),
(129, 'Discovery Channel HD', 364 DISHD),
(130, 'G4 Tech TV', 365 TECH),
(131, 'Animal Planet', 366 ANIML),
(132, 'BBC Canada', 367 BBC-C),
(133, 'Discovery Civilization', 368 CIVIL),
(134, 'Military Channel', 369 MILIT),
(135, 'National Geographic', 370 GEO),
(136, 'Travel Channel', 371 TRAVL),
(137, 'Equator HD', 372 EQATRL),
(138, 'Oasis HD', 373 OHD),
(139, 'Discovery Health', 425 HLTH),
(140, 'Fashion TV', 426 FTV),
(141, 'Fine Living', 427 FINEL),
(142, 'Men TV', 428 MENTV),
(143, 'One', 429 ONE),
(144, 'Treasure HD', 430 TRHD),
(145, 'W', 431 W),
(146, 'Out TV', 432 OUTTV),
(147, 'Punch Much', 433 PUNCH),
(148, 'Razer', 434 RAZER),
(149, 'Rush HD', 435 RUSH),
(150, 'Scream', 436 SCRM),
(151, 'Sex TV', 437 SEXTV),
(152, 'Much Vibe', 438 VIBE),
(153, 'TV Land', 439 TVLND),
(154, 'BOLD', 440 BOLD),
(155, 'Mystery', 441 MTY),
(156, 'ESPN Classic Canada', 480 ESPNC),
(157, 'Fight Network', 481 FIGHT),
(158, 'GOL TV', 482 GOLTV),
(159, 'Leafs TV', 483 LEAFS),
(160, 'Leafs TV HD', 484 LFSHD),
(161, 'NHL Network', 485 NHL-N),
(162, 'Raptor TV', 486 RAPTR),
(163, 'Raptor TV HD', 487 RAPHD),
(164, 'Speed Channel', 488 SPEED),
(165, 'Sportsnet East', 489 SNETE),
(166, 'Sportsnet HD', 490 RSNHD),
(167, 'Sportsnet Pacific', 491 SNETP),
(168, 'Sportsnet West', 492 SNETW),
(169, 'The Score', 493 SCORE),
(170, 'Wild TV', 494 WILD),
(171, 'World Fishing Network', 495 WFN),
(172, 'TSNHD', 496 TSNHD),
(173, 'The Movie Network', 550 M),
(174, 'M More', 551 MMORE),
(175, 'M Excess', 552 MXS),
(176, 'M FUN', 553 MFUN),
(177, 'M Fest', 554 MFEST),
(178, 'TMN HD', 555 TMNHD),
(179, 'Movie Pix', 556 MPIX),
(180, 'More Movie Pix', 557 MRPIX),
(181, 'Movie Pix HD', 558 PIXHD),
(182, 'Super Channel 1', 562 SUPR1),
(183, 'Super Channel 2', 563 SUPR2),
(184, 'Super Channel 3', 564 SUPR3),
(185, 'Super Channel 4', 565 SUPR4),
(186, 'Super Channel HD1', 566 SCHD1),
(187, 'Super Channel HD2', 567 SCHD2),
(188, 'CW 11', 570 CW11),
(189, 'KTLA Los Angeles', 571 KTLA),
(190, 'WGN Chicago', 572 WGN),
(191, 'Galaxie Rock', 601 GXY1),
(192, 'Galaxie Rock Alternative', 602 GXY2),
(193, 'Galaxie Adult Alter.', 603 GXY3),
(194, 'Galaxie Gold Rock', 604 GXY4),
(195, 'Galaxie Rock and Roll', 605 GXY5),
(196, 'Galaxie Hit List', 606 GXY6),
(197, 'Galaxie Pop Adult', 607 GXY7),
(198, 'Galaxie Pop Classics', 608 GXY8),
(199, 'Galaxie Easy Listening', 609 GXY9),
(200, 'Galaxie The 70's', 610 GXY10),
(201, 'Galaxie The 80's', 611 GXY11),
(202, 'Galaxie 90's', 612 GXY12),
(203, 'Galaxie New Country', 613 GXY13),
(204, 'Galaxie Country Classics', 614 GXY14),
(205, 'Galaxie Raggae', 615 GXY15),
(206, 'Galaxie Rap/Hip Hop', 616 GXY16),
(207, 'Galaxie Urban Adult', 617 GXY17),
(208, 'Galaxie Dance', 618 GXY18),
(209, 'Galaxie Electronics', 619 GXY19),
(210, 'Galaxie Jazz Masters', 620 GXY20),
(211, 'Galaxie Jazz Now', 621 GXY21),
(212, 'Galaxie Smooth Jazz', 622 GXY22),
(213, 'Galaxie Blues', 623 GXY23),
(214, 'Galaxie Big Band', 624 GXY24),
(215, 'Galaxie Standards', 625 GXY25),
(216, 'Galaxie Folk Roots', 626 GXY26),
(217, 'Galaxie Latino Tropical', 627 GXY27),
(218, 'Galaxie World/Monde', 628 GXY28),
(219, 'Galaxie Nature', 629 GXY29),
(220, 'Galaxie Instr./New Age', 630 GXY30),
(221, 'Galaxie Ambient', 631 GXY31),
(222, 'Galaxie Baroque', 632 GXY32),
(223, 'Galaxie Classic Masters', 633 GXY33),
(224, 'Galaxie Chamber Music', 634 GXY34),
(225, 'Galaxie Opera Plus', 635 GXY35),
(226, 'Galaxie Kid's Stuff', 636 GXY36),
(227, 'Galaxie Celtic', 637 GXY37),
(228, 'Galaxie Christian', 638 GXY38),
(229, 'Galaxie Franco Pop', 639 GXY39),
(230, 'Galaxie Franco Country', 640 GXY40),
(231, 'Galaxie Band a part', 641 GXY41),
(232, 'Galaxie Souvenirs', 642 GXY42),
(233, 'Galaxie Nostalgic', 643 GXY43),
(234, 'Galaxie Franco Retro', 644 GXY44),
(235, 'Galaxie Franco Fetes', 645 GXY45),
(236, 'CBC Radio 1', 646 CBCen),
(237, 'Radio Canada AM', 647 CBCfr),
(238, 'U of T Campus Radio', 648 CUIT),
(239, 'Ryerson Campus Radio', 649 CKLN),
(240, 'York Campus Radio', 650 CHRY),
(241, 'Markham Comm. Radio', 651 CHEV),
(242, 'Toronto Franc. Radio', 652 CHOQ),
(243, 'CBC Radio 2', 653 CBCen),
(244, 'Voicepoint', 654 VPRNT),
(245, 'Aboriginal Voices Radio', 655 CFIE),
(246, 'Radio Canada-FM', 656 CBCfr),
(247, 'Playboy Channel', 801 PBYC),
(248, 'South Asian TV', 802 SATV);

[toplay]

When you know the name column isn't going to ever go over a certain length, use varchar or char. Using text data type for this is an overkill.

 

CREATE TABLE `chan` (

  `ID` int(11) NOT NULL auto_increment,

  `name` varchar(25) NOT NULL,

  `num` varchar(20) default '0',

  PRIMARY KEY  (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

All the data for the last column must be surrounded by quotes. For example, change this:

 

VALUES (1, 'TV Ontario', 2 TVO),

 

to this:

 

VALUES (1, 'TV Ontario', '2 TVO'),

 

 

See manual for more details:

 

http://dev.mysql.com/doc/refman/5.0/en/char.html

 

http://dev.mysql.com/doc/refman/5.0/en/blob.html

 

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

[justspiffy]

Thanks so much for you help!!