Display MySQL columns into Rows- phpmyAdmin

SELECT r0.unit_model, r0.tab_type, r1.title_en AS “H1_EN”, r2.title_en AS “H2_EN”, r3.title_en AS “H3_EN” FROM (SELECT DISTINCT unit_model, title_en ,tab_type FROM unit_models_copy WHERE  tab_type=’hl’ AND (unit_model=’5051′ OR unit_model=’5052′ OR unit_model=’5064′)   ) r0 LEFT JOIN unit_models_copy r1 ON r1.unit_model = r0.unit_model   AND r1.tab_type=’hl’ AND r1.`ranking`=’1′ LEFT JOIN unit_models_copy r2 ON r2.unit_model = r0.unit_model  AND r2.tab_type=’hl’ AND r2.`ranking`=’2′ […]

Read More

Update if record already exist else insert-MySQL

I am uploading an excel file into the database table, What I want is, If any record from excel sheet already exists into the table then update that record with the latest data which is available on the sheet. How to do that? Well, It is pretty simple Following is the syntax to perform that […]

Read More

Mysql Display 2 columns output into one

I have 2 columns(A & B), what I want to do is to display output into a single column. INPUT: A B 12 1 134 12 145 554 OUTPUT: A 12 134 145 1 12 554   To obtain this output we need to use the  ‘UNION’  operator.   So the query would go like: […]

Read More

MySQL calculate distance between two latitude/longitude coordinates

–Returns the distance in kilometers, assuming an earth radius of 6,371 km. SELECT D.idx,D.name,( 6371 * ACOS( COS( RADIANS(C.latitude) ) * COS( RADIANS( D.latitude ) ) * COS( RADIANS( D.longitude ) – RADIANS(C.longitude) ) + SIN( RADIANS(C.latitude) ) * SIN( RADIANS( D.latitude ) ) ) ) AS kms FROM dealers D,cities C GROUP BY D.idx […]

Read More

PHP QUESTIONS AND ANSWERS

1. What do you mean by array? Explain with example various ways to define array in PHP? 2. Explain with example: foreach loop for arrays and while loop for arrays. 3. List and explain various array operators. 4. Explain with example: Multi dimensional array. A multidimensional array is an array containing one or more arrays. […]

Read More

Find Unique Values After using UNION in select query

It was a scenario when I had to fetch data from 3 databases. Fetching data was not tough as I had used UNION in the select query but the tough part for me was finding the distinct/unique name from all the 3 databases. I tried different ways to fetch the distinct name from the database […]

Read More

Count Number of Columns That Are Not Empty:PHP MYSQL

Hello friends, I have written the following script to count the columns which are not empty. In the following script i have taken a scenario where student’s batch-days are supposed to be counted.   <?php $sql=”SELECT sum((monday_time_from IS NOT NULL and monday_time_from <> ”) + (tuesday_time_from IS NOT NULL and tuesday_time_from <> ”) + (wednesday_time_from […]

Read More

Insert into more than 1 database:PHP

I have a table called vendor_keys. It has 2 fields: 1. vendor_name 2. key_provided. The Same table is inside 3 databases.And I want to insert the details into all 3 databases at once. Following is the code to insert details into 3 databases. <?php header(“Access-Control-Allow-Origin: *”); $vendor_name=$_POST[‘vendor_name’]; $key_provided=$_POST[‘key_provided’]; $password=$_POST[‘password’]; $servername = “localhost”; $username = “root”; […]

Read More

Form With Generate Password/Key Field: PHP

I have written this blog to generate 24 digit alphanumeric key/password while creating the signup form. CREATE A PHP FILE AND PASTE FOLLOWING CODE INTO IT. <html> <body> <h1><b>ADD VENDOR</b></h1> <form method=”POST” action=”#”> [replace # with the file path] <table> <tr><td>VENDOR NAME:</td><td><input type=”text” name=”vendor_name” required></td></tr><tr><td> <tr><td> KEY:</td><td><input type=”text” name=”key_provided” value=”<?php echo $password = random_password(24);?>” size=”35″ […]

Read More