Jump to content

Archived

This topic is now archived and is closed to further replies.

XC-Runner

SQL Structure

Recommended Posts

I'm new to mySQL, so bear with me.

 

I need to create a database like this:

 

Database (Skills)

--Table (Woodcutting)

 

Then in that table, I need these values:

 

Regular = 25

Oak = 37.5

Willow = 67.5

Teak = 85

Maple = 100

Mahogany = 125

Yew = 175

Magic = 250

 

But I'm unsure as how to structure it. I was guessing this:

 

tree (VARCHAR(15))

regular

...

magic

 

And

 

exp (VARCHAR(8))

25

...

250

 

But then I got lost at when I insert the data, doesn't this get overwritten each time?

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?

$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"INSERT INTO woodcutting(tree, exp) VALUES(\'Regular\', \'25\')\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Doesn't that insert Regular -> 25, but then rewrite over it the next tree and exp?

 

Thanks,

 

XC-Runner

Share this post


Link to post
Share on other sites

No it doesn't. INSERT should never overwrite (UPDATE) or replace (REPLACE) any records in your table. Even so, it's in your best interest to create a unique id field. It will probably come in handy later on when dealing with intertable relations, and it's good bookkeeping practice anyways.

Share this post


Link to post
Share on other sites
No it doesn't. INSERT should never overwrite (UPDATE) or replace (REPLACE) any records in your table. Even so, it's in your best interest to create a unique id field. It will probably come in handy later on when dealing with intertable relations, and it's good bookkeeping practice anyways.

305007[/snapback]

 

Ok, thanks. Even if I won't need an id field, it's still good to have? Wouldn't that just be extra stuff I don't need?

 

XC-Runner

Share this post


Link to post
Share on other sites

Ok. So I wrote this program quickly to throw the stuff into the database.

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

 

$connection [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_connect[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'localhost\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'username\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'password\'[/span][span style=\"color:#007700\"]);

if(![/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]) {

  echo [/span][span style=\"color:#DD0000\"]\'Can\\'[/span][span style=\"color:#0000BB\"]t Connect[/span][span style=\"color:#007700\"]: [/span][span style=\"color:#DD0000\"]\' . mysql_error();

}

 

$db = mysql_select_db(\'[/span][span style=\"color:#0000BB\"]united_skills[/span][span style=\"color:#DD0000\"]\');

if(!$db) {

  echo \'[/span][span style=\"color:#0000BB\"]Can[/span][span style=\"color:#007700\"]&[/span][span style=\"color:#FF8000\"]#092;\'t Choose DB: \' . mysql_error();

[/span][span style=\"color:#007700\"]}

 

[/span][span style=\"color:#0000BB\"]$trees [/span][span style=\"color:#007700\"]= array (

[/span][span style=\"color:#DD0000\"]\"Regular\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]25[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Oak\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]37.5[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Willow\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]67.5[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Teak\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]85[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Maple\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]100[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Mahogany\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]125[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Yew\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]175[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Magic\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]250

  [/span][span style=\"color:#007700\"]);

 

foreach([/span][span style=\"color:#0000BB\"]$trees [/span][span style=\"color:#007700\"]as [/span][span style=\"color:#0000BB\"]$tree [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]$exp[/span][span style=\"color:#007700\"]) {

  [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"INSERT INTO woodcutting ( tree, exp ) VALUES ( $tree, $exp )\"[/span][span style=\"color:#007700\"];

  [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"])

    or die([/span][span style=\"color:#DD0000\"]\'Can\\'[/span][span style=\"color:#0000BB\"]t Add Data[/span][span style=\"color:#007700\"]: \'[/span][span style=\"color:#0000BB\"] . mysql_error());

}

 

?>        [/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

And it returns:

 

Can't Add Data: Unknown column 'Regular' in 'field list'

 

Which means the table isn't set up correctly...?

 

woodcutting

Field---Type--------------Null------Default

tree---varchar(20)-------Yes-------NULL

exp---varchar(5)---------Yes-------NULL

 

?

 

XC-Runner

Share this post


Link to post
Share on other sites

It should be:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

\"INSERT INTO woodcutting( tree, exp ) VALUES( \'$tree\', \'$exp\')\";

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Also, why is your exp field a VARCHAR type? Shouldn't you just use INT?

 

Thirdly, I'll bet it takes a lot more time to work out this script than just manually enter the data either with phpMyAdmin or the MySQL command prompt. :P

Share this post


Link to post
Share on other sites
It should be:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

\\\"INSERT INTO woodcutting( tree, exp ) VALUES( \'$tree\', \'$exp\')\\\";[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Also, why is your exp field a VARCHAR type? Shouldn't you just use INT?

 

Thirdly, I'll bet it takes a lot more time to work out this script than just manually enter the data either with phpMyAdmin or the MySQL command prompt. :P

305039[/snapback]

 

Thanks Arenium. And yes, it probably does take more time to right out the script, but I do like the extra practice I get writing out in PHP code. Plus I'm learning MySQL now, so it's probably best I learn how to code connectons, etc. with PHP.

 

XC-Runner

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.