Tag: MySQL

I recently needed to take a database with separate date and time columns, convert that combined date-time data into Unix time format (i.e. seconds since the epoch [1st Jan 1970]), and add a new column to the database with that data. It took me a fair bit of googling to get it all working, so here’s my notes.

Sorry this isn’t really in an article format – but I need this written down and available for the future… Also, I wrote a small Java app to read the separate date and time text files and spit out a timezone-correct unixtime text file, which I then added to a spreadsheet containing just the the primary key rows and exported it as tab-separated values suitable for MySQL (or really, MariaDB in this case) import.

/**
* Note: For this project to work the mysql.data.dll reference has to be added to the project by Right-clicking
* on References then choosing Add Reference (in the Solution Explorer pane), then clicking on the Browse tab
* and selecting the file mysql.data.dll - which you should first copy into the project folder along with the
* other .dll files extracted from the mysql-connector-net-6.5.4-noinstall.zip file from:
* http://dev.mysql.com/downloads/connector/net/#downloads
*
* When getting the Connector/.NET be sure to pick the ".NET/Mono" platform and not just "Microsoft Windows", also
* you likely want to target .Net 4.0 onwards (not .NET 2.0 onwards) so once you've extracted the connector archive
* be sure to copy the dlls from the "v4" folder and not the "v2" folder!
**/usingSystem;usingMySql.Data.MySqlClient;usingMySql.Data.Types;namespace SimpleMySQLConnector
{class Program
{staticvoid Main(string[] args){string host ="127.0.0.1";// The IP address 127.0.0.1 is the same as "localhost" - it just means "this machine"string database ="testdb";// If this database doesn't already exist we'll create itstring user ="root";// Default WampServer MySQL usernamestring password ="";// Default WampServer MySQL password (no password!)// Create a provider string from our detailsstring connectionString ="Data Source="+ host
+";Database="+ database
+";User ID="+ user
+";Password="+ password;// Create a new connection to the database (this doesn't actually connect yet - we have to call .Open for that!)
MySqlConnection dbConn =new MySqlConnection(connectionString);// Try to actually open the connection to the DBMS and databasetry{
Console.WriteLine("Attempting to connect to: "+ database +"@"+ host +"...");
dbConn.Open();}// Can't connect to the database? Then let's try creating it...catch(MySqlException e){
Console.WriteLine(e.Message);
Console.WriteLine("Could not connect to: "+ database +" - attempting to create it...");try{// Specify a connection string which doesn't mention the database we're connecting to and try to open// the connection. If this fails there's a problem with the DBMS and we're going to be forced to quit.string noDBConnString ="Data Source="+ host +";User ID="+ user +";Password="+ password;
dbConn.ConnectionString= noDBConnString;
dbConn.Open();// Assuming we can connect to the DBMS at all, attempt to create the databasestring sql ="CREATE DATABASE "+ database +"; USE "+ database;
MySqlCommand cmd =new MySqlCommand(sql, dbConn);
cmd.ExecuteNonQuery();// Create a "users" table which contains two fields: ID which is an Int, and name which is a VARCHAR(20)
cmd.CommandText="CREATE TABLE users(ID INT, name VARCHAR(20))";
cmd.ExecuteNonQuery();
Console.WriteLine("Database and tables created successfully!");}// Can't create the database and/or table? Not much else for it but to quit out...catch(MySqlException e2){
Console.WriteLine(e2.Message);
Console.ReadLine();
Environment.Exit(-1);}}// Get a valid integer from the userbool gotValidInt =false;int idNumber =0;while(!gotValidInt){// Get some input from the user
Console.WriteLine("Please enter a ID number:");string idNumberString = Console.ReadLine();int idNumberInt;if(int.TryParse(idNumberString, out idNumberInt))// Try to parse the string as an integer{
idNumber = idNumberInt;// Assign the successfully-converted-to-int value to our idNumber
gotValidInt =true;// Set our flag to say we got a valid int so can leave the while loop!}else{
Console.WriteLine("Not an integer!");}}// Get a user name from the user
Console.WriteLine("Please enter a user name:");string name = Console.ReadLine();// Use a prepared statement to insert the values we just got from the user into our databasetry{string sql ="INSERT INTO users(ID, name) VALUES (@idValue, @nameValue)";
MySqlCommand cmd =new MySqlCommand(sql, dbConn);
cmd.Prepare();
cmd.Parameters.AddWithValue("@idValue", idNumber);// Substitute in the idNumber value for @idValue
cmd.Parameters.AddWithValue("@nameValue", name);// Substitute in the name value for @nameValue
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine("Data insertion successful!");
Console.WriteLine();}catch(MySqlException e){
Console.WriteLine(e.Message);}// Display all the entries in the "users" table
Console.WriteLine("---- Table Contents ----");try{// Create a SQL query which can be executed on our database connectionstring sqlQuery ="SELECT * FROM users";
MySqlCommand cmd =new MySqlCommand(sqlQuery, dbConn);// Create an object to hold the results of our query and run the query// Note: The MySqlDataReader object is a read-only onject which allows you to quickly// get records out of a database table. It cannot be used to update a database table.
MySqlDataReader mysqlReader = cmd.ExecuteReader();// Display column headings
Console.WriteLine("ID\tName");// Loop through our results printing them out to the consolewhile(mysqlReader.Read()){// The first field (field 0) of the "users" table is the ID field (which is an int)int idFieldValue = mysqlReader.GetInt32(0);// The second field (field 1) of the "users" table is the Name field (which is a string)string nameFieldValue = mysqlReader.GetString(1);// Output the record to the console
Console.WriteLine(idFieldValue +"\t"+ nameFieldValue);}// Close our reader when we're done
mysqlReader.Close();}catch(MySqlException e){
Console.WriteLine(e.Message);}// Close our database connection when we're done
dbConn.Close();// Wait for the user to press a key before exiting
Console.ReadKey();}// End of Main method}// End of class}// End of namespace

I was helping a student with a bit of project work the other day and had to create a properly formatted Date object from input provided by some dropdown menus, so in the spirit of only ever solving the same problem once, this is how you go about it… I’ll use MySQL for this example, but if the DATEs or DATETIMEs or whatever you’re using have a slightly different format in your DBMS of choice, then the principle’s the same to generate a compatible object.

MySQL Time Formats

The MySQL DATE type has the format YYYY-MM-DD, so for example today (or at least when I’m writing this post) would be 2011-08-19.

The MySQL DATETIME type has the format YYYY-MM-DD HH:ii:SS, so if it’s 9:36am and 53 seconds into the 19th of August 2011, that would be 2011-08-19 09:36:53.

The formatting of the numbers (i.e. the sequence of digits) is super important, but the dashes and colons are optional. This means that when you’re creating a PHP object to represent a DATE or DATETIME, you could create an object which contains 2011-08-19 or 20110819 etc. and MySQL would accept them as the exact same thing.

Constructing a suitable object in PHP

To create our PHP object, we’re going to use the PHP date and mktime functions. For this example I’m just going to create some variables which hold any date or time values, but in the real world you’d probably get them from a dropdown menu or calendar control.

<?PHP// Create variables to store 1:15pm and 28 seconds on the 20th of August 2011// NOTE: If you don't enclose your values with apostrophes or quotes, then things will go wrong because// single digit values need the leading zeroes, for example the 08 below will turn into 8, and then// the whole thing's broken. See the bottom of this post for an alternative padding method for numbers.$theYear='2011';// YYYY$theMonth='08';// MM$theDay='20';// DD$theHour='13';// HH$theMinute='15';// ii$theSecond='28';// SS// Create an object suitable for insertion into a MySQL DATE field// The date 'YmD' parameters mean: year as YYYY, month as MM w/ leading zeroes, day as DD w/ leading zeroes// Full PHP date parameters can be found at: http://php.net/manual/en/function.date.php// The format of mktime is: hour, minute, second, month, day, year// Full PHP mktime parameters can be found at: http://php.net/manual/en/function.mktime.php$sqlDate=date('Ymd',mktime(0,0,0,$theMonth,$theDay,$theYear));// Create an object suitable for insertion into a MySQL DATETIME field// The date 'YmdHis' means: Ymd as above, hours as HH w/ leading zeroes, minutes as ii w/ leading zeroes, ss as seconds w/ leading zeroes$sqlDateTime=date('YmdHis',mktime($theHour,$theMinute,$theSecond,$theMonth,$theDay,$theYear));// Now you've got your DATE or DATETIME object, sling 'em in your database with something like this...$dbConn=mysql_connect("localhost","root","") or die("It is a good day to die!: ".mysql_error());// Default WAMP settings// Choose the database to work withmysql_select_db("MyLovelyDatabase",$dbConn) or die("It is a good day to fail to connect to your DB!: ".mysql_error());// Create the insert statement$sqlStatement="INSERT INTO TestTable (someDate, someDateTime) VALUES ('$sqlDate', '$sqlDateTime')";// Execute the statement$result=mysql_query($sqlStatement,$dbConn);// Display result of the statement. If this is 1 then the insertion was successful, otherwise it wasn'techo"Statement executed and gave the following result: ".$result;// Shut down the connection to the databasemysql_close($dbConn);?>

Which when we then take a look at the database shows us:

Result!

Automatically padding out numerical data with leading zeroes

If you need to work directly with numerical data (as opposed to strings), then you can use the following function (written by matrebatre over on the str_pad page) to pad out your data:

<?PHPfunction padNumber($theNumber,$numDigits){// str_pad format: value, desired number of characters, what to pad with, where to place the paddingreturnstr_pad((int)$theNumber,$numDigits,"0", STR_PAD_LEFT);}$value=5;$paddedValue= padNumber($value,1);echo$paddedValue."<br/>";// 5$paddedValue= padNumber($value,2);echo$paddedValue."<br/>";// 05$paddedValue= padNumber($value,3);echo$paddedValue."<br/>";// 005?>

I created a guide to using MySQL Connector/C++ for a class a while back which showed a not-too-pretty way to put data into a DB, and I created a second part using prepared statements, locking, rollbacks and all that other good stuff – then I never posted it! Well, no more ;)