foreign keys

keyser_soze

I am doing a clan website, and I can't figure out one VERY important part...

I have a schedule page set up, where it displays battles, IP's of the servers, passwords, etc on one row, and every row is a different battle.

Two functions i have on these rows are:
- View Roster
- Sign Up

The View Roster button displays every person who has clicked the Sign Up buttom in a popup box. That is not the problem. The problem is my Sign Up button.

I have no idea how to insert a user into a table that is already made... Someone told me i might need to use foreign keys, but i don't know how to use them.

If someone needs more explination on what I'm trying to say, ask me a specific question, because i don't want to include a hunk load of crap that wouldn't be useful.

Thanks

nnichols

Presumably you have the users ID in a sesion variable and when you click on the join button the join script knows the battle to add the user to. All you need to do then is -

$sql = INSERT INTO battles (battle_id, user_id) VALUES ($battle_id, $user_id)

Does this answer your question?

keyser_soze

the problem is, i'm not exactly sure how to get the id of the row, then insert the user into that row with the same id...

nnichols

When you build the schedule page which has the button you just need to add "?battle_id=$row['id']" when you create the link for the button.

Hope this helps 😉

P.S. If you don't see what I mean post the code that you use to create the schedule page.

keyser_soze

<body bgcolor=#515151 style="margin:0;scrollbar-face-color: #515151; scrollbar-shadow-color: #666666; scrollbar-highlight-color: #515151; scrollbar-3dlight-color: #666666; scrollbar-darkshadow-color: #515151; scrollbar-track-color: #515151; scrollbar-arrow-color: #888888;">

<?
$user = "$valid_user<br>";
?>

<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr><td width=55><p class="table_header">Ladder</td><td width=50><p class="table_header">Date</td><td width=55><p class="table_header">Clan</td><td width=80><p class="table_header">IP</td><td width=50><p class="table_header">Password</td><td width=50><p class="table_header">Current Roster</td><td width=50><p class="table_header">Sign Up</td></tr> 
<?php
$query = "SELECT * FROM member_schedule ORDER BY schedule_id DESC";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// For alternate row colour
$row_colour="#4d4d4d";
$count_rows = 0;

//loop and display the limited records being browsed
while ($row = mysql_fetch_object($result))
{
$count_rows++;
if ($count_rows%2==0)
{ $row_colour="#4d4d4d"; } // Even-numbered rows
else
{ $row_colour="#4a4a4a"; } // Odd-numbered rows
?>


<tr bgcolor="<?php echo $row_colour; ?>"> <!-- Alternate Row Colours -->

<td><p><? echo $row->schedule_ladder; ?></td>
<td><p><? echo $row->schedule_date; ?></td>
<td><p><? echo $row->schedule_clan; ?></td>
<td><p><? echo $row->schedule_ip; ?></td>
<td><p><? echo $row->schedule_password; ?></td>
<td><a href="javascript:openwindow('members_battleroster.php?id=<? echo $row->schedule_id; ?>')">View</a></td>

<?
if (!$submit)
{
?>
<form action="<? echo $PHP_SELF; ?>" method="POST">
<td><input type="Submit" name="submit" value="Sign Up" class="formbox"></td>
</form>
<?
}
else
{
 $id = "$row->schedule_id";
 mysql_select_db($db) or die ("Unable to select database!");
 $query = "INSERT INTO member_schedule(schedule_roster) VALUES('$user')";
 $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
 echo "<td><p>Signed Up</td>";
}
?>

</tr>
<?php }; ?>
</table>
</body>

i used the form button, because that was my last resort...

i'm really open to any suggestions you have

nnichols

Try this in your little form -

<?
if (!$submit)
{
?>
<form action="<? echo $PHP_SELF; ?>" method="POST">
<!-- added hidden form field to carry battle_id to join script -->
<input type="hidden" name="f_battle_id" value="<? echo $row->schedule_id; ?>">
<td><input type="Submit" name="submit" value="Sign Up" class="formbox"></td>
</form>
<?
}

Hope this helps 😉

keyser_soze

what's the f_battle_id for?

when i try the script, it inserts a blank row, with just another submit button... also this:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/teamlord/public_html/v1/members/members_schedule.php on line 41

nnichols

What is the structure of the table you enter the user in when they "sign up" for a battle?

keyser_soze

CREATE TABLE member_schedule (
schedule_id int(50) unsigned NOT NULL auto_increment,
schedule_ladder varchar(10),
schedule_date varchar(30),
schedule_clan varchar(30),
schedule_ip varchar(20),
schedule_password varchar(20),
schedule_roster varchar(255),
PRIMARY KEY (schedule_id)
);

i'm trying to get this into the schedule_roster column

keyser_soze

also, i would prefer it not to be a submit button, but it's not that big of a deal

nnichols

So, do you just want to add the users id to the schedule_roster column so that the row contains only the schedule_id and the schedule_roster columns?

Do you have an IM account (hotmail or aol) so we can chat more easily?

keyser_soze

yes, my aim name is: Teriyaki Delight

nnichols

Try this -

else
{
 $id = "$row->schedule_id";
 mysql_select_db($db) or die ("Unable to select database!");
 $query = "UPDATE member_schedule SET schedule_roster = CONCAT(schedule_roster, '$user') WHERE schedule_id=$id";
 $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
 echo "<td><p>Signed Up</td>";
}

Nice looking site!

Good luck

nnichols

Try this -

<body bgcolor=#515151 style="margin:0;scrollbar-face-color: #515151; scrollbar-shadow-color: #666666; scrollbar-highlight-color: #515151; scrollbar-3dlight-color: #666666; scrollbar-darkshadow-color: #515151; scrollbar-track-color: #515151; scrollbar-arrow-color: #888888;">

<?
$user = "$valid_user<br>";

if ($submit) {
 mysql_select_db($db) or die ("Unable to select database!");
 $query = "UPDATE member_schedule SET schedule_roster = CONCAT(schedule_roster, '$user') WHERE schedule_id=" . $_POST['add_schedule_id'];
 $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
}

?>

<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr><td width=55><p class="table_header">Ladder</td><td width=50><p class="table_header">Date</td><td width=55><p class="table_header">Clan</td><td width=80><p class="table_header">IP</td><td width=50><p class="table_header">Password</td><td width=50><p class="table_header">Current Roster</td><td width=50><p class="table_header">Sign Up</td></tr> 
<?php
$query = "SELECT * FROM member_schedule ORDER BY schedule_id DESC";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// For alternate row colour
$row_colour="#4d4d4d";
$count_rows = 0;

//loop and display the limited records being browsed
while ($row = mysql_fetch_object($result))
{
$count_rows++;
if ($count_rows%2==0)
{ $row_colour="#4d4d4d"; } // Even-numbered rows
else
{ $row_colour="#4a4a4a"; } // Odd-numbered rows
?>


<tr bgcolor="<?php echo $row_colour; ?>"> <!-- Alternate Row Colours -->

<td><p><? echo $row->schedule_ladder; ?></td>
<td><p><? echo $row->schedule_date; ?></td>
<td><p><? echo $row->schedule_clan; ?></td>
<td><p><? echo $row->schedule_ip; ?></td>
<td><p><? echo $row->schedule_password; ?></td>
<td><a href="javascript<img src="images/smilies/redface.gif" border="0" alt="">penwindow('members_battleroster.php?id=<? echo $row->schedule_id; ?>')">View</a></td>

<form action="<? echo $PHP_SELF; ?>" method="POST">
<input type="hidden" name="add_schedule_id" value="<? echo $row->schedule_id; ?>">
<td><input type="Submit" name="submit" value="Sign Up" class="formbox"></td>
</form>
</tr>

<?php }; ?>

</table>
</body>

keyser_soze

thats all good and well, except it never inserts the username 🙂

nnichols

The only thing that would have caused that was the schedule_roster column being empty. This is because CONCAT()
returns NULL if any of the arguements is NULL, so I have added an IF() to stop that problem. This also means that if
$user is not populated for some reason it would delete all the data in the column. So you must make sure that the user
is logged_in before this query is executed.

<?
$user = "$valid_user<br>";

if ($_POST['submit']) {
 mysql_select_db($db) or die ("Unable to select database!");
 $query = "UPDATE member_schedule SET schedule_roster = IF((schedule_roster IS NOT NULL),CONCAT(schedule_roster, '$user'),schedule_roster) WHERE schedule_id=" . $_POST['add_schedule_id'];
 $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
}
?>

I have tested this code and it DOES works.
Hope this helps 😉

nnichols

I have attached the file as a zip. Give it a try. It works fine on my server.

Nick

ratass2002

May I suggest to use 3 tables:

Table1 ->user info
Table2 ->scheduled games
Table3 ->signups info; (GameID,UserID) <-tiny table here
Table4 ->if you need many schedules for the same game.

This way relatively fixed info(games) will not be duplicated.

Foreign key is just another name for the primary since both keys reside in different table they gave a different name.

Now Table3 has a foreign keys UserID and GameID of Table1, and Table2 respectively.

Now GameID is an increment as I assume.
To get the GameID when you create games is as follow. You will also create a new row for your game table(Table2).

INSERT INTO Table2 VALUES(GameID,....)
SELECT MAX(GameID) FROM Table2 + 1

Now if a user wants to join the Game.

Assume you know the UserID through login, and GameID through a variable catching the GameID on the game page(or the join button). Check if user already registered for the same game, then insert the user in Table3 if everything is cool.

SELECT * FROM table3 WHERE UserID<>UserID1 AND GameID<>GameID1;
INSERT INTO Table3 VALUES(GameID1,UserID1)

To find all user from a game:

SELECT * FROM Table2,Table3, WHERE Table2.GaneID=Table3.GameID AND Table3.GameID=GameID1

My strategy might screw up all your currect logic, but it was all my pleasure 😃