Jump to content


Photo

SQL Structure


  • Please log in to reply
6 replies to this topic

#1 XC-Runner

XC-Runner
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 October 2005 - 12:00 AM

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

#2 Arenium

Arenium
  • Staff Alumni
  • Advanced Member
  • 111 posts
  • LocationMassachusetts

Posted 11 October 2005 - 12:11 AM

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.

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 11 October 2005 - 12:11 AM

INSERT is to insert a new record
use UPDATE or REPLACE.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 XC-Runner

XC-Runner
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 October 2005 - 12:23 AM

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.

View Post


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

#5 XC-Runner

XC-Runner
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 October 2005 - 12:46 AM

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

#6 Arenium

Arenium
  • Staff Alumni
  • Advanced Member
  • 111 posts
  • LocationMassachusetts

Posted 11 October 2005 - 02:29 AM

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

#7 XC-Runner

XC-Runner
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 October 2005 - 08:19 PM

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

View Post


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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users