Mysql Join Query three tables

select customer_info.fileSerial,customer_info.applicantsName ,income_and_dbr_assessment.TotalMonthlyIncome,dbrassessment.DbrAsPerSlabrec from
dbrassessment,customer_info,income_and_dbr_assessment
where customer_info.fileSerial = dbrassessment.fileSerial and customer_info.fileSerial = income_and_dbr_assessment.fileSerial


select customer_info_card.fileSerial,customer_info_card.applicantsName,income_and_dbr_assessment_card.TotalMonthlyIncome,recommendation_for_amex.DbrAllowed from
recommendation_for_amex,customer_info_card,income_and_dbr_assessment_card
where customer_info_card.fileSerial = recommendation_for_amex.fileSerial and customer_info_card.fileSerial = income_and_dbr_assessment_card.fileSerial

MySql Update One column from another table value

// Nazmus Sakib
// Datacraft Ltd

UPDATE customer_info SET `Approved_Datetime` = (SELECT `DateTime` FROM `file_process_info` WHERE `movingReason` LIKE 'Approved'
GROUP BY fileSerial ORDER BY id DESC)
WHERE customer_info.fileSerial = file_process_info.fileSerial

MySql SQL Injections

If you take user input through a webpage and insert it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users
                          WHERE username=$matches[0]");
}
 else
{
   echo "username not accepted";
}

 To demonstrate the problem, consider this script:

// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from users.

Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple queries in a single function call. If you try to stack queries, the call fails.

Preventing SQL Injection:

The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc())
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

MySql Inportent Function List

Here is the list of all important MySQL functions. Each function has been explained along with suitable example.

• MySQL Group By Clause - The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
• MySQL IN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
• MySQL BETWEEN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
• MySQL UNION Keyword - Use a UNION operation to combine multiple result sets into one.
• MySQL COUNT Function - The MySQL COUNT aggregate function is used to count the number of rows in a database table.
• MySQL MAX Function - The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
• MySQL MIN Function - The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
• MySQL AVG Function - The MySQL AVG aggregate function selects the average value for certain table column.
• MySQL SUM Function - The MySQL SUM aggregate function allows selecting the total for a numeric column.
• MySQL SQRT Functions - This is used to generate a square root of a given number.
• MySQL RAND Function - This is used to generate a random number using MySQL command.
• MySQL CONCAT Function - This is used to concatenate any string inside any MySQL command.
• MySQL DATE and Time Functions - Complete list of MySQL Date and Time related functions.
• MySQL Numeric Functions - Complete list of MySQL functions required to manipulate numbers in MySQL.
• MySQL String Functions - Complete list of MySQL functions required to manipulate strings in MySQL.

MySql Variable Declare SELECT Trigger

DELIMITER $$

DROP TRIGGER `rfc`.`update_cpv`$$

CREATE TRIGGER `rfc`.`update_cpv` AFTER UPDATE on `rfc`.`cpv_info`
FOR EACH ROW BEGIN
 
 DECLARE P1,P2 VARCHAR(50);

 SELECT orgName INTO P1 FROM cpv_info WHERE id = OLD.id;

 IF (EXISTS (SELECT Bank_Name_1 FROM report_one WHERE fileSerial = OLD.fileSerial AND Bank_Name_1 =P1)) THEN
    
   UPDATE report_one SET cpv_rcv_Date = (NEW.completingDate),CPV_Status = NEW.status,Rcv_Date_1=NEW.completingDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT Bank_Name_2 FROM report_one WHERE fileSerial = OLD.fileSerial AND Bank_Name_2 =P1)) THEN
  
    UPDATE report_one SET cpv_rcv_Date = (NEW.completingDate),CPV_Status = NEW.status,Rcv_Date_2=NEW.completingDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT Bank_Name_3 FROM report_one WHERE fileSerial = OLD.fileSerial AND Bank_Name_3 =P1)) THEN

     UPDATE report_one SET cpv_rcv_Date = (NEW.completingDate),CPV_Status = NEW.status,Rcv_Date_3=NEW.completingDate WHERE fileSerial = OLD.fileSerial;

END IF;
 END IF;
 END IF;
END$$

DELIMITER ;

MySql IF ELSE EXISTS Update Trigger

DELIMITER $$

DROP TRIGGER `rfc`.`insert_send_query_update`$$

CREATE TRIGGER `rfc`.`insert_send_query_update` AFTER UPDATE on `rfc`.`send_query`
FOR EACH ROW BEGIN

 DECLARE P1,P2 VARCHAR(50);

 IF (EXISTS (SELECT send_query_Date_Rcv1 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv1 IS NULL OR send_query_Date_Rcv1 =''))) THEN
    
   UPDATE report_one SET send_query_Date_Rcv1 = (NEW.received_date),Send_Query_SLA1 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT send_query_Date_Rcv2 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv2 IS NULL OR send_query_Date_Rcv2 =''))) THEN
  
   UPDATE report_one SET send_query_Date_Rcv2 = (NEW.received_date),Send_Query_SLA2 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT send_query_Date_Rcv3 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv3 IS NULL OR send_query_Date_Rcv3 =''))) THEN

     UPDATE report_one SET send_query_Date_Rcv3 = (NEW.received_date),Send_Query_SLA3 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;
 ELSE
     UPDATE report_one SET send_query_Date_Rcv4 = (NEW.received_date),Send_Query_SLA4 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;
 END IF;
 END IF;
 END IF;


END;$$

DELIMITER ;

MySql Update Trigger

DELIMITER $$

DROP TRIGGER `rfc`.`insert_approval_data_update`$$

CREATE TRIGGER `rfc`.`insert_approval_data_update` AFTER UPDATE on `rfc`.`dbrassessment`
FOR EACH ROW BEGIN
UPDATE report_one SET approved_ammount = NEW.LoanAmountRec,approved_tenor = NEW.TenureRec,Interest_Rate = NEW.InterestRateRec,EMI = NEW.EmiRec,dbr_ratio = NEW.DbrAsPerSlabrec WHERE fileSerial = NEW.fileSerial;
END$$

DELIMITER ;