新增 修改 刪除 查詢

JavaScript、MySQL、PHP / 作者: / 2022 年 12 月 22 日

新增 修改 刪除

tom_msg_view.php

<?php

$fun=$_POST['fun'];
$msgid=$_POST['msgid'];
$msg=$_POST['msg'];

$now = date('Y-m-d H:i:s');


$servername = "localhost";
$username = "***";
$password = "***";
$dbname = "***";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


if($fun=="a"){//新增
	$sql = "INSERT INTO `tom_wp`.`tom_msg` (`id`, `msg`, `addtime`, `fixtime`, `deltime`, `state`) VALUES (NULL, '".$msg."', '".$now."', NULL, NULL, '1');";
	$conn->query($sql);
};

if($fun=="f"){//修改
	$sql = "UPDATE `tom_wp`.`tom_msg` SET `msg`= '".$msg."',`fixtime` ='".$now."' WHERE `id`=".$msgid." ";
	$conn->query($sql);
};

if($fun=="d"){//刪除
	$sql = "UPDATE `tom_wp`.`tom_msg` SET `state`= '0',`deltime` ='".$now."' WHERE `id`=".$msgid." ";
	$conn->query($sql);
};

?>

撈資料顯示

tom_msg_view.php

<?php

$page=$_POST['page'];
$show=$_POST['show'];

$servername = "localhost";
$username = "***";
$password = "***";
$dbname = "***";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
//echo "success";

$sql = "SELECT count(*) FROM `tom_wp`.`tom_msg` AS a WHERE `state` = 1 ORDER BY a.`addtime` DESC";

$result = mysqli_query($conn,$sql);
$total = mysqli_fetch_row($result);

//echo $total."<br>";

$list->total = $total;

$sql = "SELECT * FROM `tom_wp`.`tom_msg` AS a WHERE `state` = 1 ORDER BY a.`addtime` DESC LIMIT ".$page.", ".$show." ";
if ($result = mysqli_query($conn, $sql)) {

  while ($obj = mysqli_fetch_object($result)) {

	  $list->viewdata[]=$obj;

  }
  
  mysqli_free_result($result);
}

echo json_encode($list);


?>

html於客端顯示跟動作

<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>


<!-- Modal -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" data-backdrop="static">
  <div class="modal-dialog modal-md">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span style="font-size: 3rem;" aria-hidden="true">×</span></button>
        <span class="modal-title" style="font-size: 2rem;" id="myModalLabel">訊息</span>
      </div>
      <div class="modal-body">
          <div>
            <textarea class="form-control" id="message-text" rows="3"></textarea>
          </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">關閉</button>
        <button type="button" class="btn btn-primary" id="send_msg">送出</button>
      </div>
    </div>
  </div>
</div>
<!-- /modal -->

<button class="btn btn-primary" data-toggle="modal" data-target="#myModal" data-whatever="新增" data-fun="a">新增</button>

<div id="msgtable"></div>



<script type="text/javascript">

var page;
var show;
var num;
var page_id;
var num_range;
var tmp_page_id;

//頁面載入後先撈一次資料==============
$(function() {
	//設定換頁參數==============
	page=0;
	show=5;
	page_id=0;
	num_range=5;
	reloadmsgtable();

});

//撈資料==============
function reloadmsgtable(){

	$("#msgtable").html("");
	var jqxhr = $.post( "http://192.168.1.126/tom_wp/tom_msg_view.php",{page:page,show:show}, function(data ) {
		//alert( "success");
		//console.log(data);
		var data_obj=$.parseJSON(data);
		CreatMsgHtml(data_obj);
	})

	.fail(function() {
		alert( "error" );
	});
}

//表情符號==============
var modal_button_fun;
var modal_button_msgid;

var emoji_range = [
  '[\u2764\uFE0F]',
  '[\u2694-\u2697]',
  '[\u2580-\u27BF]',
  '[\u2700-\u27BF]',
  '[\uE000-\uF8FF]',
  '[\u2011-\u26FF]',
  '\uD83E[\uDD10-\uDD5D]',
  '\uD83C[\uDF00-\uDFFF]',
  '\uD83D[\uDC00-\uDFFF]',
  '\uD83D[\uDC00-\uDE4F]',
  '\uD83D[\uDE80-\uDEFF]',
  '\uD83E[\uDD10-\uDDFF]',
];

function utf2Html(str) {
  return [...str].map((char) => char.codePointAt() > 127 ? `&#${char.codePointAt()};` : char).join('');
}

function show_emoji(content){
		var tmp_input=content.match(new RegExp(emoji_range.join('|'), 'g'));
		
		//console.log(tmp_input+"---tmp_input");
		if(tmp_input){
			var count=content.match(new RegExp(emoji_range.join('|'), 'g') || []).length;
		}else{
			var count=0;
		}
		
		//console.log(count+"---count");
		
		var i=0;
		while (i < count){
			content = content.replace(tmp_input[i], utf2Html(tmp_input[i]));
			//console.log(i+"---i");
			i++;
		}
		
		return content;
		//console.log(content+"---content");
}

//字串轉換連結
const urlify = (text) => { 
  const urlRegex = /(\b(https?|ftp|file):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/ig; 
  return text.replace(urlRegex, (url) => { 
    return '<a href="'+url+'" target="_blank">'+url+'</a>'; 
  }) 
} 



$( "#send_msg" ).click(function() {
	
	if(modal_button_fun=="a" ){//新增
		if(!$( "#message-text" ).val()){
			alert("請輸入訊息！");
			return;
		};
		
		var content=$( "#message-text" ).val();
		
		var jqxhr = $.post( "http://192.168.1.126/tom_wp/tom_msg_afd.php",{ fun:modal_button_fun,msg:show_emoji(content)}, function(data ) {
			//alert( "success");
			//console.log("add="+data);
			//window.location.reload();
			reloadmsgtable();
			$('#myModal').modal('hide');
		})

		.fail(function() {
			alert( "error" );
		});
	}

	if(modal_button_fun=="f" ){//修改

		var content=$( "#message-text" ).val();
		
		var jqxhr = $.post( "http://192.168.1.126/tom_wp/tom_msg_afd.php/",{ fun:modal_button_fun,msgid:modal_button_msgid,msg:show_emoji(content)}, function(data ) {
			//alert( "success");
			console.log("fix="+data);
			//window.location.reload();
			reloadmsgtable();
			$('#myModal').modal('hide');
		})

		.fail(function() {
			alert( "error" );
		});
	}
	
	if(modal_button_fun=="d" ){//刪除
		
		var jqxhr = $.post( "http://192.168.1.126/tom_wp/tom_msg_afd.php/",{ fun:modal_button_fun,msgid:modal_button_msgid}, function(data ) {
			//alert( "success");
			//console.log("success="+data);
			//window.location.reload();
			reloadmsgtable();
			$('#myModal').modal('hide');
		})

		.fail(function() {
			alert( "error" );
		});
	}

});

//Modal==============
$('#myModal').on('show.bs.modal', function (event) {
	var button = $(event.relatedTarget); // Button that triggered the modal
	var recipient = button.data('whatever'); // Extract info from data-* attributes
	modal_button_fun=button.data('fun');
	modal_button_msgid=button.data('msgid');
	//console.log($("#msg_"+modal_button_msgid).html());

	var modal = $(this);
	modal.find('.modal-title').text(recipient);
	
	//初始化$( "#send_msg" ) 按鈕
	$( "#send_msg" ).addClass("btn btn-primary");
	$( "#send_msg" ).text("送出");
	modal.find('textarea').val("");
	modal.find('textarea').prop("disabled", false);
	
	if(modal_button_fun=="f"){//修改
		$( "#send_msg" ).addClass("btn btn-primary");
		$( "#send_msg" ).text("送出");
		modal.find('textarea').val($("#msg_"+modal_button_msgid).text());
	}
	
	if(modal_button_fun=="d"){//刪除
		$( "#send_msg" ).addClass("btn btn-primary");
		$( "#send_msg" ).text("送出");
		modal.find('textarea').val($("#msg_"+modal_button_msgid).html());
		modal.find('textarea').prop("disabled", true);
	}
});

//呈現資料表==============
function CreatMsgHtml(jsondata){

	html="<table class='table table-hover'><thead><tr><th style='text-align: center;'>#</th><th style='text-align: center;'>訊息</th>";
	html+="<th style='text-align: center;'>修改</th><th style='text-align: center;'>刪除</th>";
	html+=	"</tr></thead><tbody>";
	
	for (var i = 0; i < jsondata.viewdata.length; i++) {
	
		const txt=jsondata.viewdata[i].msg;
		const viewdata_msg = urlify(txt); 


		html+="<tr><td style='text-align: center;'>"+(page+i+1)+"</td><td><span id='msg_"+jsondata.viewdata[i].id+"' style='font-size: 18px;word-break: break-all;white-space: pre-wrap;'>"+viewdata_msg+"</span><br><span style='color: darkgray;font-size: 10px;'>發佈時間："+jsondata.viewdata[i].addtime+"</span>";
		
		if(jsondata.viewdata[i].fixtime !== null){
		html+="<br><span style='color: darkgray;font-size: 10px;'>修改時間："+jsondata.viewdata[i].fixtime;
		}
		
			html+="</td><td style='vertical-align: middle;text-align: center;'><button class='btn btn-warning' data-toggle='modal' data-target='#myModal' data-whatever='訊息修改' data-fun='f' data-msgid='"+jsondata.viewdata[i].id+"'>修改</button></td><td style='vertical-align: middle;text-align: center;'><button class='btn btn-danger' data-toggle='modal' data-target='#myModal' data-whatever='訊息刪除' data-fun='d' data-msgid='"+jsondata.viewdata[i].id+"'>刪除</button></td>";
		
	}
	html+="</tr></tbody></table>";
	html+="<div style='white-space: nowrap; overflow: auto;'>	<nav class='text-center'>	<ul class='pagination' id='msgnavigation'>    <li class='active'>	<a href='#totallist' aria-label='totallist'>共"+jsondata.total+"筆</a>	</li><li>      <a href='#lasts' aria-label='lasts'>        <span aria-hidden='true'>«</span>      </a>    </li><li>      <a href='#last' aria-label='last'>        <span aria-hidden='true'>‹</span>      </a>    </li>";
	

	//換頁==============
	num=Math.ceil(jsondata.total/show);
	
	if( num > num_range && page_id > 2 ){
		tmp_page_id = num - num_range;
		tmp_n = num_range + tmp_page_id;
	}else{
		tmp_page_id = 0;
		if( num < num_range ){
			tmp_n = num;
		}else{
			tmp_n = num_range;
		}
	}
	
	for (var i = 0 + tmp_page_id; i < tmp_n; i++) {

		if(i==page_id){
			html+="<li class='active'><a id='nav_"+i+"' style='cursor:pointer'>"+(i+1)+"<span class='sr-only'>(current)</span></a></li>";
		}else{
			html+="<li><a id='nav_"+i+"' style='cursor:pointer'>"+(i+1)+"</a></li>";
		}
	}
	html+="    <li>      <a href='#next' aria-label='next'>        <span aria-hidden='true'>›</span>      </a>    </li>	<li>      <a href='#nexts' aria-label='nexts'>        <span aria-hidden='true'>»</span>      </a>    </li>	<li class='active'>	<a href='#total' aria-label='totalpage'>共"+num+"頁</a>	</li></ul></nav></div>";
	$("#msgtable").html(html);

	
	$('#msgnavigation li a').click(function() {
		var aria=$(this).attr("aria-label");
		var tmp=$(this).attr("id");
		
		switch (aria) {
			case 'lasts':
				page=0;
				page_id=0;
				break;
			case 'last':
				if(num>0){
					page-=show;
					page_id-=1;
				}
				break;
			case 'next':
				if(page_id<num-1){
					page+=show;
					page_id++;
				}
				break;
			case 'nexts':
				page=(num-1)*show;
				page_id=num-1;
				break;
			case 'totallist':
				//nothing...
				break;
			case 'totalpage':
				//nothing...
				break;
			default:
				page_id=tmp.slice(4);
				page=page_id*show;
		}
		
		var jqxhr = $.post( "http://192.168.1.126/tom_wp/tom_msg_view.php",{page:page,show:show}, function(data ) {
			var data_obj=$.parseJSON(data);
			//console.log(data);
			CreatMsgHtml(data_obj);
		})

		.fail(function() {
			alert( "error" );
		});
		
	});
}

</script>

參考資料：

https://www.w3schools.com/php/func_mysqli_fetch_lengths.asp

https://getbootstrap.com/docs/3.4/javascript/#modals