Allow members ability to display and edit specific info in MySQL table

Das_Kemper

I am hosting a sports website and am processing schedules for the particpating clubs to assign times and field assignments. An example table is as follows:

<table id="table1">
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Club</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Age</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Date</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>
Time/Notes</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Time</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Home</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>
Visitors</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Field</b></td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
7</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">04/21/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 2</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 2</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
9</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">04/21/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">after
1pm</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 3</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 2</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 2</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
8</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">04/28/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 2</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
7</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">05/05/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 3</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 3</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
8</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">05/05/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">before
3pm</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 3
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 1</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px">Club 3</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">
<div align="center">
9</div>
</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">05/05/07</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 3</td>
<td vAlign="top" style="border-style: solid; border-width: 1px">Team 5</td>
<td vAlign="top" style="border-style: solid; border-width: 1px"> </td>
</tr>
</table>

What I want to do allow schedules from each club to see and edit their own specific schedules (eq. Club 1 would display only their own rows and be able to input times into "Time" column and specific field assignments into "Field" column).

I am not exactly sure where to start. I have the complete schedule with 30 different clubs and 12 different ages in a MySQL database table already.

Assistance is greatly appreciated.

Roger_Ramjet

You will need to use a login so that you can identify which club the user is from, and sessions to store that login data in. So you need an access control script - can you write one?

Assuming that you have sorted out access control and verified the user you should then have their club id in a session var which you then use in a query like this

$id = $_SESSION['clubid'];

$display_query = "SELECT * FROM schedule WHERE clubid = $id";
$result = mysql_query($display_query);

// start table
echo '<table id="table1">
<tr>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Club</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Age</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Date</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>
Time/Notes</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Time</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Home</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>
Visitors</b></td>
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Field</b></td>
</tr>';

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

// code to populate your table with each row in the schedule

}

echo '</table>';

Now please learn to use the php forum tags when you next post, as I'm sure you will.

Das_Kemper

Site, membership, database already setup. This has been in the works for years now. Each Club Scheduler has already been assigned to the specific club, so I do not need the precursor "Club" select and submit.

Let's say "Club 1" Scheduler logs in. How can I write the script to display and allow editting to only "Club 2"?

Roger_Ramjet

As I said, get the club id for the logged in user and use it in your query.

Do you mean 'How do I display the results of a query in a table for editing?'. Read some of the articles at the fornt of this site and query the forums for loads of threads on the subject. Try writing some code and then post when you get a specific error.

Das_Kemper

OK, I have attempted your idea.

<?php

// mySQL Table
$db_con = mysql_connect(MyHost, MyUsername, MyPassword) or die("Connetion to database failed!");
mysql_select_db(MyDatabase);
$id = $_SESSION['clubid']; 

$display_query = "SELECT * FROM MyTable WHERE clubid = $id"; 
$result = mysql_query($display_query); 

// start table 
echo '<table id="table1"> 
<tr> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Club</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Age</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Date</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b> 
Time/Notes</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Time</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Home</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b> 
Visitors</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Field</b></td> 
</tr>'; 

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

// code to populate your table with each row in the schedule 

} 

echo '</table>'; 
  } 

?>

I received error: "Parse error: syntax error, unexpected '}' in /home/kemper/public_html/MySite/schedules.php on line 33"

I am hoping to have a form like:

Roger_Ramjet

It means an unmatched } in the code. If you just copy/pasted my code which I copy/pasted from yours then I'm not surprised. Look through the code to find it.

trehy2006

<?php 

// mySQL Table 
$db_con = mysql_connect(MyHost, MyUsername, MyPassword) or die("Connetion to database failed!"); 
mysql_select_db(MyDatabase); 
$id = $_SESSION['clubid']; 

$display_query = "SELECT * FROM MyTable WHERE clubid = $id"; 
$result = mysql_query($display_query); 

// start table 
echo '<table id="table1"> 
<tr> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Club</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Age</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Date</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b> 
Time/Notes</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Time</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Home</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b> 
Visitors</b></td> 
<td vAlign="top" style="border-style: solid; border-width: 1px"><b>Field</b></td> 
</tr>'; 

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

// code to populate your table with each row in the schedule 

} 

echo '</table>'; 

?>

Its just that last one which i have removed. see what you get!

Rob

Roger_Ramjet

EH??

trehy2006

Roger Ramjet wrote:
EH??

i was showing with brace had not been removed....

The topic is not set to resolved, so i assumed Das had not found the problem

Das_Kemper

Code looks the same as my post.

What is with the Fonz? Eh!

Roger_Ramjet

So, Das Kemper, have you fixed this yet? If so then please mark the thread as RESOLVED by using the Thread Tools at the top.