sqlite query not working with php - works in console

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
pizzipie
New php-forum User
New php-forum User
Posts: 19
Joined: Sun Aug 04, 2019 4:10 pm

Tue Dec 31, 2019 1:30 pm

I'm trying to get a list of tables from chosen sqlite database. If I query the database in a console the query returns the tables properly. If I query the database with the same query in PHP I get no return. I have attached views of what is happening. Code is below: HAPPY NEW YEAR !!

Code: Select all

<?php 
 
error_reporting (E_ALL ^ E_NOTICE);
include("../myPhpFunctions.inc");          // contains lf();   sp();  myprint; check_input(); 
//                                            shows   break   space   print_r  on browser  
 
$orgdir=getcwd();
$dir="/home/rick/DB-sql/"; 
$test=$files=array();

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

// change to working directory where the databases are and get the *.db files
 
chdir($dir); 

// get the available db file names

if (is_dir($dir)){
  if ($dh = opendir($dir)){                        // open dir and read contents
   	while (($file = readdir($dh)) !== false){
    		if(substr($file, -2)=="db") {
				$files[]=$file;
			}	
		}
	}	
}
    closedir($dh);
//  =====================================================
?>
 
 <!DOCTYPE HTML>  
 
 <html>
 <head> 
 </head>
 <body>

<form action="#" method="post">
  	    	
<p>Databases Available</p>

<!-- <select multiple="multiple" name="database[]"> to pick multiple choices-->
<select name="database" size="5">
<!-- 	<option value=""></option>   first line blank -->
<?php

	foreach($files as $file) {         
		echo "<option value=".$file.">".$file."</option>".lf();
	} // foreach 
?>

</select>

<p><input type="submit" name="submit" value="Choose Database" /></p>
</form>
<br />

</body>
</html>


<?php



if(isset($_POST['submit'])){
	
	$dbase=$_POST['database']; 

// ======================================================================
//             Connect to Database and SELECT tables for Dropdown list   
//  =====================================================================
  
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open("'".$dbase."'");  
         //$this->open('/home/rick/Desktop/sqliteDatabases/renoAZID.db');         
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database".sp().$dbase.sp()." successfully".lf();  
}

   $tablesquery = $db->query("SELECT distinct tbl_name from sqlite_master order by 1;");
    $table = $tablesquery->fetchArray();
    
myprint($table); bye(101);   <===== this is print_r with <pre> attached -- bye is exit() 
    
   echo '<pre>'.$table['tbl_name'] . '</pre>';
   $db->close();
	

} // isset $_POST

chdir($orgdir);

?> [attachment=1]open-db-ok.png[/attachment]
tbl-name-query.png
tbl-name-query.png (14.36 KiB) Viewed 186 times
Attachments
open-db-ok.png
open-db-ok.png (18.44 KiB) Viewed 186 times
User avatar
hyper
php-forum GURU
php-forum GURU
Posts: 927
Joined: Mon Feb 22, 2016 5:52 pm

Tue Dec 31, 2019 2:11 pm

Try this:

Code: Select all

SELECT name FROM sqlite_master WHERE type='table'
It's not a good idea to expose your table information to users.
pizzipie
New php-forum User
New php-forum User
Posts: 19
Joined: Sun Aug 04, 2019 4:10 pm

Tue Dec 31, 2019 9:36 pm

Sorry,

Does not work.
User avatar
hyper
php-forum GURU
php-forum GURU
Posts: 927
Joined: Mon Feb 22, 2016 5:52 pm

Wed Jan 01, 2020 4:11 am

It works for me fine:

Code: Select all

<?php

$dbpath = '/home/rick/Desktop/sqliteDatabases/renoAZID.db';

$database = new PDO('sqlite:'.$dbpath,null,null,[
      PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   => FALSE
    ]);
// Since there is no user input involved with the query, there is no need to prepare the statement first
$tables = $database->query("SELECT name FROM sqlite_master WHERE type = 'table'");
echo '<ul>';
while ($table = $tables->fetch()){
  echo '<li>' , $table['name'] , '</li>';
}
echo '</ul>';
pizzipie
New php-forum User
New php-forum User
Posts: 19
Joined: Sun Aug 04, 2019 4:10 pm

Thu Jan 02, 2020 3:17 pm

Thanks hyper,

As you probably guessed I am very new to Sqlite. Mostly I have worked with MySql but I am trying to get into the less complicated dbase. Anyway, The sites I have looked at have not shown PDO access. More like:

Code: Select all

<?php

$dbase='linuxQuest.db';
$db = new SQLite3($dbase);

//$db = new SQLite3('test.db');

$res = $db->query("SELECT * FROM show");

$col1 = $res->columnName(1);
$col2 = $res->columnName(2);

$header = sprintf("%-10s %s\n", $col1, $col2);
echo $header;

while ($row = $res->fetchArray()) {

    $line = sprintf("%-10s %s\n", $row[1], $row[2]);
    echo $line;
}

?>


I have not used PDO at all. I have seen a couple of tutorials that have shown how to use PDO for Sqlite but they did not cover the code you have shown me at all

$database = new PDO('sqlite:'.$dbpath,null,null,[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => FALSE
]);]
However, In looking at various tutorials I have discovered "SQLite3::escapeString($str)" which , the use of, allowed my program to work.
Post Reply