Follow Along as I Stumble on the Path to Learning RoR

Posts tagged “mysql

Selecting Last Records from Table

I needed to select the last records in a table.  I could do a MySQL query with no limit on records, but that could be inefficient.  Instead you could and this to the end of your MySQL query:

order by id desc limit x

For example:

SELECT * FROM articles order by id desc limit 5

This will return the last five records in the table.

Advertisements

Importing CSV into MySQL table on Windows

After completing development of one of my apps, I had to load one of my MySQL tables with data.  After putting the data in CSV format, I wanted to upload it to my MySQL table.  I searched the web and found the syntax to use (I use MySQL workbench and from there I went into the database and created and executed a new query).  However, it took awhile for me to figure out how to make the file name work.

Here’s the syntax I used in MySQL Workbench for the query to execute (replace the text in colors with your data):

load data local infile ‘C://yourdirectory/yourdirectory2/filename.csv‘ into table your_table_name fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(field1, field2, field3, field4, field5)

(for some reason the characters don’t copy correctly from this post, so you can copy similar syntax from here)


Error: mysql2 gem compiled for wrong mysql client library

I was receiving this error after switching to the MySQL2. It turns out the reason is because I needed to update my MySQL connector.

To do that I
(1) went to: http://www.mysql.com/downloads/connector/
(2) I selected Connector/C (libmysql)
(3) I downloaded the Windows (x86, 32-bit), ZIP Archive no-install
(4) I placed it in my ruby/bin directory, which for me was in: C:\RailsInstaller\Ruby1.9.3\bin


Importing a MySQL Database in Windows

I switched computers and had to migrate my dev database.  I was able to export my old database, but had trouble importing it using phpmyadmin (actually, I couldn’t get phpmyadmin to work) and MySQL Workbench.  So I had to go into the Windows command prompt to do it.  Here’s what I did after generating a dump (ie your_database_name.sql) which contained the SQL and data to rebuild the DB.  To make this work, the database needs to be created (ie rake db:create), but the tables must not exist yet (or you’ll get a SQL error telling you that the table already exists).

(1) saved the your_database_name.sql to: users/your_user_name on the PC

(2) started the command prompt (start menu >search for programs -> cmd)

(3) typed mysql -u USERNAME -p DATABASE < FILENAME.sql

Thanks to ghacks.net for teaching me this.  They also have the sql for exporting a *.sql dump if you need that.


Rails Queries not Finding Nil Values

I had some queries that were not working and I couldn’t figure out how to make them work.  Basically, I was looking for fields with nil values, so I tried:

Model.where(‘variable = ?’, nil)….and didn’t get any results

So just for the heck of it, I tried:

Model.where(‘variable != ?’, nil)…but still no results.  How is that possible?  It either has to be a value or it isn’t right?

Well, apparently you can’t use normal comparison operands with nil/null.  However, instead of =, you can compare for nil using <=>

So I ended up doing:

Model.where(‘variable <=> ?’, nil)

and it worked, finding the nil values.


Setting Field to Null in MySQL Workbench

I had some bad data in my DB where fields had a space or some other value I could not see, so I needed to set the field to null (my program looks for null).  To do this, I changed the value of the field, then clicked “Apply” and on the SQL preview screen, I changed the value to NULL (ie city = NULL where…).


MySQL Workbench

My latest tool is the MySQL Workbench – a free tool that allows you to manage your MySQL databases.  Previously I was using PHPMyAdmin, but MySQL Workbench seems to run a lot faster.  I’m using it for managing both my local databases, as well as my remote databases.

When I first downloaded the app, I was double clicking on the MySQL application which opens a shell and the closes it right away.  I didn’t see the MySQL Workbench application which was in the same zip folder – this is the one to launch the app.

I’ve found that PHPMyAdmin was initially easier to understand because all of the links are on the screen, however that meant more screens.  In MySQL workbench, you can get to the same screens from the main menu and right clicking on the table (or the submenus of the table in the menu).

If I need to manually add rows to a table:
PHPMyAdmin – Click on Insert for the table and fields for up to two rows appear, if I want to add more, I click the go button, the entries save, new page loads

MySQL Workbench – right click on the table, select “Edit Table Data”, skip to the last row and enter your data, then click enter and the next row appears – no pages reloads

Edit Record
PHPMyAdmin – go to table, next screen click browse, find your entry, click icon to edit, change field in page that loads, then save (and another page reload)

MySQL Workbench – right click on the table, select “Edit Table Data”, find your record and double click your data field, change field value, then click the Apply button when you’re done.

Those are just a couple of examples.  I think that a lot of the reason for speed differences is that MySQL Workbench is a standalone app, while PHPMyAdmin uses a browser and doesn’t incorporate as much Java for asynchronous changes (therefore the page reloads).  The other downer is that if you don’t have PHP installed on your PC, you need to do so t

PHPMyAdmin is also a great tool, but for now I’m going to try switching to MySQL Workbench.