3

I have a database named DELTASTORE in mysql in my cpanel. There are tables like ADMIN,CATAGORY,PRODUCT,ORDER. I have inserted some values in each table.
If I run sql

SELECT * FROM ADMIN

it works nicely.
But if I run sql 

SELECT * FROM ORDER

it doesn't work! Instead of that, if I run sql 

SELECT * FROM DELTASTORE.ORDER

then it works correctly.
Why does that occur?
Is it important to write database name before table name and give a dot between them all the time?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Rakib
  • 170
  • 2
  • 14

3 Answers3

2

To leave out the database prefix, you have to set a default database, with 

USE databasename

When writing programs to access the database, the API provides a way to do this. For instance, in PHP PDO you specify the default database in the DSN:

mysql:host=hostname;dbname=defaultDB

In MySQLi it's an argument to mysqli_connect(). In the obsolete mysql extension you use mysql_use_database(). There are similar methods in other programming languages.

Additionally, since ORDER is a MySQL keyword, you either have to put it in backticks:

SELECT * FROM `ORDER`

or prefix it with a database:

SELECT * FROM DELTASTORE.ORDER

It's usually best to avoid using MySQL reserved words as table or column names, to prevent problems like this. See Syntax error due to using a reserved word as a table or column name in MySQL

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • in my wamp server set up as a local server in my lapptop, i hava to write database name before table name every time i run a query. Do you have any solution for that? – Rakib Sep 05 '15 at 15:55
  • You need to use `USE databasename` to set the default database. – Barmar Sep 05 '15 at 23:46
0

The reason is, that ORDER is a SQL keyword (for ORDER BY, which you use to sort the result lines). So with the database name (or schematic name) you mean the table ORDER. It's better to not use keywords for the table.

dev.null
  • 538
  • 2
  • 7
-1

Just need to put brackets.

Select * from `ORDER`

Maxqueue
  • 2,194
  • 2
  • 23
  • 55