MySQLi Articles - Page 327 of 422

Let us first see an example to create a table, add records and display them. The CREATE command is used to create a table.mysql> CREATE table RowCountDemo -> ( -> ID int, -> Name varchar(100) > ); Query OK, 0 rows affected (0.95 sec)Records are inserted with the INSERT command.mysql>INSERT into RowCountDemo values(1, 'Larry'); Query OK, 1 row affected (0.15 sec) mysql>INSERT into RowCountDemo values(2, 'John'); Query OK, 1 row affected (0.13 sec) mysql>INSERT into RowCountDemo values(3, 'Bela'); Query OK, 1 row affected (0.15 sec) mysql>INSERT into RowCountDemo values(4, 'Jack'); Query OK, 1 row affected (0.11 sec) ... Read More

The choice depends upon the database. In ORACLE database, an empty string is converted to NULL.In MySQL, the usage of an empty string is better as compared to NULL. It is easy to check for an empty string with some boundary conditions, while this cannot be done with NULL. To find NULL, we need to add an extra condition i.e. ‘IS NULL’We can check that the length of NULL is 0 while length of empty string is 1.To check the length of NULL.mysql>SELECT count(NULL);The following is the output of the above query.+-----------------+ | count(NULL) | +-----------------+ ... Read More

We can escape single quotes with the help of the SELECT statement. For instance, when single quotes are encountered in a name, eg. “Carol’s”.Let us see the syntax.SELECT ‘SomeValue’;Here is an example that display how to include text with single quotes.mysql> SELECT 'Carol\'s Taylor.'; The following is the output.+-------------------+ | Carol's Taylor | +-------------------+ | Carol's Taylor | +-------------------+ 1 row in set (0.00 sec)

Timestamp is a data type in MySQL and works for different time zone. It is also used for date and time purpose To understand the concept, we need to create a table.Creating a tablemysql> CREATE table TimeStampDemo -> ( -> MyDataTime timestamp -> ); Query OK, 0 rows affected (0.57 sec)After creating the table, we will insert a record with the help of INSERT command.Inserting recordsmysql> INSERT into TimeStampDemo values (now()); Query OK, 1 row affected (0.12 sec)After inserting a record, we can display the records with the help of SELECT statement.Displaying recordsmysql> SELECT * from TimeStampDemo; After executing the ... Read More

The shortcoming of mysql_real_escape_string is as follows − It has main shortcoming in the modern API while we are making prepared statement. This has another shortcoming for every possible attack when you will typecast. Let us see the typecast syntax in MySQL − (TypeCast)mysql_real_escape_string($_REQUEST['anyColumnName'])); In the above syntax, we are typecasting, but in this case, it is not safer for every possible attack. The other cases include the following − It is not type safe. It is not for injection attack.

Yes, we can include a number for column name in MySQL. We need to use the symbol backtick, which is as follows( ` `)To understand, we will make a table with the help of CREATE command. Let us create a table −mysql> CREATE table NumberColumnDemo -> ( -> `123` varchar(100) -> ); Query OK, 0 rows affected (0.51 sec)Above, I have created a column name as a number with the help of backtick symbol.Now, we can check the same by inserting records with the help of INSERT command. Let us insert a record, which is as follows −mysql> INSERT into ... Read More

To understand whether a column exist or not, we have the following approaches −With the help of DESC commandUsing SHOW commandFirstly, we will create a table with columns −mysql> CREATE table ColumnExistDemo -> ( -> UniqueId int, -> UniqueName varchar(200), -> UniqueAddress varchar(200) -> ); Query OK, 0 rows affected (0.57 sec)In the first approach, we will get the entire column name with meaningful information. The syntax is as follows −DESC yourTableName; Let us apply the above query to check whether the column name exists or not. However, this approach is not good since it display all the columns.mysql> DESC ... Read More

The 1046 error occurs if you forget to select any database before creating a table. Let us see how and why this error occurs. We will try to create a table without selecting a database − mysql> CREATE table MyTable1 -> ( -> id int -> ); ERROR 1046 (3D000): No database selected Or mysql> INSERT into sample values(1); ERROR 1046 (3D000): No database selected Look at the output above, we are getting the same 1046 error: “No database selected” Now, we can resolve this error after selecting any ... Read More

To open root access from all hosts, we need to change the database to “mysql” with the help of USE command. The syntax of USE command is as follows − USE anyDatabasename; Now, I will use predefined database ‘mysql’, which is as follows − mysql> use mysql; Database changed I have changed the database above. Here is the query to get root access from the entire host − mysql> UPDATE user set host='%' where host='localhost'; Query OK, 6 rows affected (0.19 sec) Rows matched: 6 Changed: 6 Warnings: 0

Firstly, we need to create a table to understand the limit clause (as we want for SQL server).We will create a table with the help of CREATE command.Creating a tablemysql> CREATE table limitDemo -> ( -> id int, -> primary key(id) -> ); Query OK, 0 rows affected (0.58 sec)After that, let us insert records into the table −mysql> INSERT into limitDemo values(1); Query OK, 1 row affected (0.16 sec) mysql> INSERT into limitDemo values(2); Query OK, 1 row affected (0.12 sec) mysql> INSERT into limitDemo values(3); Query OK, 1 row affected (0.11 sec) mysql> INSERT into ... Read More