Mysql Newbie Seeking Nuggets Of Wisdom

[ulanji]

Greetings!

 

I've done some limited work with relational databases, and I'm grounded in basic database concepts like normalization, referential integrity, primary & foreign keys, etc. I've also done a fair amount of basic web design... HTML, CSS, JavaScript.

 

However, I have yet to touch off the explosion that happens when you let one hand talk to the other by tying a webpage to a back-end database. I am very eager to make this happen, so I was curious if anyone had tips on a good place to start.

 

I ventured blindly into the server-side MySQL interface offered by TCH, but quickly fell flat on my face (error-msg. pasted below). I wasn't sure what the best approach would be (ASP, JSP, etc.) given the deployment of MySQL at TCH.

 

To anyone who responds, thanks for your time!

 

---

 

SQL-query :

 

CREATE TABLE `EVENTS` (

 

`artist` TEXT( 80 ) NOT NULL ,

`program` TEXT( 80 ) NOT NULL ,

`date` TEXT( 10 ) NOT NULL ,

`venue` TEXT( 80 ) NOT NULL ,

`state` TEXT( 40 ) NOT NULL ,

INDEX ( `artist` , `program` , `date` , `venue` , `state` )

) COMMENT = 'Calendar of KP Artist Engagements'

 

MySQL said:

 

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(80) NOT NULL, `program` TEXT(80) NOT NULL, `date` TEXT(10) NOT

 

---

[HCSuperStores]

I think you're on the right road ... but your "text" should be "varchar". Text doesn't receive a length.

 

I'm doing some remote teaching with MySQL ... my first suggest is to envision a simple project and try to complete it. If you have questions as you make it work, they will be specific and more easy to handle.

 

Let me know. I'm always around!

[DarqFlare]

HC is correct. As always.

 

Yeah, if you need any MySQL-type help, I'm also available, as I've used it for awhile now. I could still stand to learn more myself.

[kaseytraeger]

I've done some limited work with relational databases, and I'm grounded in basic database concepts like normalization, referential integrity, primary & foreign keys, etc.

Wow!!! If you're a database newbie, what does that make me? I've never even heard of things like referential integrity and database normalization. But hey, I guess I'm not doing too bad. At least I know what a primary key is! I think I may know what a foreign key is, too, but I'll need to check myself on that one!

 

[Alan]

lol, Databases?

 

I either find a program/script someone has alrdy created, or get help making the databases, because I have NO idea what I am doing there lol.

 

I do however know the basics of HTML, Java, and Liberty Basic.

[HCSuperStores]

Hi TCH-Robert,

 

I have a secret that I've just discovered for being right: Only open your mouth when you know the answer!

 

I've been working on my "forum addiction" and have found that answering 100 posts a day was a problem. I'm seeking help.

 

Seriously, I've been doing much more reading than responding lately. I'll keep to my levels of expertise and thus keep me out of trouble and remember the 12 steps!

 

Always nice to have someone look at your answers though. The brain stalls now and then.

 

Good day!

[DarqFlare]

My brain stalls too often... Once a month is too often for us computer geeks!

 

In reference to the text/varchar... There are two different wants to do that..

 

Option #1:

 

>CREATE TABLE `EVENTS` (

`artist` TEXT NOT NULL ,
`program` TEXT NOT NULL ,
`date` TEXT NOT NULL ,
`venue` TEXT NOT NULL ,
`state` TEXT NOT NULL ,
INDEX ( `artist` , `program` , `date` , `venue` , `state` ) 
) COMMENT = 'Calendar of KP Artist Engagements'

 

Or Option #2 (Preferred):

 

>CREATE TABLE `EVENTS` (

`artist` VARCHAR( 80 ) NOT NULL ,
`program` VARCHAR( 80 ) NOT NULL ,
`date` VARCHAR( 10 ) NOT NULL ,
`venue` VARCHAR( 80 ) NOT NULL ,
`state` VARCHAR( 40 ) NOT NULL ,
INDEX ( `artist` , `program` , `date` , `venue` , `state` ) 
) COMMENT = 'Calendar of KP Artist Engagements'

[ulanji]

I changed the data type from VARCHAR to TEXT, and it worked. Thanks for the help everybody!

 

OK, now that I've got this table, anyone know how it can be accessed for inserts, updates, deletes, SQL-fetches, etc? I'm thinking ASP is the way to go, but again--I'm a newbie at this stuff. I'm not sure of:

 

A) How to invoke SQL statements from an HTML (or HTML-rendered) page, or

How to make the SQL "connect" to my database.

 

As always, thanks for any help!

 

*bows to the fellow computer geeks*

[HCSuperStores]

The short answer is ... you can't. Or at least that I am aware of.

 

To create the ability to make changes to your database, you have to write scripts (PHP and Perl) to interface with your database to execute these things.

 

If you are willing to detail more of your project and what exactly you are wanting to accomplish, I can better assist you in your goals. There's many different ways to accomplish things, but usually 1 or 2 are the best. Knowing more about your project will enable me to tell you the better direcitons to go.

 

FYI - ASP is not really support on Linux. It is ... but as an add-on and not on the TCH servers.

[DarqFlare]

Yeah. You'll need a scripting language to connect to the DB. HTML is a static language.

[ulanji]

Crikey! Looks like I'll have to learn PHP then... Which is cool--it'll be a good resumé-booster.

 

Basically I've got a calendar page right now that is a flat table:

 

*start grimacing now*

 

( http://www.kirklandproductions.com/calendar.html )

 

*whew! that was rough!*

 

I've seen some very sexy calenar interfaces out there, which I'd love to work my way up to. Right now, I'm just looking for to create a dynamic set of click-sortable columns. Like, you click on the date column to sort all by date, the artist column to sort all by artist, etc. Now, once the table is populated I might be able to accomplish these sorts of queries via XML and JavaScript (I've only read the first chapter of the XML book I picked up last month so I'm not sure yet), but I'm also hoping to get an HTML/PHP interface to accomplish write-type events (adds/updates/deletes).

 

Looks like I've got lots of studying to do!

[DarqFlare]

Good luck. PHP is a great language.