Trying to use bindValue to store generated ID. Saving as null in DB. Help with syntax??

[riverside96]

I'm new to php & I am trying to use the user input to generate an ID ie first 2 letters of last name + 4 rand number etc. 

I am unsure of where I'm making mistakes syntax wise & have been stuck with this issue for days & it's starting to look like I'm going to fail the module. Any help at all regarding this issue will be greatly appreciated. 

[Phi11W]

1 hour ago, riverside96 said:

... use the user input to generate an ID ie first 2 letters of last name ... 

Whilst this may be an assignment that you have been given, the principle is fundamentally wrong.  

• The unique identifier (Primary Key) of a table should never change, from the moment the record is created, right up to the moment that the record is finally destroyed. 
• People change their names.  

These two aims are incompatible, which is why so many database tables have a "surrogate", numeric identifier, the values of which just goes up and up [and up] forever. 

Furthermore, using Personal Data (i.e. part of a name) to create a [visible] identifier is distinctly questionable in these days of GDPR and similar legislation. 

 

Please use the "Code" button ("<>") when submitting code for others to look at - your image is very difficult to read. 

Your code manipulating ':email', ':lastname', etc. (line 30) is wrong. 
You should be using the POST'ed values here, not string literals that just happen to contain the names of the parameters. 

// This ... 
$email = ':email';
// ... should be ... 
$email = $_POST['email'];

 

Regards, 
   Phill  W.

 

[riverside96]

Thank you Phil. This was very informative but even with the changes I am still getting null for id in the  db. Could this line be the culprit .. 

  $stmt->bindValue(':id', [$id], SQLITE3_TEXT);

 

 

 

 

 

Edited December 21, 2021 by riverside96

[riverside96]

The above alteration as well as the removal of the brackets as follows fixed the problem

 

 $stmt->bindValue(':id', $id, SQLITE3_TEXT);

instead of 

 $stmt->bindValue(':id', [$id], SQLITE3_TEXT);

 

I am eternally grateful. I asked this question numerous places over the past few days & could not resolve the issue. 

Edited December 21, 2021 by riverside96

[Phi11W]

1 hour ago, riverside96 said:

The above alteration as well as the removal of the brackets as follows fixed the problem. 

And that would be because brackets matter.   

 

1 hour ago, riverside96 said:

$stmt->bindValue( ':id', $id, SQLITE3_TEXT );

This takes the value of the variable $id and binds it to the parameter named :id. 
 

1 hour ago, riverside96 said:

$stmt->bindValue( ':id', [ $id ], SQLITE3_TEXT );

This creates a single-element array whose only element is the value of the variable $id and tries to bind that to the parameter named :id. 

I'm guessing that either PHP doesn't know how to bind arrays to parameters or it's trying to do so, getting an error and then not showing that error to you (i.e. failing silently, which is almost always a Bad Thing). 

Do you have error reporting enabled?  Something like this: 

ini_set( 'display_errors', 1 );
ini_set( 'display_startup_errors', 1 );
error_reporting( E_ALL );

Regards, 
   Phill  W.

 

[gizmola]

The important takeaway here is that you shot yourself in the foot with not knowing basic PHP syntax.  Since you are using PDO, the manual is pretty clear I think that you are passing 

public PDOStatement::bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool

@Phi11W did a great job explaining that '[]' is acceptable array syntax, which is why it let you get away with it, but the thing you also missed is that the 3rd parameter needs to match the TYPE of the actual data you are passing.  In your case, you were passing the SQLITE3_TEXT constant, indicating that the parameter you are trying to bind is a string.  Right there, that should be the indicator to you to think that -- is this thing I'm passing as a value a string?  

At the point you removed the array brackets, you were just passing a string variable, and this explains your success.

With that said, and also just reiterating Phi11W's earlier point, the id shouldn't be a string.  Its only purpose is to guarantee uniqueness.  I don't know if this a tutorial you are following, but the way keys are generated via SQL Data Definition Language (DDL) varies by database engine.  SQLite has a feature where it will automagically generate a key for you, known as the ROWID.  If you declare the first column to be an integer, it essentially creates a mapping where you can use this name as an alias for the rowid.  Example:

 

CREATE TABLE test(id INTEGER PRIMARY KEY ASC, foo, bar);

At this point, for the test table, id would be an alias for the internal SQLite rowid.  

When you insert, you can either omit id entirely, or pass NULL/'' as a value.  This will cause SQLite to generate the unique rowid for you.

You then immediately follow up the insert with a call to:

$newId = $db->lastInsertId();

This assumes that you actually need the id at that point in order to create some related row linked by this id (ie. you are going to use it as a foreign key, or to look up the row you just added).