Data not being inserted into table

[phppup]

I am trying to insert from a form into a table with an auto increment primary id and a default TIMESTAMP
 

$sql = "INSERT INTO $table (a,b,c,TIMESTAMP) VALUES (NULL, ?,?,?,? )";

if($stmt = mysqli_prepare($conn, $sql)){
    mysqli_stmt_bind_param($stmt, 'ssss', $w,$x,$y,$z);

mysqli_stmt_execute($stmt);

echo "Records inserted.<br>";
} else{
    echo "ERROR". mysqli_error($conn);;

$last_id = mysqli_insert_id($conn);
    echo "Last inserted ID is: " . $last_id;

I have eliminated all ERRORS that indicated
Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

However, I am still receiving the same code generated message

Records inserted.
Last inserted ID is: 0

No data is visible when I open the actual database and view the table.

[ginerjm]

When you do an insert you don't reference the auto-increment field at all.  You have specified 4 table columns but 5 values.  Wrong.

[phppup]

I am trying to insert from a form into a table with an auto increment primary id and a default TIMESTAMP

$sql = "INSERT INTO $table (a,b,c,TIMESTAMP) VALUES ( ?,?,?,? )";

if($stmt = mysqli_prepare($conn, $sql)){
    mysqli_stmt_bind_param($stmt, 'ssss', $w,$x,$y,$z);

mysqli_stmt_execute($stmt);

echo "Records inserted.<br>";
} else{
    echo "ERROR". mysqli_error($conn);;

$last_id = mysqli_insert_id($conn);
    echo "Last inserted ID is: " . $last_id;

I have eliminated all ERRORS that indicated
Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

However, I am still receiving the same code generated message

Records inserted.
Last inserted ID is: 0

No data is visible when I open the actual database and view the table.

 

@ginerjm Actually made that correction after the posting, but same issue persists. But thanks.

PS: checked all db connectivity and everything seems to be fine from that side.

 

Edited December 7, 2022 by phppup
forgot item

[Barand]

If your table structure is like this

a int not null auto_increment primary key,
b varchar(50),
c varchar(50)
d timestamp not null default CURRENT_TIMESTAMP

then you only need to insert values for b and c

$stmt = $db->prepare("INSERT INTO mytable (b, c) VALUE (? ?);

[mac_gyver]

you need error handling for ALL the database statements that can fail - connection, query, prepare, and execute. you currently don't have any error handling for the execute call, which would be telling you why the query is failing at execution time.

the easiest way of adding error handling for all the database statements, without adding logic at each one, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) this will allow you to remove the existing error handling logic, since it will no longer get executed upon an error. the exception to this rule is when inserting/updating duplicate or out of range user submitted data. in this case, you would catch the exception in your code, test if the error number is for something that your code is designed to handle,  and setup a unique and helpful error message letting the visitor know what was wrong with the data that they submitted. for all other error numbers, just rethrow the exception and let php handle it.

to enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection, and remove the existing error handling logic you have now -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

 

[phppup]

@Barand  Already got that resolved.  There are 5 columns:  ID, a, b, c, and TIMESTAMP (which has a default)

@mac_gyver I replaced my

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

with your suggested code.  After doing that, the form page stalled at a midpoint while loading.

[ginerjm]

You added error handling for the db function calls but you should leave in the php error handling to point out syntax errors in your code until you move it into production.  And I don't think you need the display startup errors setting if you are not actually Starting Php itself.

Basically:

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

Reset the 2nd line to '0' when you are not in development.

[phppup]

Changed the INI code to 

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

Same issue. No error information generated, but still no insertion occurring.

@mac_gyver The stalled page did not affect the form, only the portion below it where processing would be taking place.

[ginerjm]

How do you define a "stalled page"?  Either it loads or it is just a blank screen.  Not sure what a "stall" looks like.  Do you get a 500 error at least? (or is that a 400?)

[phppup]

@ginerjm Stalled as in the top of the page loaded perfectly, but other PHP lines that I had an echo'ed output during development (ie: print_r or echoes from a function) simply did not display beyond a certain point; as if the code simply stalled or derailed; froze or died.

Note that while it did seem suspicious, I have reviewed my code and none of these items seems like it should interfere with an INSERT statement or the table, as they are unrelated.  I'm not entirely sure of the effects of MYSQLI_REPORT_STRICT, and really don't have the time to investigate them.

[ginerjm]

So Show us that script and mark the point where it seems to be stopping.  You do realize that PHP lines do not get echoed.  Only text and values created by PHP do.

[Barand]

19 minutes ago, phppup said:

Stalled as in the top of the page loaded perfectly, but other PHP lines that I had an echo'ed output during development (ie: print_r or echoes from a function) simply did not display beyond a certain point; as if the code simply stalled or derailed; froze or died.

Have a look at the page source and see if there are any error messages.

[phppup]

UPDATE:  Took a troubleshooting approach to this and managed to obtain an INSERT using a good 'ole fashioned SELECT statement WITHOUT any binding or prepared statements.

I suppose that indicates that, as stated, all connectivity is valid.

Now I'll move forward and count my commas, quotes, and variables to try to get to the full resolution.

Unless there are more suggestions, I'll just say THANKS for everyone's help.

[mac_gyver]

4 hours ago, phppup said:

Unless there are more suggestions

just post all your current code so that someone can actually help with what it is or is not doing, rather than guessing based on snippets of information.