How to insert values in a PHP array to a MySQL tab

问题:

I am creating a registration form, with fields for Name, Email and Phone Number. To check the validity of user input, I have a function validate_input() that returns an array $arr containing input entered by the user (if user input is valid). $arr is then passed to a separate function that inserts the values in arr into a MySQL table user containing fields for name, email and phone.

I initially tried the following:

$insert_query = "INSERT INTO user (name, email, phone)
                 VALUES ('$arr['name']',
                         '$arr['email']',
                         '$arr['phone']')";
$run_insert_query = mysqli_query($con, $insert_query);

But this didn't work. Some reading revealed that this is the wrong way to insert array values into the database. So I then tried the following (based on the accepted answer here):

$escaped_values = array_map('mysql_real_escape_string', array_values($arr));
$arr_values  = implode(",", $escaped_values);

$insert_query = "INSERT INTO user (name, email, phone) VALUES ($arr_values)";
$run_query = mysqli_query($con, $insert_query);

But this didn't work either. Any ideas on how to make this work?

回答1:

You just forgot to set the quotes right. Here's the correct syntax:

$insert_query = "INSERT INTO user (name, email, phone) 
                 VALUES ('".$arr['name']."',
                         '".$arr['email']."', 
                         '".$arr['phone']."')";

回答2:

In your insert dml statement, strings and formatted dates must be quoted in single quotes. Since mysql will attempt to dynamically cast strings to integers or floats, you could try quoting everything - but even if it you get it working, this will bite you back when you least expect it. Your second block of code is also dependent on the data being presented in the order you expect. Typically this is unlikely to change, but it is bad programming.

You can't mix mysqli_ and mysql_ functions. Read the errors and warnings php is telling you about.

Assuming that your validate_input() function also invokes mysql[i]_real_escape_string() on the data to prevent sql injection (also bad programming practice - you should validate input and escape output) then you just need to fix the semantics of your code in the first query: associative array indices should not be quoted when they are already enclosed in "....":

$insert_query = "INSERT INTO user 
 (name, email, phone)  
 VALUES ('$arr[name]', '$arr[email]', '$arr[phone]')";
$run_insert_query = mysqli_query($con, $insert_query);