ระบบการขอเพลงและตรวจสอบรายชื่อที่เพลงขอด้วย PHP และ mysql โดยใช้ jquery ช่วย

-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 08, 2013 at 03:16 PM
-- Server version: 5.5.27
-- PHP Version: 5.4.7

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `song_request`
--

-- --------------------------------------------------------

--
-- Table structure for table `request`
--

CREATE TABLE IF NOT EXISTS `request` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `song` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `time` int(11) NOT NULL,
  `ip` int(40) NOT NULL DEFAULT '0',
  `is_play` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

<?php
	require_once 'connect.php';
	require_once 'lib/IPv6.php';
	require_once 'lib/blankRemove.php';
	if(isset($_POST['song']) && !empty($_POST['song'])){		
		if(!isset($_POST['name']) OR empty($_POST['name']))
			$error[] = 'No sender name sent!';
		else{
			//var_dump($_POST);
			$_POST['song'] = $db->real_escape_string((strip_tags($_POST['song'])));
			$_POST['name'] = $db->real_escape_string((strip_tags($_POST['name'])));
			//var_dump($_POST);
			if(empty($_POST['song'])) $error[] = 'SONG is empty!';
			else if(empty($_POST['name'])) $error[] = 'NAME is empty!';
			else{
				$sql = sprintf(
					"INSERT INTO `request` (`song`,`name`,`ip`,`time`) VALUES ('%s', '%s', %u, %d)",
					$_POST['song'],
					$_POST['name'],
					intval(inet_ptoi($_SERVER['REMOTE_ADDR'])),
					time()
					);
				//var_dump($sql);
				if(!$db->query($sql)) $error[] = 'Cant INSERT!';
				else echo '<span style="color: #00AA00;">Success!</span>';
			}
		}

		if(isset($error[0]))
			echo '<div style="color: red;"><ul><li>',implode('</li><li>', $error), '</li></ul></div>';
	}
?>
<form action="?" method="POST">
	<label for="song">ชื่อเพลง </label><input type="text" name="song" id="song" /><br />
	<label for="name">ผู้ขอ </label><input type="text" name="name" id="name" /><br />
	<input type="Submit" value="ขอเพลง!" />
</form>

<?php
	if(isset($_GET['ajax'])) header('Content-Type: application/json');
	require_once 'connect.php';
	require_once './lib/IPv6.php';
	require_once './lib/fbTime.php';

	$where = $display = array();
	//update for played song
	if(isset($_GET['play']) OR isset($_POST['play'])){
		$sql = "UPDATE `request` SET `is_play` = 1 WHERE `id`";
		if(isset($_GET['play'])) $sql .= ' = '.intval($_GET['play']);
		else{
			$sql .= ' IN(';
			foreach ($_POST['play'] as $i => $id){
				$sql .= intval($id);
				if(isset($_POST['play'][$i+1])) $sql .=',';
			}
			$sql .= ')';
		}
		$result = ($db->query($sql)?true:false);
	}

	//fetch data
	$sql = "SELECT `id`, `name`, `song`, `time`, `ip`, `is_play` FROM `request`";
	if(isset($_GET['is_play'])){
		$_GET['is_play'] = intval($_GET['is_play']);
		$where[] = '`is_play` = '.$_GET['is_play'];
		$display[] = 'ค้นหาจากเพลงที่'.($_GET['is_play']?'เล่นแล้ว':'ยังไม่เล่น');
	}
	if(isset($_GET['time'])){
		$_GET['time'] = intval($_GET['time']);
		$where[] = '`time` > '.($_GET['time']);
		$display[] = 'ค้นหาจากขอเพลงหลังเวลา : '.generate_date_today("d M Y H:i", $_GET['time'], "th", true).'('.generate_date_today("d M Y H:i", $_GET['time'], "th", false).')';
	}
	if(isset($_GET['ip'])){
		$_GET['ip'] = intval($_GET['ip']);
		$where[] = '`ip` = '.($_GET['ip']);
		$display[] = 'ค้นหาจาก ip ของผู้ขอ คือ : '.inet_itop($_GET['ip']);
	}
	if(isset($where[0])) $sql .= ' WHERE '.implode(' AND ', $where);
	
	$objResult = $db->query($sql);
	$data = array();
	while($row = $objResult->fetch_assoc()){
		$row['id'] = intval($row['id']);
		$row['ip'] = intval($row['ip']);
		$row['time'] = intval($row['time']);
		$row['is_play'] = intval($row['is_play']);
		if(isset($_GET['ajax'])){
			$pre_ins = $row;
			$pre_ins['time_txt'] = generate_date_today("d M Y H:i", $row['time'], "th", false);
			$pre_ins['ip_txt'] = inet_itop($row['ip']);
			$data[] = $pre_ins;
		}
		else $data[] = sprintf(
			'<tr id="request_%d"><td>%d</td> 
			<td>%s</td> 
			<td>%s</td> 
			<td><a href="?time=%d" class="ajax_load">%s</a></td> 
			<td><a href="?ip=%d" class="ajax_load">%s</a></td> 
			<td>%s</td>
			<td>%s</td></tr>',
			$row['id'],
			$row['id'],
			$row['song'],
			$row['name'],
			$row['time'],
			generate_date_today("d M Y H:i", $row['time'], "th", false),
			$row['ip'],
			inet_itop($row['ip']),
			($row['is_play']?'เล่นแล้ว':'<a href="?play='.$row['id'].'" class="ajax_load" id="status_'.$row['id'].'">ยังไม่เล่น</a>'),
			($row['is_play']?'':'<input type="checkbox" name="play[]" value="'.$row['id'].'" class="play_check"  id="chk_'.$row['id'].'"/>')
			);
	}
	$objResult->free();
	if(isset($_GET['ajax'])) :
		$json = array(
			"display"	=> $display,
			"time"		=> time(),
			"data"		=> $data
		);
		if(isset($result)) $json['result'] = $result;
		echo json_encode($json);
		exit;
	else : ?>
<html>
<head>
	<title>รายชื่อเพลงที่ขอกันมา</title>
	<script type="text/javascript" src="./lib/jquery-1.10.2.min.js"></script>
</head>
<body>
	<div id="display">
		<?php 
			if(isset($display[0])) 
				echo '<ul><li>',implode('</li><li>', $display),'</li></ul>';
			else echo "<ul><li>ค้นหาจากทั้งหมด</li></ul>";
		?>
	</div>
	<form action="?" method="POST">
		<table id="request_song">
			<thead>
				<tr>
					<th>id</th>
					<th>ชื่อเพลง</th>
					<th>ชื่อผู้ขอ</th>
					<th>เวลาที่ขอ</th>
					<th>ไอพีผู้ขอ</th>
					<th>สถานะ</th>
					<th><input type="checkbox" id="check_all" /></th>
				</tr>
			</thead>
			<tbody>
				<?php 
					if(count($data) <= 0) echo '<tr><td colspan="7">ไมมีข้อมูลในฐานข้อมูล</td></tr>';
					else echo '<tr>',implode('</tr><tr>', $data),'</tr>';
				?>
			</tbody>
		</table>
		<?php if(count($data) > 0) echo '<input type="submit" value="UPDATE" />'; ?>
	</form>
	<a href="?">View All</a>
	<script type="text/javascript">
		var last_time = <?php echo time(); ?>;
		var freq = 3000; //milisecond
		function update(option){
			var url = 'time='+last_time+'&ajax=true';
			if(typeof option == "string") url = option+'&'+url;
			else if(url.indexOf("?")==-1) url = '?'+url;
			url +='&dummy='+parseInt(Math.random()*100000);
			$.getJSON(url, function(data){
				if(data.display.length > 0){
					var html = '<ul>';
					$.each(data.display, function(i, val){
						html += '<li>'+val+'</li>'; 
					});
					html += '</ul>';
					$("#display").html(html);
				}
				last_time = data.time;
				if(data["data"].length > 0){
					var html = '';
					$.each(data["data"], function(i, val){
						html += '<tr id="'+val['id']+'">';
							html += '<td>'+val["id"]+'</td>';
							html += '<td>'+val["song"]+'</td>';
							html += '<td>'+val["name"]+'</td>';
							html += '<td><a href="?time='+val["time"]+'" class="ajax_load">'+val['time_txt']+'</a></td>';
							html += '<td><a href="?ip='+val['ip']+'" class="ajax_load">'+val['ip_txt']+'</a></td>';
							html += '<td>'; 
								if(val['is_play']) html +='เล่นแล้ว'; 
								else html +='<a href="?play='+val['id']+'" class="ajax_load">ยังไม่เล่น</a>';
							html += '</td>';
							html += '<td>'; 
								if(!val['is_play']) html +='<input type="checkbox" name="play[]" value="'+val['id']+'" class="play_check"/>';
							html += '</td>';
						html += '</tr>';
					});
					console.log(html);
					$("#request_song tbody").prepend(html);
				}
				if(typeof data["result"] == "boolean"){
					var id = parseInt(option.split('=')[1]);
					alert(id);
					console.log(data["result"]);
					if(data["result"]){
						$("#status_"+id).parent().html("เล่นแล้ว");
						$("#chk_"+id).parent().html("");
					}
					else alert("Editing ID: "+id+" was FAIL!");
				}
			});
		}
		$(document).ready(function(){
			var interval = setInterval(function(){ update(); }, freq);
			$(".ajax_load").click(function(e){
				update($(this).attr("href"));
				return false;
			});	
		});
	</script>
</body>
</html>

<?php endif; ?>