How to update a mysql column with php?

Question

I'm making a website to connect to MySQL, but I've this function to update a SQL column in php:

<?php
    function insert_db($table, $id, $value, $id2, $value2){
       $con = mysql_connect($server, $user_name, $password); 
       $db_found = mysql_select_db($database); 
       if ($db_found){
          mysql_query(" UPDATE ".$table." SET ".$id."='".$value."' WHERE ".$id2." = '".$value2."'); //this doesn't work!
          mysql_close($con);
       }
       else {
          print "Database not found!";
          mysql_close($con);
       }
    }
?>

But this function doesn't work! Please help me! And is there a better way of doing this instead of "mysql_query()"?

Easy way: Look at the syntax highlighting to see, what is wrong. — Sirko, Dec 01 '13 at 14:00
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). Learn about [prepared statements](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php). — Quentin, Dec 01 '13 at 14:00
I edited the code and fixed it at the same time. You should also use MySQLi uk.php.net/mysqli — transilvlad, Dec 01 '13 at 14:09
@tntu when you change the complete code of someone when you edit the question, you make the complete question useless. You might change relevant errors in the code that way. — Gerald Schneider, Dec 01 '13 at 14:09

score 1 · Answer 1 · answered Dec 01 '13 at 14:06

you are missing a closing quote " at the end of your mysql_query().
your variables $server, $user_name, $password and $database do not exist inside your function. If you set it outside the function you have to import them with global $server, $user_name, $password, $database before you can use them.
The mysql_* functions are becoming deprecated. Don't write new code with them, use mysqli_* or PDO objects.

score 1 · Accepted Answer · answered Dec 01 '13 at 14:13

You can kinda answer your own question looking at the StackOverflow syntax highlights. You're missing a closing quote in the SQL statement. As for a better way, I always put my SQL into a variable first. It helps catch these kinds of things. Also, you're not sanitizing anything here in your function. I hope you're doing something elsewhere to prevent SQL injection.

I would NOT create your DB connection inside a function. You're creating a connection, executing ONE query, and then closing it. That's a lot of overhead for one function. I would pass your connection into your function and use it like that.

function insert_db($con, $table, $id, $value, $id2, $value2){
     $sql = "UPDATE " . $table . " 
       SET " . $id . "='" . $value . "' 
       WHERE " . $id2 . " = '".$value2."'";
       mysqli_query($con, $sql);
}

How to update a mysql column with php?

2 Answers2