• Home
  • ZarxBiz
  • Forum
Igniter Code
IgniterCode.com === Zarx.Biz
ReactJS

ecommerce cart react source code 2022 [ For Beginners ]

October 8, 2022 0 comment

Why and how to use sql views 2022

August 22, 2022 0 comment

how to import csv to mysql using php

August 22, 2022 0 comment
PHP

Code faster using visual studio Updated 2022

July 31, 2022 0 comment

Web socket crash course 2022 updated step by step

July 3, 2022 0 comment

How to use stored procedure in sql updated 2022

September 3, 2022 0 comment
MySQL

Must know mysql functions updated 2022

September 3, 2022 0 comment

PHP Composer Autoload Complete Guide 2022

August 13, 2022 0 comment
Category:

MySQL

Stored procedure in mysql with example [ For beginners ] 2022
MySQL

How to use stored procedure in sql updated 2022

by Manu September 3, 2022
written by Manu

 

Hi Devs in this guide we are going to see what is stored procedure in sql, So Basically when you use a query again and again then there you can use stored procedure. You create a procedure with query and call the stored procedure every time instead of calling query again and again.

Here is an example

Create a Stored Procedure

We can create store procedure as given in this statement

DELIMITER $$

	CREATE PROCEDURE user_names_procedure()
		BEGIN
			 SELECT DISTINCT first_name as name FROM users;
		END $$
    
DELIMITER ; 

After executing this statement you will be able to see stored procedure created.

Now we can call this stored procedure

Calling A Stored Procedure

call user_names_procedure;

It will return us data

Passing Arguments in Stored Procedures

This is interesting, We can pass arguments in stored procedure, let’s same example again this time with argument.

DELIMITER $$

	CREATE PROCEDURE user_names_procedure(IN user_age int)
		BEGIN
			SELECT first_name as name FROM `users` where age>user_age;
		END $$
    
DELIMITER ; 

Here we passed “user_age” as argument and its type is “integer”.

So let’s see how we will call this procedure

call user_names_procedure(50);

Here we passed 50 as “user_age”.

so we get users list where users has age more than 50. as 50 passed as argument.

IN AND OUT Arguments

Ok if you noticed in stored procedure we passed “user_age” argument as “IN”.

CREATE PROCEDURE user_names_procedure(IN user_age int)

Now we can also pass out argument as well.

so it will look like this

CREATE PROCEDURE user_names_procedure(IN user_age int, OUT argumentName int)

Here we are saying argument is of type “integer”. Let’s see complete example

First i drop the previously created procedure.

DROP STORED Procedure

DROP PROCEDURE user_names_procedure;

Now re-create procedure with output parameter.

DELIMITER $$

	CREATE PROCEDURE user_names_procedure(IN user_age int, OUT total_users int)
		BEGIN
			SELECT count(*) into total_users FROM `users` where age>user_age;
		END $$
    
DELIMITER ; 

total_users passed as parameter but also used in query.

Calling the procedure.

call user_names_procedure(50, @users_count);


select @users_count;

Here we are getting returned value in variable and then we are selecting it.

DELIMITER : If you are wondering what is this, Then i must say it is used when we work with stored procedures, function and triggers.

Hope this helps

September 3, 2022 0 comment
0 FacebookTwitterPinterestEmail
Mysql functions tutorial [ For Beginners ]
MySQL

Must know mysql functions updated 2022

by Manu September 3, 2022
written by Manu

 

Hi devs, Today we are going to see some Must know MYSQL Predefined functions, These functions can save a-lot of your time. Mysql has so-many function and they are to deal with different data types,

You can change formatting, values or do calculations from your query.

WHEN and THEN

If i want to select users data from users table i will get it as

select * from users ;

Now it will return we all columns name, email, age, gender etc.

Here you can see gender is given as “M” and “F”. But i want output as “MALE” and “Female”. So i can change my query using When and then. here is how you do it.

SELECT
    first_name, last_name,
    salary, age,
    CASE gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM users;

So here we are using Case and then we used when and then. out put will be now as

IF CONDITION

We can filter data inside sql queries, No need to do it afterwords. Here is how you do it. I want to get users data but if age more than 65 that means employee is retired otherwise still employed.

SELECT
    first_name, last_name,
    salary, age, IF(age>65, "RETIRED", "EMPLOYEE")
FROM users;

And output looks like this

Nice Right.

IFNULL

Quite similar, If value is null in table we can return something else, Here is the example

SELECT
    first_name, last_name,
    salary, age, IFNULL(NULL, 'N/A Email')
FROM users WHERE email='NULL';

Now it returns

So if column value is NULL it is replaced with provided value.

DATE_FORMAT

Want to display time but after changing its formatting, Yes you can do it straight in the query. and here is how you do it.

SELECT
    first_name, last_name,
    salary, age,
    DATE_FORMAT(created_at, "%d-%m-%Y")
FROM users;

Original date and time format we have in table can be seen from the “First image” in this post, Now the format is changed by DATE_FORMAT. so output looks like.

You can even get day name and month name from query, Here is how

SELECT
    first_name, last_name,
    salary, age,
    DAY(created_at), 
    DAYNAME(created_at), 
    MONTH(created_at), 
    MONTHNAME(created_at), 
    YEAR(created_at)
FROM users;

And output is

Add NOW to get current date and time

SELECT
    NOW()
FROM users;

ADD – SUBTRACT TIME FROM DATE

get a date add 10 days into it. all in one query.

SELECT
	 created_at,
     ADDDATE(created_at, INTERVAL 10 DAY)
FROM users;

You can add time or Subtract time

SELECT
	 created_at,
     ADDDATE(created_at, INTERVAL 10 DAY),
     ADDDATE(created_at, INTERVAL 30 MINUTE),
     ADDDATE(created_at, INTERVAL -1 HOUR),
     ADDDATE(created_at, INTERVAL -2 MONTH)
FROM users;

Out put is like

TRUNCATE

Ok this is also useful so lets say amount is shown as “345.456453”.

you can TRUNCATE it as given below

TRUNCATE("345.456453", 2)  // this will return 345.45 

SUM, MIN, MAX

You might have seen these bad girls before. we can simply get Sum of all values in column and maximum value in a column or minimum value in column.

SELECT
     SUM(salary),
     MAX(salary),
     MIN(salary)
FROM users;

Out put is like

Same way there is AVG to find out Average

So if we want to know average salary in this users table.

SELECT
     TRUNCATE(AVG(salary),2)
FROM users;

Here i have used TRUNCATE and AVE together you can do that too. Output is

STRING FUNCTIONS

SELECT
     UPPER(first_name),
     UCASE(first_name),
     REVERSE(first_name),
     TRIM('  some text  '),
     SUBSTRING_INDEX(email, "@", 1),
     email,
     last_name,
     SUBSTRING(last_name, 2, 3),
     INSERT(first_name, 1,5, "USER: ")
FROM users;

First 2 are “UPPERCASE” and “UCASE” they work as same for making string capital. Reverse of UCASE is LCASE for lowering the casing.

Then reverse will reverse the string. After that we have TRIM it removes white space from left and right of the string.

SUBSTRING_INDEX will return the string where it finds the substring.

INSERT this will insert your defined value in string and replace the string between indexes mentioned.

Here is the output

September 3, 2022 0 comment
0 FacebookTwitterPinterestEmail
Why and how to use sql views [ Super Easy ] for beginners
MySQL

Why and how to use sql views 2022

by Manu August 22, 2022
written by Manu

Hi Devs, Today we are going to see what exactly is SQL views and why you should use them. SQL views are the virtual tables created by you when you define a command to create a view. You can do all normal operation such as create,update,delete on views. Views can be very useful if used correctly.

Lets see How to Create a View

// To create a view.

mysqli_query($conn, "CREATE VIEW userCustomList AS SELECT first_name as name, age as user_age FROM users");

Here we are creating a view which will hold data “name” and “user_age” only from the main table.

Update a SQL View Selection

Incase you want to update a view

// Updating View Selection

mysqli_query($conn, "CREATE OR REPLACE VIEW userCustomList AS 
    SELECT first_name as name, age as user_age, salary FROM users WHERE salary > 90000");

Here we are also getting “salary” and we are getting only those users where salary more than 90000.

Update a Record In View

// Simple query to update view

mysqli_query($conn, "UPDATE userCustomList SET name = 'Something'");

Note : Update and delete will also take effect in original table as well, So be very careful. And take backup of your data.

Deleting A View

// This will delete the view.

mysqli_query($conn, "DROP VIEW userCustomList");

Incase you want to delete data from view

Delete Data From View

// Deleting data from view

mysqli_query($conn, "DELETE FROM userCustomList");

Note : Update and delete will also take effect in original table as well, So be very careful. And take backup of your data.

August 22, 2022 0 comment
0 FacebookTwitterPinterestEmail
how to import csv to mysql using php
MySQLPHP

how to import csv to mysql using php

by Manu August 22, 2022
written by Manu

Hi devs, In this guide we are going to see how to import millions of rows of data into database from csv file. This might take some time but for doing this we are going to process data using chunk method. So mainly we will process chunk of data and insert it into database and same process repeating again and again.

On we will follow these steps to import data

  1. Breaking large csv to smaller csv’s.
  2. reading smaller csv’s and store in database table.
  3. Each query will insert 1000 rows of data. (Max allowed in single query so).

Download the code zip if you wan it also check the video guide for practical usage.

In video guide we are also verifying if the all data inserted correctly or not.

Download Fileshttps://zarx.biz/topic/333/import-data-from-csv-file-to-database-in-php-import-millions-rows

August 22, 2022 0 comment
0 FacebookTwitterPinterestEmail

Recent Posts

  • ecommerce cart react source code 2022 [ For Beginners ]
  • How to use stored procedure in sql updated 2022
  • Must know mysql functions updated 2022
  • Why and how to use sql views 2022
  • how to import csv to mysql using php

Recent Comments

No comments to show.
  • Facebook
  • Twitter
  • Youtube
  • Email

@2022 - All Right Reserved


Back To Top
Igniter Code
  • Home
  • ZarxBiz
  • Forum