Found 4219 Articles for MySQLi

You can use phpMyAdmin, since it is one of the best free tools. This can be used for every system with PHP and MySQL. It is a free and open source administration tool for MySQL and MariaDB. PHPMYADMINHere is the URL to download −https://www.phpmyadmin.net/downloads/The following are the features of phpMyAdmin −Open source toolMySQL and MariaDB database management.One of the most popular MySQL administration toolsEasily Import data from CSV and SQLExport data to various formats. These include : CSV, SQL, XML, PDF, Word, Excel, LaTeX, etc.Administering multiple serversYou can also use EMMA. This is also a light-weight application.Read More

You can create JSON format using group_concat() function from MySQL. The syntax is as follows −SELECT yourColumnName1, GROUP_CONCAT(CONCAT('{anytName:"', yourColumnName, '", anyName:"', yourColunName, '"}')) anyVariableName from yourTableName group by yourColumnName1;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table JsonFormatDemo -> ( -> UserId int, -> UserName varchar(100), -> UserEmail varchar(100) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert ... Read More

To select the last 6 months records from news table, use the date_sub() function from MySQL since news records are arranged according to date.The syntax is as follows −select *from yourTableName where yourDateTimeColumnName >= date_sub(now(), interval 6 month);To understand the above concept, let us first create a NEWS table with only NEWS ID and the date on which it published −mysql> create table Newstable -> ( -> NewsId int, -> NewsDatetime datetime -> ); Query OK, 0 rows affected (0.66 sec)Insert records in the table using insert command. ... Read More

You can use prepare statement to Reset MySQL AutoIncrement using a MAX value from another table.The following is the syntax −set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1); SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2 AUTO_INCREMENT=', @anyVariableName1); PREPARE yourStatementName FROM @anyVariableName2; execute yourStatementName;The above syntax will reset MySQL auto_increment using a maximum value from another table. To understand the above syntax, let us create two tables. The first table will contain the records and the second table will use the maximum value from the first table and use for an auto_increment property.The query to create a table is as follows −mysql> create table FirstTableMaxValue ... Read More

The following is the syntax to work with FOR LOOP in MySQL stored procedure −delimiter // CREATE procedure yourProcedureName() wholeblock:BEGIN DECLARE anyVariableName1 INT ; Declare anyVariableName3 int; DECLARE anyVariableName2 VARCHAR(255); SET anyVariableName1 =1 ; SET anyVariableName3 =10; SET anyVariableName2 = ''; loop_label: FORLOOP IF anyVariableName1 > anyVariableName3 THEN LEAVE loop_label; END IF; SET anyVariableName2 = CONCAT(anyVariableName2 ,anyVariableName1 ,', '); SET anyVariableName1 = anyVariableName1 + ... Read More

Format the date DD/MM/YYYY using select and order by in descending order. The syntax is as follows −SELECT *FROM yourTableName where yourDatetimeColumnName order by STR_TO_DATE(yourDatetimeColumnName, ’%d/%m%Y’) desc;The above syntax will give the date in descending order. To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table DateFormatWithSelect    -> (    -> UserId int,    -> UserName varchar(100),    -> UserLoginDatetime varchar(100)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert ... Read More

To get the users logged in today, use the below syntax. Here, we are expecting that your datetime field is a string type −select yourColumnName1, yourColumnName2, yourColumnName3, ...N from youTableName WHERE STR_TO_DATE(yourColumnName1, ‘format’') =CURDATE();Let’s say we have the following “DateEqualToday “ table that stores users first and last name with the login date −+------+------------+-----------+------------+ | Id   | First_Name | Last_Name | LoginDate  | +------+------------+-----------+------------+ |    1 | James      | Smith     | 20-12-2018 | |    2 | Carol      | Taylor    | 21-12-2017 | |    3 | John       ... Read More

Update only one cell’s data with the help of UPDATE command. The syntax is as follows −UPDATE yourTableName yourColumnName=yourNewValue where yourColumnName=yourOldValue;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table changeCellsData    -> (    -> Id int,    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into changeCellsData values(101, 'Mike', 23); Query OK, 1 row affected (0.12 sec) mysql> insert into ... Read More

To loop through all rows of a table, use stored procedure in MySQL. The syntax is as follows −delimiter // CREATE PROCEDURE yourProcedureName() BEGIN DECLARE anyVariableName1 INT DEFAULT 0; DECLARE anyVariableName2 INT DEFAULT 0; SELECT COUNT(*) FROM yourTableName1 INTO anyVariableName1; SET anyVariableName2 =0; WHILE anyVariableName2 < anyVariableName1 DO    INSERT INTO yourTableName2(yourColumnName, ...N) SELECT (yourColumnName1, ...N) FROM yourTableName1 LIMIT anyVariableName2, 1;    SET anyVariableName2 = anyVariableName2+1; END WHILE; End; //To understand the above syntax, let us create two tables i.e. one has records and the second table will have records from the loop using stored procedures.The following is the query ... Read More

The now() function returns the constant time that exhibits the time at which any statement began to execute. The sysdate() function returns the exact same datetime at which it executed the statement from MySQL 5.0.13.Suppose if you are updating datetime with now() in triggers or stored procedure, the now() method returns the time at which time the triggering and stored procedure begin to execute.Here is the demo of update with now(). Let us first create a table. The query to create a table is as follows −mysql> create table NowDemo -> ( -> DueDateTime datetime -> ); Query OK, 0 ... Read More