PHP function to update MYSQL table

Question

I am trying to create a PHP function to update the SQL table to keep from reusing code over and over. However, whenever I run the function, I always get 'SQL database error.' The odd part about this is I do not get any type of error whenever I use copy/paste this snippet of code and put it by itself, not as a function. What is wrong? Why is it behaving this way?

$servername = "localhost";
$dBUsername = "username";
$dBPassword = "password";
$dBName = "databasename";

$connection = mysqli_connect($servername, $dBUsername, $dBPassword, $dBName);


  function updateSQL ($table, $row, $value, $where, $arg) {

    $SQL = '
    UPDATE `".$table."` 
       SET `".$row."`=? 
     WHERE `".$where."`=?';
    $stmt = mysqli_stmt_init($connection);
    if (!mysqli_stmt_prepare($stmt, $SQL)) {return "SQL database error."; exit();}
    else {
      mysqli_stmt_bind_param($stmt, "ss", $value, $arg);
      if (mysqli_stmt_execute($stmt)) {return "success";}
      else {return mysqli_error($connection);}
    }
  }

Incidentally, SET sets values in columns. WHERE specifies the rows. — Strawberry, Feb 21 '21 at 01:11
you could use one of the many PHP frameworks that includes database implementations and avoid a lot of reinventing scaffolding like this. — danblack, Feb 21 '21 at 01:13
I'm not much of a php programmer however [look at this list](https://kinsta.com/blog/php-frameworks/#what-are-the-best-php-frameworks-in-2021). Most SO posts I see mention the top 4 if any. — danblack, Feb 21 '21 at 01:54

score 1 · Answer 1 · answered Feb 21 '21 at 01:11

1

The $connection is not set in this scope (unless you're using it as a global variable, which we can't see in the code and it would be a bad practice anyway).

If the updateSQL() is a method of a object, you can set $this->connection property in the constructor (or any other suitable place) and then access it in the updateSQL() method.

Otherwise, pass it as an argument to the updateSQL() function the same way as you're passing the $table, $row and other arguments.

answered Feb 21 '21 at 01:11

Petr Hejda

40,554
8
72
100

I did not include the $connection piece because I know that is not the problem. Like I said in the original post, the code works just fine if i remove the "function updateSQL ($table, $row, $value, $where, $arg) {}" portion. Something about this being in a function is what is throwing the error. – Feb 21 '21 at 01:13
1

@John Then please update your question so that it provides a full reproduceable code. – Petr Hejda Feb 21 '21 at 01:14
Done! You should be able to see it now. – Feb 21 '21 at 01:16
2

This answer is correct. You have a scope problem. You need to turn on error reporting as PHP would tell you this error is occurring. – John Conde Feb 21 '21 at 01:18

PHP function to update MYSQL table

1 Answers1