MYSQL errors!
Posted: Sun Jul 24, 2011 11:46 pm
Ok, a lot of problems seem to be happening in SQL right now: Time for a short overview.
Time to explain some basics:
SQL has different properties, right? If you add a new database to your localhost ( create new database ) you can add tables. Tables need fields to work. So adding fields to a table is a logical step. The reason you use databases is to store stuff right?
Now, what happens a LOT is that people just randomly select fields for their tables. Why is that? If you have a table for a character, something like:
characterid TEXT
userid TEXT
name TEXT
age TEXT
experience TEXT
this seems fine right? NO it`s not! There are certain rules and codes to honor when using databases. So a 101 in SQL:
1. If a field ends with ID ( id.. Id) it`s GOING TO BE AN INTEGER, NO QUESTIONS ASKED. It should allways be an integer, because the software behind the databases ( MySQL, MSSQL, Oracle ) Will expext it to be. Mostly because they will add an INDEX to it. Whether you told it to be PRIMARY or not, it`s going to get an INDEX.
2. While speaking of INDEX and PRIMARY, if you create a table called "character", logic dominates and tells you the first field should be characterid. This should get a PRIMARY key added to it. This is easy in MySQL so no problems there. Also, use AUTO_INCREMENT in MYSQL. It`s right there! Use it!
3. underscore_your_field_names_for_each_new_word.
characterid is going to be character_id, userid is going to be user_id. This is important because you have a clear distinction between different words. Sometimes, you`ll need to store a field which could look like this:
table "user_character"
user_id
character_id
It shows a clear seperation between the two. This by the way should not have a PRIMARY key but hey.. it`s an example OK?
4. This one is very important: Use the correct TYPES for your fields!!!!!!! It`s really important, m`kay? Like I said before, anything ending with ID should get the INT type. If you have a name it should get the TEXT type. If you have a correctly formatted date, use DATE. Time == TIME, date and time should be DATETIME, et cetera.
If you can`t really tell what to use, use VARCHAR, but use it only as a means to an end! Don`t use it for any functions inside SQL, because varchar is exactly what it claims to be; characters variying. This means that you can store whatever you want inside of it ( up to a limit of 255 characters) but due to the fact is has no real "type", you can`t check against it!
if you want a boolean value stored, use BOOLEAN, want a list? use SET for a list of STRINGS or ENUM for INTEGERS.
Want double precision stored? Darn, INT won`t work! 1.10 won`t work in SQL because INT is an INTEGER, not a DOUBLE. So, store decimal stuff in DOUBLE or FLOAT.
Et cetera, et cetera. You get the point right?
So, the correct field TYPES for our table should be:
character_id type = INT
user_id type = INT
name type = TEXT
age type = TINYINT // is allowed only to store 4 characters, so the maximum age is 9999
experience type = INT
5. Also VERY IMPORTANT::!!!! While in PHP, use the error functions provided by PHP!
let`s say we have 2 records in our character table:
char_id: 99 user_id: 2 name: Jimmy age:29 experience: 1000
char_id: 104 user_id: 3 name: 123123 age: 29 experience: 1000
We can query all we want on this: SELECT user_id FROM character WHERE name = '123123' <------- LOOK it`s a number, but I used quotes, it`s stored as TEXT!
Or this: SELECT * FROM character WHERE age = '29' .. WIll trigger an error, why? Because I used quotes on an integer, don`t do that.
Now, let`s say we have a mysql $db running:
$sql = "SELECT * FROM character WHERE experience = '1000' ";
$result = mysql_query($sql);
if($result){ // means, we did a successfull query
echo "yay it works.. ";
} else{
echo mysql_error();
}
What will happen is this: the query failed, it will branch into the else, and return mysql errors, if any. In our case, it will give us 1 error, because experience is of type INT and not text.
Anyway, expand on this as much as you like, if you feel like contributing to this topic, be my guest. The more people read this, the better!
Time to explain some basics:
SQL has different properties, right? If you add a new database to your localhost ( create new database ) you can add tables. Tables need fields to work. So adding fields to a table is a logical step. The reason you use databases is to store stuff right?
Now, what happens a LOT is that people just randomly select fields for their tables. Why is that? If you have a table for a character, something like:
characterid TEXT
userid TEXT
name TEXT
age TEXT
experience TEXT
this seems fine right? NO it`s not! There are certain rules and codes to honor when using databases. So a 101 in SQL:
1. If a field ends with ID ( id.. Id) it`s GOING TO BE AN INTEGER, NO QUESTIONS ASKED. It should allways be an integer, because the software behind the databases ( MySQL, MSSQL, Oracle ) Will expext it to be. Mostly because they will add an INDEX to it. Whether you told it to be PRIMARY or not, it`s going to get an INDEX.
2. While speaking of INDEX and PRIMARY, if you create a table called "character", logic dominates and tells you the first field should be characterid. This should get a PRIMARY key added to it. This is easy in MySQL so no problems there. Also, use AUTO_INCREMENT in MYSQL. It`s right there! Use it!
3. underscore_your_field_names_for_each_new_word.
characterid is going to be character_id, userid is going to be user_id. This is important because you have a clear distinction between different words. Sometimes, you`ll need to store a field which could look like this:
table "user_character"
user_id
character_id
It shows a clear seperation between the two. This by the way should not have a PRIMARY key but hey.. it`s an example OK?
4. This one is very important: Use the correct TYPES for your fields!!!!!!! It`s really important, m`kay? Like I said before, anything ending with ID should get the INT type. If you have a name it should get the TEXT type. If you have a correctly formatted date, use DATE. Time == TIME, date and time should be DATETIME, et cetera.
If you can`t really tell what to use, use VARCHAR, but use it only as a means to an end! Don`t use it for any functions inside SQL, because varchar is exactly what it claims to be; characters variying. This means that you can store whatever you want inside of it ( up to a limit of 255 characters) but due to the fact is has no real "type", you can`t check against it!
if you want a boolean value stored, use BOOLEAN, want a list? use SET for a list of STRINGS or ENUM for INTEGERS.
Want double precision stored? Darn, INT won`t work! 1.10 won`t work in SQL because INT is an INTEGER, not a DOUBLE. So, store decimal stuff in DOUBLE or FLOAT.
Et cetera, et cetera. You get the point right?
So, the correct field TYPES for our table should be:
character_id type = INT
user_id type = INT
name type = TEXT
age type = TINYINT // is allowed only to store 4 characters, so the maximum age is 9999
experience type = INT
5. Also VERY IMPORTANT::!!!! While in PHP, use the error functions provided by PHP!
let`s say we have 2 records in our character table:
char_id: 99 user_id: 2 name: Jimmy age:29 experience: 1000
char_id: 104 user_id: 3 name: 123123 age: 29 experience: 1000
We can query all we want on this: SELECT user_id FROM character WHERE name = '123123' <------- LOOK it`s a number, but I used quotes, it`s stored as TEXT!
Or this: SELECT * FROM character WHERE age = '29' .. WIll trigger an error, why? Because I used quotes on an integer, don`t do that.
Now, let`s say we have a mysql $db running:
$sql = "SELECT * FROM character WHERE experience = '1000' ";
$result = mysql_query($sql);
if($result){ // means, we did a successfull query
echo "yay it works.. ";
} else{
echo mysql_error();
}
What will happen is this: the query failed, it will branch into the else, and return mysql errors, if any. In our case, it will give us 1 error, because experience is of type INT and not text.
Anyway, expand on this as much as you like, if you feel like contributing to this topic, be my guest. The more people read this, the better!
