These are the Database Optimization guidelines that I follow when writing my code, as a coding standard is already exists for the project I’ m working on. These can be helpful for the developer as it assist developers to know do’s and don’ts during the Database Optimization and coding practices. Actually Database Optimization TIPS helps any system to perform work efficiently and quickly.
As we know that CSV files are one of the most used way to back-up mysql database and tables. However it is very simple to export or backup your mysql database table as CSV file if you have PhpMyAdmin interface installed on your server. Actually problem arises when we have to deal with a shared hosting. But by using PHP script, you can export complete mysql database table to csv files. Here in this post I have given you a simple PHP script to create a CSV file. The script will call the database and select a table. Then it downloads the content line by line in a csv file.
This is a tested script just copy and paste it to your server.
In this post you’ll learn how to build a basic full-text search engine using PHP and MySQL.
The full-text search in mysql allows you to quick and very accurate search for single or multiple keywords from multiple columns. It does this by using an index on the columns that you want to search. There are a few limitations that you should know. Indexed columns can only be TEXT, MySQL does not by default index words that are less than 4 characters. There are also stop words.
Special Features of Full Text Search
So let’s start. First you need to create the index. The index should be on the columns that you will be searching. For example, to index the name, description column, you will have to run a MySQL query like below on table “Product”.
Indexing is an important feature of MySQL. MySQL supports different type of indexes like primary key index, unique index, normal index and full-text index. Indexes help to speed up the retrieval of data from MySQL database server. When retrieving the data from a database table, MySQL first checks whether the index of table exists; If yes it will use index to select exact physical corresponding rows without scanning the whole table.
Mostly we create index when creating table. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed automatically by MySQL. In addition, you can add indexes to the tables which has data. The statement to create index in MySQL is as follows:
In this post We will discuss what MySQL injection is and how important it is to prevent this attack on your mysql database. As developer it’s important to check all security measure before you make your website live.
“SQL injection is a code injection technique that exploits a security holes in a web application. These security holes happens when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL commands are thus injected from the web form into the database of an application. to change the database content or dump the database information like credit card or passwords to the attacker. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database“
Strings filtration
This is a main security holes that permits SQL statement to execute string without filtration. This means when a user input a string that will be passed on to the SQL statement, resulting in database manipulation. Below is the SQL statement with a vulnerable code:
The above SQL Query SELECT the password from the table admin_user, If the user input for the password is “‘ OR 1 = 1′” will result in the query being evaluate with an OR statement as 1 does equal 1, thus the query will return TRUE, resulting in a positive login.
After Injection: SELECT upassword FROM admin_user WHERE upassword = ” OR 1 = 1”
Login as super user ‘root’ in mysql and execute the following commands.
mysql> use mysql;
mysql> create user ‘test’@'%’ identified by ‘test’;
mysql> grant all on *.* to ‘test’@'%’ with grant option;
mysql> flush privileges;
There are 4 types of privileges.
i). Global privileges like *.* (all hosts connecting to Mysql db server)
Example: GRANT SELECT, INSERT ON *.* TO ‘someuser’@'somehost’;
ii). Database privileges like .*
Example: GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@'somehost’;
iii). Table privileges like SELECT, INSERT, UPDATE, DELETE
Example: GRANT SELECT, INSERT ON mydb.mytbl TO ‘someuser’@'somehost’;
iv). Column privileges like
Example: GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@'somehost’;
mysql> select version();
SELECT name FROM table LIMIT [, ] ;
if you want to get the rows between 10 and 20 do the following:
SELECT name FROM table LIMIT 10, 20 ;
Ans:
Yes. Please see below example.
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9) ;
ERROR 1044 (42000): Access denied for user ‘phpzag’@'localhost’ to database ‘mytest’.
Ans:
Please refer the error to DBA asking for granting the privilege to “mytest” database.
mysql > grant all on test.* to ‘user_name’ @ ‘host_name’ ;
In MySQL NULL is only equal to NULL, but NULL is not equal to ‘ ‘ ( blank value ) or 0(zero).
Command : SHOW TABLES LIKE ‘%’;
Database should be updated with timezone value from OS otherwise Mysq
MySQL is the most popular and most widely used open-source database system.
What is MySQL?
MySQL is a relational database. In relational structured database there are tables that store data. the tables are collections of related data entries and it consists of columns and rows. The columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. College,
Teachers, classes, Students).
Why We use Database?
As we all know Database has a availability of a storage system, the most obvious of these being persistence. we can identify the following reasons for wanting a Database Management Systems :
How can I import a MySQL dumpfile into my database.
You can easily restore or import MySQL data with mysql command itself.
For example:
Type the following command at the shell prompt to import sql data file:
$ mysql -u username -p -h localhost data-base-name < data.sql
If you have dedicated database server, replace localhost name with actual server name or IP address:
$ mysql -u username -p -h 210.42.1.12 databasename < data.sql
OR use hostname such as mysql.hosting.com
$ mysql -u username -p -h mysql.hosting.com database-name < data.sql
If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql
mysql -u root -p -h localhost mydatabase < D:/localhost.sql
We use the DELETE statement to delete records in a table.
Syntax
DELETE FROM table_name
WHERE some_column = some_value
Please note the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.
We use UPDATE statement to modify data in a table.
The UPDATE statement is used to update existing records in a table.
Syntax
UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value
Please Note the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.
