How to pass variable created in php to mysql database?

Question

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));
}

Did you get any errors? With an empty table, did your code added a new row with the value 0 for the field user_id? — s-hunter
– s-hunter, Commented Jul 8, 2016 at 1:34
Leading zeroes will be ignored because thw dataType of the column is int. So 00000000 is just 0. Try using 1 and see what happens — user5051310
– user5051310, Commented Jul 8, 2016 at 2:23
@s-hunter No errors... the table is not empty. I manually created the new column user_id using phpMyAdmin. Yes, it adds a zero to the column each time a new user is created. — Kevin Bright
– Kevin Bright, Commented Jul 8, 2016 at 12:42
@Terminus Damn it! I always screw up things like that! I'll try using 1 instead when I get home. Thanks. — Kevin Bright
– Kevin Bright, Commented Jul 8, 2016 at 12:43

Jeff Puckett · Accepted Answer · 2016-07-08 01:38:47Z

2

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.

edited Jul 8, 2016 at 1:38

answered Jul 8, 2016 at 1:33

Jeff Puckett

41.5k19 gold badges125 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Bright Over a year ago

I think you're right... Of course 000000 becomes 0 when using an INT datatype! What was I thinking. I'm going to use a 1 and see if that works when I get home. Thanks.

Kevin Bright · Accepted Answer · 2016-07-08 12:59:58Z

For anyone with the same problem, the comments and responses were right... I had two problems. First, '0000000' is treated as '0' when dealing with an INT datatype (DUH!), so of course my database was always receiving '0'. Second, mysql_query is not part of the PDO class I was using. I revised the code and now it works:

$userid = '1'; 

$query = "INSERT INTO users (username, password, email, firstname, lastname, user_id) VALUES ( :user, :pass, :email, :firstname, :lastname, :uid)";

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

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));
}

Collectives™ on Stack Overflow

How to pass variable created in php to mysql database?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related