Skip to content

Instantly share code, notes, and snippets.

@MatthewZaso

MatthewZaso/PHP and MySQL Code Example

Created January 18, 2013 19:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MatthewZaso/4567338 to your computer and use it in GitHub Desktop.
Save MatthewZaso/4567338 to your computer and use it in GitHub Desktop.
Download ZIP
This code example is from my "Yahtzee" game project. It shows the interaction between the PHP code and my MySQL database in the business layer of my server architecture. MySQLi prepared statements are used for obvious security reasons.
Raw
PHP and MySQL Code Example
<?php
function getChatData($roomNum){
//need to pass in room number
$room=$roomNum;
//we could have an arg coming in as the gameId so I only get back
//the chat for that game...
//for now, this is what the json spitting out would look like:
/*$c='[{"message":"Hello there","name":"danny","timeStamp":"2010-11-04 09:37:05","messageId":"1"},
{"message":"you suck","name":"steve","timeStamp":"2010-11-04 09:39:05","messageId":"2"},
{"message":"grog","name":"sarah","timeStamp":"2010-11-04 09:47:05","messageId":"3"},
{"message":"groggy","name":"Jon","timeStamp":"2010-11-04 09:47:05","messageId":"4"},
{"message":"blargh","name":"Stve","timeStamp":"2010-11-04 09:47:05","messageId":"4"}]';*/
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT yahtzee_chat.sender_id,yahtzee_chat.message_text,yahtzee_chat.timestamp,yahtzee_chat.message_id,yahtzee_user.username FROM yahtzee_chat LEFT JOIN yahtzee_user ON yahtzee_chat.sender_id = yahtzee_user.user_id WHERE room_id=?"))
{
//Bind params
$stmt -> bind_param("i", $room);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($sender,$text,$time,$messageid,$user);
$varArray = array();
//Get the values
while($stmt -> fetch()){
//echo $sender . "|" . $text . "|" . $time . "|" . $messageid . "|" . $user;
$varArray[] = array('message'=>$text,'name'=>$user,'timeStamp'=>$time,'messageId'=>$messageid);
}
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
$c = json_encode($varArray);
return $c;
}
function addChatData($roomId,$senderId,$message){
//update db with new chat
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_chat (sender_id,room_id,message_text) VALUES(?,?,?)"))
{
//Bind params
$stmt -> bind_param("iis",$senderId,$roomId,$message);
//Execute it
$stmt -> execute();
//Bind Results
/*$stmt -> bind_result($sender,$text,$time,$messageid,$user);
$varArray = array();
//Get the values
while($stmt -> fetch()){
//echo $sender . "|" . $text . "|" . $time . "|" . $messageid . "|" . $user;
$varArray[] = array('message'=>$text,'name'=>$user,'timeStamp'=>$time,'messageId'=>$messageid);
}*/
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
//return all chat
echo getChatData($roomId);
}
function chalUserData($playerName,$challName){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT user_id FROM yahtzee_user WHERE username=?"))
{
//Bind params
$stmt -> bind_param("s",$challName);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($challId);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error1";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("UPDATE yahtzee_lobby SET challenge=?,accepted=0 WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("si",$playerName,$challId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error2";
}
//close connection
$mysqli -> close();
return $challId;
}
function checkMyChalData($userId){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT accepted FROM yahtzee_lobby WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("i",$userId);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($status);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
return $status;
}
function checkForChalData($userId){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT challenge FROM yahtzee_lobby WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("i",$userId);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($status);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
$status = json_encode(array($status));
return $status;
}
function declineChalData($userId){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("UPDATE yahtzee_lobby SET challenge=0,accepted=1 WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("i",$userId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
}
function addUser($senderId){
$game = FALSE;
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_lobby (user_id,in_game) VALUES(?,?)"))
{
//Bind params
$stmt -> bind_param("is",$senderId,$game);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
}
function RemoveUser($senderId){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("DELETE FROM yahtzee_lobby WHERE user_id = ?"))
{
//Bind params
$stmt -> bind_param("i",$senderId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
}
function getUsers(){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT yahtzee_user.username FROM yahtzee_lobby LEFT JOIN yahtzee_user ON yahtzee_lobby.user_id = yahtzee_user.user_id"))
{
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($user);
$varArray = array();
//Get the values
while($stmt -> fetch()){
$varArray[] = $user;
}
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
$c = json_encode($varArray);
return $c;
}
function getGameNumData($oppId){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT game_num FROM yahtzee_lobby WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("i",$oppId);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($gameId);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error1";
}
//close connection
$mysqli -> close();
return $gameId;
}
function initGameData($playerId,$oppName){
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT user_id FROM yahtzee_user WHERE username=?"))
{
//Bind params
$stmt -> bind_param("s",$oppName);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($oppId);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error1";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_games (player_one,player_two) VALUES(?,?)"))
{
//Bind params
$stmt -> bind_param("ii",$playerId,$oppId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error2";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("SELECT game_id FROM yahtzee_games WHERE player_one=? AND player_two=?"))
{
//Bind params
$stmt -> bind_param("ii",$playerId,$oppId);
//Execute it
$stmt -> execute();
//Bind Results
$stmt -> bind_result($gameId);
//Get the values
$stmt -> fetch();
//close statement
$stmt -> close();
} else{
echo "error3";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("UPDATE yahtzee_lobby SET game_num=?,accepted=2 WHERE user_id=?"))
{
//Bind params
$stmt -> bind_param("ii",$gameId,$playerId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_turn (game_id,player_id) VALUES(?,0)"))
{
//Bind params
$stmt -> bind_param("i",$gameId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_scorecard (game_id,user_id) VALUES(?,0)"))
{
//Bind params
$stmt -> bind_param("i",$gameId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
//Connect
$mysqli = new mysqli(****,****,****,****);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
//Prepared Statement
if($stmt = $mysqli -> prepare("INSERT INTO yahtzee_scorecard (game_id,user_id) VALUES(?,1)"))
{
//Bind params
$stmt -> bind_param("i",$gameId);
//Execute it
$stmt -> execute();
//close statement
$stmt -> close();
} else{
echo "error";
}
//close connection
$mysqli -> close();
return $gameId;
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment