php work with database

Currently I take user, username, and password from an email generated by a paypal ipn php and manually enter them in my users database. I want to automate, but the web is full of useless and old examples.

The below is from my attempt thru my paypal ipn php. This is where I’m at from what I can gather. The $emailtext just gives me a printout; so far nothing but "user: ".

The $dir indicates moving back one folder from my public_html folder and forward into the cgi-bin folders. The tablename “userData” is correct.

$dir = ‘sqlite:…/cgi-bin/rio/circleCalcFiles/users.sqlite’;
$dbh = new pdo($dir) or die(“cannot open the database”);

$sqlStatement = “SELECT * FROM userData”;
$result = $dbh->query($sqlStatement);
$emailtext .= "
" . "user: " . $result[‘user’];

any ideas?

where’d all the smart people go ???

Hi John, I’m a little confused by your post. You mention automating inserting user data into your users database, but your code only shows a ‘Select’. You might want to clarify your question.

If this is a PHP question… you might want to try a PHP forum.

Trying to prove that I’ve got a connection to the database by getting something from it. I’ve tried the php forums and gotten a mess.

I’m not sure, because I’ve not used pdo with PHP. I would think the pdo object would have an error message/code you could check or maybe you could wrap your code in a try - catch block… if pdo raises an exception.

I found this PHP/PDO/SQLite example. You might take a look at it.

http://www.if-not-true-then-false.com/2012/php-pdo-sqlite3-example/

Thanks guys … I want to get the select working first. following instructions from php I get:

$dir = ‘sqlite:…/cgi-bin/rio/circleCalcFiles/users.sqlite’;
if ($db = new pdo($dir, $error)) {
$emailtext .= "
" . “connected to database”;
$sqlStatement = “SELECT * FROM userData”;
$result = $db->query($sqlStatement);
$answer = $result->fetchColumn();
} else {
die($err);
$emailtext .= "
" . "error: " . $error;
}

the $answer = $result->fetchColumn(); line is apparently causing an error (paypal ipn simulator can’t send notice). So, I’m stuck again.

This is really frustrating … I’m to the point of just guessing.

I’m following http://henryranch.net/software/ease-into-sqlite-3-with-php-and-pdo/ exactly and can’t get even get an error back.

Here’s my current code:

try {
$dir = ‘sqlite:…/cgi-bin/rio/circleCalcFiles/users.sqlite’;
if ($db = new pdo($dir, $error)) {
$emailtext .= "
" . “connected to database”;
$sqlStatement = “INSERT INTO userData VALUES (’” . $ownername . “’,’” . $username . “’,’” . $password . “’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’)”;
$db->exec($sqlStatement);
} else {
die($err);
$emailtext .= "
" . "error: " . $err;
}
} catch(PDOException $e) {
$emailtext .= "Exception: " . $e->getMessage();
}

too bad this forum doesn’t do indenting …

When you click on the paper with <> symbols, you get 2 tags: [ code ] [ /code ]
When you put your coding between those tags, you can indent the source.

try {	
	$dir = 'sqlite:../cgi-bin/rio/circleCalcFiles/users.sqlite';
	if ($db = new pdo($dir, $error)) {
		$emailtext .= "\
" . "connected to database";
		$sqlStatement = "INSERT INTO userData VALUES ('" . $ownername . "','" . $username . "','" . $password . "', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')";
		$db->exec($sqlStatement);
	} else {
		die($err);
		$emailtext .= "\
" . "error: " . $err;
	}
} catch(PDOException $e) {
	$emailtext .= "Exception: " . $e->getMessage();
}

I’m not sure what’s going on with your script, but I would:

  1. Not worry about the PayPal IPN until the SQL code is working.

  2. Output the results from the query to the browser so you can verify the
    data returned is what PayPal IPN expects.

    ( echo $result; )

  3. Turn on PHP error_reporting().
    Put the code below at the top of your script.

<?php
           ini_set('display_errors','On');
           error_reporting(E_ALL);


          // Rest of your code here
?>

Not sure if you have heard of Sitelok or not… It uses mysql though not sqlite. You can simply add the tables you would need to the sitelok standard database and have your xojo program reference the username/pw from the sitelok table.

I use Sitelok on several of my member sites and it is simple to use. Now you would have to purchase the PayPal plugin from their same site. But the process would be automatic and sends the user their login info after purchase. If you have any questions Adrian over there is very prompt .

I think I made a mistake looking at your code… change:

echo $result;

to

echo $answer;

or your could do:

foreach($result as $row)
{
echo $row[‘table_fieldname’] .’
’;
}

Here are some things to consider:

The database file - How was your sqlite database file created? If the version of the database file is different to the version supported by your php install sometimes it will allow you to connect but fail when you try to run queries.

File permissions - I run lighttpd + php on Ubuntu serving files from ~/public_html but you have to remember the scripts run as www-data not as the logged in user so the permissions for ~/public_html and its contents have to be set appropriately.

Use is_file() to check the path to your sqlite file before concatenating it into your connection string.

If $db->query() or $db->exec() fail they return false (or something that equates to false) so test for it before calling $res->fetchColumn()

Why not catch all exceptions and see what message you get then rather than just catching a PDOException?
catch(Exception $e) { echo 'Error Message: ’ .$e->getMessage(); }

Got it. thanks guys … once I could get errors it er … was trial and error. Don’t use “SQLite:…/” to get to a folder. Keeping the users database in public_html helps a lot.

try { if ($db = new pdo('sqlite:users.sqlite', $error)) { $mail_Body .= "\ " . "connected to database"; $sqlStatement = "INSERT INTO userData VALUES ('" . $ownername . "','" . $username . "','" . $password . "', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'True', 'True', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')"; $db->exec($sqlStatement); $result = $db->query('SELECT * FROM userData'); if ($result != null ) { foreach($result as $row) { $mail_Body .= " " . $row['user']; } } } else { die($err); $mail_Body .= "\ " . "error connecting: " . $error; } } catch(PDOException $e) { $mail_Body .= "\ " . "Exception: " . $e->getMessage(); }