Author: Vijdan Khalique

Vijdan Khalique

Java and MySQL Database Connectivity

A simple step-by-step guide for connecting simple console-based Java application with MySQL database.

Tools required:

  • Eclipse IDE for Java
  • HiediSQL IDE for interfacing with MYSQL

Follow these steps:

  1. Open HiediSQL and enter the username and password(typically username=root and password=root)1
  2. Create a simple database with a table and a few columns like the one shown here. the database names test_db having a table called customer with customerNumber, customerName and address.2
  3. now download java SQL connector JAR file from:  https://dev.mysql.com/downloads/connector/j/5.0.html4
  4. Create a new java application in eclipse (DatabaseConnectivity in our example)
  5. Select the newly created java project (DatabaseConnectivity), right-click on it, goto build path and then select add external jar file. 5Browse to the location where you have downloaded java mysqlconnector jar file and press ok to include it in your project. This external connector will help your code to communicate with MySQL database.
  6. write the following code:

    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    import com.mysql.jdbc.Connection;
    import com.mysql.jdbc.Statement;

    public class DatabaseConnection {

    public static void main(String[] args) {
    Connection connect = null;
    Statement statement=null;
    ResultSet resultset = null;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    connect = (Connection) DriverManager.getConnection(“jdbc:mysql://localhost/test_db”,”root”,”root” );
    if(connect != null){
    System.out.println(“Database connected”);
    }else{
    System.out.println(“Database connection failed”);
    }
    } catch (Exception e ) {
    e.printStackTrace();
    }

    try {
    statement = (Statement) connect.createStatement();
    // Result set get the result of the SQL query
    resultset = statement.executeQuery(“select * from customers”);

    while(resultset.next()){
    System.out.print( resultset.getInt(“customerNumber”)+” “);
    System.out.print(resultset.getString(“customerName”)+’\n’);
    }

    } catch (SQLException e) {
    e.printStackTrace();
    }
    }

    }

  7. Select the project, right click on it, goto run as and select java application.
  8. See the output in the console.6

This is simple code for database connectivity and selecting data out from a table.

You may now create your application with insert, update, delete and other select queries.

That’s it folks!

Code On!

Thank You 😀

Vijdan Khalique

Storing data from Checkboxes in a PHP Session

Storing data from Checkboxes in a PHP Session

Hello everyone!

This is a simple example for storing values from a form in a PHP session and displaying the values stored in the session.

The HTML page(session_check.html) has a form with the fields as shown in the image below:

session_check.html
session_check.html

session_check.html

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Untitled Document</title>
</head>

<body>
<form action=”session_check2.php” method=”GET”>
user id <input type=”text” name=”user_id” /><br />
events<input type=”checkbox” value=”events” name=”check1″ /><br />
seminars<input type=”checkbox” value=”seminars” name=”check2″ /> <br />
workshop<input type=”checkbox” value=”workshop” name=”check3″ /><br />
<input type=”submit” />

</form>

</body>
</html>

The PHP script below stores the user id and checked options in the session and show the results.

session_check2.php

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Untitled Document</title>
</head>
<?php
if(isset($_GET[‘user_id’])){
session_start();
$user = $_GET[‘user_id’];
$_SESSION[‘user’] = $user;

if(isset($_GET[‘check1’])){
$event = $_GET[‘check1’];
$_SESSION[‘event’] = $event;
}

if( isset($_GET[‘check2’])){
$seminar = $_GET[‘check2’];
$_SESSION[‘seminar’] = $seminar;
}

if(isset($_GET[‘check3’])) {
$workshop = $_GET[‘check3’];
$_SESSION[‘workshop’] = $workshop;
}
}

echo “user id: “.$_SESSION[‘user’];

if(isset($_SESSION[‘event’])){
echo “<br>event: “. $_SESSION[‘event’] ;
}

if( isset($_SESSION[‘seminar’])){
echo “<br>seminar: “. $_SESSION[‘seminar’] ;
}

if(isset($_SESSION[‘workshop’])) {
echo “<br>workshop: “.$_SESSION[‘workshop’];
}
session_destroy();
?>
<body>
</body>
</html>

here is the output:

session_check2.php
session_check2.php

Try this code. Copy and paste and run the code to see the result.

Keep posting your queries and suggestions!

Thank you..

Code On! 😀

Vijdan Khalique

Creating an XML Schema(.xsd) from and XML Document(.xml)

XSD (XML Schema Definition), a recommendation of the World Wide Web Consortium (W3C), specifies how to formally describe the elements in an Extensible Markup Language (XML) document. It can be used by programmers to verify each piece of item content in a document.

This is a simple tutorial for creating an XML schema(XSD) for an XML document. The image shows the hierarchy of elements. This hierarchy would help you to understand the structure of this XML document and make it easier for you to write an XSD.

Hierarchy of Elements in XML document (department.xml)
Hierarchy of Elements in XML document (department.xml)

It is recommended that you always follow this practice of creating a hierarchy of elements/tags before making XSD for an XML.

Following is a simple XML file show data about a department of an institute and its students.

department.xml

<?xml version=”1.0″ encoding=”utf-8″?>
<department>

<department_name>software engineering</department_name>
<institute_name>mehran UET</institute_name>
<location>jamshoro</location>

<student>
<name>Azhar</name>
<rollno>112233</rollno>
<batch>2014</batch>
</student>
<student>
<name>faisal</name>
<rollno>112244</rollno>
<batch>2013</batch>
</student>

</department>

Every root element(department and student) shown in the diagram is interpreted as complex type in XSD.

Here is the corresponding XSD for the above XML:

department.xsd

<xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema”&gt;
<xs:element name=”department”>
<xs:complexType>
<xs:sequence>
<xs:element type=”xs:string” name=”department_name”/>
<xs:element type=”xs:string” name=”institute_name”/>
<xs:element type=”xs:string” name=”location”/>
<xs:element name=”student” maxOccurs=”unbounded” minOccurs=”0″>
<xs:complexType>
<xs:sequence>
<xs:element type=”xs:string” name=”name”/>
<xs:element type=”xs:int” name=”rollno”/>
<xs:element type=”xs:short” name=”batch”/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Try this one and then try creating your own XMLs  and their XSDs.

For checking if you have created a correct XML and XSD, use this link:

http://www.freeformatter.com/xml-validator-xsd.html.

Online XML and XSD validator
Online XML and XSD validator

This is an online validator. Copy and paste the XML document and XSD to check the results.

Thank You.

Code On! 😀

Vijdan Khalique

Test Your Knowledge in PHP and MySql

PHP and MySql Database Connectivity Objective Test

Qno.1: MCQs

  1. In PHP in order to access MySQL database you will use:
    A) mysqlconnect() function
    B) mysql-connect() function
    C) mysql_connect() function
    D) sql_connect() function
  2. SQL is not case sensitive. SELECT is the same as select.\
    A) True
    B) False
  3. Use the ………….. to delete the data inside the table, and not the table itself?
    A) DROP TABLE
    B) DELETE TABLE
    C) TRUNCATE TABLE
    D) REMOVE TABLE
  4. Which one of the following methods can be used to diagnose and display information about a MySQL connection error?
    a) connect_errno()
    b) connect_error()
    c) mysqli_connect_errno()
    d) mysqli_connect_error()
  5. Which one of the following methods is responsible for sending the query to the database?
    a) query()
    b) send_query()
    c) sendquery()
    d) query_send()
  6. Which one of the following method is used to retrieve the number of rows affected by an INSERT, UPDATE, or DELETE query?
    a) num_rows()
    b) affected_rows()
    c) changed_rows()
    d) new_rows()
  7. What is the default port number for MySql?
    1. 8080
    2. 80
    3. 3306
    4. 3300
  8. What does mysql_fetch_row return?
    1. A single column of the database
    2. Number of tables
    3. Single row as an array
    4. Single row as a string
  9. Which php function is used for running the insert query?
    1. mysql_insert
    2. mysql_insert_row
    3. mysql_fetch_row
    4. mysql_query
  10. ODBC stands for ______________
    1. Open database connectivity
    2. Oracle database connectivity
    3. Operational database connectivity
    4. Organized database connection

Qno.2:True/false

  1. Database hostname is the computer on which the database is installed.
  2. The dbms never allows primary key to be null.
  3. MySql is open source DBMS.
  4. Mysql_close is used for closing the database connection
  5. The password for the database can be set as null.
  6. die() is used for terminating the page.
  7. @ is the error control operator
  8. sqli_connect and sql_connect can be used interchangeably.
  9. mysql_free_result closes the result set.
  10. mysql_fetch_assoc returns an associative array.
Vijdan Khalique

Connecting Android App with SQLite Database

Hello everyone!

Here is an example code for creating a database in SQLite and connecting it with android application.

SQLite is simple database developed to run on small devices with limited storage and processing capability like mobile phones.

Follow these steps:

1. create an android application with any name for example, SqliteDatabaseTest

2. create a new class DatabaseHelper.

3. Write the following code in MyDatabaseHelper.java class.Capture

4. Add a TextView in the layout xml file of your application

5. Now include the following code, in the MainActivity.java class:

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.TextView;

public class MainActivity extends Activity {

TextView txtData;
StringBuilder str = new StringBuilder(“”);
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
txtData = (TextView)findViewById(R.id.txtData);
MyDatabaseHelper helper = new MyDatabaseHelper(this);
helper.addStudent(“A”,”123″);
helper.addStudent(“B”,”456″);
helper.addStudent(“C”,”789″);
Cursor cursor = helper.getStudent();
cursor.moveToFirst();
while(!cursor.isAfterLast()){
str.append(cursor.getString(1)+” “+cursor.getString(2)+”\n”);
cursor.moveToNext();
}
txtData.setText(str);
}
}

6. Run application on the AVD or your device. It would display all the inserted records in the TextView.

That’s it!

Try to run this code and customize it to fit your needs.

Thank You!

Code On! 😀