this content was originally posted here.

PHP MySQL vs SQLite Count Items Function

I have been writing a few functions using sqlite and mysql. There are a couple of slight differences which can be seen in this count items_function.

PHP

/* MYSQL Version */
    public function count_items() {
        $db_table_name = $this->db->real_escape_string($this->db_table_name); 
        $sql = "select count(id) as total from $db_table_name";
        $result = $this->db->query($sql);
        while($row = $result->fetch_assoc()) {
            return($row['total']);
        }
    }  

/* SQLite Version */
    public function count_items() {
        $db_table_name = $this->db->escapeString($this->db_table_name);
        $sql = "select count(id) as total from $db_table_name";
        $result = $this->db->query($sql);
        while($row = $result->fetchArray()) {
            return($row['total']);
        }
    }  

/* Combined version - which gets a bit too complex, so i left them separated */
    public function count_items() {
        // mysql version of escape
        if($this->db_type == "mysql") {
          $db_table_name = $this->db->real_escape_string($this->db_table_name); 
        }
        // sqlite version of escape
        if($this->db_type == "sqlite") {
          $db_table_name = $this->db->escapeString($this->db_table_name);
        }

        // common query
        $sql = "select count(id) as total from $db_table_name";
        $result = $this->db->query($sql);
      
        // mysql version of the loop
        if($this->db_type == "mysql") {
          while($row = $result->fetch_assoc()) {
              return($row['total']);
          }
        }
        // sqlite version of the loop
        if($this->db_type == "sqlite") {
          while($row = $result->fetchArray()) {
              return($row['total']);
          }
        }
      
    }

Demo

database file: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/db.db
comments_dbfile: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/comments.db ($db_comments)
views_dbfile: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/views.db
$dbfood_file: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/food.db
db_linx: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/linx/db/db.db
Classes Loaded:
class/answers.class.php
class/code.class.php
class/comments.class.php
class/content.class.php
class/core.class.php
class/crud.class.php
class/crumbs.class.php
class/featured.class.php
class/food.class.php
class/functions.class.php
class/images.class.php
class/import.class.php
class/links.class.php
class/questions.class.php
class/template.class.php
class/tv.class.php
class/user.class.php
class/utilities.class.php
class/views.class.php
class/widget.class.php
Class Extend Loaded: class/extends/expenses.extend.php
Class Extend Loaded: class/extends/gary_cummins_timeline.extend.php
Class Extend Loaded: class/extends/moo.extend.php
Class Extend Loaded: class/extends/osrs_item_data.extend.php
Class Extend Loaded: class/extends/osrs_slayer.extend.php
Class Extend Loaded: class/extends/osrs_slayer_monsters.extend.php
Class Extend Loaded: class/extends/searches.extend.php
Core Database Connected: expenses.db
expenses add_to_menu:
Class Extend Auto Initialized as: expenses
Core Database Connected: gary_cummins_timeline.db
gary_cummins_timeline add_to_menu:
Class Extend Auto Initialized as: gary_cummins_timeline
Core Database Connected: moo.db
moo add_to_menu:
Class Extend Auto Initialized as: moo
Core Database Connected: osrs_item_data.db
osrs_item_data add_to_menu:
Class Extend Auto Initialized as: osrs_item_data
Core Database Connected: osrs_slayer.db
osrs_slayer add_to_menu:
Class Extend Auto Initialized as: osrs_slayer
Core Database Connected: osrs_slayer_monsters.db
osrs_slayer_monsters add_to_menu:
Class Extend Auto Initialized as: osrs_slayer_monsters
Core Database Connected: searches.db
searches add_to_menu:
Class Extend Auto Initialized as: searches
$user_logged_in:
user access level:
pages:
$p1:view
$p2:code
$p3:YZsX1
$p4:php-mysql-vs-sqlite-count-items-function
$p5:
$p6:
$p:/view/code/YZsX1/php-mysql-vs-sqlite-count-items-function/
Using Template: view-code.html