Jump to content

Issue with inserting data into database #2

phreak3r

By phreak3r
in MySQL Help

 Share
Go to solution Solved by phreak3r,

Recommended Posts

phreak3r Advanced Member

phreak3r

Posted
Posted

I am having another issue with inserting data into the database again. I am a bit tired at the moment, so pardon any mistakes/errors. I usually don't like to go to bed with problems unsolved.

So, upon registration, the user is taken to a page with a message informing them that their data was sent to the server, yadda, yadda confirmation of account is need.

However, when refreshing the database or the table, the newly registered user is not showing up in the database. Here's the particular piece of code that controls this (it appears to be fine to me):

	$username = mysqli_real_escape_string($conn, $_POST['username']);
	$password = mysqli_real_escape_string($conn, $_POST['password']);
	$hashed_password = mysqli_real_escape_string($conn, password_hash($password, PASSWORD_DEFAULT));
	$email = mysqli_real_escape_string($conn, $_POST['email_address']);
	$confirmation_status = 0;
	$account_open_date = date('d-m-Y H:i:m');
	$account_age = $account_open_date;

	$sqlinsert = "INSERT INTO profile0 (username, password, email_address, confirmation_status, account_open_date, account_age) VALUES ('$username', '$hashed_password', '$email', '$confirmation_status', '$account_open_date', '$account_age')";
	$result = mysqli_query($conn, $sqlinsert);

I must also note that prior to including variables account_open_date and account_age into the SQL statement, there were no errors and data was being inserted into the database with no problem. There are no errors in the error log on my end, I am pretty clueless.

phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted

Use mysqli_affected_rows() to determine if the query inserted anything, and if not log the $sqlinsert somewhere so you can try running it manually.

 

I think the query did not insert anything at all, if so I would have seen it in the database. Here is what the mysql log looks like (the apache log is error-free):

2018-01-17T23:07:04.089990Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-01-17T23:07:04.090083Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2018-01-17T23:07:04.274857Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-01-17T23:07:04.456628Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-0ubuntu0.17.10.1) starting as process 905 ...
2018-01-17T23:07:04.905887Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-01-17T23:07:04.905938Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-17T23:07:04.905944Z 0 [Note] InnoDB: Uses event mutexes
2018-01-17T23:07:04.905947Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-01-17T23:07:04.905950Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-01-17T23:07:04.905953Z 0 [Note] InnoDB: Using Linux native AIO
2018-01-17T23:07:04.925266Z 0 [Note] InnoDB: Number of pools: 1
2018-01-17T23:07:04.935852Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-01-17T23:07:04.936710Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-01-17T23:07:04.946154Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-01-17T23:07:04.954066Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-01-17T23:07:05.032222Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-17T23:07:05.339294Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-01-17T23:07:05.339343Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-01-17T23:07:05.578341Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-01-17T23:07:05.578839Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-01-17T23:07:05.578847Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-01-17T23:07:05.579286Z 0 [Note] InnoDB: Waiting for purge to start
2018-01-17T23:07:05.629441Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 2879136
2018-01-17T23:07:05.629751Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-01-17T23:07:05.656916Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-01-17T23:07:06.308865Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-01-17T23:07:06.308902Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-01-17T23:07:06.308917Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-01-17T23:07:06.308952Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-01-17T23:07:06.372336Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180117 17:07:06
2018-01-17T23:07:06.912378Z 0 [Note] Event Scheduler: Loaded 0 events
2018-01-17T23:07:06.912799Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.20-0ubuntu0.17.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-01-17T23:07:06.912828Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2018-01-17T23:07:06.912845Z 0 [Note] Beginning of list of non-natively partitioned tables
2018-01-17T23:07:07.431913Z 0 [Note] End of list of non-natively partitioned tables
2018-01-17T23:07:07.626505Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

P.S. I will try restarting the server.

phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted (edited)

I don't see any error checking in that code. Have you used mysqli_report before connecting to the db server?

 

eg

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

 

No I have/am not.

Edited by phreak3r
Barand Prolific Member

Barand

Posted
Posted (edited)

Don't guess, check for mysql errors.

 

The easiest way is to use mysqli_report() (as suggested above). The alternative is to test for errors after very query, which can mean a lot of extra code.

 

[edit] PS: if you are storing a datetime value then it should be DATETIME type

Edited by Barand
phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted (edited)

Don't guess, check for mysql errors.

 

The easiest way is to use mysqli_report() (as suggested above). The alternative is to test for errors after very query, which can mean a lot of extra code.

 

[edit] PS: if you are storing a datetime value then it should be DATETIME type

 

Like this, yeah?

<?php
$servername   = "localhost";
$database = "soapbox";
$username = "root";
$password = "1234";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
mysqli_select_db($conn, $database);

/*if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
} else {
	echo "Connection successful!";
}

if (!mysqli_select_db($conn, $database)) {
	echo " Database not selected!";
} else {
	echo " Database selected!";
}*/
?>

EDIT: Not sure what I am supposed to be looking out for in the console, with this added.

Edited by phreak3r
mac_gyver Prolific Member

mac_gyver

Posted
Posted

the reporting and display of any exceptions due to the mysqli_report(...) statement are dependent on php's error_reporting and display_errors settings.

 

do you have (and have confirmed using a phpinfo() statement) php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system?

phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted

the reporting and display of any exceptions due to the mysqli_report(...) statement are dependent on php's error_reporting and display_errors settings.

 

do you have (and have confirmed using a phpinfo() statement) php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system?

 

Uh.....no I do not think I do. Let me go and find that.

phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted (edited)

Bingo! All errors have been turned on:

[Thu Jan 18 00:51:50.905149 2018] [php7:error] [pid 13738] [client 127.0.0.1:39012] PHP Fatal error:  Uncaught mysqli_sql_exception: Incorrect datetime value: '18-01-2018 00:51:01' for column 'account_open_date' at row 1 in /var/www/html/soapbox/confirmation.php:17\nStack trace:\n#0 /var/www/html/soapbox/confirmation.php(17): mysqli_query(Object(mysqli), 'INSERT INTO pro...')\n#1 {main}\n  thrown in /var/www/html/soapbox/confirmation.php on line 17, referer: http://localhost/soapbox/signup.php

If that is not a suitable datetime value, then what is?

Edited by phreak3r
  • Like 1
phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted (edited)

YYYY-MM-DD HH:MM:SS format is.

 

See also: Mysql Date and time literals

 

Oh, I guess this will not work with MySQL then:

$account_open_date = date('d-m-Y H:i:m');

Thank You!

 

EDIT: I am still having issues, unfortunately.

Edited by phreak3r
phreak3r Advanced Member

phreak3r

Posted
  • Author
Posted

Just specify as DATETIME without any size constraint (a datetime can hold the range 1901-01-01 00:00:00 to 9999-12-31 23:59:59. RTFM Kicken gave you the link)

 

I have removed the size constraint, the error still persists.

PHP Fatal error:  Uncaught mysqli_sql_exception: Incorrect datetime value: '2018-Jan-Thu' for column 'account_open_date' at row 1 in /var/www/html/soapbox/confirmation.php:17\nStack trace:\n#0 /var/www/html/soapbox/confirmation.php(17): mysqli_query(Object(mysqli), 'INSERT INTO pro...')\n#1 {main}\n  thrown in /var/www/html/soapbox/confirmation.php on line 17, referer: http://localhost/soapbox/signup.php
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.