update not working 1

[Sardamil]

I'm trying to update a field in a table using a radio button. For some reason it's not working and I've no idea why? Can anybody help me please? Posting the login.php too as I think that might be relevant.


login.php
<?php

//Betekent dat er met sessies word gewerkt

ob_start();
session_start();

/* Dit document heet: login.php */


//Met database connecten

$host = 'xxxxx'; //Host

$gebruikersnaam = 'xxxxx'; //Gebruikersnaam

$wachtwoord = 'xxxxx'; //Wachtwoord

$database = 'xxxxx'; //Database



/* Alle gegevens met belangrijk: Een mysql_error() dat geeft een foutmelding aan als de gegevens fout zijn*/

mysql_connect($host, $gebruikersnaam, $wachtwoord);

mysql_select_db($database) or die(mysql_error());



//Simpele loginformulier in html

$loginform = '

<h4>Inloggen</h4><br>

<form action="login.php" method="post">

Id:<br>

<input type="text" name="speler" /><br>

Wachtwoord:<br>

<input type="password" name="wachtw" /><br>

<input type="submit" name="login" value="Log in" />

</form>';



//Als er geklikt is op "login"

if (isset($_POST['login'])){



//Kijken of alle velden zijn ingevuld.

if(empty($_POST['speler']) || empty($_POST['wachtw'])){

//Zoniet, geef foutmelding.

echo 'Het inloggen ging verkeerd of je hebt nooit ingelogd1!';

exit();

}

//De gebruikersnaam en het wachtwoord in een var zetten

$speler = $_POST['speler'];

$wachtw = md5($_POST['wachtw']);



//Query maken om alle gegevens op te halen.

$query = sprintf("SELECT * FROM spelers WHERE user = '%s' AND wachtwoord = '%s' LIMIT 1;", mysql_real_escape_string($speler), mysql_real_escape_string($wachtw));

//Query uitvoeren

$uitvoeren = mysql_query($query) or die('Er ging iets fout bij de query! Raporteer de webmaster hier over!');

//Kijken of er rijen zijn met de gegevens die zijn ingevoerd

$result = mysql_num_rows($uitvoeren);

//Als de rij er niet is(0)

if($result = 0){



//Geef foutmelding

echo 'Het inloggen ging verkeerd of je hebt nooit ingelogd2!';

echo $loginform;



//Else.. Anders.

}

else

{



//Zet sessies

$_SESSION['ingelogd'] = 1;

$_SESSION['gb'] = $speler;

header('Location: aanwezig.php');
exit();
}

}

//Anders

else

{

//Laat loginform zien

echo $loginform;

}
ob_end_flush();
?>

aanwezigcode.php:
<?php
//Betekent dat er met sessies word gewerkt
session_start();

/* Deze pagina heet: aanwezigcode.php */

//Connecteren met wedstrijden

$mysqlhost = 'xxxxx'; //Host

$mysqlgebruikersnaam = 'xxxxx'; //Gebruikersnaam

$mysqlwachtwoord = 'xxxxx'; //Wachtwoord

$mysqldatabase = 'xxxxx'; //Database



$conn1 = mysql_connect($mysqlhost, $mysqlgebruikersnaam, $mysqlwachtwoord);

mysql_select_db($mysqldatabase) or die(mysql_error());


//Query opstellen om gegevens uit de database te halen.
$que = "SELECT * FROM wedstrijden";
//Query uitvoeren
$uitvoeren1 = mysql_query($que) or die(mysql_error());

// de table beginnen
echo '
<A HREF = logout.php>Log out</A>
<table>
<tr>
<td width="130">Datum</td>
<td width="180">Thuis</td>
<td width="180">Uit</td>
<td width="300">Aanwezig</td>';

//rij maken zodat je gegevens kan gebruiken
while($rij = mysql_fetch_assoc($uitvoeren1)){
//Echo(laat zien simpel form)
array_map('htmlspecialchars', $rij);
echo '
<form action="aanwezig.php" method="POST">
</tr>
<tr>
<td width="130">'.$rij['datum'].'</td>
<td width="180">'.$rij['thuis'].'</td>
<td width="180">'.$rij['uit'].'</td>
<td width="300"><input type="radio" name="aanwezig" value="aanwezig" />Aanwezig
<input type="radio" name="aanwezig" value="afwezig"/>Afwezig
<input type="submit" name="submit" value="Verstuur" /></td>';

// en tenslotte de boel afsluiten
echo '
</tr>
</table>;
</form>';
}
//Even een dingetje opstellen voor de database
$wedstrijdid = $_POST['wedstrijdid'];
//Als form verstuurd is
if( isset($_POST['submit'])){
//Connecteren met aanwezigheid

$host = 'xxxxx'; //Host

$gebruikersnaam = 'xxxxx'; //Gebruikersnaam

$wachtwoord = 'xxxxx'; //Wachtwoord

$database = 'xxxxx'; //Naam database

$conn = mysql_connect($host, $gebruikersnaam, $wachtwoord);
mysql_select_db($database) or die(mysql_error());
//Een query maken en beveiligen met mysql_real_escape_string

$query = " UPDATE aanwezigheid
SET aanwezigheid = '".mysql_real_escape_string($_POST['aanwezig'])."'
where spelerid= (select s.spelerid from spelers s where s.user = '".mysql_real_escape_string($_SESSION['gb'])."')
and wedstrijdid = '".mysql_real_escape_string($_POST['wedstrijdid'])."'";

$uitvoeren = mysql_query($query) or die(mysql_error());
}
?>

Anything that can go wrong will go wrong

Window to my world

 

[Sardamil]

Sorry, couldn't help myself (being a tester). If you login with a different name, the script will still log you in. It just will not show you anything. It will not give you an error message.

I logged in with peterx/test and I got in. That's what I did wrong earlier.

Anything that can go wrong will go wrong

Window to my world

 

[jpadie]

that's good to know.

i will fix the login script.

in the meantime, can you confirm that the app (in general) works the way you envisage? you can tell that the db update has been successful by the green flash effect. you can confirm aswell by refreshing the page and verifying that the switch is in the right position.

when the script works properly i will upload to here for the login and gamepresence scripts and provide a zipped download of the other necessary files on my server

 

[Sardamil]

this is what I was looking for. Of course I never thought of a slider, but that's ok.

Anything that can go wrong will go wrong

Window to my world

 

[jpadie]

surreal. this will be my last post in this thread.

anyway, the site is refreshed with a working login.php. i will maintain it and the zip file for another day.

the login.php file looks like this

<?php
session_start();
//Betekent dat er met sessies word gewerkt
/* Dit document heet: login.php */

define('USERTABLE', 'spelers');
define('LOGINEXPIRES', true);	//set to true in order to 'time out' users after TIMEOUT seconds
define('TIMEOUT', 900);	//depending on LOGINEXPIRES, sets the timeout value to 15 minutes.
define('STORELOGININDB', true); //set to true in order to store the last login time in the database.  you will need a column called lastLoginTime (int(15))
define('DEBUG', true);	//set to false for production user
define('DEBUGFILE', './debugfile.txt');
if (DEBUG){
	error_reporting(E_ALL);
	ini_set('display_errors', true);
} else {
	ini_set('display_errors', false);
}

/**
 * function to connect to the db. 
 * 
 * this only needs to be called ONCE per script.
 * @return 
 */

function dbConnect(){
	//Met database connecten 
	$host =                    ''; //Host
	$gebruikersnaam =     ''; //Gebruikersnaam
	$wachtwoord =             ''; //Wachtwoord
	$database =             ''; //Database
	/* Alle gegevens met belangrijk: Een mysql_error() dat geeft een foutmelding aan als de gegevens fout zijn*/
	
	$conn = @mysql_connect($host, $gebruikersnaam, $wachtwoord); 
	if ($conn){
		@mysql_select_db($database) or mysql_bail(mysql_error(), 'select db'); 
	} else {
		mysql_bail('cannot connect to database server', '');
	}
}

/**
 * this function starts the motor.  
 * 
 * nothing else is needed as if logged in you want this whole script to be silent. login failures etc automatically display the login form with appropriate messages and then kill the script execution
 *  
 * @return 
 */
function startLoginProcess(){
	if (isLogout()){
		logout();
		displayLoginForm('You have been logged out');
	}
	if(isLoggedIn()){
		updateLastLogin();
	} else {
		//not logged in
		//so check login attempt
		checkLogin();
	}
}


/**
 * helper function to display the login form.
 * if this is a second/subsequent login attempt, the username field is prefilled
 * 
 * @param object $message [optional] 
 * @return void
 */
function displayLoginForm($message = null){
	if (!empty($message)){
		$message = "<div id=\"loginMessage\">$message</div>";
	}
	$speler = empty($_POST['user']) ? '' : $_POST['user'];
	echo <<<HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" 
content="text/html;charset=utf-8" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<title>Game Presence Indicator - Login</title>
<style type="text/css">
@import url('css/pageLevel.css');
</style>
<script type="text/javascript">
window.onload = function(){
	document.getElementById('user').focus();
}
</script>
</head>
<body>
	<h4>Inloggen</h4><br>
	$message 
	<form action="{$_SERVER['PHP_SELF']}" method="post" name="form"> 
		Id:<br/> 
		<input type="text" name="user" value="$speler" id="user"/><br> 
		Wachtwoord:<br> 
		<input type="password" name="wachtwoord" /><br> 
		<input type="submit" name="login" value="Log in" /> 
	</form>
</body>
</html>
HTML;
	exit;
}


/**
 * helper function to determine whether a user has already logged in
 * @return 
 */
function isLoggedIn(){
	if (empty($_SESSION['loggedIn']) || $_SESSION['loggedIn'] !== true){
		return false;
	}
	if (!empty($_GET['logout'])){
		logout();
		displayLoginForm('You have been logged out');
	}
	if (logInExpired()){
		logout();
		displayLoginForm('Your login has expired. Please log in once more');
	} else {
		return true;
	}
}


/**
 * helper function to validate the login form data and fork off as required
 * 
 * @return 
 */
function checkLogin(){
	if (!empty($_POST['login'])){
		if (empty($_POST['user']) || empty($_POST['wachtwoord'])){
			//data not provided
			displayLoginForm('You must provide a username and password');
		} else {
			//data provided
			$isValid = validateCredentials($_POST['user'], $_POST['wachtwoord']);
			if (!$isValid){
				displayLoginForm('Either your username or password is incorrect');
			} else {
				logIn($_POST['user']);
			}
		}
	} else {
		displayLoginForm();
	}
}


/**
 * helper function to retrieve the user credentials for a given username
 * 
 * @param object $user
 * @return	associative array of userdata
 * ToDo: filter the return data
 */
function getUserCredentials ($user){
	$sql = "select * from " . USERTABLE .' where naam=%s';
	$user = dbReady($user);
	$query = sprintf($sql, $user);
	$result = mysql_query($query) or mysql_bail(mysql_error(), $query);
	return mysql_fetch_assoc($result);
}

/**
 * helper function to set the log-in flags and session data
 * @param object $user
 * @return 
 */
function logIn($user){
	$credentials = getUserCredentials($user);
	$_SESSION['loggedIn'] = true; 
	$_SESSION['userData'] = $credentials;
	
	//import from OP code
	$_SESSION['ingelogd'] = 1; 
	$_SESSION['gb'] = $user;
	updateLastLogin();
}

/**
 * helper function to update the time that the user was last seen.
 * 
 * data is stored in both the session table and the user table (in a column called lastLoginTime);
 * @return 
 */
function updateLastLogin(){
	$_SESSION['lastLogIn'] = time();
	if (STORELOGININDB){
		if (!dbHasColumn(USERTABLE, 'lastLoginTime')){
			addDBColumn(USERTABLE, 'lastLoginTime', 'int(15) not null default 0');
		}
		$sql = 'update '. USERTABLE . ' set lastLoginTime=%d where idspeler=%s';
		$query = sprintf($sql, $_SESSION['lastLogIn'], dbReady($_SESSION['userData']['idspeler']));
		mysql_query($query) or mysql_bail(mysql_error(), $query);
	}
}

/**
 * helper function to add a column to table
 * @param object $table	the table to alter
 * @param object $column the name of the column to add
 * @param object $definition the column definition
 * @return 
 */
function addDBColumn($table, $column, $definition){
	$query = "ALTER TABLE $table ADD COLUMN $column $definition";
	mysql_query($query) or mysql_bail(mysql_error(), $query);
}


/**
 * helper function to check whether database has a particular column in it
 * @param object $table
 * @param object $column
 * @return true if the column exists, false otherwise/
 */
function dbHasColumn ($table, $column){
	$query = "show columns in $table like '$column'";
	$result = mysql_query($query) or mysql_bail (mysql_error(), $query);
	$row = mysql_fetch_assoc($result);
	return $row;
}


/**
 * helper function to determine whether a user's login has timed out.
 * @return true for a timeout, false otherwise.
 */
function logInExpired(){
	if( ! LOGINEXPIRES)	return false;
	if (empty($_SESSION['lastLogIn'])) return true;
	return (($_SESSION['lastLogIn'] + TIMEOUT) < time());
}


/**
 * helper function to check whether the user is authorised to access the system
 * 
 * @param object $user
 * @param object $pwd
 * @return (bool) true if authorised, false if not
 */
function validateCredentials($user, $pwd){
	$sql = "Select count(*) as cnt from " .USERTABLE ." where naam=%s and wachtwoord=%s";
	$params = array($user, encode($pwd));
	$params = array_map('dbReady', $params);
	$query = vsprintf($sql, $params);
	$result = mysql_query($query) or mysql_bail(mysql_error(), $query);
	$row = mysql_fetch_assoc($result);
	return ($row['cnt'] == 1);
}


/**
 * helper function to encode data in md5/sha4 etc
 * 
 * @param object $data
 * @return encoded data or false on error
 */
function encode($data){
	return md5($data); 
}


/**
 * helper function to enquote and return escaped data for mysql usage
 * 
 * @param object $data
 * @return 
 */
function dbReady($data){
	//trim the data
	$data = trim($data);
	if (function_exists('mysql_real_escape_string')){
		$result = @mysql_real_escape_string($data);
		if (!$result){
			$result = mysql_escape_string($data);
		}
	} else {
		$result = mysql_escape_string($data);
	}
	return "'". $result. "'";	
}

/**
 * function to handle logouts
 * @return 
 */
function logout(){
	unset($_SESSION);
	session_destroy();
}

function isLogout(){
	return (isset($_REQUEST['logout']));
}
function mysql_bail($error, $query){
	$time = date('r');
	$message = <<<HTML
<h2>Mysql Error</h2>
<div id="message">
$error
</div>
<div id="query">
Query was 
<pre>$query</pre>
</div>
<div id="timestamp">
Timestamp: $time
</div>
HTML;
	file_put_contents(DEBUGFILE, $message, FILE_APPEND);
	if (DEBUG){
		echo $message;
		exit;
	} else {
		echo "An unrecoverable error has occurred.  The administrator has been informed.  Please try back later.";
		exit;
	}
}

dbConnect();
startLoginProcess(); //that's all folks!
?>

<?php
require_once './login.php';
define ('USESLIDERS', true);

function displayGameGrid($message = null){
	// de table beginnen
	if (!empty($message)){
		$message = "<div id=\"errorMessage\">$message</div>";
	}
	if (USESLIDERS){
		$sliders = 1;
	} else {
		$sliders = 0;
	}
	echo <<<HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xml:lang="en" lang="en">

<head>
	<meta http-equiv="content-type" 
		content="text/html;charset=utf-8" />
	<meta http-equiv="Content-Style-Type" content="text/css" />
<title>Game Presence Indicator</title>
<link type="text/css" href="css/style.css" rel="stylesheet" />
<script type="text/javascript" src="js/jQuery.js"> </script>
<script type="text/javascript" src="js/highlightFade.js"></script>
<script type="text/javascript" src="js/iphone-style-checkboxes.js"></script>
<script type="text/javascript">
var useSlider = $sliders;
</script>
<script type="text/javascript" src="js/pageLevel.js"></script>
<style type="text/css">
@import url('css/pageLevel.css');
</style>
</head>
<body>
<div id="header">
You are logged in as {$_SESSION['userData']['naam']}. <a class="logout" href="{$_SERVER['PHP_SELF']}?logout">Logout here</a>
</div>
<div id="wrap">
$message
<h3>Game Attendance for {$_SESSION['userData']['naam']}</h3>
<table>
	<thead>
    <tr>
        <th width="130">Datum</td>
        <th width="180">Thuis</td>
        <th width="180">Uit</td>
        <th width="300" class="centre">Aanwezig</td>
	</tr>
	</thead>
	<tbody>
HTML;

	//Query opstellen om gegevens uit de database te halen.
	$sql = "SELECT date_format( w.datum, '%%D %%b %%Y') as d, w.* , a.aanwezigheid as present
			FROM  `wedstrijden` w
			JOIN aanwezigheid a ON ( a.wedstrijdid = w.idwedstrijd ) 
			WHERE a.spelerid = %s
			ORDER BY UNIX_TIMESTAMP(w.datum) ASC";
	
	$query = sprintf($sql, dbReady($_SESSION['userData']['idspeler']));
	$result = mysql_query($query) or mysql_bail(mysql_error(), $query);
	//rij maken zodat je gegevens kan gebruiken
	$class='';
	while($rij = mysql_fetch_assoc($result)){
	    array_map('htmlspecialchars', $rij);
		//set initial state
		if ($rij['present'] == 'aanwezig' || $rij['present'] == '1'){
			$_aanwezig = 'checked="checked"';
			$_afwezig = '';
		} else {
			$_afwezig = 'checked="checked"';
			$_aanwezig = '';
		}
		$class = ($class=='alt') ? '' : 'alt';
	    echo <<<HTML
			<tr class="$class">
				<td width="130">{$rij['d']}</td>
				<td width="180">{$rij['thuis']}</td>
				<td width="180">{$rij['uit']}</td>
				<td width="300" id="d_{$rij['idwedstrijd']}" class="centre" align="center">

					<form action="{$_SERVER['PHP_SELF']}" method="POST" id="form_{$rij['idwedstrijd']}">
						<input type="hidden" name="gameID" value="{$rij['idwedstrijd']}" />
						<input type="hidden" name="action" value="updateGame" />
						<input type="checkbox" name="aanwezig" value="1" $_aanwezig /><span class="checkboxLabel">&nbsp;Tick if you're coming</span>						
						<input type="submit" name="submit" value="Save"/>
					</form>
				</td>
			</tr>
HTML;
	}	//end while
		echo <<<HTML
		
			</tbody>
		</table>
		</div>
		</body>
HTML;
} //end function

function updateGameGrid(){
	//validate input
	if (empty($_POST['gameID']) ){
		return false;
	} else {
		$val = empty($_POST['aanwezig']) ? 0 : 1;
		$result = updateGameTable($_POST['gameID'], $val, $_SESSION['userData']['idspeler']);
		if ($result === false){
			$message = 'something went wrong updating the game table';
		} else {
			$message = 'ok';
		}
		return $message;
	}
}

function updateGameTable($gameID, $present, $player){
	$sql = "replace 
			into aanwezigheid 
			set aanwezigheid=%s,spelerid=%s, wedstrijdid=%s";
	$params = array($present, $player, $gameID);
	$params = array_map('dbReady', $params);
	$query = vsprintf($sql, $params);
	$result = mysql_query($query) or mysql_bail(mysql_error(), $query);
	return $result;	
}

/**
 * helper function to determine what we need to do on the page
 * @return 
 */
function getAction(){
	return  empty($_POST['action'])
				? (empty($_GET['action']) 
					? ''
					: trim($_GET['action']) )
				: trim ($_POST['action']);
}


/**
 * helper function to determine whether db update request is an ajax request or not.
 * @return 
 */
function isAjax(){
	return (!empty($_POST['isAjax']));
}

/**
 * helper function to cache jQuery in the local filesystem
 * 
 * @return 
 */
function cacheJQuery(){
	$url = '[URL unfurl="true"]http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js';[/URL]
	$ch = curl_init($url);
	curl_setopt_array($ch, array(CURLOPT_RETURNTRANSFER=>true));
	$file = curl_exec($ch);
	if (strlen($file) > 0 ){
		file_put_contents('jQuery.js', $file);
	}
}

$action = getAction();
switch ($action){
	case 'updateGame':
		$result = updateGameGrid();
		if (isAjax()){
			echo json_encode(array('result'=>$result, 'elem'=> 'd_' . $_POST['gameID']));
			exit;
		} else {
			displayGameGrid($result);
		}
	break;
	case 'inclJQuery':
		$c = 0;
		clearstatcache();
		while (!file_exists('jQuery.js')){
			if ($c > 10){
				echo 'cannot get the file';
				exit;
			}
			cachejQuery();
			$c++;
		}
		readfile ('jQuery.js');
		break;
	default:
		displayGameGrid();
}
?>

to use a checkbox set the USESLIDERS definition to false in line 3 of index.php.

the other required files are packaged in a zip file available here

 

[Sardamil]

if (!$isValid){
displayLoginForm('Either your username or password is incorrect');

Is this correct? Now I always get the message.

Sorry, I know you said that was your last post.

Anything that can go wrong will go wrong

Window to my world

 

[Sardamil]

I think it should be something like this. Haven't tried it yet though. Will do when I get home.

function checkLogin(){
if (!empty($_POST['login'])){
if (empty($_POST['user']) || empty($_POST['wachtwoord'])){
//data not provided
displayLoginForm('You must provide a username and password');
} else {
//data provided
$isValid = validateCredentials($_POST['user'], $_POST['wachtwoord']);
if (!$isValid){logIn($_POST['user'])
displayLoginForm('Either your username or password is incorrect');
} else {
displayLoginForm('Either your username or password is incorrect');
}
}
} else {
displayLoginForm();
}
}

Anything that can go wrong will go wrong

Window to my world

 

[jpadie]

sfaik, my code is correct.

remember in the schema you posted above you inserted the password as 'test' rather than md5('test'). i ignored this on the basis that it was incorrect.

 

[Sardamil]

For some reason I kept getting the incorrect login message. I replaced the login script with the old one and now everything is working perfectly.

Thanks a lot for your patience and time.

Anything that can go wrong will go wrong

Window to my world

 

[Sardamil]

Working perfectly in firefox, but IE is have probs. Trying to figure that out.

Anything that can go wrong will go wrong

Window to my world

 

[Sardamil]

Just set the USESLIDERS to false and now even IE is working perfectly.

Anything that can go wrong will go wrong

Window to my world