Continually updating a database for a prediction competition

jerryhughes101 · April 16, 2010

Hi,

I'm currently running a sports prediction competition for me and my friends and I'm doing it all via php/mysql on my own website.

I've created a form and a database for them to make their predictions from.

The mysql database currently as 6 fields in it, (user, password, fifaworldcup, usopengolf, wimbledon, tourdefrance - but these will increase as I add more sporting competitions to it). Each person who has entered has their own row in the database with their name and password already stored in it.

I then have a form on my website which links to the database, it checks that their password entered is correct before entering into the database, although it accepts the username & password when it's right and rejects it when it's wrong, it doesn't enter any of their picks still and I think it's because I'm using 'INSERT INTO' rather than maybe updating their field every time. The code for my page is currently,

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/layout.css"/>

<title>jerryhughes.co.uk</title>
</head>

<body>

<div id="container">

<div id="topheader">


</div>

<div id="mainheader">

<div id="insidemainheader">
<img src="images/jerryhughesheader.jpg" class="jerryhughestext" />
</div>

</div>

<div id="bottomheader"> </div>

<div id="maincontent">

<div id="topbarmaincontent">

you are: home >> upc >> make my picks

</div>

<div id="navbarmaincontent">

<ul>
<li><a href="index.html">home</a> </li>
<li><a href="portfolio.html">portfolio</a> </li>
<li><a href="cv.html">cv</a> </li>
<li><a href="about.html">about</a> </li>
<li><a href="contact.php">contact me</a> </li>

</ul>

</div>

<div id="innerpagesrightbox">

<h1>upc</h1>

<h2>make my picks</h2>

<?php
//THE MySQL table 'users'
//user_id int(10) PrimaryKey auto_increment
//user varchar(50)
//password varchar(50)

//MYSQL connection information
$db_host = "localhost";
$db_user = "jerryhug";	
$db_password = "70exile101";
$db_name = "jerryhug_upc";
$path = $_SERVER['DOCUMENT_ROOT'];

$connection = mysql_connect($db_host, $db_user, $db_password); 
mysql_select_db($db_name, $connection);

//check if somthing has been posted
if (isset($_POST['password'])){

//get the username and password and protect against mysql Injection
$user = mysql_real_escape_string($_POST['person']);
$password = mysql_real_escape_string($_POST['password']); 

//run the query to see if a user and password match
$getname = "SELECT user FROM upcpicks WHERE user ='$user' AND password = '$password'";
$nameresult = mysql_query($getname, $connection) or die(mysql_error());
$rows = mysql_num_rows($nameresult);

$info = mysql_fetch_array( $nameresult );
$user_id =  $info['user_id'];

//if there is a match then run the code to update the DB, otherwise the password does not match
if ($rows==1){


	$sql="INSERT INTO upcpicks (user, fifaworldcup, usopengolf, wimbledon, tourdefrance)
VALUES
('$_POST[username]','$_POST[fifaworldcup]','$_POST[golfusopen]', '$_POST[wimbledontennis]', '$_POST[tourdefrance]')";

}else {
	echo 'Password Not Matched';
}

} else {
?>
<form method="post">

<p>Name <br /><br />
<select name="person">
  <option value="jerryhughes">Jerry Hughes</option>
  <option value="roblowe">Rob Lowe</option>
  <option value="mitchturner">Mitch Turner</option>
  <option value="simoneccleston">Simon Eccleston</option>
  <option value="neilwilmer">Neil Wilmer</option>
  <option value="andrewhawkings">Andrew Hawkings</option>
  <option value="tomjohnson">Tom Johnson</option>
  <option value="robsmith">Rob Smith</option>
  <option value="samfaulkner">Sam Faulkner</option>
  <option value="russhewitt">Russ Hewitt</option>
  <option value="andycrowe">Andy Crowe</option>
  <option value="benshaw">Ben Shaw</option>
  <option value="richhancock">Rich Hancock</option>
  <option value="chrisjefferson">Chris Jefferson</option>
  <option value="paulellis">Paul Ellis</option>
  <option value="jonwibberley">Jon Wibberley</option>
  <option value="markfoster">Mark Foster</option>
  <option value="benpeddie">Ben Peddie</option>
  <option value="adamhiggs">Adam Higgs</option>
  <option value="adammcclure">Adam McClure</option>
</select>
</p>

<p>Please enter your Password <br /><br />
<input name="password" type="text" /></p>

<p>FIFA World Cup (Football) <br /><br />
<select name="fifaworldcup" />

<option name="algeria">Algeria</option>
<option value="argentina">Argentina</option>
<option value="australia">Australia</option>
<option value="brazil">Brazil</option>
<option value="chile">Chile</option>
<option value="denmark">Denmark</option>
<option value="england">England</option>
<option value="france">France</option>
<option value="germany">Germany</option>
<option value="ghana">Ghana</option>
<option value="greece">Greece</option>
<option value="honduras">Honduras</option>
<option value="italy">Italy</option>
<option value="ivorycoast">Ivory Coast</option>
<option value="japan">Japan</option>
<option value="koreadpr">Korea DPR</option>
<option value="korearepublic">Korea Republic</option>
<option value="mexico">Mexico</option>
<option value="netherlands">Netherlands</option>
<option value="newzealand">New Zealand</option>
<option value="nigeria">Nigeria</option>
<option value="paraguay">Paraguay</option>
<option value="portugal">Portugal</option>
<option value="serbia">Serbia</option>
<option value="slovakia">Slovakia</option>
<option value="slovenia">Slovenia</option>
<option value="southafrica">South Africa</option>
<option value="spain">Spain</option>
<option value="switzerland">Switzerland</option>
<option value="uruguay">Uruguay</option>
<option value="usa">USA</option>

</select></p>

<p>US Open (Golf) <br /><br />
<input name="golfusopen" type="text" /></p>

<p>Wimbledon (Tennis) <br /><br />
<select name="wimbledontennis">
<option value="federer">Roger Federer</option>
<option value="nadal">Rafael Nadal</option>
<option value="murray">Andy Murray</option>
<option value="roddick">Andy Roddick</option>

</select></p>

<p>Tour De France (Cycling) <br /><br />
<input name="tourdefrance" type="text" /></p>



<input name="submit" type="submit" />

</form>
<?php } ?>


</div>


<div id="twitterboxleft">

<h1>follow me on twitter</h1>

<img src="images/twitterlogo.jpg" alt="follow me on twitter" class="twitterlogo" />

<div id="twitter_div"><ul id="twitter_update_list"><li> </li><li> </li></ul></div>

</div>



<div id="upcboxleft">

</div>

</div>



</div>


<script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>
<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/jerryhughes101.json?callback=twitterCallback2&count=3"></script>
</body>
</html>

Does anyone know what I'm doing wrong here, is it what I think it is and I'm trying to insert into a field rather than update it or whatever?

Thanks for any help in advance,

Jerry

Ken2k7 · April 16, 2010

Well, there isn't an UPDATE query.

jerryhughes101 · April 16, 2010

Should that be in there instead of the INSERT INTO then?

ChemicalBliss · April 16, 2010

Lol nice.

Ill explain for you:

$sql="INSERT INTO upcpicks (user, fifaworldcup, usopengolf, wimbledon, tourdefrance)
VALUES
('$_POST[username]','$_POST[fifaworldcup]','$_POST[golfusopen]', '$_POST[wimbledontennis]', '$_POST[tourdefrance]')";

Should be something like:

$sql="UPDATE upcpicks SET (fifaworldcup='".$_POST[fifaworldcup]."', usopengolf='".$_POST[golfusopen]."', wimbledon='".$_POST[wimbledontennis]."', tourdefrance='".$_POST[tourdefrance]."' WHERE user='".$_POST[username]."')";

You need to update if ypou want to change, you insert if you want something new.

-cb-

jerryhughes101 · April 16, 2010

Okay, I've changed accordingly with your code and I still don't get any input within the database.. Code now stands at...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/layout.css"/>

<title>jerryhughes.co.uk</title>
</head>

<body>

<div id="container">

<div id="topheader">


</div>

<div id="mainheader">

<div id="insidemainheader">
<img src="images/jerryhughesheader.jpg" class="jerryhughestext" />
</div>

</div>

<div id="bottomheader"> </div>

<div id="maincontent">

<div id="topbarmaincontent">

you are: home >> upc >> make my picks

</div>

<div id="navbarmaincontent">

<ul>
<li><a href="index.html">home</a> </li>
<li><a href="portfolio.html">portfolio</a> </li>
<li><a href="cv.html">cv</a> </li>
<li><a href="about.html">about</a> </li>
<li><a href="contact.php">contact me</a> </li>

</ul>

</div>

<div id="innerpagesrightbox">

<h1>upc</h1>

<h2>make my picks</h2>

<?php
//THE MySQL table 'users'
//user_id int(10) PrimaryKey auto_increment
//user varchar(50)
//password varchar(50)

//MYSQL connection information
$db_host = "localhost";
$db_user = "jerryhug";	
$db_password = "70exile101";
$db_name = "jerryhug_upc";
$path = $_SERVER['DOCUMENT_ROOT'];

$connection = mysql_connect($db_host, $db_user, $db_password); 
mysql_select_db($db_name, $connection);

//check if somthing has been posted
if (isset($_POST['password'])){

//get the username and password and protect against mysql Injection
$user = mysql_real_escape_string($_POST['person']);
$password = mysql_real_escape_string($_POST['password']); 

//run the query to see if a user and password match
$getname = "SELECT user FROM upcpicks WHERE user ='$user' AND password = '$password'";
$nameresult = mysql_query($getname, $connection) or die(mysql_error());
$rows = mysql_num_rows($nameresult);

$info = mysql_fetch_array( $nameresult );
$user_id =  $info['user_id'];

//if there is a match then run the code to update the DB, otherwise the password does not match
if ($rows==1){


	$sql="UPDATE upcpicks SET (fifaworldcup='".$_POST[fifaworldcup]."', usopengolf='".$_POST[golfusopen]."', wimbledon='".$_POST[wimbledontennis]."', tourdefrance='".$_POST[tourdefrance]."' WHERE user='".$_POST[user]."')";


}else {
	echo 'Password Not Matched';
}

} else {
?>
<form method="post">

<p>Name <br /><br />
<select name="person">
  <option value="jerryhughes">Jerry Hughes
  <option value="roblowe">Rob Lowe</option>
  <option value="mitchturner">Mitch Turner</option>
  <option value="simoneccleston">Simon Eccleston</option>
  <option value="neilwilmer">Neil Wilmer</option>
  <option value="andrewhawkings">Andrew Hawkings</option>
  <option value="tomjohnson">Tom Johnson</option>
  <option value="robsmith">Rob Smith</option>
  <option value="samfaulkner">Sam Faulkner</option>
  <option value="russhewitt">Russ Hewitt</option>
  <option value="andycrowe">Andy Crowe</option>
  <option value="benshaw">Ben Shaw</option>
  <option value="richhancock">Rich Hancock</option>
  <option value="chrisjefferson">Chris Jefferson</option>
  <option value="paulellis">Paul Ellis</option>
  <option value="jonwibberley">Jon Wibberley</option>
  <option value="markfoster">Mark Foster</option>
  <option value="benpeddie">Ben Peddie</option>
  <option value="adamhiggs">Adam Higgs</option>
  <option value="adammcclure">Adam McClure</option>
</select>
</p>

<p>Please enter your Password <br /><br />
<input name="password" type="text" /></p>

<p>FIFA World Cup (Football) <br /><br />
<select name="fifaworldcup" />

<option name="algeria">Algeria</option>
<option value="argentina">Argentina</option>
<option value="australia">Australia</option>
<option value="brazil">Brazil</option>
<option value="chile">Chile</option>
<option value="denmark">Denmark</option>
<option value="england">England</option>
<option value="france">France</option>
<option value="germany">Germany</option>
<option value="ghana">Ghana</option>
<option value="greece">Greece</option>
<option value="honduras">Honduras</option>
<option value="italy">Italy</option>
<option value="ivorycoast">Ivory Coast</option>
<option value="japan">Japan</option>
<option value="koreadpr">Korea DPR</option>
<option value="korearepublic">Korea Republic</option>
<option value="mexico">Mexico</option>
<option value="netherlands">Netherlands</option>
<option value="newzealand">New Zealand</option>
<option value="nigeria">Nigeria</option>
<option value="paraguay">Paraguay</option>
<option value="portugal">Portugal</option>
<option value="serbia">Serbia</option>
<option value="slovakia">Slovakia</option>
<option value="slovenia">Slovenia</option>
<option value="southafrica">South Africa</option>
<option value="spain">Spain</option>
<option value="switzerland">Switzerland</option>
<option value="uruguay">Uruguay</option>
<option value="usa">USA</option>

</select></p>

<p>US Open (Golf) <br /><br />
<input name="golfusopen" type="text" /></p>

<p>Wimbledon (Tennis) <br /><br />
<select name="wimbledontennis">
<option value="federer">Roger Federer</option>
<option value="nadal">Rafael Nadal</option>
<option value="murray">Andy Murray</option>
<option value="roddick">Andy Roddick</option>

</select></p>

<p>Tour De France (Cycling) <br /><br />
<input name="tourdefrance" type="text" /></p>



<input name="submit" type="submit" />

</form>
<?php } ?>


</div>


<div id="twitterboxleft">

<h1>follow me on twitter</h1>

<img src="images/twitterlogo.jpg" alt="follow me on twitter" class="twitterlogo" />

<div id="twitter_div"><ul id="twitter_update_list"><li> </li><li> </li></ul></div>

</div>



<div id="upcboxleft">

</div>

</div>



</div>


<script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>
<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/jerryhughes101.json?callback=twitterCallback2&count=3"></script>
</body>
</html>

Since it's not doing anything within the database, I'm thinking it may be a problem with the action of the form... which is at the moment

<form method="post">

I used to have a .php file doing the action but I've now c hanged that and have the php within the same document. Do I need anything else within this form tag to make it work?

Thanks, J

hcdarkmage · April 16, 2010

You may want to put your PHP code above your HTML code. When you submit the form, you are basically refreshing the page and it is read from top to bottom.

jerryhughes101 · April 16, 2010

You may want to put your PHP code above your HTML code. When you submit the form, you are basically refreshing the page and it is read from top to bottom.

Didn't do anything, the page went a bit AWOL too not loading up the next page properly when I clicked submit, whereas before I stayed on my website on the same page!

ChemicalBliss · April 16, 2010

Take note of the comments in this code.

<?php
// Code At The Top plox

//THE MySQL table 'users'
   //user_id int(10) PrimaryKey auto_increment
   //user varchar(50)
   //password varchar(50)
   
//MYSQL connection information
   $db_host = "localhost";
   $db_user = "jerryhug";   
   $db_password = "70exile101";
   $db_name = "jerryhug_upc";
   $path = $_SERVER['DOCUMENT_ROOT'];
      
   $connection = mysql_connect($db_host, $db_user, $db_password); 
   mysql_select_db($db_name, $connection);

//check if somthing has been posted
if (isset($_POST['password']) && isset($_POST['person'])){

   //get the username and password and protect against mysql Injection
   $user = mysql_real_escape_string($_POST['person']);
   $password = mysql_real_escape_string($_POST['password']); 
   
   //run the query to see if a user and password match
   $getname = "SELECT user FROM upcpicks WHERE user ='$user' AND password = '$password'";
   $nameresult = mysql_query($getname) or die(mysql_error());
   $rows = ;
   $user_id =  mysql_result($nameresult,0,'user'); // Easier
   
   //if there is a match then run the code to update the DB, otherwise the password does not match
   if (mysql_num_rows($nameresult) >= 1){
		// Sanitize these post values!
		$sql="UPDATE upcpicks SET (fifaworldcup='".$_POST['fifaworldcup']."', usopengolf='".$_POST['golfusopen']."', wimbledon='".$_POST['wimbledontennis']."', tourdefrance='".$_POST['tourdefrance']."' WHERE user='".$_POST['user']."')";
		$updateresult = mysql_query($sql) or die(mysql_error()); // actually execute the update query.
   }else {
	  echo 'Password Not Matched';
   }
}

// Keep form, just seperate it

echo('<BR><BR>');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/layout.css"/>
<title>jerryhughes.co.uk</title>
</head>
<body>
<div id="container">
	<div id="topheader"></div>
	<div id="mainheader">
		<div id="insidemainheader">
			<img src="images/jerryhughesheader.jpg" class="jerryhughestext" />
		</div>
	</div>
	<div id="bottomheader"> </div>
	<div id="maincontent">
		<div id="topbarmaincontent">
you are: home >> upc >> make my picks
		</div>
		<div id="navbarmaincontent">
			<ul>
				<li><a href="index.html">home</a> </li>
				<li><a href="portfolio.html">portfolio</a> </li>
				<li><a href="cv.html">cv</a> </li>
				<li><a href="about.html">about</a> </li>
				<li><a href="contact.php">contact me</a> </li>

			</ul>
		</div>
		<div id="innerpagesrightbox">
			<h1>upc</h1>
			<h2>make my picks</h2>
<form method="post">

<p>Name <br /><br />
<select name="person">
  <option value="jerryhughes">Jerry Hughes
  <option value="roblowe">Rob Lowe</option>
  <option value="mitchturner">Mitch Turner</option>
  <option value="simoneccleston">Simon Eccleston</option>
  <option value="neilwilmer">Neil Wilmer</option>
  <option value="andrewhawkings">Andrew Hawkings</option>
  <option value="tomjohnson">Tom Johnson</option>
  <option value="robsmith">Rob Smith</option>
  <option value="samfaulkner">Sam Faulkner</option>
  <option value="russhewitt">Russ Hewitt</option>
  <option value="andycrowe">Andy Crowe</option>
  <option value="benshaw">Ben Shaw</option>
  <option value="richhancock">Rich Hancock</option>
  <option value="chrisjefferson">Chris Jefferson</option>
  <option value="paulellis">Paul Ellis</option>
  <option value="jonwibberley">Jon Wibberley</option>
  <option value="markfoster">Mark Foster</option>
  <option value="benpeddie">Ben Peddie</option>
  <option value="adamhiggs">Adam Higgs</option>
  <option value="adammcclure">Adam McClure</option>
</select>
</p>

<p>Please enter your Password <br /><br />
<input name="password" type="text" /></p>

<p>FIFA World Cup (Football) <br /><br />
<select name="fifaworldcup" />

<option name="algeria">Algeria</option>
<option value="argentina">Argentina</option>
<option value="australia">Australia</option>
<option value="brazil">Brazil</option>
<option value="chile">Chile</option>
<option value="denmark">Denmark</option>
<option value="england">England</option>
<option value="france">France</option>
<option value="germany">Germany</option>
<option value="ghana">Ghana</option>
<option value="greece">Greece</option>
<option value="honduras">Honduras</option>
<option value="italy">Italy</option>
<option value="ivorycoast">Ivory Coast</option>
<option value="japan">Japan</option>
<option value="koreadpr">Korea DPR</option>
<option value="korearepublic">Korea Republic</option>
<option value="mexico">Mexico</option>
<option value="netherlands">Netherlands</option>
<option value="newzealand">New Zealand</option>
<option value="nigeria">Nigeria</option>
<option value="paraguay">Paraguay</option>
<option value="portugal">Portugal</option>
<option value="serbia">Serbia</option>
<option value="slovakia">Slovakia</option>
<option value="slovenia">Slovenia</option>
<option value="southafrica">South Africa</option>
<option value="spain">Spain</option>
<option value="switzerland">Switzerland</option>
<option value="uruguay">Uruguay</option>
<option value="usa">USA</option>

</select></p>

<p>US Open (Golf) <br /><br />
<input name="golfusopen" type="text" /></p>

<p>Wimbledon (Tennis) <br /><br />
<select name="wimbledontennis">
<option value="federer">Roger Federer</option>
<option value="nadal">Rafael Nadal</option>
<option value="murray">Andy Murray</option>
<option value="roddick">Andy Roddick</option>

</select></p>

<p>Tour De France (Cycling) <br /><br />
<input name="tourdefrance" type="text" /></p>



<input name="submit" type="submit" />

</form>


		</div>
		<div id="twitterboxleft">
			<h1>follow me on twitter</h1>
			<img src="images/twitterlogo.jpg" alt="follow me on twitter" class="twitterlogo" />
			<div id="twitter_div"><ul id="twitter_update_list"><li> </li><li> </li></ul></div>
		</div>
		<div id="upcboxleft">
		</div>
	</div>
</div>
<script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>
<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/jerryhughes101.json?callback=twitterCallback2&count=3"></script>
</body>
</html>

you should try to keep your code in the order you want it to execute. Its better to check if and manipulate form values before a new form is processed etc.

Formatting is key as well, make sure you keep this code clean, it will help a lot in the future.

-cb-

jerryhughes101 · April 17, 2010

Just looking over what you've done, first problem I did with that it gave me standard syntax error saying

'Parse error: syntax error, unexpected ';' in /home/jerryhug/public_html/upcpicks.php on line 29'

Which corresponds to

$rows = ;

I'm not sure if this is even needed, so going through some trouble shooting and I deleted that out and then I got this next error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(fifaworldcup='australia', usopengolf='', wimbledon='federer', tourdefrance='' W' at line 1

Looking over the code that it's in there now around the UPDATE part in the code, there is

		$sql="UPDATE upcpicks SET (fifaworldcup='".$_POST['fifaworldcup']."', usopengolf='".$_POST['golfusopen']."', wimbledon='".$_POST['wimbledontennis']."', tourdefrance='".$_POST['tourdefrance']."' WHERE user='".$_POST['user']."')";
		$updateresult = mysql_query($sql) or die(mysql_error()); // actually execute the update query.

The final bit where it's executing, we've got the variable updateresult but there's nothing defined for it anywhere else, I thought about changing $sql="UPDATE... etc to '$updateresult=' but I think that's wrong and it gave me another error anyway!

Any ideas?

ChemicalBliss · April 17, 2010

Sorry bout that, but hese are simple mistakes, you should get used to them now.

<?php
// Code At The Top plox

//THE MySQL table 'users'
   //user_id int(10) PrimaryKey auto_increment
   //user varchar(50)
   //password varchar(50)
   
//MYSQL connection information
   $db_host = "localhost";
   $db_user = "jerryhug";   
   $db_password = "70exile101";
   $db_name = "jerryhug_upc";
   $path = $_SERVER['DOCUMENT_ROOT'];
      
   $connection = mysql_connect($db_host, $db_user, $db_password); 
   mysql_select_db($db_name, $connection);

//check if somthing has been posted
if (isset($_POST['password']) && isset($_POST['person'])){

   //get the username and password and protect against mysql Injection
   $user = mysql_real_escape_string($_POST['person']);
   $password = mysql_real_escape_string($_POST['password']); 
   
   //run the query to see if a user and password match
   $getname = "SELECT user FROM upcpicks WHERE user ='$user' AND password = '$password'";
   $nameresult = mysql_query($getname) or die(mysql_error());
   $user_id =  mysql_result($nameresult,0,'user'); // Easier
   
   //if there is a match then run the code to update the DB, otherwise the password does not match
   if (mysql_num_rows($nameresult) >= 1){
		// Sanitize these post values!
		$sql="UPDATE upcpicks SET fifaworldcup='".$_POST['fifaworldcup']."', usopengolf='".$_POST['golfusopen']."', wimbledon='".$_POST['wimbledontennis']."', tourdefrance='".$_POST['tourdefrance']."' WHERE user='".$_POST['user']."'";
		$updateresult = mysql_query($sql) or die(mysql_error()); // actually execute the update query.
   }else {
	  echo 'Password Not Matched';
   }
}

// Keep form, just seperate it

echo('<BR><BR>');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/layout.css"/>
<title>jerryhughes.co.uk</title>
</head>
<body>
<div id="container">
	<div id="topheader"></div>
	<div id="mainheader">
		<div id="insidemainheader">
			<img src="images/jerryhughesheader.jpg" class="jerryhughestext" />
		</div>
	</div>
	<div id="bottomheader"> </div>
	<div id="maincontent">
		<div id="topbarmaincontent">
you are: home >> upc >> make my picks
		</div>
		<div id="navbarmaincontent">
			<ul>
				<li><a href="index.html">home</a> </li>
				<li><a href="portfolio.html">portfolio</a> </li>
				<li><a href="cv.html">cv</a> </li>
				<li><a href="about.html">about</a> </li>
				<li><a href="contact.php">contact me</a> </li>

			</ul>
		</div>
		<div id="innerpagesrightbox">
			<h1>upc</h1>
			<h2>make my picks</h2>
<form method="post">

<p>Name <br /><br />
<select name="person">
  <option value="jerryhughes">Jerry Hughes
  <option value="roblowe">Rob Lowe</option>
  <option value="mitchturner">Mitch Turner</option>
  <option value="simoneccleston">Simon Eccleston</option>
  <option value="neilwilmer">Neil Wilmer</option>
  <option value="andrewhawkings">Andrew Hawkings</option>
  <option value="tomjohnson">Tom Johnson</option>
  <option value="robsmith">Rob Smith</option>
  <option value="samfaulkner">Sam Faulkner</option>
  <option value="russhewitt">Russ Hewitt</option>
  <option value="andycrowe">Andy Crowe</option>
  <option value="benshaw">Ben Shaw</option>
  <option value="richhancock">Rich Hancock</option>
  <option value="chrisjefferson">Chris Jefferson</option>
  <option value="paulellis">Paul Ellis</option>
  <option value="jonwibberley">Jon Wibberley</option>
  <option value="markfoster">Mark Foster</option>
  <option value="benpeddie">Ben Peddie</option>
  <option value="adamhiggs">Adam Higgs</option>
  <option value="adammcclure">Adam McClure</option>
</select>
</p>

<p>Please enter your Password <br /><br />
<input name="password" type="text" /></p>

<p>FIFA World Cup (Football) <br /><br />
<select name="fifaworldcup" />

<option name="algeria">Algeria</option>
<option value="argentina">Argentina</option>
<option value="australia">Australia</option>
<option value="brazil">Brazil</option>
<option value="chile">Chile</option>
<option value="denmark">Denmark</option>
<option value="england">England</option>
<option value="france">France</option>
<option value="germany">Germany</option>
<option value="ghana">Ghana</option>
<option value="greece">Greece</option>
<option value="honduras">Honduras</option>
<option value="italy">Italy</option>
<option value="ivorycoast">Ivory Coast</option>
<option value="japan">Japan</option>
<option value="koreadpr">Korea DPR</option>
<option value="korearepublic">Korea Republic</option>
<option value="mexico">Mexico</option>
<option value="netherlands">Netherlands</option>
<option value="newzealand">New Zealand</option>
<option value="nigeria">Nigeria</option>
<option value="paraguay">Paraguay</option>
<option value="portugal">Portugal</option>
<option value="serbia">Serbia</option>
<option value="slovakia">Slovakia</option>
<option value="slovenia">Slovenia</option>
<option value="southafrica">South Africa</option>
<option value="spain">Spain</option>
<option value="switzerland">Switzerland</option>
<option value="uruguay">Uruguay</option>
<option value="usa">USA</option>

</select></p>

<p>US Open (Golf) <br /><br />
<input name="golfusopen" type="text" /></p>

<p>Wimbledon (Tennis) <br /><br />
<select name="wimbledontennis">
<option value="federer">Roger Federer</option>
<option value="nadal">Rafael Nadal</option>
<option value="murray">Andy Murray</option>
<option value="roddick">Andy Roddick</option>

</select></p>

<p>Tour De France (Cycling) <br /><br />
<input name="tourdefrance" type="text" /></p>



<input name="submit" type="submit" />

</form>


		</div>
		<div id="twitterboxleft">
			<h1>follow me on twitter</h1>
			<img src="images/twitterlogo.jpg" alt="follow me on twitter" class="twitterlogo" />
			<div id="twitter_div"><ul id="twitter_update_list"><li> </li><li> </li></ul></div>
		</div>
		<div id="upcboxleft">
		</div>
	</div>
</div>
<script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>
<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/jerryhughes101.json?callback=twitterCallback2&count=3"></script>
</body>
</html>

This shuld fix those two irregularities.

-cb-

jerryhughes101 · April 17, 2010

No errors come up but I still don't get anything happening in the database...

The field 'user' in the database contains all the names that are the same as the option values in the form.. I was thinking that it may not be recognizing them, but it reads the passwords fine so I don't see that being a problem!

ChemicalBliss · April 17, 2010

Very strange. Try this tell us what it says:

<?php
// Code At The Top plox

//THE MySQL table 'users'
   //user_id int(10) PrimaryKey auto_increment
   //user varchar(50)
   //password varchar(50)
   
//MYSQL connection information
   $db_host = "localhost";
   $db_user = "jerryhug";   
   $db_password = "70exile101";
   $db_name = "jerryhug_upc";
   $path = $_SERVER['DOCUMENT_ROOT'];
      
   $connection = mysql_connect($db_host, $db_user, $db_password); 
   mysql_select_db($db_name, $connection);

//check if somthing has been posted
if (isset($_POST['password']) && isset($_POST['person'])){

   //get the username and password and protect against mysql Injection
   $user = mysql_real_escape_string($_POST['person']);
   $password = mysql_real_escape_string($_POST['password']); 
   
   //run the query to see if a user and password match
   $getname = "SELECT user FROM upcpicks WHERE user ='$user' AND password = '$password'";
   $nameresult = mysql_query($getname) or die(mysql_error());
   $user_id =  mysql_result($nameresult,0,'user'); // Easier
   
   //if there is a match then run the code to update the DB, otherwise the password does not match
   if (mysql_num_rows($nameresult) >= 1){
		// Sanitize these post values!
		$sql="UPDATE upcpicks SET fifaworldcup='".$_POST['fifaworldcup']."', usopengolf='".$_POST['golfusopen']."', wimbledon='".$_POST['wimbledontennis']."', tourdefrance='".$_POST['tourdefrance']."' WHERE user='".$_POST['user']."'";
		echo($sql);
		$updateresult = mysql_query($sql) or die(mysql_error()); // actually execute the update query.
   }else {
	  echo 'Password Not Matched';
   }
}

// Keep form, just seperate it

echo('<BR><BR>');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/layout.css"/>
<title>jerryhughes.co.uk</title>
</head>
<body>
<div id="container">
	<div id="topheader"></div>
	<div id="mainheader">
		<div id="insidemainheader">
			<img src="images/jerryhughesheader.jpg" class="jerryhughestext" />
		</div>
	</div>
	<div id="bottomheader"> </div>
	<div id="maincontent">
		<div id="topbarmaincontent">
you are: home >> upc >> make my picks
		</div>
		<div id="navbarmaincontent">
			<ul>
				<li><a href="index.html">home</a> </li>
				<li><a href="portfolio.html">portfolio</a> </li>
				<li><a href="cv.html">cv</a> </li>
				<li><a href="about.html">about</a> </li>
				<li><a href="contact.php">contact me</a> </li>

			</ul>
		</div>
		<div id="innerpagesrightbox">
			<h1>upc</h1>
			<h2>make my picks</h2>
<form method="post">

<p>Name <br /><br />
<select name="person">
  <option value="jerryhughes">Jerry Hughes
  <option value="roblowe">Rob Lowe</option>
  <option value="mitchturner">Mitch Turner</option>
  <option value="simoneccleston">Simon Eccleston</option>
  <option value="neilwilmer">Neil Wilmer</option>
  <option value="andrewhawkings">Andrew Hawkings</option>
  <option value="tomjohnson">Tom Johnson</option>
  <option value="robsmith">Rob Smith</option>
  <option value="samfaulkner">Sam Faulkner</option>
  <option value="russhewitt">Russ Hewitt</option>
  <option value="andycrowe">Andy Crowe</option>
  <option value="benshaw">Ben Shaw</option>
  <option value="richhancock">Rich Hancock</option>
  <option value="chrisjefferson">Chris Jefferson</option>
  <option value="paulellis">Paul Ellis</option>
  <option value="jonwibberley">Jon Wibberley</option>
  <option value="markfoster">Mark Foster</option>
  <option value="benpeddie">Ben Peddie</option>
  <option value="adamhiggs">Adam Higgs</option>
  <option value="adammcclure">Adam McClure</option>
</select>
</p>

<p>Please enter your Password <br /><br />
<input name="password" type="text" /></p>

<p>FIFA World Cup (Football) <br /><br />
<select name="fifaworldcup" />

<option name="algeria">Algeria</option>
<option value="argentina">Argentina</option>
<option value="australia">Australia</option>
<option value="brazil">Brazil</option>
<option value="chile">Chile</option>
<option value="denmark">Denmark</option>
<option value="england">England</option>
<option value="france">France</option>
<option value="germany">Germany</option>
<option value="ghana">Ghana</option>
<option value="greece">Greece</option>
<option value="honduras">Honduras</option>
<option value="italy">Italy</option>
<option value="ivorycoast">Ivory Coast</option>
<option value="japan">Japan</option>
<option value="koreadpr">Korea DPR</option>
<option value="korearepublic">Korea Republic</option>
<option value="mexico">Mexico</option>
<option value="netherlands">Netherlands</option>
<option value="newzealand">New Zealand</option>
<option value="nigeria">Nigeria</option>
<option value="paraguay">Paraguay</option>
<option value="portugal">Portugal</option>
<option value="serbia">Serbia</option>
<option value="slovakia">Slovakia</option>
<option value="slovenia">Slovenia</option>
<option value="southafrica">South Africa</option>
<option value="spain">Spain</option>
<option value="switzerland">Switzerland</option>
<option value="uruguay">Uruguay</option>
<option value="usa">USA</option>

</select></p>

<p>US Open (Golf) <br /><br />
<input name="golfusopen" type="text" /></p>

<p>Wimbledon (Tennis) <br /><br />
<select name="wimbledontennis">
<option value="federer">Roger Federer</option>
<option value="nadal">Rafael Nadal</option>
<option value="murray">Andy Murray</option>
<option value="roddick">Andy Roddick</option>

</select></p>

<p>Tour De France (Cycling) <br /><br />
<input name="tourdefrance" type="text" /></p>



<input name="submit" type="submit" />

</form>


		</div>
		<div id="twitterboxleft">
			<h1>follow me on twitter</h1>
			<img src="images/twitterlogo.jpg" alt="follow me on twitter" class="twitterlogo" />
			<div id="twitter_div"><ul id="twitter_update_list"><li> </li><li> </li></ul></div>
		</div>
		<div id="upcboxleft">
		</div>
	</div>
</div>
<script type="text/javascript" src="http://twitter.com/javascripts/blogger.js"></script>
<script type="text/javascript" src="http://twitter.com/statuses/user_timeline/jerryhughes101.json?callback=twitterCallback2&count=3"></script>
</body>
</html>

This shuld fix those two irregularities.

-cb-

jerryhughes101 · April 18, 2010

Still no action within the database, and I've just noticed something different actually!

There's a tiny bit of white space at the top of the website now and it's displaying some syntax within that, I didn't notice before because the font colour on the website is white and couldn't see it!

When you fill in the form with the correct password and submit it, it displays this

'UPDATE upcpicks SET fifaworldcup='Algeria', usopengolf='', wimbledon='federer', tourdefrance='' WHERE user='''

at the top of the page!

If you submit it with the wrong password it says

'Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 2 in /home/jerryhug/public_html/upcpicks.php on line 29

Password Not Matched'

jerryhughes101 · April 18, 2010

Just having a look over it, the line spaces that have been put in at the end of the php code, what were they for? Because there what's causing this white space gap at the top, where they there for a particular reason?

echo('<BR><BR>');

jerryhughes101 · April 18, 2010

Okay so the next step of what I've done, in the actual phpmyadmin I've tried running the SQL UPDATE straight into it, so I put in

UPDATE upcpicks SET fifaworldcup='spain', usopengolf='woods', wimbledon='federer', tourdefrance='armstrong' WHERE user='jerryhughes;'

As thats what the database should be getting from the form I think, and when I do that nothing happens in the database still, getting the 0 rows affected.

jerryhughes101 · April 18, 2010

seem to have found the problem

the UPDATE function had

$sql="UPDATE upcpicks SET (fifaworldcup='".$_POST['fifaworldcup']."', usopengolf='".$_POST['golfusopen']."', wimbledon='".$_POST['wimbledontennis']."', tourdefrance='".$_POST['tourdefrance']."' WHERE user='".$_POST['user']."')";

The problem with that was

WHERE user='".$_POST['user']."')

that needed to be

WHERE user='".$_POST['person']."')

as it's person not user in the code from their username!

Mchl · April 18, 2010

The mysql database currently as 6 fields in it, (user, password, fifaworldcup, usopengolf, wimbledon, tourdefrance - but these will increase as I add more sporting competitions to it).

Why don't you just do it in a spreadsheet then? Wither read about data normalisation, or stick to Excel.

jerryhughes101 · April 18, 2010

The mysql database currently as 6 fields in it, (user, password, fifaworldcup, usopengolf, wimbledon, tourdefrance - but these will increase as I add more sporting competitions to it).

Why don't you just do it in a spreadsheet then? Wither read about data normalisation, or stick to Excel.

sorry, what do you mean?

Mchl · April 18, 2010

What I mean is, your database design is not normalised. You have to add a column for each new competition you add. When designed well, a database should not require such changes.

jerryhughes101 · April 18, 2010

Okay, so what would be the normal way of doing it? Using a Excel spreadsheet like you just mentioned? in what way?

Mchl · April 18, 2010

Start here:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

jerryhughes101 · April 18, 2010

Okay, I'll have a read through that then, I'm only quite basic with this stuff so trying to learn what I can.

I've kind of noticed a few fatal flaws in this already anyway, because I've done it as the person doesn't have to sign in or anything, I won't be able to call up their previous picks from the database and put the variables into the select boxes for each individual.

SO whenever they go on the page, they'll have the default value and they'll reset any previously chosen picks to the default value unless they go through them all and change them to their pick every time which is obviously a HUGE nuisance!

might have to hit the drawing board again for this one I think

Sign In

Continually updating a database for a prediction competition

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information