[SOLVED] New to Sqlite need pointers?

[Kryllster]

Ok you may laugh at this puny attempt to make a character creation page for a game. I had been using mysql but I have been wanting to switch to Sqlite.

Here is the code.

<?php
// Define form variablesand checks
$connectionError = "This did not work this time.";
$message = "Please do NOT leave any fields empty Thank You!!";
// Start the process of creation
	$username = $_POST['username'];
	$email = $_POST['email'];
	$password = $_POST['password'];
	$sex = $_POST['sex'];
	$race = $_POST['race'];
	$fclass = $_POST['fclass'];

if (empty($_POST['username'])){
	echo $message;
	exit();
}

elseif (empty($_POST['email'])){
	echo $message;
	exit();
}
elseif (empty($_POST['password'])){
	echo $message;
	exit();
}
elseif (empty($_POST['sex'])){
	echo $message;
	exit();
} 
elseif (empty($_POST['race'])){
	echo $message;
	exit();
} 
elseif (empty($_POST['fclass'])){
	echo $message;
	exit();
}

// connect to database
$db = $_SERVER['DOCUMENT_ROOT']."../database/crimsond.db";

$handle = sqlite_open($db) or die("Could not open database");

// select table and check for duplicates
$query = "select * from cr_diamond where username='" . $_POST['username'] . "'";

// compare  
$result = sqlite_open($db);
if (sqlite_num_rows($result) >= 1) { 
	echo "That Username is already taken please choose another!";
	}

// continue if all these checks are ok Im hoping
else {

// Encrypt Password
$encrypted_passwd = md5($password);
$password = ($encrypted_passwd);

// These variables below are added as starting resource's for each player
$armor = "Leather Outfit";
$armor_bonus = 0;
$weapon = "Big Club";
$weapon_bonus = 0;
$advance = 1;
$level = 1;
$skill1_name = "None";
$skill1_bonus = 0;
$skill2_name = "None";
$skill2_bonus = 0;
$skill3_name = "None";
$skill3_bonus = 0;
$special_item = "None";
$special_bonus = 0;
$hitpoints = 15;
$onhand = 2000;
$diamond = 5;
$dbalance = 5;
$rubie = 10;
$rbalance = 5;
$rations = 1;
$bank = 5000;
$avatar = 00;

$dbquery = "INSERT INTO cr_diamond (username, email, password, sex, race, fclass, armor, armor_bonus, weapon, weapon_bonus, advance, level, skill1_name, skill1_bonus, skill2_name, skill2_bonus, skill3_name, skill3_bonus, special_item, special_bonus, hitpoints, onhand, diamond, dbalance, rubie, rbalance, rations, bank, avatar) Values('$username','$email','$password','$sex','$race','$fclass','$armor','$armor_bonus','$weapon','$weapon_bonus','$advance','$level','$skill1_name','$skill1_bonus','$skill2_name','$skill2_bonus','$skill3_name','$skill3_bonus','$special_item','$special_bonus','$hitpoints','$onhand','$diamond','$dbalance','$rubie','$rbalance','$rations','$bank','$avatar')");

// Close Database
	sqlite_close($handle);

// Direct on Creation Success testing sqlite
echo "<META HTTP-EQUIV=\"Refresh\"CONTENT=\"0; URL=create_success.php\">";
//echo "The Initial test has worked check database for confirmation!";
}
?>

um the database is there and the table is created I just cant seem to get it populated when the user signs up?

 

I have been searching all over the web google, sqlite website, php and here. Any help on this will be appreciated.

 

Thanks

 

Kryllster

[trq]

You never execute your query. See sqlite_exec.

[Kryllster]

I checked out the sqlite_exec() where it was suggested also have been looking all day for a reliable source of info. I even abandoned my own Wamp for another. As well as trying PDO and Using firefox sqlite addon all with no luck. I suppose there is something Im missing however I dont see what that is. Not sure where to go from here??

 

Thanks for helping tho!

 

Kryllster

[trq]

Damn, just took me ten minutes to clean this up.

 

<?php

if (isset($_POST['submit'])) { // <-- requires your submit button is named submit

  $connectionError = "This did not work this time.";
  $message = "Please do NOT leave any fields empty Thank You!!";

  $username = $_POST['username'];
  $email = $_POST['email'];
  $password = $_POST['password'];
  $sex = $_POST['sex'];
  $race = $_POST['race'];
  $fclass = $_POST['fclass'];

  if (
    empty($_POST['username']) ||
    empty($_POST['email']) ||
    empty($_POST['password']) ||
    empty($_POST['sex']) ||
    empty($_POST['race']) ||
    empty($_POST['fclass'])
  ) {
    echo $message;
    exit();
  }

  $db = $_SERVER['DOCUMENT_ROOT']."../database/crimsond.db";

  $handle = sqlite_open($db) or die("Could not open database");

  $sql = "SELECT username FROM cr_diamond WHERE username='{$_POST['username']}'";

  if ($result = sqlite_query($db, $sql)) {
    if (sqlite_num_rows($result)) { 
      echo "That Username is already taken please choose another!";
    } else {
      $password = md5($password);
      $armor = "Leather Outfit";
      $armor_bonus = 0;
      $weapon = "Big Club";
      $weapon_bonus = 0;
      $advance = 1;
      $level = 1;
      $skill1_name = "None";
      $skill1_bonus = 0;
      $skill2_name = "None";
      $skill2_bonus = 0;
      $skill3_name = "None";
      $skill3_bonus = 0;
      $special_item = "None";
      $special_bonus = 0;
      $hitpoints = 15;
      $onhand = 2000;
      $diamond = 5;
      $dbalance = 5;
      $rubie = 10;
      $rbalance = 5;
      $rations = 1;
      $bank = 5000;
      $avatar = 00;

      $sql = "INSERT INTO cr_diamond (
        username,
        email,
        password,
        sex,
        race,
        fclass,
        armor,
        armor_bonus,
        weapon,
        weapon_bonus,
        advance,
        level,
        skill1_name,
        skill1_bonus,
        skill2_name,
        skill2_bonus,
        skill3_name,
        skill3_bonus,
        special_item,
        special_bonus,
        hitpoints,
        onhand,
        diamond,
        dbalance,
        rubie,
        rbalance,
        rations,
        bank,
        avatar
      ) VALUES (
        '$username',
        '$email',
        '$password',
        '$sex',
        '$race',
        '$fclass',
        '$armor',
        '$armor_bonus',
        '$weapon',
        '$weapon_bonus',
        '$advance',
        '$level',
        '$skill1_name',
        '$skill1_bonus',
        '$skill2_name',
        '$skill2_bonus',
        '$skill3_name',
        '$skill3_bonus',
        '$special_item',
        '$special_bonus',
        '$hitpoints',
        '$onhand',
        '$diamond',
        '$dbalance',
        '$rubie',
        '$rbalance',
        '$rations',
        '$bank',
        '$avatar')"
      );

      if (sqlite_exec($handle, $sql)) {
        sqlite_close($handle);
        header("Location: create_success.php");
        exit();
      } else {
        sqlite_close($handle);
        echo "INSERT FAILED<br />$sql<br />" . sqlite_error_string(sqlite_last_error());
      }
    }
  } else {
    echo "SELECT FAILED<br />$sql<br />" . sqlite_error_string(sqlite_last_error());
  }
}

?>

 

You might want to remove those big FAILED messages once its working. There just there for debugging.

 

Hope this helps.

[Kryllster]

Either my server is configured incorrectly or I am a complete dumb $^5. 1 of the 2 most likely the latter. Anyway I copied and pasted the entire rewrite you did for me and pulled up a blank page. I did a .csv dump from firefox sqlite manager and the table is correct, yet it does not get populated once the form is processed. No error messages or anything, just a blank page. Any recommendations where to go next?? I am on XP btw, does that matter??

 

Thanks for spending the time on rewriting this!

 

Kryllster

[trq]

Did you name your submit button submit? eg;

 

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

[Kryllster]

Yes Sir I did here is what I have:

<input type="submit" name="submit" value="Create Account">
</form>

 

I am wondering if it is the server config tho. I usually dont have a hard time trying to solve problems, but this one has me stumped??

 

http://us.php.net/sqlite_query this is what I have been referring to for this. And the download there says PECL (whatever that is) is not bundled and I think it also says it only supports Sqlite3. Also says something about PDO not sure about that either. So I am at a loss other than to make a smaller app to learn instead of this??

 

Thanks

[trq]

Check phpinfo and post anything related to sqlite and pdo.

 

Can you also place....

 

error_reporting(E_ALL) ; ini_set('display_errors',1);

 

at the top of the script?

[Kryllster]

Ok I had enabled logging errors to a text file in the doc root in php. Here is what is in there several times

[05-Feb-2009 19:35:11] PHP Warning:  sqlite_query() expects parameter 1 to be resource, string given in C:\Apaph\WebDocs\create_character.php on line 33

[05-Feb-2009 19:35:11] PHP Warning:  sqlite_last_error() expects exactly 1 parameter, 0 given in C:\Apaph\WebDocs\create_character.php on line 133

 

Also when I run the script I get this output:

SELECT FAILED
SELECT username FROM cr_diamond WHERE username='Terry'
not an error

 

Another thing I did is placed the database 1 level below the doc root as well and created a new data base with this script:

 

<?php

$db = sqlite_open('../crimsond.sq') or die("Could Not open Database");

// create cr_diamond table

$makecdtable = 'CREATE TABLE cr_diamond(
"player_id" INTEGER PRIMARY KEY ,
"username" TEXT NOT NULL ,
"password" TEXT NOT NULL ,
"email" TEXT NOT NULL ,
"sex" TEXT NOT NULL ,
"race" TEXT NOT NULL ,
"fclass" TEXT NOT NULL ,
"armor" TEXT NOT NULL ,
"armor_bonus" INTEGER NOT NULL,
"weapon" TEXT NOT NULL ,
"weapon_bonus" INTEGER NOT NULL ,
"level" INTEGER NOT NULL ,
"hitpoints" INTEGER NOT NULL ,
"onhand" INTEGER NOT NULL ,
"skill1_name" TEXT NOT NULL ,
"skill2_name" TEXT NOT NULL ,
"skill3_name" TEXT NOT NULL ,
"skill1_bonus" INTEGER NOT NULL ,
"skill2_bonus" INTEGER NOT NULL ,
"skill3_bonus" INTEGER NOT NULL ,
"special_item" TEXT NOT NULL ,
"special_bonus" INTEGER NOT NULL ,
"diamond" INTEGER NOT NULL ,
"dbalance" INTEGER NOT NULL,
"rubie" INTEGER NOT NULL ,
"rbalance" INTEGER NOT NULL,
"rations" INTEGER NOT NULL,
"bank" INTEGER NOT NULL ,
"advance" INTEGER NOT NULL,
"avatar" TEXT NOT NULL)';

@sqlite_exec($db, $makecdtable);
sqlite_close($db);

?>

 

So I have php_pdo.dll and php_sqlite.dll enabled in php.ini. I see that there is something in the database it shows 4k.

 

?????

What next

 

[Kryllster]

finally some success after going thru this I downloded sqlitemanager and basically created the db thru there. I changed some of the code to this and it worked!

 

  //$db = $_SERVER['DOCUMENT_ROOT']."../sqlitemanager/crimsond.sq";
  $db = "sqlitemanager/crimsond.sq";

  $handle = sqlite_open($db) or die("Could not open database");

  $sql = "SELECT username FROM cr_diamond WHERE username='{$_POST['username']}'";

  if ($result = sqlite_query($handle, $sql)) { // changes $db in the first parameter with $handle
    if (sqlite_num_rows($result)) { 
      echo "That Username is already taken please choose another!";

 

I went to the success page and then checked it in the sqlitemanager and the info was there. I undoubtably will run into more problems. I thank you for helping me out I learned a lot. Things I learned: php error logging, sqlite commands, and that I have a long way to go.

 

My question is how do I make it secure as now its not as far as I know. I will check into that as well!

 

Thanks Again,

Any other pointers are welcome too.