[SOLVED] New to Sqlite need pointers?

Kryllster · February 4, 2009

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 · February 4, 2009

You never execute your query. See sqlite_exec.

Kryllster · February 5, 2009

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 · February 5, 2009

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 · February 5, 2009

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 · February 5, 2009

Did you name your submit button submit? eg;

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

Kryllster · February 5, 2009

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 · February 5, 2009

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 · February 6, 2009

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 · February 6, 2009

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.

Sign In

[SOLVED] New to Sqlite need pointers?

Recommended Posts

Kryllster

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Kryllster

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Kryllster

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Kryllster

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Kryllster

Link to comment

Share on other sites

Kryllster

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information