php/mysql help

[Dub_Dude]

Ok guys, im a building a Arsenal FC fan site and am currently trying to figure out the the league table. What I want to be able to do is create a league table like the one shown here:

http://www.arsenal.com/table.asp?tit...on+-+Match+Day

I want to place the data into the database with the relevant headings and then display the data on a web page sorted by the points(pts) coloumn in ascending order. It also needs to be updatable as the data changes every week. To enter the data I need a form. If anybody can help me with this or at least point me towards a good tutorial that would be great.

Thank You!

Brad

[bigguy]

Have you tried hotscripts.com for an online league script ? I know they have some over there.

[Dub_Dude]

ok, excellent, i'll go have a look over there, thank you

[bigguy]

No problem, I know you`ll find one there. :-)

[Bryon]

Check out this tutorial I wrote:

http://nedren.com/viewtutorial.php?t...UxTUlFWnZjbTF6

That should help you begin to understand how to use HTML forms with PHP.

Do you have any PHP knowledge at the moment?

For the database table, you could have every "column" in the table you want to dynamically build/create as a column in the database table, along with a row to contain an auto-increment "ID" number.

When you wanted to "get" all of the rows from the database and display them, you could do something like:

...
   $tableResults = mysql_query("SELECT * FROM `tablename`") or die(mysql_error());
   while ($tableRow = mysql_fetch_array($tableResults)) {
      echo '
<tr>
<td>'. $tableRow['id'] .'</td>
<td>'. $tableRow['teamName'] .'</td>
<td>'. $tableRow['column1'] .'</td>
<td>'. $tableRow['column2'] .'</td>
<td>'. $tableRow['column3'] .'</td>
<td>'. $tableRow['column4'] .'</td>
[, etc etc]
</tr>
      ';
   }
... 

Now when you wanted to "edit", "delete", and even "add" each row, you could have a page with:

<?php

   mysql_connect('localhost','username','password');
   mysql_select_db('database');
   echo '<table><tr><td colspan="8"><a href="'. $PHP_SELF .'">Back To Main</a></td></tr>';

   if ($_GET['do'] == 'delete') {
      $rid = (int) $_GET['rid'];
      mysql_query("DELETE FROM `tablename` WHERE `id` = '$rid' LIMIT 1") or die(mysql_error());
      echo '<tr><td>Row ID <b>'. $rid .'</b> was successfully deleted!</td></tr>';
   }
   elseif($_GET['do'] == 'edit') {
      $rid = (int) $_GET['rid'];

      if ($_POST['submit']) {
         $teamName = $_POST['teamName'];
         $column1 = $_POST['column1'];
         $column2 = $_POST['column2'];
         $column3 = $_POST['column3'];
         $column4 = $_POST['column4'];

         mysql_query("UPDATE `tablename` SET `teamName` = '$teamName', `column1` = '$column1', `column2` = '$column2', `column3` = '$column3', `column4` = '$column4' WHERE `id` = '$rid' LIMIT 1") or die(mysql_error());
         echo '<tr><td>Row ID <b>'. $rid .'</b> was successfully updated!</td></tr>';
      }
      else {

         $rowResults = mysql_query("SELECT * FROM `tablename` WHERE `id` = '$rid' LIMIT 1") or die(mysql_error());
         $tableRow = mysql_fetch_array($rowResults);
         echo '
<tr>
<td>

<form action="'. $PHP_SELF .'?do=edit&rid='. $rid .'" method="post">

<table>
<tr>
<td><b>ID:</b></td>
<td>'. $tableRow['id'] .'</td>
</tr>

<tr>
<td><b>Team&nbsp;Name:</b></td>
<td><input type="text" value="'. $tableRow['teamName'] .'" name="teamName" /></td>
</tr>

<tr>
<td><b>Column 1:</b></td>
<td><input type="text" value="'. $tableRow['column1'] .'" name="column1" /></td>
</tr>

<tr>
<td><b>Column 2:</b></td>
<td><input type="text" value="'. $tableRow['column2'] .'" name="column2" /></td>
</tr>

<tr>
<td><b>Column 3:</b></td>
<td><input type="text" value="'. $tableRow['column3'] .'" name="column3" /></td>
</tr>

<tr>
<td><b>Column 4:</b></td>
<td><input type="text" value="'. $tableRow['column4'] .'" name="column4" /></td>
</tr>
[, etc etc]

<tr>
<td colspan="2" style="text-align: center;"><input type="submit" name="submit" value="Update Row" /></td>
</tr>
</table>

</form>

</td>
</tr>
         ';
      }
   }
   else {

      $tableResults = mysql_query("SELECT * FROM `tablename`") or die(mysql_error());
      while ($tableRow = mysql_fetch_array($tableResults)) {
        echo '
<tr>
<td>'. $tableRow['id'] .'</td>
<td>'. $tableRow['teamName'] .'</td>
<td>'. $tableRow['column1'] .'</td>
<td>'. $tableRow['column2'] .'</td>
<td>'. $tableRow['column3'] .'</td>
<td>'. $tableRow['column4'] .'</td>
[, etc etc]
<td><b><a href="'. $PHP_SELF .'?rid='. $tableRow['id'] .'&do=edit">Edit Row</a></b></td>
<td><b><a href="'. $PHP_SELF .'?rid='. $tableRow['id'] .'&do=delete">Delete Row</a></b></td>
</tr>
         ';
      }
   }

   echo '</table>';
?>

Now that is just a rough page that gets the job done. It's not very "nice" looking, but it works.

You just need to create a database table, and replace "tablename" in each of the mysql_query()'s above with the exact name of the table you made. You can then update the column names (Add more, change the names, take some away) to suit your needs.

If you have any problems, just post back. Hopefully you will be able to see what I have done and how I did it.

[Dub_Dude]

ok, i don't have any php/mysql knowledge although i can kinda see how it works, im still not sure how to submit data from a form to the database im fairly confident i can make the form though.

EDIT: Ignore that, got it working, I just need to format it so it looks better, any ideas on that would be great

See it at: http://tsof.x10hosting.com/joomla/index.php -- click on premiership table