...

View Full Version : Need help with coding excel for php and mysql use???



waynem80
07-07-2007, 03:05 AM
Hello,

Ok, so i have an excel document that i can enter my current golf scores in on and it'll calculate different stuff with the data that i enter...

Is there a way that i could use the formula from excel that calculates that data, and make it so that one could use it on my website and it would also save and store the data that they entered, as well as calculate it like excel does and display the current data..

So quick and to the point, I want a database based way of users using what i would use at home kinda, from excel...

wayne

StupidRalph
07-07-2007, 03:45 AM
Yes. There is a way :thumbsup:. You'd have to translate your code to PHP tho. In addition to adding to a database, you can also save the results in a .csv file, an excel compatible format. For more assistance I think you'd have to post more details on what you're attempting to accomplish.

waynem80
07-07-2007, 04:21 PM
Should i upload the excel file?

And all i'm trying to acomplish really is to have a handicap/golf score tracker for my users so that they can enter there score etc.. and their score will be saved, so it'll keep track of it for them :P

wayne

rfresh
07-07-2007, 04:43 PM
Should i upload the excel file?

And all i'm trying to acomplish really is to have a handicap/golf score tracker for my users so that they can enter there score etc.. and their score will be saved, so it'll keep track of it for them :P

wayne

SR already mentioned you'll have to code the excel formula into PHP...so I'm not sure why you want to "upload the excel file"??? What are you hoping that will accomplish? We need more info please.

waynem80
07-07-2007, 05:28 PM
Lol, sorry, duh... I'm very new to php :P ok, well how do i start to code the excel formula into php? Suggestions on how i go about doing this?

thank you,
wayne

Anyone???

waynem80
07-07-2007, 11:27 PM
Can anyone atleast give me an idea of what to google on this topic?

thanks,
wayne

StupidRalph
07-08-2007, 12:21 PM
I think you're missing what we are saying.


We can't give you any more info. b/c we don't know specifically what your formula is. Perhaps you can break down whats going on in your excel sheet...how it works. Different excel formulas will be coded differently in PHP...try posting one of your formulas that needs to be translated.

If you upload your excel file, most people may see it as insulting as if you are trying to get them to do your work for you. Most people are willing to help...but not do it for you. Just meet us half way and we'll get you there.

CFMaBiSmAd
07-08-2007, 03:02 PM
An excel spreadsheet cannot directly be used by PHP for a web page. You would need to write PHP code that specifically accesses each cell you want, and PHP code to load and store values...

Even if you have excel on the same server as the PHP, I am not sure if using a COM object to put a value in a cell, if formulas will even operate. See these links for how you might read a native excel file using PHP -

http://www.php.net/manual/en/class.com.php
http://sourceforge.net/projects/phpexcelreader/
http://www.eephp.com/

Here is an excel search example - http://www.php.net/manual/en/ref.com.php#51780 That is a lot of code to just search for something in a spreadsheet. Do you really want to write a lot of PHP code to manipulate a spreadsheet?

A spreadsheet is not "Web friendly" nor is it really a database. To do what you want using PHP will require you to use a real database and completely code your application in PHP. Any formulas you have in the spreadsheet would need to be written out using PHP variables and code.

Edit: To use PHP and a real database (mysql) you will need to learn PHP and mysql. Start with the programming reference manuals -
http://www.php.net/docs.php
http://dev.mysql.com/doc/

I just searched www.hotscripts.com for "golf" under the PHP category and it only returned three hits, none of which were for a script that records scores and keeps track of handicaps...

Searching on google for "php golf score script" did not turn up much either for a web based script (there are a number of PC based ones), so finding an existing script to do this for you might be hard.

CFMaBiSmAd
07-08-2007, 03:37 PM
Here is guy that has written about what you want (read his other threads in that forum too) - http://www.dbforums.com/showthread.php?t=1605256

Reading his other threads will also give you an idea of how you would need to create forms, read/write information to the database...

You might try to email him through his link in his profile to see if he would give you his code as a starting point.

Len Whistler
07-08-2007, 09:47 PM
waynem80....what is the math formula that excel uses for the calculation?

I doubt there are many golf enthusiasts on this board and we do not know how handicap scores are calculated. PHP has very powerful math capabilities.

waynem80
07-08-2007, 11:48 PM
Ok guys, i've just now finished reading everyone's comments :P very nice.. i'll have to give yall some examples of the math formulas, and then maybe ya'll can help further.

I really feel like i'm in over my head.

But ya know what, i'm gonna give it my best try :P

Here's an example site of what i'm trying to do exactly...

http://www.oobgolf.com/
It's basically a stat tracking php program... and it'll also calculate their handicap with the stats and display that, the handicap comes from the players stats..

another example from this site... http://www.greenskeeper.org/handicaptracker/handicaptrackersample.cfm

thank you,
wayne

Len Whistler
07-09-2007, 12:25 AM
I did a quick Google search and came up with this. I believe this is the math you may need.

http://golf.about.com/cs/handicapping/a/howcalculated.htm

waynem80
07-09-2007, 12:43 AM
Yep your right... and all else i need is a way for my users to enter there scores into the data base and it save them for them...

I'm just new to php, so i find this very overwhelming, but i really just need the right direction to start and i'm sure i can get it.

So i'll also need for the user to enter in the rating/slope of the course on their own, or i could already have that data stored on my database, but i'd rather them just enter that in.

wayne

CFMaBiSmAd
07-09-2007, 01:19 AM
Inputting any kind of data on a web page requires HTML forms and code to process the data when it reaches the server (PHP). Here is some basic info -
http://w3schools.com/html/html_forms.asp
http://w3schools.com/php/php_forms.asp

waynem80
07-09-2007, 01:35 AM
ok, well that makes sense, i'll try and learn a bit on that 2nite

wayne

Len Whistler
07-09-2007, 04:26 AM
I looked at your project a bit more and have come up with what you may need to do, I would say this is at the Intermediate PHP/MySQL level.

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

MySQL database stucture:

player_id, first_name, last_name, golf_score, course_rating, slope_rating, player_handicap, plus other player info.

Form would have these fields:

first_name, last_name, golf_score, course_rating, slope_rating, plus any other info you may want.

During the form submission PHP will fill in the player_handicap column using this formula:

player_handicap = (golf_score - course_rating) x 113 / slope_rating

When the webpage is viewed PHP will then take the player_handicap column and output using the Golf Handicap Calculation, example:

18 rows of player_handicap data.
Add up the 8 lowest scores and divide by 8.
Multiply by .96
Results = Handicap

7 rows of player_handicap data.
Add up the 2 lowest scores and divide by 2.
Multiply by .96
Results = Handicap

Looks like you will need 10 php if statements - or php switch - to handle less than 20 rows of data since the math is slightly different. I assume more than 20 rows of data will have the same math.

waynem80
07-09-2007, 08:02 AM
Ok i have a lot better understanding now of what i'm trying to do , and what needs to be done...

First thing first i've got to build my tables in the data base.. this is where the info the user enters will be stored :P

then i'll need a little help from u guys on how to identify a certain user with his/her own data.

and i c the php will post/get user data, and calculate "the math" of x = x and show the stats of the users data that was entered, ah ha, :P off to a good start i hope :)

wayne

ps, i just read the lastest reply :P haha, thank you very much, this will be fun for me :)

waynem80
07-09-2007, 08:08 AM
I looked at your project a bit more and have come up with what you may need to do, I would say this is at the Intermediate PHP/MySQL level.

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

MySQL database stucture:

player_id, first_name, last_name, golf_score, course_rating, slope_rating, player_handicap, plus other player info.

Form would have these fields:

first_name, last_name, golf_score, course_rating, slope_rating, plus any other info you may want.

During the form submission PHP will fill in the player_handicap column using this formula:

player_handicap = (golf_score - course_rating) x 113 / slope_rating

When the webpage is viewed PHP will then take the player_handicap column and output using the Golf Handicap Calculation, example:

18 rows of player_handicap data.
Add up the 8 lowest scores and divide by 8.
Multiply by .96
Results = Handicap

7 rows of player_handicap data.
Add up the 2 lowest scores and divide by 2.
Multiply by .96
Results = Handicap

Looks like you will need 10 php if statements - or php switch - to handle less than 20 rows of data since the math is slightly different. I assume more than 20 rows of data will have the same math.

Yeah this seems to be pretty accurate :) i've been reading up on php/mysql all nite..

I don't think i'll need the first/last name, cause for the player_id, i'm gonna what it to be the users login name

would that be correct?

thanks,
wayne

ps, take your time getting back to me, i've gotta go get some sleep for about 7 hours and tackle this problem tomorrow :) it's 3:00 am here :P

waynem80
07-09-2007, 04:00 PM
ok, time to get started :P

waynem80
07-09-2007, 04:34 PM
if i'm making it so that the user logs in and the data is stored under that persons authentication/username, do i have to create tables in the username part of the database?

wayne

Len Whistler
07-09-2007, 07:40 PM
if i'm making it so that the user logs in and the data is stored under that persons authentication/username, do i have to create tables in the username part of the database?

wayne

I have never created a members website but I believe you will need two MySQL tables connected with a unique player_id field.

Table One
player_id, user_name, player_password

Table Two
player_id, first_name, last_name, golf_score, course_rating, slope_rating, player_email, ........ etc.

waynem80
07-09-2007, 09:54 PM
ok, sounds good

wayne

timgolding
07-10-2007, 01:11 PM
here a class that will read excel spreadsheets

http://pinman-designs.co.uk/Excel.rar

waynem80
07-10-2007, 06:47 PM
I saved it.. it might come in handy :)

waynem80
07-10-2007, 09:27 PM
Oh, i've found something really usefull ,check it out guys :) hehe, i really like this, it's a script class...

http://phpclasses.promoxy.com/browse/package/2546.html

example site : http://www.gaclarke.com/agolfhandicap/handicapu.php

i can use this script to add on to i think...

So, i've downloaded the script, and i'm sure i'll have some php ???'s but i'm gonna try and get it working first :)

wayne

waynem80
07-10-2007, 11:37 PM
CREATE TABLE `golfhcapu` (
`id` int(11) NOT NULL auto_increment,
`user` varchar(30) NOT NULL default '',
`course` varchar(50) default NULL,
`date` varchar(10) NOT NULL default '',
`score` int(11) NOT NULL default '0',
`crating` decimal(11,1) NOT NULL default '0.0',
`srating` int(11) default NULL,
`hcapdiff` decimal(11,1) NOT NULL default '0.0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=16 ;

this is the SQL i'm using to create the db, Since i already have users, for my forum, and that's what i want my users name to be, and they'll only be able to access this when signed in, what suggestions would you guys give me to the SQL???


wayne

waynem80
07-10-2007, 11:45 PM
<?php
// class.agolfhandicap.php
// version 1.0.1, 27 August, 2005
//
// AGolfHandicap class is a multi user database and can keep track of games for
// multiple players and calculate their handicap indexes.
// First enter your name or user name then the game information. It then
// calculates the handicap differential for the entered game using the score,
// course rating, and the slope rating.
// A minimum of 5 games must be played before a handicap index can be calculated.
//
// This script really should be used with a user/password login system for security.
//
// version 1.0.1 -- 4 September, 2005
// Corrected a possible error when there are no games played or less than
// five games playes.
// version 1.1.0 -- 9 September,2005
// Added a check to make sure all fields of a game are entered before
// saving it in the database.
// Added a Delete button to each line of the table displaying the games played.
// Added an Edit button to each line of the table displaying the games played.
// Made the date save in the database in the format of mm/dd/yyyy (with leading
// zeros) to fix a sorting bug.
//
// License
//
// Copyright (C) 2005 George A. Clarke, webmaster@gaclarke.com, http://gaclarke.com/
//
// This program is free software; you can redistribute it and/or modify it under
// the terms of the GNU General Public License as published by the Free Software
// Foundation; either version 2 of the License, or (at your option) any later
// version.
//
// This program is distributed in the hope that it will be useful, but WITHOUT
// ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
// FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License along with
// this program; if not, write to the Free Software Foundation, Inc., 59 Temple
// Place - Suite 330, Boston, MA 02111-1307, USA.
//

class agolfhandicap
{
var $user; // Username of golfer
var $dbuser; // Username for database
var $pw; // Password for database
var $dbase; // Database name
var $db;
var $all = array(); // Array used to return all game information from database
var $use=array(0=>0,0,0,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,8,9,10);
// $use is an array used to determine how many games
// to use to calculate the handicap index.

// Set up the MYSql database access.
// $dbuser = database username
// $pw = database password
// $dbase = database name

function setupdb($dbuser,$pw,$dbase)
{
$this->dbuser=$dbuser;
$this->pw=$pw;
$this->dbase=$dbase;
$this->db = mysql_connect('localhost',$dbuser,$pw);
}

// Sets the player's name.
function setuser($user)
{
$this->user=$user;
return true;
}

// Retrieves the submitted new game information and stores it in the database.
function getnewgame($id="")
{
if($_POST['submit']){
// print "submit=".$_POST['submit'];
// $id=$_POST['id'];
$user=$_POST['user'];
$this->user=$user;
$date =$_POST['date1'];
$d=explode("/",$date);
$date=date("m/d/Y",mktime(0,0,0,$d[0],$d[1],$d[2]));

$score = $_POST['score1'];
$crat = $_POST['crat1'];
$srat = $_POST['srat1'];
$course = $_POST['course1'];
if(!$date || !$score || !$crat || !$srat || !$course){
return false;
}else{
$hcap=round(($score-$crat)*113/$srat,1);
if($id==""){
$queryInsert = "INSERT INTO `golfhcapu` (user,course,date,score,crating,srating,hcapdiff) VALUES ('$user','$course','$date','$score','$crat','$srat','$hcap')";
$resultGetPages = mysql_db_query($this->dbase, $queryInsert) or die ("Query failed: error was ".mysql_error());
}else{
$upd="UPDATE `golfhcapu` SET user='$user',date='$date',course='$course',score='$score',crating='$crat',srating='$srat',hcapdiff=' $hcap' WHERE id='$id'";
$result=mysql_db_query($this->dbase,$upd);
}
$_POST['edit']="";
$_POST['id']="";
$_POST['date1']="";
$_POST['score1']="";
$_POST['crat1']="";
$_POST['srat1']="";
$_POST['course1']="";
return true;
}
}else{
return false;
}
}

// This function displays a form to fill out to input the following
// new game information:
// Date
// 18 hole score
// Course Rating
// Slope Rating of the course
// Name of the golf course
function showform($id="") //Give it the id number of a record to edit it.
{
if($id == ""){ //If not editing, get the POST data
$date=$_POST['date1'];
$score=$_POST['score1'];
$crat=$_POST['crat1'];
$srat=$_POST['srat1'];
$course=$_POST['course1'];
}else{ //If editing get the values from the database
$queryGetPages = "SELECT * FROM `golfhcapu` WHERE `id`='$id'";
$resultGetPages = mysql_db_query($this->dbase, $queryGetPages) or die ("Query failed: error was ".mysql_error());
$row = mysql_fetch_array($resultGetPages);
$date=$row['date'];
$score=$row['score'];
$crat=$row['crating'];
$srat=$row['srating'];
$course=$row['course'];
}
print ($id=="")?"<h4>Enter a new game.</h4>":"<h4>Edit the game</h4>";
print "<table border=1>";
print "<tr align='center'>";
print "<td>Date<br>(mm/dd/yyyy)</td>";
print "<td>Adjusted<br>Gross Score</td>";
print "<td>USGA Course<br>Rating</td>";
print "<td>USGA Slope<br>Rating</td>";
print "<td>Course Name</td>";
print "</tr>";
print "<tr align='center'>";
print "<form name='frm' action='$PHP_SELF' method='POST'>\n";
print "<input type='hidden' name='user' value='$this->user'>\n";
print "<input type='hidden' name='id' value='$id'>\n";
print "<td><input type='text' size='15' name='date1' value='$date'></td>\n";
print "<td><input type='text' size='5' name='score1' value='$score'></td>\n";
print "<td><input type='text' size='5' name='crat1' value='$crat'></td>\n";
print "<td><input type='text' size='5' name='srat1' value='$srat'></td>\n";
print "<td><input type='text' size='30' name='course1' value='$course'></td>\n";
print "</tr></table>\n";
print "<br><table><tr>";
print "<td><input type='submit' name='submit' value='Submit'>\n";
print "</form></td>";
print "<td><form name='frm' action='$PHP_SELF' method='POST'>\n";
print "<input type='hidden' name='user' value='$this->user'>\n";
print "<input type='submit' name='cancel' value='Cancel'>\n";
print "</form></td></tr></table>";
return true;
}


// Displays a table of all the games played by user with the latest game first. The
// information displayed is:
// Game number
// Date of the game
// Adjusted Gross Score
// USGA Course Rating
// USGA Slope Rating of the course
// Handicap Differential of the game as calculated by this class
// Course name

function showall()
{
if($all=$this->getAll()){;
print "<table border=1>";
print "<tr align='center'>";
print "<td>Game<br>Number</td>";
print "<td>Date</td>";
print "<td>Adjusted<br>Gross Score</td>";
print "<td>USGA Course<br>Rating</td>";
print "<td>USGA Slope<br>Rating</td>";
print "<td>Handicap<br>Differential</td>";
print "<td>Course Name</td>";
print "<td>Edit</td>";
print "<td>Delete</td>";
print "</tr>";
$n=count($all)-1;
for($i=$n;$i>=0;$i--){
$id=$all[$i]['id'];
$j=$i+1;
print "<tr align='center'>";
print "<form method='POST' action='$PHP_SELF'>\n";
print "<input type='hidden' name='user' value='$this->user'>\n";
print "<input type='hidden' name='id' value='$id'>\n";
print "<td>$j</td>";
print "<td>".$all[$i]['date']."</td>";
print "<td>".$all[$i]['score']."</td>";
print "<td>".$all[$i]['crating']."</td>";
print "<td>".$all[$i]['srating']."</td>";
print "<td>".$all[$i]['hcapdiff']."</td>";
print "<td>".$all[$i]['course']."</td>";
print "<td><input type='submit' name='edit' value='Edit'></td>\n";
print "<td><input type='submit' name='delete' value='Delete'></td>\n";
print "</form>";
print "</tr>";
}
print "</table>";
}

}

// Retrieves the information for each game entered for 'user' in the database
// and returns it in an multidimensional array.
//
// Data for the first game:
// array[0]['id'] id number of record in database
// array[0]['date'] date game was played
// array[0]['score'] adjusted gross score of game
// array[0]['crating'] course rating
// array[0]['srating'] slope rating
// array[0]['hcapdiff'] handicap differential
// array[0]['course'] course name

// Data for the second game:
// array[1]['id']
// array[1]['date']
// array[1]['score']
// array[1]['crating']
// array[1]['srating']
// array[1]['hcapdiff']
// array[1]['course']
//
// etc
//
function getAll()
{
$queryGetPages = "SELECT * FROM `golfhcapu` WHERE `user`='$this->user' ORDER BY `date`";
$resultGetPages = mysql_db_query($this->dbase, $queryGetPages) or die ("Query failed: error was ".mysql_error());
$n=mysql_num_rows($resultGetPages);
if($n > 0){
$i=0;
while ($row = mysql_fetch_array($resultGetPages)){
$this->all[$i]['id']=$row['id'];
$this->all[$i]['date']=$row['date'];
$this->all[$i]['score']=$row['score'];
$this->all[$i]['crating']=$row['crating'];
$this->all[$i]['srating']=$row['srating'];
$this->all[$i]['hcapdiff']=$row['hcapdiff'];
$this->all[$i]['course']=$row['course'];
$i=$i+1;
}
return $this->all;
}else{
return false;
}
}

// Reads the database and retrieves up to the last 20 games played.
// Determines how many of these games to use, and which ones, to calculate
// the handicap index.
// Using the chosen games, calculates the handicap index and returns it.
// If fewer than five games have been played, returns 0.
// A minimum of five games must have been played to determine the handicap index.
//
function gethcap()
{
$queryGetGames = "SELECT * FROM `golfhcapu` WHERE `user`='$this->user' ORDER BY `date` DESC LIMIT 20";
$resultGetGames = mysql_db_query($this->dbase, $queryGetGames) or die ("Query failed: error was ".mysql_error());
$nr=mysql_num_rows($resultGetGames);
if($nr > 4){
$tot=0;
$n=$this->use[$nr];
for($i=0;$i<$nr;$i++){
$row = mysql_fetch_array($resultGetGames);
$hcd[$i]=$row['hcapdiff'];
}
sort($hcd);
for($i=0;$i<$n;$i++){
$tot=$tot+$hcd[$i];
}
$hcap=floor(10*(($tot/$n)*.96))/10;
return $hcap;
}else{
return 0;
}
}
function deleteGame($id)
{
$sql = "DELETE FROM golfhcapu WHERE id=$id";
$result = mysql_db_query($this->dbase, $sql) or die ("Delete failed: error was ".mysql_error());
return;
}
}
// End of class


here's the php code, Any suggestions, on how i could go about making this so that it uses there username in my db, only when there signed in, and i guess it'll use cookies to remember them right?

wayne

waynem80
07-11-2007, 01:18 AM
<?php
// This is an example script using the agolfhandicap class to record games played
// and calculate your golf handicap index.
// It is a multi user database and can keep track of games for different players.
// First enter your name or user name then the game information.
// This script really should be used with a user/password login system for security.
//
// Version 1.1.0: Added Edit and Delete for a record in the database.
//
// ############ You must edit the username, password and database name further down.

include "class.agolfhandicap.php";
$gh=new agolfhandicap();

// ######### Edit the next line to put your MySQL username, password, and database name. ########
$gh->setupdb('','','');

// Get the user name if submitted.
if($_POST['user']){
$user=$_POST['user'];
$gh->setuser($user);
}
if($_POST['delete']){ //delete the record
$id=$_POST['id'];
$gh->deleteGame($id);
}
if($_POST['edit']){ //edit the record
$id=$_POST['id'];
}
// If no user yet, ask for it.
if(!isset($_POST['user'])){
print "<form name='usr' action='$PHP_SELF' method='POST'>";
print "Enter your user name:<input type='text' name='user' size='20'>";
print "<input type='hidden' name='hid' value='true'>";
print "<input type='submit' value='Submit' name='submits'>";
print "</form>";
}else{
// We have a username, so get new game data and display handicap and all games.
print "<h2>Golf Game Database and Handicap Calculator for $user</h2>\n";
$newgame=$gh->getnewgame($id);
$id=($_POST['edit']=="")? "":$id;
$gh->showform($id);
$hc=$gh->gethcap();
print ($hc==0)?"<h3>You need at least 5 games for a handicap index.</h3>":"<h3>Handicap for $user is $hc</h3><br>";
// If you just want the data without displaying it, use next line.
//$al=$gh->getAll();
$gh->showall();
}
?>

waynem80
07-11-2007, 01:41 AM
Ok... i've atleast got that setup working on my site... now i've got to figure out how it'll take the forum username in place of it's regular user setup with the SQL...

anyone can help with this please :)

wayne

Len Whistler
07-11-2007, 04:30 AM
Which forum are you using? I'm sort of familiar with phpBB.

With phpBB they have some user profile data that can only be entered once the user has logged in. You can modify this by adding the form fields - except for the "id" and "user" fields - from the golf script, you would then delete the MySQL "user" and "id" columns for the golf script since you will be using your forums user ID's.

The results from the golf script and database is then outputted onto your site for all to see.

waynem80
07-11-2007, 04:35 AM
Which forum are you using? I'm sort of familiar with phpBB.

With phpBB they have some user profile data that can only be entered once the user has logged in. You can modify this by adding the form fields - except for the "id" and "user" fields - from the golf script, you would then delete the MySQL "user" and "id" columns for the golf script since you will be using your forums user ID's.

The results from the golf script and database is then outputted onto your site for all to see.

I use Vanilla :)

wayne

isshin
07-12-2007, 08:43 AM
yeah why not huh



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum