Category: mysql

5 Proven Methods to Repair Corrupt MySQL Table

phpmypassion

MySQL Server stores all the information in the form of tables.  Certain factors like abrupt system shutdowns, bad sectors on your system’s hard disk, software issues, insufficient storage space, etc. can corrupt MySQL tables at any time. You might not be able to access the MySQL database’s tables if this occurs. Also, you may fail to run queries which were related to the affected table and face errors. It can interrupt your workflow and productivity as well. In this post, we will share 5 proven methods to repair corrupt MySQL…

Difference between TRUNCATE , DELETE and DROP in MySql Server

phpmypassion

TRUNCATE TRUNCATE is a DDL(Data Manipulation Language) command TRUNCATE is executed using a table lock and whole table is locked for remove all records. We cannot use Where clause with TRUNCATE. TRUNCATE removes all rows from a table. Minimal logging in transaction log, so it is performance wise faster. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log. Identify column is reset to its seed value if table contains any identity column. To…

Process to Change MySql ip-Address to Listen for all IP’s

phpmypassion

*************** process to change MySql ipaddress to listen for all ip ******** -> update user set Host=”%” where User=”phpmyadmin”; -> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf [change “bind-address = 0.0.0.0”] -> flush privilage -> check by netstat -nlp | grep 3306 [ouput should be start listen on 0.0.0.0] -> telnet 192.168.1.19 3306 [now check its connecting or not with the ip]

Difference Between DDL and DML?

phpmypassion

DML DML statements are SQL statements that manipulate data. DML stands for Data Manipulation Language. The SQL statements that are in the DML class are INSERT, UPDATE and DELETE. Some people also lump the SELECT statement in the DML classification. DML example SQL statements are below – SELECT – retrieve data from the a database INSERT – insert data into a table UPDATE – updates existing data within a table DELETE – Delete all records from a database table MERGE – UPSERT operation (insert or update) CALL – call a…

Get A Insert Query Statement For All Rows in MySql

phpmypassion

This is a common thing that usually required for back end programming.  Sometimes we need all rows of a table as an query statement just like – INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (100,’some vlaue’,’some value’,’2018-10-20′); So the question is “How do we get the insert query statement for this easily ?” Here I am gonna write a simple PHP function by that you can get the insert query statement for all rows in a table :- <?php function makeInsertQuery($mysqli,$table, $where=null) { $sql=”SELECT * FROM `{$table}`”.(is_null($where) ? “” : ” WHERE “.$where).”;”;…

MySQL Group By Day, Month Or Year

phpmypassion

Most frequently occurred problem that is faced by developers.  Here I am gonna explain you for the query Group by Day, Month or Year in MySQL. Using the DATE_FORMAT operator, you can easily group the timestamp, date or datetime column using any format you want. For example, I needed to group rows that were added on the same day. Here is my query: select count(*), DATE_FORMAT(created_at,”%Y-%m-%d”) as created_day FROM widgets GROUP BY DATE_FORMAT(created_at,”%Y-%m-%d”) This query will give you result like this : count(*) | created_day 126 | 2012-04-12 168 |…

How To Insert Records Using Stored Procedure in MySql with PHP ?

anil

If you want to insert large records in a table so it is great to do that by using of Stored Procedure. There are number of benefits to insert record with the use of Stored Procedure Stored procedure cached on server so the speed is more fast.  A Stored Procedures will be in one place so that there’s no confusion of having business rules spread over potentially disparate code files in the project. More secure because there is no direct access with tables in database. I am describing the process…

How to Create a Simple Stored Procedure in mysql?

anil

What is a Stored Procedure? A stored procedure is a set of SQL statements that form a logical unit to perform a specific task. It encapsulates a group of operations or queries executed on a database server. Why Use Stored Procedures? Stored procedures isolate the client from implementation details and the underlying schema. They also reduce network traffic by executing SQL statements in batches, minimizing multiple client requests. Syntax: DELIMITER $$ CREATE PROCEDURE `simpleprocedure`(IN name varchar(50), IN user_name varchar(50), IN branch varchar(50)) BEGIN INSERT INTO student (name, user_name, branch) VALUES…