Jump to content

database php help

Justafriend

By Justafriend
in PHP Coding Help

 Share

Recommended Posts

Justafriend Member

Justafriend

Posted
Posted

ok a few years back i had paid someone to write this code but  cant get ahold of him and honestly cant afford to pay to have someone rewrite it so i have been going through the code and got it 90 percent working i know its not very secure  which at this point isnt a big issue as  only 2 people will be able to enter any data into it  but i came across 3 problems when implementing it

 

Problem one is the entrys into the points directory will not accept duplicates as trhe playername is set at primary so i tried to fix by adding another column called id with auto increment and set it as index id  but the issue becomes that the entry of the data is being refused because there is a different  number of columns between the 2 so how can i tell it to use id column and do nothing but let it use the auto increment feature

 

problem 2 the toc list i need it to change from   basically 3 colums in any order to a straigh alphabetical list ignoring   duplicate entrys

 

and finally i need to set it so that it will aquire data for toc  list betweeen 2 dates

and the points between 2 dates

here is the code

<html>
<style type="text/css">

<!--

Table {border-collapse:collapse; border-color:#000000; border-width:2px; cellpadding:3px;}

Table THEAD TD{background-color:#00FFFF; color:#FF0000; font-weight:bold; font-size:larger;; padding:3px; border:1px solid black}

Table TR TD {padding:3px;border: 1px solid black}

-->

</style>

<?php

	//print_r($_SERVER);

		$sn = $_SERVER['PHP_SELF'];

		mysql_connect(localhost, "dbs", "xxxxxxxxx"); //Put login and password for mysql here. Replace null with mysql server address if provided by host.

	

		mysql_select_db("dbs_forms") or die(mysql_error());//Put databasename here

		mysql_query("create table if not exists tourne_report (Host TEXT, Date_Time DATETIME, PlayersCount INT, Winner TEXT, WinnerEmail TEXT, SecondPlace TEXT, SecondEmail TEXT, ThirdPlace TEXT, ThirdEmail TEXT, ThirdPlace2 TEXT, ThirdEmail2 TEXT)");

		mysql_query("create table if not exists emails (PlayerName char(30) key not null, email TEXT)");

		mysql_query("create table if not exists points (PlayerName char(30) key not null, Points INT, Date_Time DateTime)");



	if (isset ($_GET["act"]))

	{

		if ($_GET["act"] == "erase")

		{

			mysql_query("drop table tourne_report");

			mysql_query("drop table emails");

			mysql_query("drop table points");

			mysql_query("create table if not exists tourne_report (Host TEXT, Date_Time DATETIME, PlayersCount INT, Winner TEXT, WinnerEmail TEXT, SecondPlace TEXT, SecondEmail TEXT, ThirdPlace TEXT, ThirdEmail TEXT, ThirdPlace2 TEXT, ThirdEmail2 TEXT)");

			mysql_query("create table if not exists emails (PlayerName char(30) key not null, email TEXT)");

			mysql_query("create table if not exists points (PlayerName char(30) key not null, Points INT, Date_Time DateTime)");



		}

		if ($_GET["act"] == "points")

		{

			?>

			<table>

			<thead>

			<td>Place</td>

			<td>PlayerName</td>

			<td>Points</td>

			</thead>

			<?

				$i = 0;

				$d = date("Y-m-");

				$d .= "01 00:00:00";

				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");



				$r = mysql_query("select * from points where Date_Time > '$d' order by Points DESC");

				while ($v = mysql_fetch_assoc($r))

				{

					++$i;

					echo("<tr><td>$i</td><td>${v["PlayerName"]}</td><td>${v["Points"]}</td></tr>\n");

				}

			

			?>

			</table>

			<?//<a href="<?echo ($sn);?><?//">Back</a>?>

			<?

			exit();

			

		}

		if ($_GET["act"] == "emails")

		{

			?>

			<table>

			<thead>

			<td>PlayerName</td>

			<td>EMail</td>

			</thead>

			<?

				

				$r = mysql_query("select * from emails order by PlayerName ASC");

				while ($v = mysql_fetch_assoc($r))

				{

					if ($v["PlayerName"] != "")

						echo("<tr><td>${v["PlayerName"]}</td><td>${v["email"]}</td></tr>\n");

				}

			

			?>

			</table>

			<?//<a href="<?echo ($sn);?><?//">Back</a>?>

			<?

			exit();

			

		}

		if ($_GET["act"] == "toc")

		{

				$d = date("Y-m-");

				$d .= "01 00:00:00";

				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");



			?>

			<h2>TOC:</h2>

			<table>

			<tr>

			<?

				$i = 0;

				$all = array();

				$r = mysql_query("select * from tourne_report where Date_Time > '$d' order by Date_Time DESC");

				while ($v = mysql_fetch_assoc($r))

				{

					

				if ($v["Winner"] != "")

					{

						$all[] = $v["Winner"];

					}

				if ($v["SecondPlace"] != "")

					{

						$all[] = $v["SecondPlace"];

					}						

				if ($v["ThirdPlace"] != "")

					{

						$all[] = $v["ThirdPlace"];

					}						

				if ($v["ThirdPlace2"] != "")

					{

						$all[] = $v["ThirdPlace2"];

					}						

				}

				sort($all,SORT_STRING);

				$all = array_unique($all);

				foreach ($all as $k)

				{

						echo("<td>$k</td>");

						$i++;

						if ($i == 3)

						{

							echo("</tr><tr>");

							$i = 0;

						}

						

				}

				

				while ($i != 3 && $i!=0)

				{

					echo("<td></td>");

					++$i;

				}

				

			

			?>

			</tr>

			</table>			

			<?//<a href="<?echo ($sn);?><?//">Back</a>?>

			<?

			exit();

			

		}

		if ($_GET["act"] == "months")

		{

			?>

			<table>

			<thead>

			<td>Host</td>

			<!--<td>Nick</td> -->

			<td>Date_Time</td>

			<td>Winner</td>

			<td>#of players</td>

			</thead>

			<?

				$d = date("Y-m-");

				$d .= "01 00:00:00";

				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");

				$r = mysql_query("select * from tourne_report where Date_Time>'$d' order by Date_Time DESC");

				while ($v = mysql_fetch_assoc($r))

				{

					echo("<tr><td>${v["Host"]}</td><td>${v["Date_Time"]}</td><td>${v["Winner"]}</td><td>${v["PlayersCount"]}</td></tr>\n");

				}

			

			?>

			</table>

			<?//<a href="<?echo ($sn);?><?//">Back</a>?>

			<?

			exit();

			

		}



	}

	function not_2($var)

	{

		/*echo("<br><br>");

		print_r($var);*/

		

		if (count($var) != 2) return false;

		else return true;

	}

	if (isset($_POST["asfile"]))

	{

		//print_r($_POST);

		echo("<br>");

		$file = $_POST["file"];

		$points = strstr($file, "START POINTS");

		//print_r($points);

		$_POST["Points"] = $points;

		$file = explode("\n", $file);

		foreach ($file as $k)

		{

			$lines[] = explode(":", $k, 2);

		}

		$lines = array_filter($lines, "not_2");

		//print_r($lines);

		foreach ($lines as $value) 

		{

			$value[0] = trim($value[0]);

			$value[1] = trim($value[1]);

			switch (strtolower($value[0])) {

					case "date": $_POST["At"] = $value[1];

						break;

					case "scheduled start": $_POST["AtTime"] = $value[1];

						break;

					case "host": $_POST["Host"] = $value[1];

						break;

			

					case "number of players": $_POST["PC"] = $value[1];

						break;						

					case "1st place": $_POST["p1"] = $value[1];

						break;						

					case "1st place email": $_POST["e1"] = $value[1];

						break;												

					case "2nd place": $_POST["p2"] = $value[1];

						break;						

					case "2nd place email": $_POST["e2"] = $value[1];

						break;							

					case "3rd place":

							if (isset($_POST["p3"]))

							{

								$_POST["p23"] = $value[1];

							}

							else 

							{

								$_POST["p3"] = $value[1];

							}

						break;						

					case "3rd place email": 				

							if (isset($_POST["e3"])) $_POST["e23"] = $value[1];

							else $_POST["e3"] = $value[1];

						break;																													

					default:

						break;

				}	

				$_POST["send"] = 1;

				



				



		}

						$vars = array("At", "AtTime", "Host", "PC", "p1", "e1", "p2", "e2", "p3", "e3", "p23", "e23");

				foreach ($vars as $k)

				{

					if (!isset($_POST[$k]))

						$_POST[$k] = "";

				}



/*						echo("Here:<br>");

		print_r($_POST);		

				echo("<br>");*/

		

	}

	if (isset ($_POST["send"]))

	{

		foreach ($_POST as $k=>$v)

		{

			$$k = $v;

		}

		//$d = explode(" ", $At);

		$dat = explode ("/",$At);

		if (count($dat)!=3) die("Bad date format.");

		$tim = explode(":", $AtTime);

		if (count($tim)<2) die("Bad time format.");

		$plus = 0;

		if (strstr(strtoupper($tim[1]), "PM"))

		{

			$plus = 12;

		}

		$h = $tim[0];

		$m = explode(" ", $tim[1]);

		$m = $m[0];

		$h+= $plus;

		$At = "${dat[2]}-${dat[0]}-${dat[1]} $h:$m:00";

//		echo("$At");

//		$d = strtotime($At);

		//$At = date("Y-m-d H:i:s", $d);

		//echo("At: $At<br/>");

		//echo("At: $At<br/>");

		mysql_query("insert into tourne_report values ('$Host', '$At', $PC, '$p1', '$e1', '$p2', '$e2', '$p3', '$e3', '$p23', '$e23')") or die (mysql_error());

		$Points = explode("\n", $Points);

		mysql_query("insert ignore into emails values ('$p1', '$e1'),('$p2', '$e2'), ('$p3', '$e3'), ('$p23', '$e23')") or die (mysql_error());

		foreach ($Points as $v)

		{

			if ($v == "") continue;

			$v = explode (" ", $v);

			if (count($v) != 2)

				die("Incorrect values in POINTS input (check spaces!)<br>");

			if (strstr(strtolower($v[1]), 'points')) continue;

			

			$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

			if (!$r) die (mysql_error());

		}

		//print_r($Points);

	}

?>

<!--

<form method="POST" action="<?echo ($sn);?>">

<table>

<tr>

<td>

Host:</td> <td><input type = "text" name = "Host" /></td><td>Host name</td>

</tr>

<!--<tr>

<td>



Nick:</td> <td> <input type = "text" name = "Nick" /></td><td>Nick //What is it?</td> -->

<!--

<tr>

<td>



Date: </td> <td><input type = "text" name = "At" /></td><td>In "MM/DD/YYYY" format</td>

<tr>

<td>



Time: </td> <td><input type = "text" name = "AtTime" /></td><td>In "HH:MM A" format where A is either AM or PM</td>

<tr>

<td>



PlayersCount:</td> <td> <input type = "text" name = "PC" /></td><td>Integer value</td>

<tr>

<td>



Winner:</td> <td> <input type = "text" name = "p1" /></td><td>Winner name</td>

<tr>

<td>



Winner's email: </td> <td><input type = "text" name = "e1" /></td><td>Winner email</td>

<tr>

<td>



2nd place:</td> <td> <input type = "text" name = "p2" /></td><td>2nd name</td>

<tr>

<td>



2nd place email:</td> <td> <input type = "text" name = "e2" /></td><td>2nd email</td>

<tr>

<td>



3rd place:</td> <td> <input type = "text" name = "p3" /></td><td>3rd name</td>

<tr>

<td>



3rd place email:</td> <td> <input type = "text" name = "e3" /></td><td>3rd email</td>

<tr>

<td>



2_3rd place:</td> <td> <input type = "text" name = "p23" /></td><td>Second third place :-)</td>

<tr>

<td>



2_3rd place email:</td> <td> <input type = "text" name = "e23" /></td><td>Second third place email</td>

</table>

Points:<br>

<textarea name="Points" cols="30" rows="7"></textarea>

<br/>

<input type = "submit" name="send" value="Send results"/>

</form>

-->

<img src = "/Posting%20pOints.jpg" /> 

<form method="POST" action="<?echo ($sn);?>">

File contents:<br/>

<textarea name="file" cols="100" rows="20"></textarea><br/>

<input type="submit" name="asfile" value="Send a file" />

</form>

<a href = "<?echo ($sn);?>?act=points">Points</a><br>

<a href = "<?echo ($sn);?>?act=toc">Toc qualifiers</a><br>

<a href = "<?echo ($sn);?>?act=emails">Emails</a><br>

<a href = "<?echo ($sn);?>?act=months">Monthly report</a><br>

<?//<a href = "<?echo ($sn);?><?//?act=erase">ERASE</a><br>

?></html>

any help will be much appreciated

Link to comment
Share on other sites
mike12255 Advanced Member

mike12255

Posted
Posted

Im really not sure what your asking remember your talking to people who have no idea what your end goal is I think I did read one part correctly, you want to retrive things from the database and list them in alphabetical order? you want to use the ORDER by clause

 

ex: "SELECT * FROM `Some_Table` ORDER BY `Name` ASEC" <-- would list entries by alphabetical order. Although if you have very limited php/mysql knowledge Im not sure you will be able to do everything you want. Also not many people like writing code for someone but rather helping them understand theories or better methodology. 

 

And in my personal opinion and I think many other would agree, when it comes to a database no matter who should be able to access it security is always essential! Also after a quick browse through the script you uploaded unless there is a lot more that you just have not shown us I dearly hope you did not pay more then $20 for that the code is not complex. 

Link to comment
Share on other sites
Justafriend Member

Justafriend

Posted
  • Author
Posted

200 dollars for it yes i know i was taken on it ok and now a brief idea of what im doing i run a  backgammon tourney group and this backgammon group uses a program called nojoks we get results that look like this

 

Nojoks's Tourney Bracket Tool Version 1.2.1.84
Tournament:  DeepBlueSea
Date:  11/22/2013
Day:  Friday
Scheduled Start:  9:30
Actual Start:  5:32:28 PM
Closed:  5:44:08 PM
Host:  justme67 ()
Number of Players:  16
1st place:  nurith
1st place email: 
2nd place:  TheAfterlife
2nd place email: 
3rd place:  _Cristina_
3rd place email: 
3rd place:  Morty
3rd place email: 
START POINTS
_BOMBER_ 1
ADG_ISABELLE 1
EU_IT_mirejo 1
EU_IT_petra 1
Sallyforth 1
TM7_CharrM5_ 1
UBG_Bonny 1
ziko_14 1
blackred 4
UBG_DBLEDAREYA 4
UBG_DJ_Cuber 4
UBG_Mila 4
_Cristina_ 17
Morty 17
TheAfterlife 30
nurith 42
STOP POINTS

now with this code it makes 3  databases one is an email list  aquired from

 

1st place:  nurith
1st place email: 
2nd place:  TheAfterlife
2nd place email: 
3rd place:  _Cristina_
3rd place email: 
3rd place:  Morty
3rd place email:

it takes the 1st 2nd and 3rd place players and enters them in the data base for a tournament of champions  list which iis for the last month  and finally makes a list of points by players  and orders their points and adds them right now the problem is with the  primary key being set as playername if any of the players play in more then one tour then the names wont be accepted as they arent unique and i thought about adding the points but the problem is that if i do it  that way that i cant set a date range so that it would only show the points winners for the current month to give a better idea of it here is the layout of the data base

3 tables

table 1

is player name    email which aquires each players name listed in the top part and puts the email with it

table 2 is points received from this

 

START POINTS
_BOMBER_ 1
ADG_ISABELLE 1
EU_IT_mirejo 1
EU_IT_petra 1
Sallyforth 1
TM7_CharrM5_ 1
UBG_Bonny 1
ziko_14 1
blackred 4
UBG_DBLEDAREYA 4
UBG_DJ_Cuber 4
UBG_Mila 4
_Cristina_ 17
Morty 17
TheAfterlife 30
nurith 42
STOP POINTS

player name points and date this is where the data is  given with the points for each player for the curreent month

and finally 

tourne report received from this

 

Nojoks's Tourney Bracket Tool Version 1.2.1.84
Tournament:  DeepBlueSea
Date:  11/22/2013
Day:  Friday
Scheduled Start:  9:30
Actual Start:  5:32:28 PM
Closed:  5:44:08 PM
Host:  justme67 ()
Number of Players:  16
1st place:  nurith
1st place email: 
2nd place:  TheAfterlife
2nd place email: 
3rd place:  _Cristina_
3rd place email: 
3rd place:  Morty
3rd place email:

which is where host name  date time ast 2nd 3rd place and emails is listed 

hope this explains more clearer

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.