Phpmysql novice to ninja: How to add more generic code to the databaseTable

PHP
,
1

@TomB How can I fit in an updateWhere() function to work with the save() function. Am writing a program that has to update a particular column in the database and I created the updateWhere() function with this code

private function updateWhere($column, $value){
        $query = 'UPDATE `' . $this->tableName . '` SET ';
        $query .= '`' . $column . '` = :value';

        $parameters = [':value' => $value];

        // Set the primaryKey variable
        $value['primaryKey'] = $value[$this->primaryKey];

        $fields = $this->processDates($value);

        $this->query($query, $fields);
    }

Meanwhile this is the save function

public function save($record){
        $entity = new $this->className(...$this->constructorARgs);

        try{
            if($record[$this->primaryKey] == ''){
                $record[$this->primaryKey] = null;
            }
            $insertId = $this->insert($record);

            $entity->{$this->primaryKey} = $insertId;
        }catch(\PDOException $e){
            $this->update($record) ?? $this->updateWhere();
        }

        foreach($record as $key => $value){
            if(!empty($value)){
                $entity->$key = $value;
            }
        }

        return $entity;
    }

Now, Is it possible to make it work with the save function and if it is not, how can I go about it.

Thank you for your reply in adv ance

2

Hi,

From a performance perspective, this makes sense but from an Object-Oriented Programming one, it doesn’t.

In OOP you want to deal with objects, in this case an object that represents a complete single item. An Author, A joke, A category, etc.

Using updateWhere you are no longer dealing in complete objects but making changes to the underlying data directly, you are dealing with a small part of multiple single things. This breaks encapsulation as any validation or processing that the containing object might do is bypassed. For example, you could validate the data in the joke when setting the various properties.

You are better off doing this:


$records = $table->find('column', 'value');

foreach ($records as $record) {
    $record->updateColumn = 'updateValue';
    $table->save($record)
}

This is better from an OOP perspective but marginally slower. To mitigate that, you could cache the prepared query in the query function.

3

Depending on how many results you get though might run into out of memory exceptions. There are ways around that though. Basically what you’d want is not return all results at once but query several times using OFFSET and LIMIT. Combine this with a PHP Generator and you’re good to go. Let me know if you want an example of that.

4

I will like to see an example of how to do that

5

@TomB will it be something like this

public function save($record){
        $entity = new $this->className(...$this->constructorARgs);

        try{
            if($record[$this->primaryKey] == ''){
                $record[$this->primaryKey] = null;
            }
            $insertId = $this->insert($record);

            $entity->{$this->primaryKey} = $insertId;
        }catch(\PDOException $e){
            $this->update($records); // Notice the change in the $record variable. It has changed to $RECORDS
        }
	$records = $this->tableName->find('column', 'value'); // Knowingly change it to $this->tableName which I think it should be
        foreach($records as $record){
            $record->lastActivity = NOW(); // lastActivity is the column to be updated while mysql NOW() function is what should saved inside the lastActivity column
            $this->tableName->save($record)
        }

        return $entity;
    }

Sorry for my late response

6

I’m not sure exactly what it is you’re trying to achieve but that apparoch is certainly the wrong direction.

            $this->update($records); // Notice the change in the $record variable. It has changed to $RECORDS
  1. The $records variable does not exist so this won’t work
  2. The update method expects an array or object that represents a single record, you’d need to amend the update method to update multiple records.
            $record->lastActivity = NOW(); // lastActivity is the column to be updated while mysql NOW() function is what should saved inside the lastActivity column

The DatabaseTable class in the book was designed to work with any table. This line will mean it can only work with tables that have a lastActivity column.

7

@TomB This is what am trying to achieve, for every page visited by a login user, I want to update

lastActivity column

of the user table to the current time and I think the best place to implement it is in the

Authentication class

.

The lastActivity column

will now be retrieve to know the user who are online(I will do a query for all users having a last-updated-time within the last 5 minutes) .

Always sorry for my late reponse

8

Basically it looks like this:

public function findAll() {
$limit = 100;
$offset = 0;

while (true) {
    $result = $this->query('SELECT * FROM ' . $this->table.' ORDER BY id OFFSET '.$offset.' LIMIT '.$limit);

    $rows = $result->fetchAll();

    if (count($rows) === 0) {
       return;
    }

    foreach ($rows as $row) {
        yield $row;
    }

    $offset += 100;
}

This assumes there is an id column that can be sorted on. If you this without an ORDER BY the result is undefined; it may return some rows multiple times and some rows not at all. With an ORDER BY it works.

What the code does instead of querying everything in the entire table at once, it fetches them in batches of 100, which should easily fit in memory.

9

I am struggling to see why you need the updateWhere functionality at all since you’re only updating one record at a time.

This code, using the original version of the class, will do what you want:

$user = [
    'id' => 1234,
    'lastActivity' => new \DateTime()
];
$usersTable->save($user);
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.