MYSQL errors!

Place for questions and answers for all newcomers and new coders. This is a free for all forum, no question is too stupid and to noob.

MYSQL errors!

Postby Xaleph » Sun Jul 24, 2011 6: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!
Xaleph
 
Posts: 897
Joined: Sun Feb 06, 2011 9:55 pm
Location: Groningen, Netherlands
Has thanked: 0 time
Been thanked: 2 times
Blog: View Blog (6)

Re: MYSQL errors!

Postby 62896dude » Sun Jul 24, 2011 8:15 pm

This is an excellent description! It should be stickied for future users, as it really covers everything you need to know about MySQL!
Languages: C++, C#, Javascript + Angular, PHP
Programs: Webstorm 2017, Notepad++, Photoshop
Current Project: HP Destiny
User avatar
62896dude
 
Posts: 516
Joined: Wed Jan 19, 2011 9:39 pm
Location: Wisconsin
Has thanked: 3 times
Been thanked: 1 time

Re: MYSQL errors!

Postby hallsofvallhalla » Wed Jul 27, 2011 10:16 am

62896dude wrote:This is an excellent description! It should be stickied for future users, as it really covers everything you need to know about MySQL!


done


and nice post.
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11998
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 164 times
Blog: View Blog (3)

Re: MYSQL errors!

Postby Chris » Mon Sep 05, 2011 4:28 pm

Little tip, anytime you request information from the database `'s to wrap around your table and field names, eg:

This will work:
Code: Select all
SELECT `select` FROM `from` WHERE `where` = 'still works'

whereas this will not:
Code: Select all
SELECT select FROM from WHERE where = 'does not'


Will save lots of time if you called your field a reserved phrase in SQL without realizing it.
Fighting for peace is declaring war on war. If you want peace be peaceful.
User avatar
Chris
 
Posts: 1575
Joined: Wed Sep 30, 2009 2:22 pm
Has thanked: 4 times
Been thanked: 5 times
Blog: View Blog (2)


Return to Beginner Help and Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron

x