Linking form with DB!

hilln2

heya, struggling newbie here

I have created a form where I enter data which will end up updating the database. I.e. for a fanatsy football site, on my form is the section of the 11 players who played for a team. However Im struggling to write the intelligence behind the form.

I need it to loop through the 11 entries and update the score ofeach of those eleven players.
Example: If player 'x' is on starting eleven, find that player in DB and update his score by 'y' points!

Form is set up as follows:

<form method="post" action="process.php">
<b>First XI</b><BR>
<SELECT name="$first11" size=1>
<option selected value = 0>--Select Player 1--</option>
<option value = 1> "BLAH"</option>
<option value = 1> "BLAH"</option>
<option value = 1> "BLAH"</option>
...etc...
</form>

In process.php I wanna do something like:

function getPlayer()
{
SELECT p_name FROM players
WHERE p_id = $first11;
}

for($i = 0; $i < 11; $i++)
{
while(getPlayer() != 0)
{
UPDATE players
SET weekPts = weekPts + 3
WHERE p_id = $first11;
}
}

As you may have noticed Im not the 'may west' at scripting languages and am pretty sure Im on the wrong track...but was wondering if anyone could help a struggling student out!!

Thanx a million 😕

ir4z0r

<SELECT name="$first11" size=1>
should be
<SELECT name="first11" size=1>
and used in the script as $_POST['first11']

your queries themselves are okay, but you have to establish
a connection, select a db and commit the query
afterwards you can use the result data

but I suggest, you search for php-mysql-tuts first and find
out how to do this ...

hilln2

thanx for the reply!

I had actually got just name = "first11", that was just a typo!
thanx anyway!

however when you say I use it in the script as $_POST['first11']...
what do you mean? and how!?

I have established my connection and Im using MySQL.

will I have to define my connection before every loop, or can i just put it at the top like:

include("connect2db.php");

connect2db.php......
<?php

$mysql_database="blah";
$mysql_username="blah";
$mysql_password="blah";

$link = mysql_connect("blah",$mysql_username,$mysql_password) or die ("Unable to connect to SQL server");
mysql_select_db($mysql_database,$link) or die ("Unable to select database");

ir4z0r

once include("connect2db.php"); is enough

please explain the principle of your "game" a bit more detailled,
you said, there were 11 players, but your dropdown only
passes 1 ...

u have to use $_POST['first11'] to get the data of the form
input field

hilln2

I didnt include all the drop-downs, I do have eleven of them and then I have 3 more for subs(defined as different variable!)

Also have drop downs for goals scored/conceeded yellow cards and red cards!

At the moment now im getting a parse error:
its on line 103:

99: if($conceeded > 0)
100: {
101: $conc = conceeded * 5
102:
103: $g_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = $_POST'[g_con]'");
104: $row10 = mysql_fetch_array($g_conc);
105: }

can you see the problem!?

$conceeded is an input name of type "Text"

ir4z0r

$POST'[g_con]'
has to be $POST['g_con']

and mysql_fetch_array($g_conc);
should use the result from the query = $g_conceed

hint: always post the complete error-msg, makes life easier 😉

and give
$conc = intval($conceeded) * 5;
a try

hilln2

I tried putting the quotes the other side, it didnt work!
heres the snippet of code! line 103 is $g_conceed = mysql_query..

// Goals Conceeded
// goalkeeper
if($conceeded > 0)
{
$conc = conceeded * 5

$g_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = $_POST['g_con']");
$row10 = mysql_fetch_array($g_conceed);

}
//defenders
if($conceeded > 0)
{
for($co=0; $co<5; $co++)
{
$conc = conceeded * 3
$d_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = $_POST['d_con']");
$row11 = mysql_fetch_array($d_conceed);
}
}

Parse error: parse error in /users/cais3/nhill-cais3/public_html/Footie/3rd yr project/3rdYrProj/process.php on line

I even tried leaving out the $conc but it still through the same err!

ir4z0r

if you get an error in line x, it's possible that the error occured
in line x-1, cause you've forgotten a ; behind
$conc = $conceeded * 5
and missed the $ in front of the var

also try
$conc = intval($conceeded) * 5;
like I said before

and
don't do a mysql_fetch_result() when only UPDATEing or
INSERTing, that doesn't do anything

hilln2

yeah Ive tried all that...still getting the same parse error on line 103!

if($conceeded > 0)
{
$conc = intval($conceeded) * 5;

$g_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = $_POST['g_con']");

}

I still cant see the problem!
All my other loops are very similar and have no errors!
Ive tried commenting out the $conc = intval($conceeded)*5;
and still I get the same error on the same line!

If anyone has any suggestions, Please help! Deadline for this project is this week!! 🙁

hilln2

I have commented out the errorenous code to test if the rest of the code works!
However when I fill out the form and submit it, it redirects to process.php alright but it doesnt update the database.

Here's the code:

----------------updatePlayers.html-------------------
<form method="post" action="process.php">

<b>First XI</b><BR>

<SELECT name="first11" size=1>
<option selected value = 0>--Select Player 1--</option>
<option value = 1> BLAH</option>
<option value = 1> BLAH</option>
<option value = 1> BLAH</option>
</select><BR>
<SELECT name="first11" size=1>
<option selected value = 0>--Select Player 2--</option>
<option value = 1> BLAH</option>
<option value = 1> BLAH</option>
<option value = 1> BLAH</option>
// ....etc!!!
// also...
<b>Substitutes</b><BR>
<SELECT name="player12" size=1>
<option selected value = 0>--Select Player 12--</option>
<option value = 1> BLAH</option>
<option value = 1> BLAH</option>
// ...etc!
// have other drop downs for goals scored, discipline...etc.

---------------------process.php----------------------------

<?php
include("connect2db.php");

$getplayer = mysql_query("SELECT p_id FROM players WHERE p_id = $_POST['first11']");
$rows = mysql_fetch_array($getplayer);

// First Team
for($i = 0; $i < 11; $i++)
{
while($rows != 0)
{
$firstTeam = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = $_POST['first11']");

}

// Subs
if($sub != 0)
{
for($j=0; $j<3; $j++)
{
$sub = mysql_query("UPDATE players SET weekPts = weekPts + 1 WHERE p_id = $_POST['sub']");
}
// this goes on for all scoring systems!!

Why wont it update the database!? Am I missing out on something simple! As ive said before, scripting languages aren't my strong point!

All help much appreciated!

ir4z0r

at first: use a syntax-highlighting editor to find missing " or } or ;
in all the lines before the error-line
(if you can find one, use the [.php] tags here in the board, simply
put a [.php] in front and a [./php] at the end of your code or use
the php-button (the []-tags without . )

SELECT p_id FROM players WHERE p_id = $_POST['first11']"
this makes absolutely no sense, u r asking for the p_id you already
have (in first11)

while($rows != 0)
makes no sense, too, $row is an array containing the requested
data (one row of your table)

your second dropdown-field has to have another name
<SELECT name="first11_02" size=1> or similar
maybe it would work to use "first11[]" for all, returning an array,
but I'm not sure :rolleyes:

another attempt:

$first11 = $_POST['first11'];
for ($i=0; $i < sizeof($file11); $i++)
{
  mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id =" . $first11[$i]); 
}

if that doesn't work ... , hm, anybody else wants to try :rolleyes:

hilln2

Ive got rid of the $getplayers and $rows

my query now looks like:

$firstTeam = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = .'$_POST[first11]'.");

then i also changed all the values of the dropdown options to that of the p_id.
Also changed the option names from first11 to first11_02...and so on, like u suggested!

Im not getting any errors, just not updating the DB like it should

ir4z0r

would you mind, posting your new code (with [.php]-tags)

edit: echo your complete query like you would send it to mysql

$query ="SELECT ....";
echo $query;
mysql_query($query);

hilln2

If you need my HTML file aswell which contains the form, give me an email address which i can send it to you.
Appreciate all the help

[.PHP]
include("connect2db.php");

//set player week pts to zero!
//UPDATE players
// SET weekPts = 0;

//$getplayer = mysql_query("SELECT p_id FROM players WHERE p_name = '$_POST[first11]'");
//$rows = mysql_fetch_array($getplayer);

// First Team

	$firstTeam = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = .'$_POST[first11]'.");
	print("query processed");
	//$row = mysql_fetch_array($firstTeam);		 
	$firstTeam1 = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = .'$_POST[first11_02]'.");
	print("query processed");

// Subs
if($sub != 0)
{
for($j=0; $j<3; $j++)
{
$sub = mysql_query("UPDATE players SET weekPts = weekPts + 1 WHERE p_id = '$_POST[sub]'");
//$row2 = mysql_fetch_array($sub);
}

}
// Strikers Goal
if(striker != 0)
{
for($s=0; $s<5; $s++)
{
$s_goal = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = '$_POST[striker]'");
//$row3 = mysql_fetch_array($s_goal);
}
}

// Midfielders Goal
if(mid != 0)
{
for($m=0; $m<5; $m++)
{
$m_goal = mysql_query("UPDATE players SET weekPts = weekPts + 7 WHERE p_id = '$_POST[mid]'");
//$row4 = mysql_fetch_array($m_goal);
}
}

// Defenders Goal
if(def != 0)
{
for($m=0; $m<3; $m++)
{
$d_goal = mysql_query("UPDATE players SET weekPts = weekPts + 10 WHERE p_id = '$_POST[def]'");
//$row5 = mysql_fetch_array($d_goal);
}
}

// Yellow Card
if(yel != 0)
{
for($y=0; $y<6; $y++)
{
$y_card = mysql_query("UPDATE players SET weekPts = weekPts - 3 WHERE p_id = '$_POST[yel]'");
//$row6 = mysql_fetch_array($y_card);
}
}

// Red Card
if(red != 0)
{
for($r=0; $r<6; $r++)
{
$r_card = mysql_query("UPDATE players SET weekPts = weekPts - 5 WHERE p_id = '$_POST[red]'");
//$row7 = mysql_fetch_array($r_card);
}
}

//Clean Sheet
if($conceeded = 0)
{
$c_sheet = mysql_query("UPDATE players SET weekPts = weekPts + 10 WHERE p_id = '$POST[g_clean]'");
//$row8 = mysql_fetch_array($c_sheet);
}
if($conceeded = 0)
{
for($c=0; $c<5; $c++)
{
$cl_sheet = mysql_query("UPDATE players SET weekPts = weekPts + 10 WHERE p_id = '$POST[d_clean]'");
// $row9 = mysql_fetch_array($ch_sheet);
}
}
// Goals Conceeded
// goalkeeper
if($conceeded > 0)
{
$conc = intval($conceeded) * 5;

$g_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = '$_POST[g_con]'");
//$row10 = mysql_fetch_array($g_conceed);

}
//defenders
if($conceeded > 0)
{
for($co=0; $co<5; $co++)
{
$conc = intval($conceeded) * 3;
$d_conceed = mysql_query("UPDATE players SET weekPts = weekPts - $conc WHERE p_id = '$_POST[d_con]'");
//$row11 = mysql_fetch_array($d_conceed);
}
}

print("finsihed");

[./PHP]

ir4z0r

$firstTeam = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id ='" . $_POST[first11] . "'");

that's one thing I've seen, I'll have some time tomorrow, now I've got to go
to bed (Central Europe if you're wondering 😃 )

btw, can you edit your post and leave the . out of the php-tags?
i have to write them like this cause otherwise the board would
translate it

hilln2

<?PHP

include("connect2db.php");

//$getplayer = mysql_query("SELECT p_id FROM players WHERE p_name = '$_POST[first11]'");
//$rows = mysql_fetch_array($getplayer);

// First Team

$firstTeam = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = '".$POST[first11]."'");
print("query processed");
//$row = mysql_fetch_array($firstTeam);
$firstTeam1 = mysql_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id = '".$POST[first11_02]."'");
print("query processed");
//..etc

// Subs

$sub = mysql_query("UPDATE players SET weekPts = weekPts + 1 WHERE p_id = '".$_POST[player12]."'");
//$row2 = mysql_fetch_array($sub);

if you need more code ill send it to you

ir4z0r

hi,

first of all, name all your dropdown-selects (11 pieces) like
Player 1:<select name="team1[]" size=1>
...
</select>
Player 2:<select name="team1[]" size=1>
...
</select>

all your selections will then be saved in an array called team1

in your php-script:

$team1 = $_POST['team1'];
for ($i=0; $i < sizeof($team1); $i++)
{
  $retVal = myqsl_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id='" . $team1[$i] . "'");
}

for the subs you do the same: name the
dropdown subs[], copy the code above and replace
team1 with subs

hilln2

hey

Ive tried that, but still getting the same result!

Ive now tried putting the connection to the DB within the php file instead of the include() function I previously had.....still no result!

Its reading all the code no problem, im not getting any errors in the code either. But for some reason it just wont update the DB!

ir4z0r

that's a hard one 🙁

now for debugging:

for ($i=0; $i < sizeof($team1); $i++)
{
  $retVal = myqsl_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id='" . $team1[$i] . "'");
  $retVal = myqsl_query("SELECT * FROM players WHERE p_id='" . $team1[$i] . "'");
   echo (mysql_num_rows($result);
}

this should give 11x "1" as output
if it doesn't, my last idea would be:

for ($i=0; $i < sizeof($team1); $i++)
{
  $retVal = myqsl_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id=" . $team1[$i]);
}

notice that I removed the ' , maybe numerical
values don't need them

btw. do you have method=post in your form header?

I'm running out of ideas, check these changes and post the results

it's not a bad idea to post a little screenshot of your phpMyAdmin-table, if you have one

hilln2

Thanx for all your help man, but ive cracked it!

I tried printing the variable $team1[$i] and also tried printing '$_POST[first11]' to see if it was reading it, but it wasn't.
Then it finally clicked:
I ended up using $firstTeam = myqsl_query("UPDATE players SET weekPts = weekPts + 3 WHERE p_id='$player1';
then done the same for $player2,$player3...etc.

I had tried this originally but somehow it didnt work.
But hey, its workin now....thanx for all your efforts, very much appreciated.

I do have a little problem thou.......(here we go again )😃

one of the rules for new members is that when picking their squad, they cant pick more than two players from the one team.
i.e. if they pick D. Seaman from Arsenal, they can only pick one more player from Arsenal.
Also, I need to stop them from picking the same player twice.

Any suggestions how to achieve this in a very short space of time!!? 🙁
Arrays arent my strongest point so thats why i need help
8-)

thanx again