Browsing the MySQL category

mysql update field to random string

I needed to update all fields in my database to a random value.
RAND() returns a random number between 0 and 1
MD5() creates an alpha-numeric hash, that is 32 characters.
I ended up with:

select MD5(RAND())

…You can add a SUBSTRING() on there to ensure you get only a certain number of characters.

Read the article

Web Development for Intermediate Programmers – with PHP

I’ve written a ~300 page book on PHP!  While I do go over the basics, I go over “common” concepts of database manipulation and Ajax. I’ve also sprinkled in some User Interface and User Experience comments:

Read the article

Migrating databases to conform to new structure didn’t work

I have a database application at work that I recently upgraded.  There was a new database structure, in a new database, and I had to move the existing tables into the new database (and most importantly, transfer the data).  I was using an ODBC connection in MS Access connecting to a Microsoft SQL Server database, but this really applies to all databases.

The primary key is an auto-number. I was able to append the “old” data into the new database, and allowed the auto-number field to properly increment.  The problem came when I did this for ALL tables in the database; and there were tables that were linked / relied on each other (even though a Foreign Key wasn’t explicitly stated – maybe that could have solved this whole problem?).
I did some spot-checks to verify that the data was correct: it wasn’t.  The problem was that I had deleted some of the rows, therefore throwing-off the auto-number fields.  For example, in my old database I had rows with an ID of 1,2,3,5 … in the new database, it came across as 1,2,3,4.
The solution was to create an additional (temporary) field in my table for what the old ID was.  I let the table pre-populate correctly with the auto-numbers.  Then in my other table (where the foreign key was), I did an update query to that extra temporary field.
Lesson Learned: when migrating a database and the primary/foreign key is an auto-number, check that some entries weren’t previously deleted; if so, it will make your data incorrect.
Possible ways around this: set up official foreign keys (which I didn’t do).  Also, maybe I shouldn’t have “deleted” the rows, but just set a “status” field to active/inactive.

Read the article

import large excel file into mysql / navicat mysql invalid variant operation

I have a lot of data in Excel that I needed to import into a database.  MS Access wasn’t powerful enough, so I figured I’d put a MySQL database on my computer.  I downloaded the NaviCat program, and got an error: “navicat mysql invalid variant operation” .  A quick Google search yielded this site:
It turns out that even though my Operating System (Windows 7) is running on a 64-bit machine, the version of MS Office I had was a 32-bit version.  I downloade the 32-bit Navicat version, and was able to bypass this error.  However, I still had trouble, so I ended up with a MS SQL Server solution for importing Excel files.

Read the article