php中SQL语句记录

获取参数

$openid=$_GET["openid"];
$name = $_POST['name'];

数据库连接

$servername = "？数据库ip";
$username = "？用户名";
$password = "？密码";
$dbname = "？库名"; 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

查询

$sql = "SELECT *  FROM `wx_user` WHERE `openid` = '".$openid."' AND state = '是'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
	$aa=$row['name']."@".$row['office'];
}

^_^统计数量
$sql = "SELECT COUNT(*)  FROM `wx_food` WHERE `openid` = '".$openid."'";
$result = mysqli_query($conn, $sql);
$aa = $aa."@".(string)mysqli_fetch_array($result)[0];

^_^时间条件between
$sortT=array();
$a=array();
$sql = "SELECT `name`,`sfID` FROM `wx_faceUserWork` where  `source` like '".$ss."' and `timeT` between '".$time1." 00:00:00' and '".$time2." 23:59:59'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
	array_push($a,$row);
}
//注意：between两个时间为大于小于，故拼接后面的时分秒，以便获取涵盖两个时间的数据。

^_^分组统计
$sql = "select `foodTDate` as 日期, count(*) as 数量  from `wx_food`  WHERE `foodTDate` >= '".$startDate."'  AND `foodTDate` <= '".$endDate."'  group by `foodTDate` ";

^_^表字段
$result = mysqli_query($conn,"show columns from wx_user");
while ($row = mysqli_fetch_array($result)) {
     echo '<th >'.$row['Field'].'</th>';
}

^_^查询排序
$sql = "SELECT *  FROM `wx_voteDish` order by voteNum desc";

^_^多表连接查询
$sql = "select B.office,A.user,count(*) from wx_food A LEFT JOIN wx_user B on A.user = B.name  where  A.state = '是' and A.foodTDate between  '".$t1."' and '".$t2."' group by A.user ORDER BY count(*) desc";

增加

$sql2 = "INSERT into wx_faceUser (pic,source) values ('".$pic."','".$source."')";
if ($conn->query($sql2) === TRUE) {
	echo "注册成功";
} else {
	echo "Error: " . $sql2 . "<br>" . $conn->error;
}

更新

$sql2 = "UPDATE wx_faceUserWork SET workDate = CONCAT(workDate, '','@".date("H:i:s")."')  WHERE `timeT` like '".date("Y-m-d")."%' and `sfID`='".$sfid."' and `name`='".$name."'";
if ($conn->query($sql2) === TRUE) {
	echo '复签成功';
} else {
	echo "Error: " . $sql2 . "<br>" . $conn->error;
}
//注意：like时间时，前面不能放置%，应为like 2012%

删除

DELETE FROM 表名 WHERE 条件

其它

^_^时间
echo "昨天:".date("Y-m-d",strtotime("-1 day"));
echo "一周后:".date("Y-m-d",strtotime("+1 week"));
echo "一个月前:".date("Y-m-d",strtotime("last month"));
echo "十年后:".date("Y-m-d",strtotime("+10 year"));

^_^对象数据取值？
$sortText2=array_count_values(explode("、",$sortText));
foreach ($sortText2 as $key => $value){
    $sortTPeo[0]=$sortTPeo[0].$key.'*'. $value.'、';
}

^_^取百分值
$burstTate=round(($target3/($target2+$target3))*100,2);

^_^多维数据参考排序
$o1=array($sortTNum[0],$sortTNum[1],$sortTNum[2],$sortTNum[3],$sortTNum[4],$sortTNum[5],$sortTNum[6],$sortTNum[7],$sortTNum[8]);
array_multisort($o1,SORT_DESC,$sortTEnd);
switch ($rowSort['office']) {
	case '综合管理部':
		$sortTNum[0]+=$value;
		$sortTPeo[0]=$sortTPeo[0].$key.'*'. $value.'、';
		break;
        default:
		# code...
		break;
}

^_^数组赋值及输出结果
array_push($sortT,$a);
print_r(json_encode($sortT));