www.webdeveloper.com
Results 1 to 2 of 2

Thread: Loop through a mysql database using an object

Hybrid View

  1. #1
    Join Date
    Jul 2009
    Location
    UK
    Posts
    174

    Loop through a mysql database using an object

    hi

    Some data first:

    Code:
    mysql> select * from booking;
    +----+---------------------+--------+------------+---------+
    | id | booking_date        | places | motorcycle | user_id |
    +----+---------------------+--------+------------+---------+
    | 10 | 2013-02-19 08:50:14 |      2 | auto_50    |       1 |
    | 14 | 2013-02-20 00:00:00 |      2 | auto_120   |       3 |
    | 15 | 2013-02-28 00:00:00 |      3 | gear_125   |       1 |
    | 16 | 2013-02-20 00:00:00 |      2 | auto_120   |       3 |
    +----+---------------------+--------+------------+---------+
    4 rows in set (0.00 sec)
    I have a class below. The query() method performs insert queries fine. It's when I want to retreive results through a select query that I come unstuck.



    PHP Code:
    <?php
    final class BookingMySQLi 
    {
        private 
    $mysqli;

        public function 
    __construct($hostname$username$password$database
        {

            
    $this->mysqli = new mysqli($hostname$username$password$database);

            if (
    $this->mysqli->connect_error
            {
                
    trigger_error('Error: Could not make a database link (' $this->mysqli->connect_errno ') ' $this->mysqli->connect_error);
            }

            
    $this->mysqli->query("SET NAMES 'utf8'");
            
    $this->mysqli->query("SET CHARACTER SET utf8");
            
    $this->mysqli->query("SET CHARACTER_SET_CONNECTION=utf8");
            
    $this->mysqli->query("SET SQL_MODE = ''");
        }

        public function 
    query($sql
        {
            
    $result $this->mysqli->query($sql);

            if (
    $this->mysqli->errno
            {
                
    trigger_error('Error: ' $this->mysqli->error '<br />Error No: ' $this->mysqli->errno '<br />' $sql);
                exit();
            }

            if (
    is_resource($this->mysqli)) 
            {
                
    $i 0;
                
    $data = array();

                while (
    $row $result->fetch_object()) 
                {
                    
    $data[$i] = $row;
                    
    var_dump$data);
                    
    $i++;
                }

                
    $result->close();

                
    $query = new stdClass();
                
    $query->row = isset($data[0]) ? $data[0] : array();
                
    $query->rows $data;
                
    $query->num_rows $result->num_rows;

                unset(
    $data);

                return 
    $query;    
            } else {
                return 
    true;
            }
        } 
    // query($sql)

        
    public function escape($value) {
            return 
    $this->mysqli->real_escape_string($value);
        }

        public function 
    countAffected() {
            return 
    $this->mysqli->affected_rows;
        }

        public function 
    getLastId() {
            return 
    $this->mysqli->insert_id;
        }

        public function 
    __destruct() {
            
    $this->mysqli->close();
        }



    }
    I mean, if I use:

    PHP Code:
    require './inc/database.class.php';

    $db = new BookingMySQLi('localhost','user','pass','bookings01');

    $sql "select * from booking";
    $db->query($sql); 

    in my view file, then I would expect to see the results of
    PHP Code:
    var_dump$data); 
    displayed in my browser? All I see is white space. Usually white space is associated with a PHP critical error. Well I'm working locally with errors turned on and nothing is displayed so I'm at a loss.

    I then tried a method as follows:

    PHP Code:
    public function getRows($query)
        {
            if (
    $result $this->mysqli->query($query)) 
            {

                    
    /* fetch object array */
                    
    while ($obj $result->fetch_object()) 
                {
                        
    printf ("%s %s \n<br>"$obj->places$obj->booking_date);
                    }

                    
    /* free result set */
                    
    $result->close();
            }
        } 
    and with a call to:

    PHP Code:
    $db->getRows($sql); 
    in my view file, sure enough the browser displays:

    Code:
    2 2013-02-19 08:50:14
    4 2013-02-20 00:00:00
    3 2013-02-28 00:00:00
    but I'm duplicating code, and I already have
    Code:
    $result->fetch_object()
    functionality built in from my
    Code:
    public function query($sql)
    method.

    I'd like to utilise the
    Code:
    public function query($sql)
    method to perform INSERT, UPDATE, SELECT & DELETE queries.

    any ideas?

    help
    Success is the ablility to go from one failure to another without loss of enthusiasm.
    -- Sir Winston Churchill.

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    prepend your script with:

    PHP Code:
    <?php
    ini_set
    ("display_errors"1);
    error_reporting(E_ALL);
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles