MySQL select today and last 30 days records

If you need to select records from MySQL table records with date today and last 30 days records.
based on the Server date.

MySQL select today and last 30 days records

SELECT * FROM posts WHERE 
date_of_create BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

In case if your field data type were DATETIME format you need to convert your datetime format into date

SELECT DATE_FORMAT(date_of_create, '%m/%d/%Y') FROM posts
WHERE   date_of_create BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

MySQL Date Functions

Working with date in MySQL is very important for date based query processing. today we going to get some important MySQL date functions frequently used in projects.

Here is the date functions with example queries from Article table  have the column date_of_post

CURDATE() - Return the current date

Example:

SELECT * FROM articles WHERE date_of_post = CURDATE()

 

DAY()   - Return day number from the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = 1

 

MONTH() - Return Month number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2

 

YEAR() – Return year number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2011

 

LAST_DAY() -  Return Last date as number, From the month of the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = LAST_DAY(date_of_post)

 

DAYNAME() - Return day name as string from the date column

SELECT DAYNAME (date_of_post) AS `day_of_post` FROM article

 

MONTHNAME() - Return Month name as string from date column

Example:
SELECT MONTHNAME(date_of_post) as `month_of_post` FROM article

 

QUARTER() - Return Quarter Number as 1 to 4 Format as number

Example:
SELECT QUARTER(date_of_post) AS `post_quarter` FROM article

 

WEEK() - Return Week Number Number (0-53)

Example:
SELECT WEEK(date_of_post) AS `week ` FROM article

Optimize All MySQL Tables PHP

If you looking for optimizing MySQL database tables from PHP dynamically, here is a solution to optimize entire tables a database.

Optimizing Tables we need to get Entire table list using "SHOW TABLES" query. as this return entire table names as record, after getting executed of this command call OPTIMIZE TABLE and REPAIR TABLE as per the table needs.

Primary Optimization will failed if the table been damage or corrupt. we will apply REPAIR TABLE

<?php

mysql_connect("localhost","root","");
mysql_select_db("your_db_name");


echo "<br /> OPTIMIZING TABLES ";

$alltables = mysql_query("SHOW TABLES");

// Process all tables.
while ($table = mysql_fetch_assoc($alltables))
{
 foreach ($table as $db => $tablename)
 {
 // Optimize them!
 if(mysql_query("OPTIMIZE TABLE ".$tablename))
  echo "<br>OK Optimized : ".$tablename;
 else
 {
  echo "<br>Error Optimizing Applying Reapir... ".$tablename;
  // Apply Reapirt if Optimization Failed;
  if(mysql_query("REPAIR TABLE ".$tablename))
  echo "Repaired !";
  else
  echo "Error Repairing Table!";
 }
 }
}
mysql_close();
?>

Find the Second largest value from MySql, MsSql, Sql Server table

Finding Second Bigger or largest value from the table column in MySQL / MS SQL / SQLServer, here is the simple sql query to identify the Second toppest value from database table.

Using MAX(fieldname) function

salary_master: (Sample Data)

id     name        salary
1      AAA         20000
2      BBB         18000
3      CCC         19000

SELECT MAX(salary) from salary_master 

above Query will return output (Eg: 20000) the first largest or biggest value from table column.
Using SubQuery we will extract the less than maximum value data and than find the MAX
below the code which get the Maximum salary from salary_master table which was less than the maximum salary (ie., return records except 20000)

SELECT * from salary_master 
where salary < (SELECT MAX(salary) from salary_master)

Output:
id     name        salary
2      BBB         18000
3      CCC         19000

Now will process the MAX(salary) instead of * we will get the output Second Largest Value

SELECT MAX(salary) from salary_master 
where salary < (SELECT MAX(salary) from salary_master)

Output:
20000

Connect Multiple MySQL Database in PHP

If your website move on to a traffic worthy website. you need to work with multiple database for load balancing and overhead distribution. So we need to work with multiple database on single project. MySQL and PHP provide some efficient logic to handle multiple database on single project. Let us see how to work with Multiple database in efficient way.



Example Project working with Multiple Database.

include/connectdb.php

<?php
// Define Connection Details
define(DB_SERVER,"localhost");
define(DB_USER,"root");
define(DB_PASSWORD,"");

// Define Database Names and Table Names
define(DB_CUSTOMER,"w3lessons_customer_db");
define(DB_SERVICE,"w3lessons_service_db");

// Define Table Name prefix with database name
// For Customer Table
define(TBL_CUSTOMER,DB_CUSTOMER.".tbl_customers");
// For Service Table
define(TBL_SERVICE,DB_SERVICE.".tbl_services");

// Connect MySQL Server
$conn=mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD);
?>

index.php

<?php
include("include/connectdb.php");

if(isset($_POST['customer_id']) and $_POST['customer_id']!="")
{
	$sql="select * from ".TBL_CUSTOMER." where customer_id='{$_POST['customer_id']}'";
	$result=mysql_query($sql);
	$row=mysql_fetch_array($result);

//	$customer_id=$row['customer_id'];
//	$email_id=$row['email_id'];
//	$service_id=$row['service_id'];
// Instead of above three lines we can use	
	extract($row);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Working with Multiple Database :Service Update</title>
<style type="text/css">

<!--
body,td,th {
	font-family: Verdana, Geneva, sans-serif;
	font-size: 11px;
	color: #000;
	font-weight: bold;
}
body {
	margin-left: 10px;
	margin-top: 10px;
}
-->
</style></head>

<body>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post" id="thisForm">

<table border="0" cellspacing="0" cellpadding="7" style="border:1px solid #999;">
  <tr>
    <td colspan="2" bgcolor="#D6D6D6">Customer Service Update Sheet</td>

    </tr>
  <tr>
    <td>Customer Name</td>
    <td><select name="customer_id" id="customer_id" onchange="javascript:document.getElementById('thisForm').submit()">

      <option value="">Select Customer</option>
      <?php
	// Load Service Names from Customer DB
	$sql="select * from ".TBL_CUSTOMER;
	$result=mysql_query($sql);
	while($row=mysql_fetch_array($result))
	{
		if($row['customer_id']==$customer_id)
		echo '<option value="'.$row['customer_id'].'" selected="selected">'.$row['customer_name'].'</option>';
		else		
		echo '<option value="'.$row['customer_id'].'">'.$row['customer_name'].'</option>';
	}							  
	?>

    </select></td>
    </tr>
  <tr>
    <td>Customer Email ID</td>
    <td><input name="email_id" type="text" id="email_id" value="<?=@$email_id?>" /></td>

    </tr>
  <tr>
    <td>Service Requirement</td>
    <td>
      <select name="select" id="select">

        <?php
	// Load Service Names from Service DB
	$sql="select * from ".TBL_SERVICE;
	$result=mysql_query($sql);
	while($row=mysql_fetch_array($result))
	{
		if($row['service_id']==$service_id)
		echo '<option value="'.$row['service_id'].'" selected="selected">'.$row['service_name'].'</option>';
		else
		echo '<option value="'.$row['service_id'].'">'.$row['service_name'].'</option>';
	}							  
	?>

        </select>
    </td>
    </tr>
  </table>
<br />
<br />
<a href="http://www.w3lessons.com/">View Article for Working with Multiple Database</a>

</form>
</body>
</html>

Output:

Download Script