Selection from database

Started by sabulba, Sep 20, 2022, 01:51 AM

Previous topic - Next topic

sabulbaTopic starter

To query data from a database, I am using the following code snippet:

date_default_timezone_set('Europe/Paris');
$date = new DateTime('-17 days');
$stml = $adapter->pdo->query("SELECT * FROM records WHERE date='$date'");

Can you guide me on adding an additional condition such that the PHONE field is not repeated if the record is dated after today? For instance, if a certain number (89515463966) has been recorded 15 days ago and just yesterday, it should not be included in the results. However, if the same number was recorded 17 days ago and not mentioned again, it can be included. Also, how can I add DISTINCT to the field 'a.number'?

On another note, it is important to ensure the security of the database by sanitizing user input before querying.
  •  

GrahamJohn

By default, MySQL developers have enabled ONLY_FULL_GROUP_BY, which can be inconvenient at times and many people choose to turn it off. However, in a specific query, you can list all the returned fields including SELECT a.* FROM records a LEFT JOIN records b ON(a.number=b.number AND b.date > a.date) WHERE a.date=(CURRENT_DATE - INTERVAL 17 DAY) AND b.date IS NULL GROUP BY 1,2,3,4 or all numbered fields 1...N, where N is the total number of fields in the records table. This may seem like a workaround, but turning off this mode requires an additional configuration change and one more request.

It is important to note that disabling ONLY_FULL_GROUP_BY can lead to unexpected results and potential security issues. Therefore, it is crucial to thoroughly test any changes made to the database configuration. Additionally, using parameterized queries can prevent SQL injection attacks and improve the overall security of the database.
  •  

JamesFC

To add an additional condition to avoid repeating the PHONE field, you can modify your SQL query as follows:

```php
$date = new DateTime('-17 days');
$dateFormatted = $date->format('Y-m-d');
$stmt = $adapter->pdo->query("
    SELECT DISTINCT a.number
    FROM records a
    WHERE a.date = '$dateFormatted'
    AND NOT EXISTS (
        SELECT 1
        FROM records b
        WHERE b.number = a.number
        AND b.date > '$dateFormatted'
    )
");
```

In this modified query, we use a subquery with `NOT EXISTS` to check if there are any records with the same phone number (`b.number`) and a later date (`b.date > '$dateFormatted'`). By adding this condition, only the phone numbers that have not been recorded after the specific date will be included in the results.

Regarding sanitizing user input, it's crucial to prevent SQL injection attacks by using prepared statements instead of directly inserting user-supplied values into the SQL query. Here's an example:

```php
$date = new DateTime('-17 days');
$dateFormatted = $date->format('Y-m-d');

$stmt = $adapter->pdo->prepare("
    SELECT DISTINCT a.number
    FROM records a
    WHERE a.date = :date
    AND NOT EXISTS (
        SELECT 1
        FROM records b
        WHERE b.number = a.number
        AND b.date > :date
    )
");

$stmt->bindValue(':date', $dateFormatted);
$stmt->execute();
```

By using prepared statements and binding the user-supplied value (`$dateFormatted`) to the `:date` parameter, you can mitigate the risk of SQL injection attacks.

Here's some additional information:

To add the `DISTINCT` keyword to the `a.number` field, you can modify the SQL query as shown below:

```php
$stmt = $adapter->pdo->prepare("
    SELECT DISTINCT a.number
    FROM records a
    WHERE a.date = :date
    AND NOT EXISTS (
        SELECT 1
        FROM records b
        WHERE b.number = a.number
        AND b.date > :date
    )
");
```

This ensures that only unique values of `a.number` are returned in the result set.

Additionally, when sanitizing user input, it's recommended to use parameterized queries with prepared statements, as demonstrated in the code snippet above. By binding the user-supplied values to named or positional placeholders (e.g., `:date`), you protect against SQL injection attacks by separating the data from the SQL logic during query execution.
  •