Kevin Bright Kevin Bright - 5 months ago 10
SQL Question

How to pass variable created in php to mysql database?

I'm working on an app. I've published a few apps, but I only have limited experience with PHP. This app uses a mysql database and a php script to pass data from the app to the database. I've figured out how to use POST to get data from the input fields in the app to the database, but for some reason I can't figure out how to pass a variable created in php to the database, i.e., without using POST.

The variable I'm having trouble with is a user_id variable. I'm going to create it within the registration.php script, which also passes the inputs from the app via POST. Here's the relevant portion of the code. Everything works except the

user_id
variable never makes it to the database (i.e., the column always shows '0').

EDIT: In the database, the
user_id
column is INT(11) type.

//I have a whole script prepared for creating the unique user_id, but to keep it simple for
// testing, I'm just using '0000000'.
// This part doesn't work.
$query = "INSERT INTO users (user_id) VALUES ('0000000')";
mysql_query($query);

// everything from here down works:
$query = "INSERT INTO users (username, password, email, firstname, lastname) VALUES ( :user, :pass, :email, :firstname, :lastname)";

$query_params = array(
':user' => $_POST['username'],
':pass' => $_POST['password'],
':email' => $_POST['email'],
':firstname' => $_POST['firstName'],
':lastname' => $_POST['lastName'],
);


try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {

$response["success"] = 0;
$response["message"] = "Failed to run query: " . $ex->getMessage();
die(json_encode($response));
}

Answer

mysql_query is not part of the PDO class that you use in your working code below.

Use the PDO class to execute that statement too.

$query = "INSERT INTO users (user_id) VALUES (:uid)"; 

$query_params = array(
   ':uid' => '0000000'
);


try {
$stmt   = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {

$response["success"] = 0;
$response["message"] = "Failed to run query: " . $ex->getMessage();
die(json_encode($response));
}

It's also curious why you say that you're inserting '000000' and the result is always 0 - this makes sense.