I've at least *seen* PHP code before, and have modified my own phpBB forum moderately.

However, I've been set a project to build a web interface for recording skittles scores (like 10-pin bowling, but with 9 pins!!), and producing a nice ordered table with player names, number of games played, their aggregate scores, highest/lowest score, and average score (like here). I've got the table at least designed to fit with our phpBB board here!

I've done some MSSQL DB work before, so I'm not too fazed with getting a reasonable DB structure designed - theory must be the same on MySQL? But when it comes to building an interface in PHP that will perform checks on the DB to make sure you're entering valid data (no more than 12 players/scores per match, one score per match per person, etc), and then retrieving that data, processing it, performing maths on it, sorting it, and displaying it, I have no idea where to start!!

Someone suggested Fireserv elsewhere to develop on, which I'm going to do, but I was really looking for an indication of how complex a project this sounds. Would an experienced PHP developer look at it and think it's "a bit hard"? If so, I think I'll give up now! :o

If I go ahead with it, I'll need some hand holding verging on complete coding help no doubt! :D

Hi BTW!

EDIT: If anyone has a recent up-to-date copy of Fireserv available, could you let me know please. Their site seems to be down, and the only copies available are over 2 years old.

Thanks, downloaded and installed, and it does indeed look very good.

Anyone able to comment on how tricky the project I described would be for a newbie, or indeed an expert?

I was considering breaking it down slightly, and initially just building a page that takes a comma-delimited text file as an input with the basic data for the table, and PHP takes that, sorts the rows, and displays it. Would that be particularly tricky, or perhaps more suitable for a newbie?

Anyone able to give me, or knock up, a sample bit of PHP code that would take the following text file (called statistics.txt)...

Player 1, 4, 140, 38, 35, 35.0
Player 2, 5, 200, 46, 36, 40.0
Player 3, 4, 150, 40, 35, 37.5

...sort it based on the last column, and display it in a table?

Once I've got that, I can then expand it and develop it to pull the data from a database, and then onto the actual inputting of data into the DB.

The project seems like a good place to get your feet wet with php / mysql. Initially it looks pretty simple. I would recommend using a db rather than text file so that mysql can do the math and sorting rather than relying on php for sorting.

Thanks for the reply. I've actually had a look around this site and discovered some code snippets here, which shows how to build a page that simply stores and retrieves data from a DB.

I'm going to have a play with that, and see if I can make any sense of it.

Right, I can see how to interact with a DB now. The next step, for my submit page I want to pull all the player names from a table in the database, and have those names available under a drop-down list (via the form OPTION VALUE="name" method).

The only way I can think of doing this would be to pull the values from the DB, get the number of hits and form a WHILE loop, to repeat the OPTION VALUE="" for as many times as there are names.

Is this the way to do it? If so, how do I construct it? If not, what is the best way?

It would look something like this:

$sql = mysql_query("select name from table_name");

while ( $row = mysql_fetch_array($sql)) {

echo ("OPTION VALUE='$row[name]' ");

}

Hope this helps

Woo hoo! PHP is starting to make sense to me! Thanks - I've got it updating the database now.

Now I know there is a DB section on this site, but I figured most of the background is in here, so I'll pose my next query here too...

I've now hit upon my next weakness; joining tables in SQL. I'm *okay* with nesting SQL queries, but I've just discovered that pre-MySQL 4.1 doesn't support nested queries, only joins. :cry:

I've got 3 tables:

matches - matchid, date, homeaway, season
players - playerid, fullname, nickname
scoreslist - scoreid, playerid, matchid, score

And as mentioned previously, I need to retrieve the appropriate scores (for the current season - 2004), and produce an ordered table of statistics (ordered on average column) as shown below - I've added notes on what each column relates to in the DB:

Player Name (players.fullname)
Games Played (just a count of the number of scores relating to a player this season)
Aggregate (the sum of all those scores relating to a player)
Highest (highest value from that list of scores)
Lowest (lowest value from that list of scores)
Average (games played / aggregate)

I'm having real problems knowing where to begin, and looking for guidance really. If only it supported nested queries! :-|

Actually, even my nested skills obviously aren't up to scratch - all I've got so far is...

SELECT playerid, score
FROM scoreslist
WHERE IN (
SELECT matchid
FROM matches
WHERE season =2004
)

I've got the added problem of MySQL not having linked tables enabled, although from what I've read this can be worked around? I'm thinking for linking the scores (through playerid) to the fullnames column.

Actually, even my nested skills obviously aren't up to scratch - all I've got so far is...

SELECT playerid, score
FROM scoreslist
WHERE IN (
SELECT matchid
FROM matches
WHERE season =2004
)

I've got the added problem of MySQL not having linked tables enabled, although from what I've read this can be worked around? I'm thinking for linking the scores (through playerid) to the fullnames column.

Actually, that should be...

SELECT playerid, score
FROM scoreslist
WHERE matchid in (
SELECT matchid
FROM matches
WHERE season =2004
);

I've installed MySQL 4.1 now, purely for development purposes. Anything I develop will have to be converted back for pre-4.1.

Any ideas how I can link the scores to the fullnames in that query, without using linked tables?

SELECT nickname, count( * ) , sum( score ) , max( score ) , min( score ) , avg( score )
FROM players, scoreslist
WHERE players.playerid = scoreslist.playerid AND scoreslist.matchid
IN (

SELECT matchid
FROM matches
WHERE season =2004
)
GROUP BY nickname
ORDER BY avg( score ) DESC

Here's my final(ish) nested query. There's only the one nest in there, but I'm still at a loss to know how to convert it to a non-nested query. Can anyone help?

PS. I'm very nearly there!!

This article has been dead for over six months. Start a new discussion instead.