Welcome Guest, Not a member yet? Register   Sign In
Query builder return NULL on second iteration
#1

I have this data inside the table bllog_post_meta:

[Image: YHNSp.png]

I wrota a controller method called update which execute the addMeta function to add the record in the table above:


Code:
public function update($id)
{
    $this->db->transBegin();

    try {
        $this->addMeta($id, $meta);

        $this->db->transCommit();
    } catch (\Exception $e) {
        $this->db->transRollback();
        return redirect()->back()->with('sweet-error', $e->getMessage());
    }

    return redirect()->back()->with('sweet-success', lang('dashboard.blog.post.msg.msg_update', [$id]));
}

I have this data inside the table blog_post_meta
[color=var(--theme-link-color)][Image: YHNSp.png][/color]
I wrota a controller method called update which execute the addMeta function to add the record in the table above:
Code:
public function update($id)
{
    $this->db->transBegin();

    try {
        $this->addMeta($id, $meta);

        $this->db->transCommit();
    } catch (\Exception $e) {
        $this->db->transRollback();
        return redirect()->back()->with('sweet-error', $e->getMessage());
    }

    return redirect()->back()->with('sweet-success', lang('dashboard.blog.post.msg.msg_update', [$id]));
}
I removed the unnecessary code. The method above call addMeta which contains this structure:
Code:
protected function addMeta($postId, $meta)
{
    if (isset($meta['categories'])) {
        foreach ($meta['categories'] as $ctgId) {
            $this->postMeta->addPostMeta($postId, 'category', $ctgId);
        }
    }
}


the $meta['categories']contains the exact same values that you see in the image, $this->postMeta is an instance of the PostMetaModel, this is the method called:




Code:
public function addPostMeta(int $postId, string $key, mixed $value)
{
    // check meta existence
    $exists = $this->getMetaByPostId($postId, $key, $value, true);
    $postMeta = new PostMetaEntity();

    if (!$exists)
        $postMeta = new PostMetaEntity();

    $postMeta->post_id = $postId;
    $postMeta->meta_key = $key;
    $postMeta->meta_value = $value;

    if (!$exists) {
        var_dump($exists); // this contains null only for the 67 item
        $this->insert($postMeta);
    } else
        $this->update($postMeta->id, $postMeta);
}

before insert the meta, I check if it exists on the table calling the getMetaByPostId:


Code:
public function getMetaByPostId(int $postId, $key = '', $value = '', $single = false)
{
    $builder = $this->builder()
        ->select('*')
        ->where('post_id', $postId);

    if ($key != '') {
        $builder->where('meta_key', $key);

        if ($value) {
            $builder->where('meta_value', $value);
        }

        if ($single) {
            return $builder
                ->get()
                ->getRow();
        }

        return $builder
            ->get()
            ->getResultArray();
    }
}

the situation here is really surprising, infact, in the first iteration, so: 40 - 32 - category - 1, I correctly get the meta stored in the blog_post_meta, but when the second iteration is reached, so: 67 - 32 - category - 1

I get: 
NULL.
As you can see both records exists in the table, so why CodeIgniter query builder return NULL?
This strange behavior happen only on the second iteration, which is the latest.

Reply
#2

(This post was last modified: 04-10-2021, 08:25 PM by iRedds.)

This answer does not solve your problem. But he explains that with your code you will NEVER get a record with ID 67.

Every time you make the same request to the database
Code:
SELECT * FROM some_table WHERE post_id = 32 AND meta_key = 'category' AND meta_value = 1
And each time your query returns both records from the database, because both records match the query condition.

Next, you call the getRow() method with no arguments, which means it always returns the first value from the result.
That is, it will always be a record with ID 40.


The getRow() method will return null only in one case, when the query result is empty.

You can get the last SQL query through the BaseConnection class method:
PHP Code:
$this->db->getLastQuery() 
Reply
#3

(04-10-2021, 08:24 PM)iRedds Wrote: This answer does not solve your problem. But he explains that with your code you will NEVER get a record with ID 67.

Every time you make the same request to the database
Code:
SELECT * FROM some_table WHERE post_id = 32 AND meta_key = 'category' AND meta_value = 1
And each time your query returns both records from the database, because both records match the query condition.

Next, you call the getRow() method with no arguments, which means it always returns the first value from the result.
That is, it will always be a record with ID 40.


The getRow() method will return null only in one case, when the query result is empty.

You can get the last SQL query through the BaseConnection class method:
PHP Code:
$this->db->getLastQuery() 

Hi, thanks for you answer, I will try to replicate the app behavior step by step. When the form is submitted, I sent the `meta['categories']` array which contains this:

PHP Code:
Array ( [0] => [1] => 

then, I call the `addMeta` function which execute the following:

PHP Code:
if (isset($meta['categories'])) {
            foreach ($meta['categories'] as $ctgId) {
                $this->postMeta->addPostMeta($postId'category'$ctgId);
            }
        

as you can see I check if the categories are actually setted, if so I will insert or update them using the method `addPostMeta` that contains this code:

PHP Code:
public function addPostMeta(int $postIdstring $keymixed $value)
    {
        // check meta existence
        $exists $this->getMetaByPostId($postId$key$valuetrue);
        $postMeta = new PostMetaEntity();

        echo (string)$this->db->getLastQuery();
        var_dump($exists);
        ... 

the problem here is inside `getMetaByPostId`:


PHP Code:
public function getMetaByPostId(int $postId$key ''$value ''$single false)
    {
        $builder $this->builder()
            ->select('*')
            ->where('post_id'$postId);

        if ($key != '') {
            $builder->where('meta_key'$key);

            if ($value) {
                $builder->where('meta_value'$value);
            }

            if ($single) {
                return $builder
                    
->get()
                    ->getRow();
            }

            return $builder
                
->get()
                ->getResultArray();
        }
        ... 

These are the meta available in the table for the postId 32:

[Image: 6j4trTG.png]

based on the code above I have this result for each iteration of the loop of `addMeta`:

first iteration

> $ctgId: 1
> getLastQuery() => `SELECT * FROM `blog_post_meta` WHERE `post_id` = 32 AND `meta_key` = 'category' AND `meta_value` = '1'`
> $exists: NULL
> PhpMyAdmin result:

[Image: lZKNpLY.png]


second iteration

> $ctgId: 3
> getLastQuery() => `SELECT * FROM `blog_post_meta` WHERE `post_id` = 32 AND `meta_key` = 'category' AND `meta_value` = '3'`
> $exists: NULL


> PhpMyAdmin result:

[Image: NIeveyv.png]
so, as you can see both record exists, but for some reason CodeIgniter returns NULL in both cases. I don't know if I'm doing something wrong actually, could you please help?
Thanks
Reply
#4

Screenshots from PMA show that database queries via PMA are being executed.

Return all query results using your model and see how many there are.
Reply
#5

(04-11-2021, 06:32 AM)iRedds Wrote: Screenshots from PMA show that database queries via PMA are being executed.

Return all query results using your model and see how many there are.

if I var_dump getRow on here: I get always NULL:


PHP Code:
$builder $this->builder()
            ->select('*')
            ->where('post_id'$postId);

        if ($key != '') {
            $builder->where('meta_key'$key);

            if ($value) {
                $builder->where('meta_value'$value);
            }

            if ($single) {
                var_dump($builder->get()->getRow()); 
Reply
#6

Forget about getRow().
use
PHP Code:
$result $this->builder()
            ->
select('*')
            ->
where('post_id'$postId)
            ->
where('meta_key'$key)
            ->
where('meta_value'$value)
            ->
get()
            ->
getResultArray();
dd($result); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB