SELECT after INSERT and/or DELETE

[Ícaro Obregón]

Hi again,

When I execute a SELECT after an INSERT and/or DELETE I get the next message:

NOTICE: Undefined property: Database::$_query

This is the code I use:

            // Delete unselected tags
            if(count($del) > 0) {
                $db->delete('photo_tag')->where_in('id', $del)->execute();
            }

            // Insert new selected tags
            if(count($ins) > 0) {
                for($i = 0; $i < count($ins); $i++) {
                    $data = array(
                        'photo_id' =>   $id,
                        'tag_id' => $db->escape($ins[$i])
                    );

                    $last_id = $db->insert('photo_tag', $data);
                }
            }

            $photo_tags = $db->select('id, tag_id')->from('photo_tag')->where('photo_id', $id)->order_by('tag_id', 'asc')->fetch();

            $tags = array();

            foreach($photo_tags as $photo_tag) {
                $tags[] = array(
                    'relation_id' => $photo_tag['id'],
                    'tag_id' => $photo_tag['tag_id'],
                    'literal' => $literal[(int) $photo_tag['tag_id']]
                );
            }

The error message disappear if I initialize again $db between the INSERT and DELETE and the other one.

Best,

[Vivek N]

Assuming that photo_tag is the table name, the correct delete query should be

$db->delete()->from('photo_tag')->where('id', $del) -> execute();

// Produces DELETE FROM photo_tag WHERE id = '11';     // assuming that $del = 11 

Note that you are using "where_in" instead of "where".

[Ícaro Obregón]

Yes, I´m using 'where_in' because $del is an array (could be a single row array of course, but works perfectly in that case).

I replaced my query with the one provided by you, but I keep getting the error message if I didn´t initialize again with:

$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

before the SELECT query :c(

I´m developing in XAMMP for OSX with PHP 5.5.9 (if this kind of data helps in some way)

[Vivek N]

Alright, I got you now. I am going to go ahead and try to duplicate this issue at my end.

[Vivek N]

Can you paste the table schema ? I am unable to duplicate the issue from your description. For example,

$insert = ['firstname' => 'Vivek', 'lastname' => 'V' , 'email' => '[e-mail]' ] ;
$id = $db->insert('tblusers', $insert) ;
$row = $db->from('tblusers')->where('id', $id)->fetch_first();
print_r($row); 

This works fine at my end. Also tell me at which line do you get this error.

[Vivek N]

Well, I just noticed that you are getting a "NOTICE". You can ignore this error using error_reporting()

[Ícaro Obregón]

I´m setting error_reporting(0) and the message disappears (obviously!) :c)

Although this solution works, if you want to try to duplicate the issue, this is the table schema:

$sql = 'CREATE TABLE IF NOT EXISTS photo_tag (' .
            'id int(10) NOT NULL AUTO_INCREMENT, ' .
            'photo_id int(10) NOT NULL, ' .
            'tag_id int(10) NOT NULL, ' .
            'PRIMARY KEY (id)' .
            ') ENGINE=MyISAM DEFAULT CHARSET=utf8;';
$db->query($sql)->execute();

$sql = 'INSERT INTO photo_tag (photo_id, tag_id) VALUES' .
            '(2, 1), ' .
            '(1, 6), ' .
            '(1, 2), ' .
            '(1, 4), ' .
            '(2, 5), ' .
            '(2, 3), ' .
            '(1, 8), ' .
            '(2, 7), ' .
            '(2, 9);';
$db->query($sql)->execute();

And the code:

// Dummy vars for testing
$id = 1;
$del = [2];
$ins = [5, 3];

require_once 'php-mysqli-class/class.database.php';

$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

if(count($del) > 0) {
    $db->delete()->from('photo_tag')->where_in('id', $del)->execute();
}

if(count($ins) > 0) {
    for($i = 0; $i < count($ins); $i++) {
        $data = array(
            'photo_id' =>   $id,
            'tag_id' => $db->escape($ins[$i])
        );

        $db->insert('photo_tag', $data);
    }
}

// $db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

$photo_tags = $db->select('id, tag_id')->from('photo_tag')->where('photo_id', $id)->order_by('tag_id', 'asc')->fetch();

foreach($photo_tags as $photo_tag) {
    print_r($photo_tag);
}

Turning on and off the comment in line 25 (and with no specific error_reporting setting) you should get the message.

[Vivek N]

PHP throws a notice error if a variable is not set during a script execution. Though this will not affect the functions of this php class, it is recommend that you turn off the error notice.

For a development environment, I would recommend E_ALL ~ E_NOTICE

ini_set('display_errors', 'on');
error_reporting(E_ALL & ~E_NOTICE);