2021.06 - 2022.02
PHP MySql Amazon EC2 Amazon S3 XAMPPX
<aside> <img src="/icons/arrow-southeast_gray.svg" alt="/icons/arrow-southeast_gray.svg" width="40px" /> 목록으로 돌아가기
</aside>
백엔드 언어 : PHP
데이터베이스: MYSQL
유저 관리
파트너 관리
서비스 운영
데이터베이스 연동
유저 위치를 기반으로 가게 출장가능 반경에 포함되어있으면 모두 반환하는 기능
<?php
require_once $_SERVER['DOCUMENT_ROOT']."/dbconnect.php";
include $_SERVER['DOCUMENT_ROOT']."/common.php";
header('Content-type: application/json');
$lat = $_POST['lat'];
$lon = $_POST['lon'];
$category_num = $_POST['category_num'];
if(!is_numeric($category_num) || $category_num == ""){
$conn -> close();
error_message("text_error");
}
/*
카테고리 num이 공백이거나 숫자가 아니라면 text_error을 반환합니다.
*/
$array = array();
/*
$sql = "SELECT `idx`, `image_adress`, `shop_name`, `sectors_name`, `shop_location`, `shop_work_location`, `score` FROM `shop_main` WHERE local = '$city' and sectors = '$category_num' ORDER BY `score` DESC";
별점순으로 정렬하기
*/
/*
$stmt = $conn->prepare("SELECT `idx`, `image_adress`, `shop_name`, `sectors_name`, `shop_location`, `shop_work_location`, IF(`reviews_number`= 0, 0, `score`/`reviews_number`) FROM `shop_main` WHERE local LIKE CONCAT('%',?,'%') and category = ? and visible = 1 ORDER BY rand()");
$stmt->bind_param('ss', $city, $category_num);
$stmt->execute();
$result = $stmt->get_result();
*/
/*
SELECT *,(6371*acos(cos(radians(36.103890))*cos(radians(latitude))*cos(radians(longitude)-radians(128.336215))
+sin(radians(36.103890))*sin(radians(latitude)))) AS distance_temp from shop_main having distance_temp < distance ORDER BY rand()
SELECT `idx`, `image_adress`, `shop_name`, `sectors_name`, `shop_location`, `shop_work_location`, IF(`reviews_number`= 0, 0, `score`/`reviews_number`) from shop_main WHERE category = "1" AND (6371*acos(cos(radians(36.103890))*cos(radians(latitude))*cos(radians(longitude)-radians(128.336215)) +sin(radians(36.103890))*sin(radians(latitude)))) < distance ORDER BY rand()
*/
$stmt = $conn->prepare("SELECT `idx`, `shop_image`, `shop_name`, `shop_industry`, `shop_location`, `shop_work_location`, IF(`shop_review_count`= 0, 0, `shop_score`/`shop_review_count`) from shop_main WHERE category = ? AND (6371*acos(cos(radians(?))*cos(radians(latitude))*cos(radians(longitude)-radians(?)) +sin(radians(?))*sin(radians(latitude)))) < distance ORDER BY `shop_priority` DESC, rand()");
$stmt->bind_param('ssss', $category_num, $lat, $lon, $lat);
$stmt->execute();
$result = $stmt->get_result();
while ($row = mysqli_fetch_row($result)) { //Goes through each row of the query
array_push($array, $row);
}
/*
반경에 위차한 가게를 카테고리에 맞게 db에서 가져옵니다.
*/
$array = array("status" => "success", "value" => $array);
echo json_encode($array);
$conn -> close();
?>
이미지 처리 기능
<?php
function image_process($base64Image){
/*
input base64Image String
save image
output image name
*/
$DateTime = date('Y_m_d_h_i_s_', time());
$FileName = $DateTime.generateRandomString().".jpg"; //ImageName = 2021_09_16_02_07_22_ + randomtext 15length +.jpg
$ImageData = base64_decode($base64Image);
imagejpeg(imagecreatefromstring($Imagedata), 'images/'.$FileName, 50); //saved .../images/$filename
return $FileName;
}
function generateRandomString($length = 15) {
$characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
$charactersLength = strlen($characters);
$randomString = '';
for ($i = 0; $i < $length; $i++) {
$randomString .= $characters[rand(0, $charactersLength - 1)];
}
return $randomString;
}
?>
리뷰 작성 기능
<?php
header('Content-type: application/json');
require_once $_SERVER['DOCUMENT_ROOT']."/dbconnect.php";
include $_SERVER['DOCUMENT_ROOT']."/common.php";
$session = $_POST['session'];
$shop_idx = $_POST['shop_idx'];
$tech_score = $_POST['tech_score'];
$price_score = $_POST['price_score'];
$kind_score = $_POST['kind_score'];
$comment = $_POST['comment'];
$base64Image1 = $_POST['base64Image1'];
$base64Image2 = $_POST['base64Image2'];
if($session == ""){
$conn -> close();
error_message("login_error");
}
/*
세션이 공백이라면 login_error 반환
*/
$user_data = userCheck($session,$conn);
if($user_data[0] == ""){
error_message("login_error");
}
/*
user 정보가 없다면 login_error 반환
*/
if($user_data[1] != ""){
error_message("banned_error");
}
/*
세션과 일치하는 user가 정지되었다면 banned_error을 반환합니다.
*/
//$stmt = $conn->prepare("SELECT TIMESTAMPDIFF(DAY, (SELECT `time` FROM `review` WHERE `user_id` = ? order by idx desc limit 1),(SELECT NOW()))");
$stmt = $conn->prepare("SELECT IFNULL(times, 1) FROM (SELECT TIMESTAMPDIFF(DAY, (SELECT `time` FROM `review` WHERE `member_id` = ? order by idx desc limit 1),(SELECT NOW())) AS times)c");
$stmt->bind_param('s', $user_data[0]);
$stmt->execute();
$result = $stmt->get_result();
$result = mysqli_fetch_row($result); //return object중 반환된 배열 가져오기
$result = $result[0];
if($result < 1){
$conn -> close();
error_message("count_error");
}
/*
가장 최근에 작성한 리뷰 날짜와 현재 시간을 비교하여 하루가 지나지 않았다면 count_error반환
(근수저는 1유저 1일 1개의 리뷰 작성만 허용함)
*/
$stmt = $conn->prepare("SELECT `time` FROM `review` WHERE shop_idx = ? AND `member_id` = ?");
$stmt->bind_param('ss', $shop_idx, $user_data[0]);
$stmt->execute();
$result = $stmt->get_result();
$result = mysqli_fetch_row($result); //return object중 반환된 배열 가져오기
$result = $result[0];
if($result != ""){
$conn -> close();
error_message("one_error");
}
/*
이미 리뷰를 쓴 가게라면 one_error반환
*/
if(($tech_score == "2.5" || $tech_score == "5") && ($price_score == "2.5" || $price_score == "5") && ($kind_score == "2.5" || $kind_score == "5")){
//리뷰 점수는 2.5(불만족), 5(만족) 둘중 하나여야 함
$DateTime = date('Y_m_d_h_i_s_', time());
//이곳은 이제 사진있나 체크하고 작성하는곳
if($base64Image1 != ""){
$filelocation = 'images/'.$DateTime.generateRandomString().".jpg";
$Imagedata = base64_decode($base64Image1);
imagejpeg(imagecreatefromstring($Imagedata), $filelocation, 80);
$base64Image1 = "<http://domain.dothome.co.kr/user/>".$filelocation;
/*
사진이 있다면 현재시간+랜덤문자 조합으로 images폴더에 사진을 저장.
추후에 aws s3로 변경하여야함.
*/
}
if($base64Image2 != ""){
$filelocation = 'images/'.$DateTime.generateRandomString().".png";
$Imagedata = base64_decode($base64Image2);
imagejpeg(imagecreatefromstring($Imagedata), $filelocation, 80);
$base64Image2 = "<http://domain.dothome.co.kr/user/>".$filelocation;
}
$stmt = $conn->prepare("INSERT INTO `review`(`shop_idx`, `tech_score`, `price_score`, `kind_score`, `comment`, `image1`, `image2`, `member_idx`,`member_id`, `visible`, `report`) VALUES (?,?,?,?,?,?,?,?,?, 1, 0)");
$stmt->bind_param('sssssssss', $shop_idx, $tech_score, $price_score, $kind_score, $comment, $base64Image1, $base64Image2, $user_data[2], $user_data[0]);
$stmt->execute(); //review db에 데이터 추가
$stmt = $conn->prepare("update `shop_main` set `shop_review_count` = `shop_review_count` + 1, `shop_score` = `shop_score` + ((?+?+?)/3) where `idx` = ?");
$stmt->bind_param('ssss',$tech_score, $price_score, $kind_score, $shop_idx);
$stmt->execute(); //shop_main review_num과 총score 수정
$status = "success";
$final = array();
$final = array("status" => $status);
echo json_encode($final);
$conn -> close();
}
else{
$conn -> close();
error_message("value_error");
}
//리뷰 점수가 이상하다면 value_error 반환
?>