Reserved keyword conflict in MySQL. about codeigniter-aauth

[thedmeyer]

I suggest changing the 'read' column to 'date_read' and make it a datetime type.

This would do the following:

• Show when the PM was read.
• If it's null, then the PM hasn't been read.
• Cleaner implementation, in my opinion.

If you like this idea, I can draft a PR for it.

[REJack]

👍 good idea

[REJack]

i can write this in my PR too, should i?

[tswagger]

I concur that this is a good idea. I would take it a step further and change the "date" column to "sent" or "date_sent" for consistency.

[thedmeyer]

Sounds good. Eventually, we can add on to the pm feature to allow support for group messaging

[thedmeyer]

@tswagger, @REJack did as you said. https://github.com/REJack/CodeIgniter-Aauth/commit/a11bdbe5800b9d85d4bb01df08acc675e540cb26

[REJack]

it was a briliant idea 😃

[thedmeyer]

👍 Is there a way to modify the pm system to allow for multiple recipients?

Say an admin wants to message all the users on their website...

[thedmeyer]

It would be a tricky implementation, perhaps it's best to make a site-wide announcement feature

(something separate from pm's)

[REJack]

idk i could write really easy :P but i would remove the error if the user is banned

[REJack]

i would write it like this 😄
$receiver_id can be int or array

public function send_pm( $sender_id, $receiver_id, $title, $message ){
    if(!is_array($receiver_id)){
        $receiver_id = array($receiver_id);
    }

    $query = $this->aauth_db->where('id', $sender_id);
    $query = $this->aauth_db->get( $this->config_vars['users'] );

    if ( $query->num_rows() < 1 ){
        $this->error($this->CI->lang->line('aauth_error_no_user'));
        return FALSE;
    }

    foreach ($receiver_id as $receiver) {
        if ( $sender_id == $receiver ){
            $this->error($this->CI->lang->line('aauth_error_self_pm'));
            continue;
        }
        $query = $this->aauth_db->where('id', $receiver);
        $query = $this->aauth_db->get( $this->config_vars['users'] );

        if ( $query->num_rows() < 1 ){
            $this->error('UserID: '.$receiver.' '.$this->CI->lang->line('aauth_error_no_user'));
            continue;
        }

        $data = array(
            'sender_id' => $sender_id,
            'receiver_id' => $receiver,
            'title' => $title,
            'message' => $message,
            'date_sent' => date('Y-m-d H:i:s')
        );

        $query = $this->aauth_db->insert( $this->config_vars['pms'], $data );
    }
    return TRUE;
}

[thedmeyer]

From your implementation, I see you are creating multiple messages in the pms table. I feel this is unsatisfactory from a big data standpoint. It's bloat / extra fat in the DB...

[REJack]

than its to tricky with the readed status for any receiver

[thedmeyer]

Better Implementation?

What about creating two tables? One that stores aauth_pms and another that holds the recipients with their respective date_read.

Call these two tables:

• aauth_pms
• aauth_pm_receivers

The table storing the pm would contain:

• id
• sender_id
• title
• message
• date_sent

The table holding recipients would contain:

• pm_id
• receiver_id (user's id)
• date_read

With this implementation then list_pms() could run the SQL query GET * FROM 'aauth_pm_receivers' WHERE 'receiver_id' = $user_id

[REJack]

that sounds good

[emreakay]

i think it is solved 👍
Thank you @REJack and @Manebit @tswagger