SQLite Forum

Select Query will not work in PHP program
Login

Select Query will not work in PHP program

(1) By rp (pizzipie) on 2020-04-23 19:04:34 [link] [source]

I have created a PHP program to generate INSERT queries. However the code to actually insert the record into the database will not work. Attached is the full process. The query produced works just fine if executed in 'sqlitebrowser'.

Hope someone can solve this problem for me as it has happened before and I don't know the cause. Thanks in advance for help.

R

//Thu Apr 23, 2020 11:30am

// ============================ database ========================

// From SQLite - PHP INSERT Operation // https://www.tutorialspoint.com/sqlite/sqlite_php.htm

   class MyDB extends SQLite3 {
  function __construct() {
  	global $database;
     $this->open($database); // optumRx.db
  }

}

$db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully
"; }

// =================================================================

	$str2.= "'".$recArray['Cost'][$i]."',";           // main part of script dynamicaly building the query
	$str2.= "'".$recArray['ReFills'][$i]."');";
	$query=$str.$str2;
	fwrite($fh, $query."\n\n");                  // write query to log 
	fwrite($fh, "\n  ========================================================\n\n");
	insertRecord($query);                        // call to function to INSERT query to database 
	$str2=" VALUES( ";                           // begin next record
}	

// ========================= functions ==========================

// Taken From SQLite - PHP INSERT Operation and turned into funtion insertRecord($sql) // https://www.tutorialspoint.com/sqlite/sqlite_php.htm

function insertRecord($sql) { echo $sql; // for testing purposes only $ret = $db->exec($sql); if(!$ret) { echo $db->lastErrorMsg(); } else { echo "Records created successfullyn"; // keep for test only } $db->close(); }

// Run the program and this is the results: // Response Payload taken from Firefox Webtools

Opened database successfully // database opened OK

//input query

INSERT INTO rxdata (AcctFor, InvDate, OrderNo, DrugNo, Description, Cost, Refills) VALUES( 'peter','2020-04-14','123-A','q34','some bizare stuff','4573.87','1');

// If this query is run in "sqlitebrowser" as is shown above it does the insert properly.

ERROR MESSAGE: This is the problem

[Thu Apr 23 11:12:52.223509 2020] [php7:error] [pid 4918] [client 127.0.0.1:45760] PHP Fatal error: Uncaught Error: Call to a member function exec() on null in /var/www/db-sql/www/insertQ.php:87nStack trace:n#0 /var/www/db-sql/www/insertQ.php(57): insertRecord('INSERT INTO rxd...')n#1 {main}n thrown in /var/www/db-sql/www/insertQ.php on line 87, referer: http://mysql.com/www/insertQuery.html

(2) By Stephan Beal (stephan) on 2020-04-23 19:41:55 in reply to 1 [link] [source]

You need to send this to the PHP forums. This forum is only for the sqlite C library and related components, not the hundred+ 3rd-party wrappers which wrap that library.

(5) By rp (pizzipie) on 2020-04-23 22:11:48 in reply to 2 [link] [source]

Thanks, I did not realize this.

R

(3) By Tim Streater (Clothears) on 2020-04-23 20:46:17 in reply to 1 [link] [source]

How d'ye expect your insertRecord method to know what $db is? You need to pass that through to that method as a parameter:

insertRecord ($db, $query);                        // call to function to INSERT query to database


function insertRecord ($db, $sql)
     {
     echo $sql; // for testing purposes only
     $ret = $db->exec($sql);
     if(!$ret)
          {
          echo $db->lastErrorMsg();
          }
     else
          {
          echo "Records created successfully\n";
          }
     }

and why are you closing the database in your insertRecord method?

etc.

(4) By rp (pizzipie) on 2020-04-23 22:10:42 in reply to 3 [source]

" ... How d'ye expect your insertRecord method to know what $db is? ..."

$db = new MyDB(); $db is the handle to the database as shown at the top of the script.

Thanks R

(6) By Kees Nuyt (knu) on 2020-04-24 14:24:46 in reply to 4 [link] [source]

As Tim told you, $db is not known in the context of the insertRecord() function.

It is out of scope of that function. It is the same problem as the first one you posted, where $fatabase was out of scope of the MyDB class constructor.

Stephan is correct, your problem has nothing to do with SQL or SQLite. It is not even a problem of the PHP wrapper around SQLite, but purely misunderstanding of PHP variable scope.

The error message is quite clear.

 Uncaught Error: Call to a member function exec() on null 
-- 
Regards,
Kees Nuyt