Hey,

I am very new to php. I have a PHP script (modified from http://woogley.net/misc/Highscore/)that I call from a my Java code to access a database in order to read/write records. The SELECT part of it was perfect. However, i can't write to the database when I try to UPDATE.

Do you know what might be the problem? Please see my code below.

Note: Database contains 2 columns: Sentence and RecordTime.


PHP CODE

<?php
header("Content-type: text/plain");
$GLOBALS["access_code"] = "123"; // application must provide this number for security
$GLOBALS["db_name"] = "db_name"; // name of the database
$GLOBALS["table_name"] = "SentenceList"; // name of the table in the database
$GLOBALS["view_user"] = "me"; // public access MySQL user
$GLOBALS["view_pass"] = "pass"; // public password
$GLOBALS["db_error"] = 0;
$GLOBALS["db_link"] = null;
function is_error() {
	$result = ($GLOBALS["db_error"] != 0);
	return $result;
}
function error() {
	$GLOBALS["db_error"] = 1;
	print "Error: ".mysql_error()."\n";
}
function sql($q) {
	$result = @mysql_query($q) or error();
	return $result;
}
function select_db() {
	if (!mysql_select_db($GLOBALS["db_name"])) error();
}
function db_connect($user,$pass) {
	if ($GLOBALS["db_link"] != null) {
		@mysql_close($GLOBALS["db_link"]);
	}
	$GLOBALS["db_link"] = @mysql_connect("localhost",$user,$pass) or error();
	if (is_error()) {
		db_close();
		exit;
	}
}
function db_close() {
	if ($GLOBALS["db_link"] != null) {
		@mysql_close($GLOBALS["db_link"]);
	}
	print (is_error()?"0":"1");
	$GLOBALS["db_link"] = null;
}
function db_view() {
	db_connect($GLOBALS["view_user"],$GLOBALS["view_pass"]);
	select_db();
}
function db_store($p,$s,$user,$pass) {
	if ($p != "") {
		db_connect($user,$pass);
		select_db();
		mysql_query("UPDATE `{$GLOBALS['table_name']}` SET `RecordTime` = '{.$s}' WHERE `Sentence` = '{.$p}'");
		db_close();
		exit;
	}
}
function db_list() {
	db_view();
	$scores = sql("SELECT `Sentence`,`RecordTime` FROM `{$GLOBALS['table_name']}` ORDER BY `Sentence`");
	$numrows = @mysql_num_rows($scores);
	for ($j = 0;$j < $numrows;$j++) {
		$row = mysql_fetch_row($scores);
		print "{$row[0]} {$row[1]}".($j == $numrows-1?"":"\n");
	}
	@mysql_free_result($scores) or error();
	@mysql_close($GLOBALS["db_link"]) or error();
	exit;
}
function db_install($user,$pass) {
	db_connect($user,$pass);
	sql("DROP DATABASE IF EXISTS `{$GLOBALS['db_name']}`");
	sql("CREATE DATABASE `{$GLOBALS['db_name']}`");
	select_db();
	sql("CREATE TABLE `{$GLOBALS['table_name']}` (`Sentence` VARCHAR(25),`RecordTime` INT UNSIGNED)");
	db_close();
	exit;
}
function check_access() {
	if (!isset($_GET["access_code"])) {
		$GLOBALS["db_error"] = 1;
		db_close();
		exit;
	}
	if ($_GET["access_code"] != $GLOBALS["access_code"]) {
		$GLOBALS["db_error"] = 1;
		db_close();
		exit;
	}
}
if (isset($_GET["action"])) {
	check_access();
	if ($_GET["action"] == "install" && isset($_GET["admin_user"]) && isset($_GET["admin_pass"])) {
		db_install($_GET["admin_user"],$_GET["admin_pass"]);
	}
	else if ($_GET["action"] == "submit" && isset($_GET["admin_user"]) && isset($_GET["admin_pass"]) && isset($_GET["Sentence"]) && isset($_GET["RecordTime"])) {
		db_store($_GET["Sentence"],$_GET["RecordTime"],$_GET["admin_user"],$_GET["admin_pass"]);
	}
	else if ($_GET["action"] == "list") {
		db_list();
	}
}
?>

Java code

public static void saveData(){
		URL url=null;
		try {
			String urlPart1 = "http://mysite.com/getdbase.php?action=submit&admin_user=me&admin_pass=pass";
			String urlPart2 = "&Sentence=Hello Sir.";
			String urlPart3 ="&RecordTime=10";
			String urlPart4 = "&access_code=123";
			String fullUrl = urlPart1+urlPart2+urlPart3+urlPart4;
			//http://yoursite.com/highscore.php?action=submit&admin_user=foo&admin_pass=bar&name=Bob&score=100&access_code=1234
			url = new URL(fullUrl);
		} catch (MalformedURLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
		    URLConnection myURLConnection = url.openConnection();
		    myURLConnection.connect();
	        BufferedReader in = new BufferedReader(new InputStreamReader(
	                                		myURLConnection.getInputStream()));
	        String inputLine;

	        while ((inputLine = in.readLine()) != null) 
	            System.out.println(inputLine);
	        in.close();

		} catch (MalformedURLException e) {     // new URL() failed
		    
		} catch (IOException e) {               // openConnection() failed
		   System.out.println("Failed connection");
		}

		
	}

Thanks so much for your help.
Also, If you have any other opinion on how to safely read/write a database from a Java Applet (small online game needs to store highscore), please suggest it. God Bless.

try to remove the . on line 50 '{.$s}'==>'{$s}' & '{.$p}'==>'{$p}'
also you can put an 'or die()' statement for

mysql_query("UPDATE `{$GLOBALS['table_name']}` SET `RecordTime` = '{$s}' WHERE `Sentence` = '{$p}'") or die("update error = ".mysql_error());

to try and figure out what your error message is.

try to remove the . on line 50 '{.$s}'==>'{$s}' & '{.$p}'==>'{$p}'
also you can put an 'or die()' statement for

mysql_query("UPDATE `{$GLOBALS['table_name']}` SET `RecordTime` = '{$s}' WHERE `Sentence` = '{$p}'") or die("update error = ".mysql_error());

to try and figure out what your error message is.

I corrected that already although that wasnt the problem. The script was perfect but the URL i used in calling it had the problem - it had spaces instead of the url conversion of spaces. I just applied URLEncoder to the String and Voila!!, my problems were solved.

Thanks for your opinions.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.