CODEWITHSUNDEEP
phpmysqlsqlsql-delete
Daniil
Daniil

Reputation: 43

SQL Update a whole table with new data

I am running a query every 15 minutes to retrieve new data from an API and store this data in my database.

So every 15 minutes I would like to store the new data in the table and get rid of the all old data in that table.

I am currently using the following method:

$sql = "DELETE FROM self_user_follower
        INSERT INTO self_user_follower (username, profile_picture, full_name, user_id, last_updated)
        VALUES (:query_username, :query_profile_picture, :query_full_name, :query_user_id, :query_last_updated)";

But it gives me the following error:

Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO self_user_follower (username, profile_picture, full_name, user_id, l' at line 2
)
query_error

Is this the best way to do it or is there a nicer and cleaner way to do this?

Upvotes: 0

Views: 61

Answers (2)

Glorfindel
Glorfindel

Reputation: 22651

If you want to put two SQL queries into one statement string, you have to

  1. separate them with a semi-colon (;)
  2. use the function mysqli_multi_query which supports multiple queries.

Unless you have a very good reason, modify your code so that it executes each query separately. MySQLi offers transaction support if you need that.*

The reason why you need a separate function is instructive; as mentioned in the docs:

An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.

*: Actually, I'm not even sure the multi_query will execute both queries in the same transaction - I'm just guessing for your reason to use a multi-query.

Upvotes: 5

Madhivanan
Madhivanan

Reputation: 13700

Multiple statements should be terminated by semi colon

Try this

$sql = "DELETE FROM self_user_follower;
        INSERT INTO self_user_follower 
(username, profile_picture, full_name, user_id, last_updated)
        VALUES (:query_username, :query_profile_picture, :query_full_name, :query_user_id, 
  :query_last_updated)";

Upvotes: 1

Related Questions