[MySQL - All programming platforms] The correct way to do queries

[Komakech]

Hi everyone,

Recently a few threads have popped up on the help section in regards to MySQL syntax being incorrect. This is a quick guide on why this happens and how to fix it.

So firstly we are going to pretend this is our query:

select * from users where character = 21;

So firstly any commercial instructor would (should) tell you that command words must be capitalized. Although this is not a syntax issue it makes reading the query much easier!

Our new query:

SELECT * FROM users WHERE character = 21;

Okay so this query would error due to "character" being a command and not a field.

To safely escape any issues like this all fields/table names should be escaped using the ` (tildes) do not confuse this with an apostrophe.

So our new query looks like this:

SELECT * FROM `users` WHERE `character` = 21;

Now this query would run fine with no issues but even then the syntax is not commercially viable.

The final step is to place apostrophes around any values, in this case 21.

SELECT * FROM `users` WHERE `character` = '21';

Now this is a MySQL query that any decent company would teach to their employees while programming, whether it be C#, PHP or Java.

I hope this might clear up some stuff, please note this syntax stands for queries no matter how complex.

Edit:

I'd like to make it a point that when using PDO to run mysql queries with PHP, do not surround the variable with apostrophes. I did this and it bugged out because of that.

$statement->prepare("SELECT * FROM `users` WHERE `character` = ':var'");

This bugs out when binding :var with PDO. So make sure not to put apostrophes around :var, as such:

$statement->prepare("SELECT * FROM `users` WHERE `character` = :var");

Since we have joined the discussion further than syntax this comment is a very good one.

All the best,
Richard Komakech.

 

[Doctorate]

I know a person who got fired for not using the correct syntax, using it is good practice.

+1 Like + Rep.

Don't want to sound cocky, but might just take the liberty to add this:

 

[Dave]

I would only use the backticks when it's necessary.
If you must use backticks, then you should consider if you designed your database in the right way.

 

[Ron]

Still wrong, use mysqli:repare and don't use SELECT * in queries.

 

[Komakech]

I would only use the backticks when it's necessary.
If you must use backticks, then you should consider if you designed your database in the right way.

Although it's technically correct syntax to not include backticks it is commercially sought after to do queries in a reasonable way. Another good reason to always use backticks is ease of reading. In opinion I can read a query using my syntax much easier than one without backticks.

I'd prefer to view other's work using commercial standard syntax compared to one not using backticks and capitalization.

Still wrong, use mysqli:repare and don't use SELECT * in queries.

I did not at all speak about how you run your queries only what their syntax should be, I even expressed in the title it was against all programming platforms and noted at the end the syntax should be across all platforms e.g. C#, PHP or even Java.

mysqli will not work in C#...

As for don't use SELECT *, although it is not best to use it, a lot of the systems I have worked on use SELECT * knowing the tables never exceed 1mb, it has a minimal impact with tiny tables.

Just to note SELECT * is not actually part of the syntax that is a completely different discussion on optimization and practises, not syntax.

All the best,
Richard Komakech.

 

[Ron]

I know its not what you meant, but the way you wrote it implies using SELECT * is okay practice. Its not, and its never safe to assume SELECT * is okay because you assume the result will always be small.

mysqli wont work in C#

I quote from you again,

Now this is a MySQL query that any decent company would teach to their employees while programming, whether it be C#, PHP or Java.

Regardless, preparing statements is in the MySQL syntax and just because mysqli is PHP doesn't mean the same concept doesn't apply.

You must be registered to see links

 

[Komakech]

I know its not what you meant, but the way you wrote it implies using SELECT * is okay practice. Its not, and its never safe to assume SELECT * is okay because you assume the result will always be small.

Regardless, preparing statements is in the MySQL syntax and just because mysqli is PHP doesn't mean the same concept doesn't apply.

You must be registered to see links

I'm not quite sure how syntax turns into an argument over the content of the actual query itself. I understand MySQL is not just made up of those 3 commands but just because in a simple situation I have devised to show the syntax includes the SELECT * case it does not mean I am telling people they should use it, if that's the case I should also point out that character is a command word and should be renamed...

On top of that character seems to be linking to another table so they should rename the field to character_id, they should look to use InnoDB and make this field character_id be an index, which has a relation to the character_id field on their character_type table.

Oh did I mention you first need to make the table character_type with the field character_id set to auto increment.

Suddenly my tutorial has become a lot longer, more confusing and basically explains how to start a database for some sort of game...

All the best,
Richard Komakech.

 

[Ron]

Well you said you wanted to teach the concepts of "company level programming", so you might as well explain how 90% of "company level programmers" will use proper database classes.

 

[TimeBomb]

I'd like to make it a point that when using PDO to run mysql queries with PHP, do not surround the variable with apostrophes. I did this and it bugged out because of that.

$statement->prepare("SELECT * FROM `users` WHERE `character` = ':var'");

This bugs out when binding :var with PDO. So make sure not to put apostrophes around :var, as such:

$statement->prepare("SELECT * FROM `users` WHERE `character` = :var");

 

[Smitty]

I'd like to make it a point that when using PDO to run mysql queries with PHP, do not surround the variable with apostrophes. I did this and it bugged out because of that.

Yeah, that is the norm for most parameterized queries. Enclosing the parameter in quotes usually causes the parser to ignore it.

Also, ~ is a tilde, not `. ` is commonly referred to as a backtick.

 

[שเ๒єtгเ๒є]

The final step is to place apostrophes around any values, in this case 21.

SELECT * FROM `users` WHERE `character` = '21';

strictly speaking your final step is a security hole and forces MySQL to convert your value (not optimum ressourcing usage) when `character` is a numeric column. in this case, using single quotes is invalid in a moralic way.
in practice the waste is microscopically small, the cast problem makes more sense not to use quotes however:
by using single quotes something like '64rghdth' would become 64 on MySQL casting, while 64rghdth would throw an exception.

Komakech, i can already see u are a php programmer without even reading any of your other posts.

 

[Komakech]

strictly speaking your final step is a security hole and forces MySQL to convert your value (not optimum ressourcing usage) when `character` is a numeric column. in this case, using single quotes is invalid in a moralic way.
in practice the waste is microscopically small, the cast problem makes more sense not to use quotes however:
by using single quotes something like '64rghdth' would become 64 on MySQL casting, while 64rghdth would throw an exception.

Komakech, i can already see u are a php programmer without even reading any of your other posts.

Ahaha, I'm not sure wether to take it as a compliment or not!

Strictly that is true that 21 should not be '21' but as I haven't provided a database structure for this example it is not wrong but not right..

When I get some time later on tonight I will add in some bits to match the comments left pointing out these sorts of things. I'm at work at the moment so I can't do it now unfortunately. (My code's compiling \o/ )

All the best,
Richard Komakech.

 

[שเ๒єtгเ๒є]

Ahaha, I'm not sure wether to take it as a compliment or not!

Strictly that is true that 21 should not be '21' but as I haven't provided a database structure for this example it is not wrong but not right..

When I get some time later on tonight I will add in some bits to match the comments left pointing out these sorts of things. I'm at work at the moment so I can't do it now unfortunately. (My code's compiling \o/ )

All the best,
Richard Komakech.

u can take this as compliment and as no compliment too, it meant both.

the compliment part is, even i started with PHP, it helped me to get into the programming scene, there is nothing wrong on your post too, but i like to make things perfect so i droped my opinion.

in other case of no compliment, i dislike PHP and see it as very unprofessional. i even think your post lacks on that information, its not professional too (but everybody starts small, so its ok ^_^). i care alot about typesafe things, cuz it doesnt allow u to cut a tree with a hammer in methaphoric ways.

actually i want to help u to learn about this and improve your guide for all the others who are learning from it. and then, i was sitting at work enjoying my noon break with my bread roll in my hand and just were bored, so why not? :]

 

[ThuGie]

Is it me or is it missing some info about LIMIT, often adding LIMIT 1 < or other value depending on how many results you expect.
Can speed up the query quite a bit.

 

[Razer]

Is it me or is it missing some info about LIMIT, often adding LIMIT 1 < or other value depending on how many results you expect.
Can speed up the query quite a bit.

Is it me or did you just bump this thread?

 

[ThuGie]

Lies!

I never bump into things, well sometimes i don't quite check how far something is.
But always make sure i dont quite bump into stuff.

So it should have been at least on he first page .

 

[Parker]

Is it me or did you just bump this thread?

Bumped thread isn't the end of the world, it's still on topic and he brings up a pretty important point imo.

 

[ThuGie]

Ofcourse it isnt the end.
And yeah LIMIT usage can speed up a query quite a bit more so with a big butt database.
And one way or another a table will always grow to become huge .

Helped some people and by just adding LIMIT it can speed up query's by quite a bit the bigger the table the faster it becomes.
Ofcourse table design is also important. can get a table that takes 1+ seconds to 0.03 or lower.

 

[Ron]

Ofcourse it isnt the end.
And yeah LIMIT usage can speed up a query quite a bit more so with a big butt database.
And one way or another a table will always grow to become huge .

Helped some people and by just adding LIMIT it can speed up query's by quite a bit the bigger the table the faster it becomes.
Ofcourse table design is also important. can get a table that takes 1+ seconds to 0.03 or lower.

In addition to this, its good practice to also never use 'SELECT * ...' . Always select only the columns you are processing data for, and limit your results to no more than how many results you are actually going to display.

I see too many people using SELECT *, returning all results then using PHP to handle pagination on every page load. This puts a lot of stress on the DB if you start getting higher traffic.

 

[ThuGie]

You are totaly right 99% of the code i seen they do SELECT * even though its so easy to select the fields you need.

Edit:
Btw what i also often notice mostly in php.
People who just want 1 result. aka LIMIT 1
Still have a while loop after it.. even though there is always 1 result..