LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
Reload this Page [SOLVED] SQLite3 - PHP Fatal error: Uncaught Error: Call to a member function exec()
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 07-09-2020, 01:57 PM   #1
pizzipie
Member
 
Registered: Jun 2005
Location: Hayden, ID
Distribution: Ubuntu 20.04
Posts: 441

Rep: Reputation: 12
SQLite3 - PHP Fatal error: Uncaught Error: Call to a member function exec()

I have been trying to sort a SQLite3 database on a 'Date' (type=text) Column. No matter what format I use "07/09/2020" or "2020-07-09" The database will not sort it accurately. Because of this I am going to try using julian days (type=real)

So in trying to Update SQLite3 database I get a Fatal error.
Don't have a clue how to interpret this or fix it.

The code below is the PHP program which will create the database, create and populate the table and attempt to update the table.

I want to run this in Firefox after this bug is fixed!

Code:
<?php
// file /var/www/rickSQL.com/public_html/renoAZID/aatest00.php
// Thu Jul 9, 11:04

error_reporting (E_ALL ^ E_NOTICE);

global $db;

   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test00.db');
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
  
"DROP TABLE IF EXISTS `testdata`";

$query=
"CREATE TABLE IF NOT EXISTS `testdata` (
	`Id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`Date`	TEXT NOT NULL,
	`Location`	TEXT NOT NULL,
	`Description`	TEXT NOT NULL,
	`Category`	TEXT NOT NULL,
	`Amount`	REAL
);"; 

   $ret = $db->exec($query);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Table created successfully\n";
   }
  

$query=
"INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/03/2019','419 Upper Blvd', 'Kitchen Sink And Faucet','appliances',438.5);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/21/2019','419 Upper Blvd','Refrigerator And BR Light','appliances',1567.55);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('05/03/2019','419 Upper Blvd','Washer Dryer','appliances',1102.1);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('05/17/2019','419 Upper Blvd','Kitchen Lights','appliances',113.67);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/01/2019','419 Upper Blvd','Kitchen Fixtures Stove - Ect','appliances',862.82);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/26/2019','419 Upper Blvd','House Cleanup','cleaning', 130);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/12/2018','419 Upper Blvd','Remove Wallpaper','demo', 650);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/19/2018','419 Upper Blvd','Remove Sinks And Toilets – Baths','demo',206.85);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/19/2018','419 Upper Blvd','Gas In Kitchen – Off-  Rm Lr Heater','demo',99);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/01/2019','419 Upper Blvd','Misc','tools',26.35);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/13/2019','419 Upper Blvd','Misc','tools',84.79);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('09/24/2019','419 Upper Blvd','Misc','tools',19.58);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/29/2019','3607 Smithfield','Service-New Rollers-Lube','maint', 208.2);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('03/01/2019','3607 Smithfield','Seasonal Inspection','maint',39.95);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/16/2020','3607 Smithfield','New Remote and Estimate for seals','maint',46.84);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/01/2020','419 Upper Blvd','Sprinkler Turn-on;  Backflow Test','landscape',65.0);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/10/2020','419 Upper Blvd','Fertilize & Weed Control','landscape','0.00');
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/21/2020','419 Upper Blvd','Fertilize & Weed Control - paid for April also','landscape',126.0);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/16/2018','3607 Smithfield','New Remote and Estimate for seals','maint', 46.84);
";

 $ret = $db->exec($query);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
//  ================================================================================================

//                                               UPDATE DATES  HERE

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

   $query="SELECT * FROM testdata;";
   
   $ret = $db->query($query);  // FATAL ERROR POINTS TO THIS
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Operation done successfully\n";

	for($i=0; $i<count($rows); $i++) {
		$val=$rows[$i]['Date'];
		$index=(int)$rows[$i]['Id']; 
		$x=explode("/",$val);                             		
		updateDb($index, $x);
		 
/*		 OUTPUT INCLUDING THE ERROR MESSAGE 

Opened database successfully
Table created successfully
Records created successfully
Operation done successfully

PHP Fatal error:  Uncaught Error: Call to a member function exec() on null in /var/www/rickSQL.com/public_html/renoAZID/aatest00.php:136
Stack trace:
#0 /var/www/rickSQL.com/public_html/renoAZID/aatest00.php(84): updateDb(1, Array)
#1 {main}
  thrown in /var/www/rickSQL.com/public_html/renoAZID/aatest00.php on line 136

*/		          
	}	

//print_r($rows); exit("Leaving Update area - Line 75\n\n");

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

 // THIS SECTION WILL NOT RUN - HELP 
     
   $query="SELECT * FROM testdata;";
   
   $ret = $db->query($query);
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Operation done successfully after update \n";

$db->close();

print_r($rows);


//  ==============================  FUNCTIONS HERE =============================


function updateDb($index, $val) {   // update database to julian dates 

$x=gregoriantojd($val[0], $val[1], $val[2]);
		
$query="UPDATE testdata SET `Date`= ".$x." WHERE `Id`= ".$index.";";

//exit($query.".... bye from line 131 \n\n");

// $query = UPDATE testdata SET `Date`= 2458577 WHERE `Id`= 1;
	
 $ret = $db->exec($query);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Dates updated successfully\n";
   }
 } // update()
 
?>
Last edited by pizzipie; 07-09-2020 at 02:08 PM.
 
Old 07-09-2020, 04:48 PM   #2
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
The ISO standard date format is Y-M-D and should sort as ASCII text so conversion to Julian is not necessary. I tested your script and the actual error is in your updatedb function i.e line

$ret = $db->exec($query);

Although you defined $db as a global variable it isn't. If you add the database descriptor as a passed parameter your script/function works. I changed it to Y-M-D.

Code:
function updateDb($index, $val, $fd) {   Convert to Y-M-D 

  $query="UPDATE testdata SET Date='".$val[2]."-".$val[0]."-".$val[1]."' where Id=".$index;		

  $ret = $fd->exec($query);

}
If you want to see how sorting works try this test from sqlite3 command line after running the php script.

.open test00db
select * from testdata order by Date desc;

Code:
sqlite> select * from testdata order by Date desc;
15|2020-10-16|3607 Smithfield|New Remote and Estimate for seals|maint|46.84
18|2020-06-21|419 Upper Blvd|Fertilize & Weed Control - paid for April also|landscape|126.0
16|2020-06-01|419 Upper Blvd|Sprinkler Turn-on;  Backflow Test|landscape|65.0
17|2020-04-10|419 Upper Blvd|Fertilize & Weed Control|landscape|0.0
13|2019-10-29|3607 Smithfield|Service-New Rollers-Lube|maint|208.2
12|2019-09-24|419 Upper Blvd|Misc|tools|19.58
11|2019-06-13|419 Upper Blvd|Misc|tools|84.79
10|2019-06-01|419 Upper Blvd|Misc|tools|26.35
4|2019-05-17|419 Upper Blvd|Kitchen Lights|appliances|113.67
3|2019-05-03|419 Upper Blvd|Washer Dryer|appliances|1102.1
6|2019-04-26|419 Upper Blvd|House Cleanup|cleaning|130.0
2|2019-04-21|419 Upper Blvd|Refrigerator And BR Light|appliances|1567.55
1|2019-04-03|419 Upper Blvd|Kitchen Sink And Faucet|appliances|438.5
5|2019-04-01|419 Upper Blvd|Kitchen Fixtures Stove - Ect|appliances|862.82
14|2019-03-01|3607 Smithfield|Seasonal Inspection|maint|39.95
8|2018-11-19|419 Upper Blvd|Remove Sinks And Toilets – Baths|demo|206.85
9|2018-11-19|419 Upper Blvd|Gas In Kitchen – Off-  Rm Lr Heater|demo|99.0
7|2018-11-12|419 Upper Blvd|Remove Wallpaper|demo|650.0
19|2018-10-16|3607 Smithfield|New Remote and Estimate for seals|maint|46.84
If you need more advanced date functions that is a different issue...
Last edited by michaelk; 07-09-2020 at 05:21 PM.
 
1 members found this post helpful.
Old 07-09-2020, 07:31 PM   #3
pizzipie
Member
 
Registered: Jun 2005
Location: Hayden, ID
Distribution: Ubuntu 20.04
Posts: 441

Original Poster
Rep: Reputation: 12
Thanks for the reply.

I set $db to global since through this process I get an error message that $db was undefined.

Where did $fd come from?

I changed the updateDb() function and other things like eliminate global $db. Here is new program if you would be so kind to look. It now works without error messages and does update the 'Date' records with 'sqlite date formatted' dates. The database will now sort properly.

Thank you!!

R

Code:
<?php
// file /var/www/rickSQL.com/public_html/renoAZID/aatest00.php
// Thu Jul 9, 17:25

// eliminated global $db;

error_reporting (E_ALL ^ E_NOTICE);

   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test00.db');
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
  
"DROP TABLE IF EXISTS `testdata`";

$query=
"CREATE TABLE IF NOT EXISTS `testdata` (
	`Id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`Date`	TEXT NOT NULL,
	`Location`	TEXT NOT NULL,
	`Description`	TEXT NOT NULL,
	`Category`	TEXT NOT NULL,
	`Amount`	REAL
);"; 

   $ret = $db->exec($query);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Table created successfully\n";
   }
  

$query=
"INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/03/2019','419 Upper Blvd', 'Kitchen Sink And Faucet','appliances',438.5);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/21/2019','419 Upper Blvd','Refrigerator And BR Light','appliances',1567.55);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('05/03/2019','419 Upper Blvd','Washer Dryer','appliances',1102.1);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('05/17/2019','419 Upper Blvd','Kitchen Lights','appliances',113.67);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/01/2019','419 Upper Blvd','Kitchen Fixtures Stove - Ect','appliances',862.82);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/26/2019','419 Upper Blvd','House Cleanup','cleaning', 130);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/12/2018','419 Upper Blvd','Remove Wallpaper','demo', 650);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/19/2018','419 Upper Blvd','Remove Sinks And Toilets – Baths','demo',206.85);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('11/19/2018','419 Upper Blvd','Gas In Kitchen – Off-  Rm Lr Heater','demo',99);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/01/2019','419 Upper Blvd','Misc','tools',26.35);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/13/2019','419 Upper Blvd','Misc','tools',84.79);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('09/24/2019','419 Upper Blvd','Misc','tools',19.58);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/29/2019','3607 Smithfield','Service-New Rollers-Lube','maint', 208.2);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('03/01/2019','3607 Smithfield','Seasonal Inspection','maint',39.95);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/16/2020','3607 Smithfield','New Remote and Estimate for seals','maint',46.84);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/01/2020','419 Upper Blvd','Sprinkler Turn-on;  Backflow Test','landscape',65.0);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('04/10/2020','419 Upper Blvd','Fertilize & Weed Control','landscape','0.00');
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('06/21/2020','419 Upper Blvd','Fertilize & Weed Control - paid for April also','landscape',126.0);
INSERT INTO `testdata` (Date,Location,Description,Category,Amount) VALUES ('10/16/2018','3607 Smithfield','New Remote and Estimate for seals','maint', 46.84);
";

 $ret = $db->exec($query);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
//  ================================================================================================
//                                               UPDATE DATES  HERE
//  ================================================================================================ 

   $query="SELECT * FROM testdata;";
   
   $ret = $db->query($query);
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Operation done successfully\n";

	for($i=0; $i<count($rows); $i++) {
		$val=$rows[$i]['Date'];
		$index=(int)$rows[$i]['Id']; 
		$x=explode("/",$val);                             		
		updateDb($index, $x, $db); // added $db to parameters 16:18
		 
/*		 OUTPUT INCLUDING ERROR MESSAGE 

MY FATAL MESSAGE - DOES NOT CHANGE DATABASE
I USE SQLITEBROWSER TO CHECK

Opened database successfully
Table created successfully
Records created successfully
Operation done successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
1 Dates updated successfully
Selected 19 Records. Operation done successfully after update 


*/		          
} // for	


//  =================================================================
//                        OUTPUT HERE
//  =================================================================

    unset($rows); // clear out selection from previous
     
   $query="SELECT * FROM testdata ORDER BY `Date` desc;";
   
   $ret = $db->query($query);
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Selected ".count($rows)." Records. Operation done successfully after update \n\n";

$db->close();

echo "OUTPUT of SELECT query ORDER BY Date desc \n\n";
echo "Rec   Id     Date\n\n";

for ($i=0; $i<count($rows); $i++) {
	echo $i." - ".$rows[$i]['Id'].",   ".$rows[$i]['Date']."\n";

}

//  ============================================================================
//                                FUNCTIONS HERE
//  ============================================================================ 


function updateDb($index, $val, $db) {   // update Dates to SQLite3 format 

  $query="UPDATE testdata SET Date='".$val[2]."-".$val[0]."-".$val[1]."' where Id=".$index;			

//exit($query.".... bye from line 130 \n\n");
// test output from exit - UPDATE testdata SET Date='2019-04-03' where Id=1;

 $ret = $db->exec($query);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Date updated successfully\n";
   }
 } // updateDb()
 
?>
Last edited by pizzipie; 07-09-2020 at 07:48 PM.
 
Old 07-09-2020, 07:51 PM   #4
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
For round one I did not use the updatedb function. This does the same thing i.e converts the date from M-D-Y to Y-M-D
Code:
//  ================================================================================================
//                                               UPDATE DATES  HERE
//  ================================================================================================ 
... 

	for($i=0; $i<count($rows); $i++) {
	   $val=$rows[$i]['Date'];
           $index=(int)$rows[$i]['Id']; 
	   $x=explode("/",$val);                             	I	
  	   //updateDb($index, $x);
           $query="UPDATE testdata SET Date='".$x[2]."-".$x[0]."-".$x[1]."' where Id=".$index;
           $ret = $db->exec($query);
}
For round two I passed the database descriptor which is what I was referencing in my other post.
Code:
for($i=0; $i<count($rows); $i++) {
	   $val=$rows[$i]['Date'];
           $index=(int)$rows[$i]['Id']; 
	   $x=explode("/",$val);                             	I	
  	   updateDb($index, $x, $db);
           //$query="UPDATE testdata SET Date='".$x[2]."-".$x[0]."-".$x[1]."' where Id=".$index;
           //$ret = $db->exec($query);
}

function updateDb($index, $val, $fd) {   // update database to Y-M-D 

  //$x=gregoriantojd($val[0], $val[1], $val[2]);
  $query="UPDATE testdata SET Date='".$val[2]."-".$val[0]."-".$val[1]."' where Id=".$index;		
  $ret = $fd->exec($query);

}
Last edited by michaelk; 07-09-2020 at 07:52 PM.
 
Old 07-09-2020, 08:11 PM   #5
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
I see that it is now working.

FYI if you do not know how to reset the sequence back to zero...
delete from sqlite_sequence where name='testdata';
 
Old 07-09-2020, 09:02 PM   #6
pizzipie
Member
 
Registered: Jun 2005
Location: Hayden, ID
Distribution: Ubuntu 20.04
Posts: 441

Original Poster
Rep: Reputation: 12
Thanks michaelk,

Now, Come the second phase where I am trying to adapt the above to run in firefox. Not so good so far. Changing aatest00.php to revDateToSqlFotmat.php using test00.db and testdata table. What is really frustrating to me is why this happens and what does it all mean. Can you enlighten me? (a little bit?).

Code:
<?php
// file /var/www/rickSQL.com/public_html/renoAZID/aatest00.php ==> adapted to firefox with revDateToSqlFotmat.php
// Thu Jul 9, 11:04 rev. 17:35 aatest00.php
// Thu Jul 9, 18:51 revDateToSqlFotmat.php using test00.db

error_reporting (E_ALL ^ E_NOTICE);

$database="test00.db";
$table='testdata';

   class MyDB extends SQLite3 {
      function __construct() {
         $this->open($database);
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully<br>";
   }
  

//  ================================================================================================
//                                               UPDATE DATES  HERE
//  ================================================================================================ 

   $query="SELECT * FROM ".$table.";";
   //exit($query."bye from line 28");
   $ret = $db->query($query);
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Operation done successfully<br>";

	for($i=0; $i<count($rows); $i++) {
		$val=$rows[$i]['Date'];
		$index=(int)$rows[$i]['Id']; 
		$x=explode("/",$val);                             		
		updateDb($index, $x, $db); // added $db to parameters 16:18
} // for	
		 
/*		 OUTPUT INCLUDING ERROR MESSAGE 

Opened database successfully

Thu Jul 09 18:41:30.284411 2020] [php7:error] [pid 5687] [client 127.0.0.1:50508]
PHP Fatal error:  Uncaught Error: Call to a member function fetchArray() on boolean in
/var/www/rickSQL.com/public_html/renoAZID/revDateToSqlFormat.php:30\n
Stack trace:\n#0 {main}\n  thrown in /var/www/rickSQL.com/public_html/renoAZID/revDateToSqlFormat.php on line 30, 
referer: http://ricksql.com/renoAZID_Frontend.html

*/		          

//  =================================================================
//                        OUTPUT HERE
//  =================================================================

   $query="SELECT * FROM ".$table." ORDER BY `Date` desc;";
   
   $ret = $db->query($query);
      while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      $rows[]=$row;
   }
   echo "Selected ".count($rows)." Records. Operation done successfully after update <br><br>";

$db->close();

echo "OUTPUT of SELECT query ORDER BY Date desc <br><br>";
echo "Rec   Id     Date<br><br>";

for ($i=0; $i<count($rows); $i++) {
	echo $i." - ".$rows[$i]['Id'].",   ".$rows[$i]['Date']."<br>";

}

//  ============================================================================
//                                FUNCTIONS HERE
//  ============================================================================ 


function updateDb($index, $val, $db) {   // update Dates to SQLite3 format 

  $query="UPDATE ".$table." SET Date='".$val[2]."-".$val[0]."-".$val[1]."' where Id=".$index;			

//exit($query.".... bye from line 130 <br><br>");
// test output from exit - UPDATE testdata SET Date='2019-04-03' where Id=1;

 $ret = $db->exec($query);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Date updated successfully<br>";
   }
 } // updateDb()
 
?>
 
Old 07-09-2020, 09:05 PM   #7
individual
Member
 
Registered: Jul 2018
Posts: 315
Blog Entries: 1

Rep: Reputation: 233Reputation: 233Reputation: 233
If you don't have to do it in PHP, you could do it all from the Sqlite3 client (or just call this statement once in PHP).
Code:
-- Assumes that Date is in the format of mm[SEPARATOR]dd[SEPARATOR]YYYY
UPDATE testdata SET Date=substr(Date, 7)||'-'||substr(Date, 0, 3)||'-'||substr(Date, 4, 2);
It's ugly, to be sure, but it saves a lot of code to do something relatively simple.
 
Old 07-09-2020, 11:37 PM   #8
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
Must likely problem is that need to add an absolute path to the location of your DB file and apache has to have permissions to access the directory.

You could put it in your document root directory for the short term...
Last edited by michaelk; 07-09-2020 at 11:42 PM.
 
Old 07-10-2020, 12:24 AM   #9
pizzipie
Member
 
Registered: Jun 2005
Location: Hayden, ID
Distribution: Ubuntu 20.04
Posts: 441

Original Poster
Rep: Reputation: 12
Ddatabase is opened. It is the query that fails.
 
Old 07-10-2020, 12:35 AM   #10
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=15, FreeBSD_12{.0|.1}
Posts: 6,263
Blog Entries: 24

Rep: Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194Reputation: 4194
Quote:
Originally Posted by pizzipie View Post
Ddatabase is opened. It is the query that fails.
I think the trailing semi-colon in the query string may be the problem:

Code:
 $query="SELECT * FROM ".$table.";";
Try changing to:


Code:
 $query="SELECT * FROM ".$table;
 
Old 07-10-2020, 04:35 AM   #11
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
I had thought the same thing with the semicolon but it doesn't matter. If you go back and look at the apache logs you will find two errors. The first is table not found. The second is the fetchArray error.

The reason is the new mydb function will open a new file if the specified one does not exist. In this the case success status is misleading. If you go back to your original script the db file was automatically created in the same directory. Without a path the db file is assumed to be in the apache document root directory.

As stated apache needs access permissions to the directory where the file is located if in in document root.
Last edited by michaelk; 07-10-2020 at 04:58 AM.
 
Old 07-10-2020, 10:59 AM   #12
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,856
Blog Entries: 1

Rep: Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869Reputation: 1869
The first part I don't understand is this:

"I want to run this in Firefox after this bug is fixed!"

So far, I honestly thought Firefox cannot run PHP at all.
 
Old 07-10-2020, 12:28 PM   #13
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,678

Rep: Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892Reputation: 5892
I assumed the OP was trying to run the script from a web server and therefore via firefox...

I mentioned moving the DB file to document root. However, that will not work since the web server runs as user www-data which does not have permissions to write to /var/www/html...
Last edited by michaelk; 07-10-2020 at 03:29 PM.
 
Old 07-10-2020, 07:43 PM   #14
pizzipie
Member
 
Registered: Jun 2005
Location: Hayden, ID
Distribution: Ubuntu 20.04
Posts: 441

Original Poster
Rep: Reputation: 12
Well ... The solution is below: All works now. Database will now produce sorted `Dates` properly!!

This "test" script is actually a small part of the real program in which I could not get the 'Dates' to sort properly.

I learned that the dates had to be formatted, in what I call, SQLite date format (YYYY-MM-DD). The input I had done for the actual database was many, many records therefore, I needed to convert my (MM/DD/YYYY) dates which led to my initial POST.

You, my teachers, have pointed out the mechanics of a few things so I will work on them to make the code more efficient.

Thank you all very much for your time in helping me!

R

Code:
<?php
// file /var/www/rickSQL.com/public_html/renoAZID/aatest00.php ==> adapted to firefox with revDateToSqlFotmat.php
// Thu Jul 9, 11:04 rev. 17:35 aatest00.php
// Thu Jul 9, 18:51 revDateToSqlFotmat.php using test00.db

error_reporting (E_ALL ^ E_NOTICE);

$database="renoAZID.db";
global $table;// <<=============== added global
$table='rdata';

   class MyDB extends SQLite3 {
      function __construct() {
      	global $database;// <<=============== added global
         $this->open($database);
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully<br>";
   }

code continues ......
  //
 
Old 07-10-2020, 11:46 PM   #15
SoftSprocket
Member
 
Registered: Nov 2014
Posts: 399

Rep: Reputation: Disabled
Somewhere in the hazily remember past I switched to use YYYY-MM-DD for all dates where I have the choice. They naturally sort in that format, treated as a string or number (YYYYMMDD).
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
PHP Fatal error: Uncaught Error: Class 'FarsiGD' not found. shams Programming 3 07-30-2017 08:52 AM
PHP Fatal error: Call to undefined function mysql_connect() grob115 Programming 4 11-27-2010 12:01 AM
my first activity runing happen error:Uncaught handler: thread main exiting due to un firmlyjin Linux - Mobile 1 09-03-2010 04:43 PM
Unison backup Uncaught exception Invalid_argument("String.create") uhcafigdc Linux - Software 2 05-22-2009 01:02 PM
Fatal Error - Call to undefined function : pg_pconnect() in filename.php on line xx mwpeng Linux - Newbie 1 02-26-2005 08:57 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 06:09 PM.

Contact Us - Advertising Info - Rules - Privacy - LQ Merchandise - Donations - Contributing Member - LQ Sitemap -
Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration