//Convert MM/DD/YYYY to YYYY-MM-DD. This format is useful if you want to insert into a MySQL date field.
function date_to_mysql_date($date){
$date_year=substr($date,6,4);
$date_month=substr($date,0,2);
$date_day=substr($date,3,2);
$date=date("Y-m-d", mktime(0,0,0,$date_month,$date_day,$date_year));
return $date;
}
Open /protected/config/main.php
uncomment the following to enable the Gii tool:
'gii'=>array(
'class'=>'system.gii.GiiModule',
'password'=>'rajeev',
// If removed, Gii defaults to localhost only. Edit carefully to taste.
'ipFilters'=>array('127.0.0.1','::1'),
),
uncomment the following to use MySql db:
'db'=>array(
'connectionString' => 'mysql:host=localhost;dbname=db_sch_library',
'emulatePrepare' => true,
'username' => 'root',
'password' => '',
'charset' => 'utf8',
),
Next you have to create a databse in mysql for your application and a table:
In my case it is db_sch_library, and table is books. The structure is given below:
CREATE TABLE books ( id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , title VARCHAR( 255 ) NOT NULL , description TEXT NOT NULL , isbn VARCHAR( 50 ) NOT NULL , price VARCHAR( 50 ) NOT NULL ) ENGINE = MYISAM ;
Then you have to call the gii using browser like following:
http://localhost/sch_library/index.php?r=gii
Then it will ask for gii password, enter password
Then you will get a code generator page with following options:
1. Controller Generator
2. Crud Generator
3. Form Generator
4. Model Generator
5. Module Generator
Click on the like Model Generator
In the model generator page, you have to enter Table Name. The Model class will come automatically in the Model Class
text box. Click on Preview button. Then it will show the code file and Generate Button. Click on Generate button.
Then click on link Crud Generator, and it will ask for the Model Class name and Controller ID. Enter the Model name which
you have created earlier.The controller id will come automalically. Click on Preview button to preview the code files and
after that Click on Generate Button.
Then there will be a link “try it now”to see the result or you can browse like this:
http://localhost/<web_app_dir>/index.php?r=books
That’s it
In my previous article, I’ve explained how to create a MySQL database back up using gzip compression.
If you have plain sql file, the command to restore the database is,
cat db_backup.sql | mysql -u mysqluser -p mysqldatabase
“cat” command to output the contents of the backup.sql file, and pipe its contents into the mysql program. mysql program takes the same options as the mysqldump explained in my article about creating a database backup using command-line Gzip utility.
For GZip compressed file, we can’t just output its contents into mysql, as it will be a compressed data, so inorder to extract the data execute the following command:
gunzip < db_backup.sql.gz | mysql -u mysqluser -p mysqldatabase
Here we run “gunzip” to decompress the backup.sql.gz file, we then pipe the decompressed output into the “mysql” program, which will extract the data and restore the database.. that’s it!!
Backing up your database is very important in a database driven application. Ideally database should be backed up often. There are a lot of ways to accomplish this. I’ll show how to back up database using command-line commands.
The command to run the backup is:
mysqldump -u mysqluser -p mysqldatabase
“mysqldump” program is a tool for creating database backups.
The parameters used are:
“-u” switch means you’re going to specify a username to connect with, which must follow, like “-u mysqluser” above
“-p” switch means you’re either going to immediately specify the password to use (with no space), or it’ll prompt you for one
The final parameter used in the example above is the name of the database to backup
If you run the command above, you would see the contents of your database go scrolling on the screen.
To place the contents of the output into a file, execute the following command
mysqldump -u mysqluser -p mysqldatabase > db_backup.sql
Now you should be able to see a file named “db_backup.sql”, and if you open it you can see a SQL script with the structure and content of your database ready for restoration or migration.
Now compress this SQL script using GZip compression. Instead of gzip, you can use bzip, tar etc..
To add compression into this command, just execute the following command.
mysqldump -u mysqluser -p mysqldatabase | gzip > db_backup.sql.gz
Now your database backup is ready to import/export for future use.
To connect with a database, you need the following things.
1. A Database System.
2. A Database Connector
3. Apache Tomcat
4. jdk
The database system we use is MySQL. You can download it from the following location.
http://www.filehippo.com/download_mysql/
At the time of installation, you can configure the username and password.
After installation, you can go to Start->Programs->MySQL->MySQL Command Line Client. Give password to login.
You can create a database and table using the following commands in MySQL.
SQL> create database Company;
SQL> use Company;
SQL> create table Employee(Empid varchar(20),EmpName varchar(20),Designation varchar(20),primary key(Empid));
SQL> insert into Employee values('SCH001','Krishna','Software Engineer');
SQL> insert into Employee values('SCH002','Steve','Interface Designer');
Also we need an appropriate database connector which makes connection between jdk and MySQL.
You can download the connector from the following link.
http://mysqlmirror.netandhost.in/Downloads/Connector-J/mysql-connector-java-5.1.11.zip
Extract the contents of the zipped file to any location in your computer.
After doing this, you need to set class path in Envirornment variables. You can do that by following the steps below:
My Computer->Properties->Advanced->Environment Variables->User Variables.
You can click New and have 2 text fields: Variable Name and Variable Value.
You can give as follows:
Variable Name: classpath
Variable Value: .;C:\mysql-connector-java-5.0.8\mysql-connector-java-5.0.8-bin.jar;
Note: If you have a different version of Connector, you need to give appropriate connector name.
After this, create a JSP page with the following code. The Variable Value should also contain .; in the beginning and ; in the end. Also copy the jar file of Connector inside the lib directory of Tomcat.
Now you can write a JSP page with the following code as follows:
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/html4/loose.dtd”>
<%@ page import=”java.sql.*” %>
<%@ page import=”java.io.*” %>
<html>
<head>
<title>Connection with mysql database</title>
</head>
<body>
<%
try {
String connectionURL = “jdbc:mysql://localhost:3306/Company”;
Connection connection = null;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
connection = DriverManager.getConnection(connectionURL, “root”, “root”);
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(“select * from Employee”);
%>
<table cellpadding=”15″ border=”1″ style=”background-color: #ffffcc;”>
<%
while (rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
</tr>
<% } %>
<% }
catch(Exception ex){
out.println(“Unable to connect to database.”);
}
%>
</body>
</html>
Place the JSP file inside a folder which should be resided in C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\MyDatabase
Here I gave the folder name as “MyDatabase”.
Make sure that the Tomcat has been started, Take any browser, type the folowing link.
http://localhost:8080/MyDatabase/DatabaseDemo.jsp
You can see the values in the JSP Page in the form of a Table.
Quick notes on how to install Ruby on Rails under Windows
