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.