Destinator GPS Navigation Development

Active: April 2011 – July 2011
Employer: Intrinsyc Software
Customer: Mclaren/ICD

Project Description:
To customize the Destinator GPS Navigation System’s appearance and functionalities for McLaren’s new sports car.

Tasks Involved:
Customized the Windows CE based Destinator GPS Navigation System’s appearance and functionalities to McLaren’s specifications.

Posted in Projects | Leave a comment

Android Custom Launcher Development

Active: January 2011 – April 2011
Employer: Intrinsyc Software
Customer: Internal

Project Description:
To develop an array of custom Android Launcher replacement apps that can be demonstrated to potential customer when selling the company’s Android BSP and OS customization services.

Tasks Involved:
Developed a selection of different Android launchers with different features and visual layouts designed for specific usage scenarios such as a medical care device, an ereader, or a children’s tablet.

Posted in Projects | Leave a comment

Gridded Workspace For Android Part 4

Continuing to build the gridded workspace for Android, we will need to add more child views to our Workspace (which is an extension of ViewGroup). At this point we will just use TextViews as our individual screens. Assuming we want a 3×3 gird (9 screens), let’s modify our layout xml file as follows:

<?xml version="1.0" encoding="utf-8"?>
<waffle.android.launcher.Workspace
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 1"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 2"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 3"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 4"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 5"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 6"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 7"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 8"
    />
<TextView
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:text="Screen 9"
    />
</waffle.android.launcher.Workspace>

Next we need to modify our Workspace’s onMeasure and onLayout functions to process all 9 child views. In onMeasure(), replace the line:

getChildAt(0).measure(widthMeasureSpec, heightMeasureSpec);

with:

final int count = getChildCount();
for (int i = 0; i < count; i++) {
    getChildAt(i).measure(widthMeasureSpec, heightMeasureSpec);
}

Then in onLayout(), replace:

final View child = getChildAt(0);
getChildAt(0).layout(0, 0, child.getMeasuredWidth(), child.getMeasuredHeight());

with:

int childLeft = 0;
int childTop = 0;

final int count = getChildCount();
for (int i = 0; i < count; i++) {
    final View child = getChildAt(i);
    if (child.getVisibility() != View.GONE) {
        final int childWidth = child.getMeasuredWidth();
        final int childHeight = child.getMeasuredHeight();

        childLeft = (i%3)*childWidth;
        childTop = (i/3)*childHeight;
        child.layout(childLeft, childTop, childLeft + childWidth,
                         childTop + childHeight);
    }
}

The i%3 and i/3 will give us a 3×3 grid.

Next let us add scrolling to the Workspace by overriding onTouchEvent(). We will need to take care of the prevention of scrolling the individual screens past the limit of the parent view:

@Override
public boolean onTouchEvent(MotionEvent ev) {
  final int action = ev.getAction();
  final float x = ev.getX();
  final float y = ev.getY();        

  switch (action) {
  case MotionEvent.ACTION_DOWN:
    // Remember where the motion event started
    mLastMotionX = x;
    mLastMotionY = y;
    break;
  case MotionEvent.ACTION_MOVE:
    // Scroll to follow the motion event
    final int deltaX = (int) (mLastMotionX - x);
    mLastMotionX = x;

    final int deltaY = (int) (mLastMotionY - y);
    mLastMotionY = y;

    final int scrollX = getScrollX();
    final int scrollY = getScrollY();

    if (deltaX < 0) {
      if(deltaY < 0) {
        if (scrollX > 0 && scrollY > 0) {
          scrollBy(Math.max(-scrollX, deltaX), Math.max(-scrollY, deltaY));
        } else if (scrollX > 0 && scrollY <=0 ) {
          scrollBy(Math.max(-scrollX, deltaX), 0);
        } else if (scrollX <= 0 && scrollY > 0) {
          scrollBy(0, Math.max(-scrollY, deltaY));
        }
      } else if (deltaY > 0) {
        final int availableToScrollY = getChildAt(getChildCount() - 1).getBottom() -
          scrollY - getHeight();
        if (scrollX > 0 && availableToScrollY > 0) {
          scrollBy(Math.max(-scrollX, deltaX), Math.min(availableToScrollY, deltaY));
        } else if (scrollX > 0 && availableToScrollY <=0 ) {
          scrollBy(Math.max(-scrollX, deltaX), 0);
        } else if (scrollX <= 0 && availableToScrollY > 0) {
          scrollBy(0, Math.min(availableToScrollY, deltaY));
        }
      } else if (deltaY == 0) {
        if(scrollX > 0) {
          scrollBy(Math.max(-scrollX, deltaX), 0);
        }
      }
    } else if (deltaX > 0) {
      final int availableToScrollX = getChildAt(getChildCount() - 1).getRight() -
        scrollX - getWidth();
      if (deltaY < 0) {
        if (availableToScrollX > 0 && scrollY > 0) {
          scrollBy(Math.min(availableToScrollX, deltaX), Math.max(-scrollY, deltaY));
        } else if (availableToScrollX > 0 && scrollY <= 0) {
          scrollBy(Math.min(availableToScrollX, deltaX), 0);
        } else if (availableToScrollX <= 0 && scrollY > 0) {
          scrollBy(0, Math.max(-scrollY, deltaY));
        }
      } else if (deltaY > 0) {
        final int availableToScrollY = getChildAt(getChildCount() - 1).getBottom() -
          scrollY - getHeight();
        if (availableToScrollX > 0 && availableToScrollY > 0) {
          scrollBy(Math.min(availableToScrollX, deltaX),
            Math.min(availableToScrollY, deltaY));
        } else if (availableToScrollX > 0 && availableToScrollY <=0 ) {
          scrollBy(Math.min(availableToScrollX, deltaX), 0);
        } else if (availableToScrollX <= 0 && availableToScrollY > 0) {
          scrollBy(0, Math.min(availableToScrollY, deltaY));
        }
      } else if (deltaY == 0) {
        if(availableToScrollX > 0) {
          scrollBy(Math.min(availableToScrollX, deltaX), 0);
        }
      }
    } else if (deltaX == 0) {
      if (deltaY < 0) {
        if(scrollY > 0) {
          scrollBy(0, Math.max(-scrollY, deltaY));
        }
      } else if (deltaY > 0) {
        final int availableToScrollY = getChildAt(getChildCount() - 1).getBottom() -
          scrollY - getHeight();
        if(availableToScrollY > 0) {
          scrollBy(0, Math.min(availableToScrollY, deltaY));
        }
      }
    }
    break;
    }
    return true;
}

Now we can scroll our workspace around without letting the screens go out of the parent’s boundary. To make the individual screens more distinctive, we can add different background colors to each TextView using the the android:background property such as:

android:background="#111111"

Android Workspace Scrolling

Posted in Android, Java | Tagged , , , | 2 Comments

Gridded Workspace For Android Part 3

The draggable Android workspace with multiple screens is actually a ViewGroup containing multiple child views. Each of these child views is one screen which may contain icons, folders or widgets. A wallpaper is loaded onto the ViewGroup spanning multiple screens.

By default, our Android project’s main.xml file contains a TextView wrapped around by a LinearLayout. To continue building the gridded workspace, we shall replace the LinearLayout with a custom ViewGroup called Workspace. In Eclipse, right click on your Java package and choose New->Class. Name it Workspace and type android.view.ViewGroup for the Superclass. Note that by default a class which extends ViewGroup must at least implement a constructor and override the onLayout() function in order to compile. Eclipse can generate these method stubs for you:

package waffle.android.launcher;

import android.content.Context;
import android.util.AttributeSet;
import android.view.ViewGroup;

public class Workspace extends ViewGroup {

    public Workspace(Context context, AttributeSet attrs) {
        super(context, attrs);
        // TODO Auto-generated constructor stub
    }

    @Override
    protected void onLayout(boolean arg0, int arg1, int arg2, int arg3, int arg4) {
        // TODO Auto-generated method stub
    }

}

Now in our main.xml, replace the LinearLayout with our new custom ViewGroup:

<?xml version="1.0" encoding="utf-8"?>
<waffle.android.launcher.Workspace
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<TextView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="@string/hello"
    />
</waffle.android.launcher.Workspace>

At this point, your project will compile. However if you run the activity, nothing will show on the screen. This is because we haven’t implemented the necessary methods in our custom ViewGroup to draw its child views onto the screen. More information regarding building custom Android components can be found at the Android Developer site. Essentially, there are two methods we will need to extend, which are onMeasure() and onLayout(). Since we only have one child view so far, we don’t need to do much to make it visible. Simply add the following code to the Workspace class:

@Override
protected void onMeasure(int widthMeasureSpec, int heightMeasureSpec) {
    super.onMeasure(widthMeasureSpec, heightMeasureSpec);

    final int widthMode = MeasureSpec.getMode(widthMeasureSpec);
    if (widthMode != MeasureSpec.EXACTLY) {
        throw new IllegalStateException("Workspace can only be used in EXACTLY mode.");
    }

    final int heightMode = MeasureSpec.getMode(heightMeasureSpec);
    if (heightMode != MeasureSpec.EXACTLY) {
        throw new IllegalStateException("Workspace can only be used in EXACTLY mode.");
    }

    getChildAt(0).measure(widthMeasureSpec, heightMeasureSpec);
}

@Override
protected void onLayout(boolean changed, int left, int top, int right, int bottom) {
    final View child = getChildAt(0);
    child.layout(0, 0, child.getMeasuredWidth(), child.getMeasuredHeight());
}

The onMeasure() and onLayout() functions will require more work as we continue to add code and build towards the draggable, gridded workspace that we want.

Posted in Android | Tagged , , , | Leave a comment

Gridded Workspace For Android Part 2

Let’s begin building the custom Android workspace introduced in Part 1 of this series. I will be doing my development on an Windows machine, using the Eclipse IDE with the Android ADT (Android Development Tools) plugin:

In Eclipse, select File->New->Android Project. I will name the project “Waffle”. At the time of this writing, the best Android target to use is Android 2.3.3. Although Android 3.0 Honeycomb is already available in the SDK, the emulator runs extremely slow in Honeycomb and is practically unusable. I will also name the Application “Waffle”, name the package waffle.android.launcher, and name the Activity Launcher. Since we are choosing the Android 2.3.3 target, the Min SDK version shall be 10. Finally click Finish to create the project.

waffle create project

The newly created project only gives us a typical Android application. However what we are interested in is building a special type of Android application called a Launcher, also known as the Home Screen. What distinguish a Launcher from a typical Android application? As a basic start should have at least the following 3 characteristics:

  • When the Home button is pressed, the Launcher should appear in the list of available Launchers.
  • It should not show the Android application title bar.
  • When the Back button is pressed at the Home screen, the user should stay at the Home screen and not directed somewhere else.

To make the application appear in the list of Launchers, we need to add the HOME and DEFAULT intent categories to our AndroidManifest.xml file. We should also remove the LAUNCHER category that are given to the activity by default so that our launcher doesn’t appear as an application in our app drawer:

<intent-filter>
    <action android:name="android.intent.action.MAIN" />
    <category android:name="android.intent.category.HOME" />
    <category android:name="android.intent.category.DEFAULT" />
</intent-filter>

Next to remove the title bar, we will make use of the android:windowNoTitle apply a theme to our activity. First we create a styles.xml file inside the res/values/ folder within the project, in which we will put:

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <style name="Theme" parent="android:Theme">
        <item name="android:windowNoTitle">true</item>
    </style>
</resources>

Then we apply the theme to our application in the manifest file:

<application
    	android:icon="@drawable/icon"
    	android:label="@string/app_name"
    	android:theme="@style/Theme">

Finally we will deal with the Back and Home button presses by overriding dispatchKeyEvent() in Launcher.java:

@Override
public boolean dispatchKeyEvent(KeyEvent event) {
    if (event.getAction() == KeyEvent.ACTION_DOWN) {
        switch (event.getKeyCode()) {
            case KeyEvent.KEYCODE_BACK:
                return true;
            case KeyEvent.KEYCODE_HOME:
                return true;
        }
    }
    return super.dispatchKeyEvent(event);
}

Having completed the above steps, our new application will appear in the launcher list when the Home button is pressed. It also will hide the Title bar when it runs.

launcher-list

launcher-screen

Before we wrap up this part, we can also consider hiding the Android status bar by adding the following code in the onCreate() method of Launcher.java:

getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULLSCREEN,
    WindowManager.LayoutParams.FLAG_FULLSCREEN);

android-no-status-bar

Posted in Android, Java | Tagged , | Leave a comment

Gridded Workspace for Android Part 1

I came across a cool jQuery faux-3D effect by David DeSandro, and thought it would be cool if this was adopted as a mobile device home screen. The effect involves overlapping rectangles moving at different speeds inside a 3 by 3 grid. I have reproduced the effect using jQuery down below. Click on the buttons below the view-port to see the effect in motion.

Top LeftTopTop TightLeftCenterRightBottom LeftBottomBottom Right

I have been working with the Android SDK recently and this is a perfect coding exercise for Android. The factory launcher of the Android OS features multiple home screens that can only be scrolled sideways. In subsequent articles I will walk through the process of implementing a launcher with a 3 by 3 gridded workspace using the Android SDK.

Posted in Android, JavaScript, jQuery | Tagged | Leave a comment

iQ Development Kit For Qualcomm IEM Modules

Active: November 2010 – December 2010
Employer: Intrinsyc Software
Customer: Qualcomm

Project Description:
Intrinsyc was chosen by Qualcomm to develop the iQ Development Kit for their compact and powerful Internet of Everything Modules (IEMs).

Tasks Involved:

  • Created PCB decals for the iQ kit PCB using PADS
  • Reviewed and signed off PCB layout designs and gerber packages for manufacturing.
Posted in Projects | Tagged | Leave a comment

Android Market On Emulator

As and Android developer you probably would at some point want to try out different apps on the emulator. The best way to get new apps is probably through the Android Market, however, the Android SDK by default does not come with the Android Market. Luckily, there is an easy way to get the Android Market running on your emulator.

Step 1) Download the Android 1.6 System Image with Android Market from here the HTC Developer Center: http://developer.htc.com/google-io-device.html

Step 2) In Eclipse, open the Android SDK and AVD Manager and create a new AVD targeting Android 1.6.

Step 3) Launch the new AVD once, check that there is a network connection, then terminate it. Without performing this step one may not be able get an Internet connection with the downloaded image.

Step 4) Unzip the image file downloaded in Step 1, and copy the system.img file to your new AVD directory. Typically the folder can be located in %userprofile%/.android/avd if under Windows.

Step 5) Launch the new AVD, and the image should be loaded.

HTC Developer Image Screen Shot

Step 6) Follow the on screen instructions to set up the system. If you get stuck at a screen where you are asked to slide open the keypad, simply skip or cancel out of it and return to the home screen.

Step 7) The Android Market should be in your app drawer now. Launch it and sign in with your Google account, and you will be able to browse and install new apps just like on a real phone.

Android Market On Emulator Screen Shot

I even got Talking Tom running in the emulator!

Talking Tom On Emulator Screen Shot

Note: If you keep running into network connection problems, try using the images provided on this site:
http://techdroid.kbeanie.com/2009/11/android-market-on-emulator.html

Posted in Android | Tagged , , , | 1 Comment

Using Basic Android UI Widgets

Android’s GUI toolkit include all the common UI widgets like text labels, buttons, input fields, etc. While these widgets can be created and attached to our activities using Java code, in Android development it is more common to do it using XML-based layout files. An XML layout files is a specification of the widgets’ relationships to each other, and are stored in the res/layout directory inside an Android project tree.

Labels
The simplest UI element is probably the label, which is basically just text. In Android the label is referred to as a TextView. If you have created an application skeleton with Eclipse, the text that the default activity outputs to the screen is actually such a TextView. The TextView is defined in main.xml, and placed into the activity’s view via a setcontentView call:

 setContentView(R.layout.main);

The definition of the TextView itself looks like this:

<TextView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="@string/hello"
    />

Note that the text specified in the above TextView definition tells the widget to use the string resource with the identifier “hello” as the text, which is defined in res/values/strings.xml as:

<resources>
    <string name="hello">Hello World, HelloWorld!</string>
</resources>

If we run the application, we will see that this label widget showing the text properly on the screen.

Android Hello World Application

Buttons
The Android Button class is actually a subclass of TextView, to define a button we can try adding the following code to our layout xml file:

<Button
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="Button"
    />

When we run the activity, here is what we will see:

Android Button

Since all we have done is adding the button to the layout, it will not do anything when we click on it. What we need to do is to define a method in our activity, then associate the button to this method by adding an onClick attribute to the button XML element. Note that when implementing this we need to import two additional packages to our code:

import android.view.View;
import android.widget.Button;

Now in the activity we can add the method:

    public void onClick(View theButton) {
    	Button btn=(Button)findViewById(R.id.button);
    	btn.setText("Button Clicked!");
    }

And the following to the xml layout file:

<Button
    android:id="@+id/button"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="Button"
    android:onClick="onClick"
    />

The above code changes the text of our button when we click on it. When running the activity on the emulator here is what you should see:

Android Button Clicked

Note that this method is only supported by Android 1.6 and above. Although there is another possible method to achieve the same thing, which is to define the activity to implement the View.OnClickListener interface.

ImageView and ImageButton
As their names suggest, ImageView and ImageButton are the image based versions of TextView and button. To specify the image to be used, we use the android:src attribute in the XML element, which usually references a drawable resource in the project. For instance, when the Eclipse project wizard creates a project skeleton, it includes an image, icon.png in the drawable folder by default. We can try to show this image by adding the following code to the layout XML file:

<ImageView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:src="@drawable/icon"
    />

When we run the activity in the emulator here is what we will see:

Android Image Widget

ImageButton works the same way as the regular button, with which we can use an onClick attribute to attach a handler method to a click event.

Fields
Fields are input areas where the user can type in text. To use Android fields, use the EditText widget in the layout XML file as follows:

<EditText
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
/>

What will show up in our UI is this:

Android Field

The EditText widget is also a subclass of the TextView, but it has many other attributes that can be specified to alter the behavior of the field. For instance: android:autoText, android:capitalize, android:digits and android:singleLine to name a few. The purposes of these attributes are self-evident.

Checkbox
To use Android checkboxes, use the CheckBox widget in the layout XML file as follows:

<CheckBox
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="This is a checkbox"
/>

Android Checkbox

In Java, we can use the methods isChecked(), setChecked(), toggle() to check or modify the state of our checkbox. Alternatively, we can implement the CompoundButton.OnCheckedChangedListener interface with our activity, and use setOnCheckedChangeListener() to attach a callback function to handle the check/uncheck events.

Radio Buttons
Android’s RadioButton widget inherits from the CompoundButton, which in turn inherits from TextView. Multiple RadioButtons can be put inside a RadioGroup, which means that only one button of the group can be selected at a time. The following example XML code place a group of RadioButtons in our activity’s layout:

<RadioGroup
	android:orientation="vertical"
	android:layout_width="fill_parent"
	android:layout_height="fill_parent"
	>
	<RadioButton
		android:layout_width="wrap_content"
		android:layout_height="wrap_content"
		android:text="Radio Button 1" />
	<RadioButton
		android:layout_width="wrap_content"
		android:layout_height="wrap_content"
		android:text="Radio Button 2" />
	<RadioButton
		android:layout_width="wrap_content"
		android:layout_height="wrap_content"
		android:text="Radio Button 3" />
</RadioGroup>

When the activity is run in the emulator, this is what it will look like:

Android Radio

Posted in Android | Tagged , , , | 1 Comment

Basic MySQL Interactions With PHP

Most websites today are not static, and in order to support constantly updating content, a common practice is to store the content data in a database and use server side scripts to retrieve or store the content. MySQL databases and PHP scripts are probably the most commonly used combination for this purpose. WordPress and Drupal are for instance two of the most popular PHP and MySQL based publishing platform today. In this article let us examine some basic interactions we can perform with MySQL using PHP.

Creating A Database and MySQL User Manually
Before we are able to access MySQL using PHP, we should first setup manually a database and a MySQL user with proper permission to that database. If you are working with a typical Linux server with cPanel, then this can be easily accomplished in cPanel. For this example I have created in cPanel a MySQL database called testdb, and MySQL user called testuser, and added the user to the database with full access privileges.

The Scenario
Before trying to do things with the database, let us imagine a scenario for which we will be writing our PHP script for: assume we want to create a guest book for a website, where visitors can leave comments along with their names. We will create a table called guest book two columns. The first column will keep the names of the visitors and the second column will keep comments they leave.

Creating The Table
To create a table, we will first need to establish a connection to MySQL using mysql_connect(), then we will use mysql_query() to send MySQL queries, then finish by closing the connection using mysql_close(). Note that when creating a table, we need to also specify a primary key column. Each primary key value must be unique within the table and cannot be null because the database engine requires a unique value to locate the record. We will create a file called test.php which contains the following code:

<?php
$con = mysql_connect("localhost","testuser","*******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create table
mysql_select_db("testdb", $con);
$sql = "CREATE TABLE guestbook
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID),
Name varchar(100),
Comment text
)";

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

Dropping The Table
We were able to create a MySQL table with PHP, however since we are in the development phase of our script, we may at times want to start over from scratch again for testing purpose and thus we want to be able to delete the table using PHP as well. We will modify the above code in such a way so that both operations are contained within one script, but whether to create or drop the table will depend on a user provided argument provided in the URL when accessing the script:

<?php

$action = $_POST['action'] ? $_POST['action'] : $_GET['action'];

$con = mysql_connect("localhost","testuser","*******");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("testdb", $con);

if ($action == "create")
{
	$sql = "DROP TABLE IF EXISTS guestbook";

	// Create table
	$sql = "CREATE TABLE guestbook
	(
	ID int NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (ID),
	Name varchar(100),
	Comment text
	)";
}

elseif ($action =="drop")
{
	$sql = "DROP TABLE IF EXISTS guestbook";
}

if($sql)
{
	// Execute query
	mysql_query($sql,$con);
}

mysql_close($con);

?>

Now we can drop the table we created by accessing /test.php?action=drop, and create the table again by accessing /test.php?action=create. If we don’t provide an argument or one other than create or drop, the script will do nothing.

Inserting Data
Now we have a database table to hold the guest book content, but we need some way for the visitors to submit their comments. To do so we can simply use the usual html form. PHP and HTML code can coexist in the same file, so we can simply add the html form to the same test.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
 <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
 <title>Guest Book</title>
</head>
<body>
 <form action="test.php?action=submit" method="post">
  <p>Name:<br /><input type="text" name="name" /></p>
  <p>Comment:<br /><textarea name="comment" rows="5" cols="50"></textarea></p>
  <p><input type="submit" value="Submit" /></p>
 </form>
</body>
</html>

Next we need to modify the PHP script so that the form data submitted to it is parsed and inserted into the MySQL table. The SQL command we will use is INSERT INTO. Add the following action handler to test.php:

elseif ($action =="submit")
{
	$sql = "INSERT INTO guestbook (Name, Comment)
	VALUES ('".$_POST['name']."', '".$_POST['comment']."')";
}

At this point, when a visitor accesses test.php, enters a name and a comment and submit the form, the php script will insert this data into the database table.

Retrieving Data
Now visitors can submit their comments to the guest book, but we also want to be show to visitors all the comments previously submitted. To do so we will retrieve the data stored in the database table and output them on the same page for the visitors to see. The SQL command we will use is SELECT, and we will simply get all the data in the table by using the “*” wildcard operator. We will add the following code to the PHP script right before we close the MySQL connection to retrieve all data in the MySQL table, then construct an html table to be shown to the user.

$result = mysql_query("SELECT * FROM guestbook");

$output = "<table><tr><th>Name:</th><th>Comment:</th></tr>";
while($row = mysql_fetch_array($result))
{
 $output .= "<tr><td>" .$row['Name'] . "</td>
 <td>" . $row['Comment'] . "</td></tr>";
}
$output .= "</table>";

Finally we show the data to the visitor by echoing the output table in the HTML body:

<?php echo $output; ?>

After submitting a few test comment, you should see something like below:

MySQL PHP Example

The complete source code of test.php:

<?php

$action = $_GET['action'];

$con = mysql_connect("localhost","testuser","*******");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("testdb", $con);

if ($action == "create")
{
	$sql = "DROP TABLE IF EXISTS guestbook";

	// Create table
	$sql = "CREATE TABLE guestbook
	(
	ID int NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (ID),
	Name varchar(100),
	Comment text
	)";
}

elseif ($action =="drop")
{
	$sql = "DROP TABLE IF EXISTS guestbook";
}

elseif ($action =="submit")
{
	$sql = "INSERT INTO guestbook (Name, Comment)
	VALUES ('".$_POST['name']."', '".$_POST['comment']."')";
}

if($sql)
{
	// Execute query
	mysql_query($sql,$con);
}

$result = mysql_query("SELECT * FROM guestbook");

$output = "<table><tr><th>Name:</th><th>Comment:</th></tr>";
while($row = mysql_fetch_array($result))
{
	$output .= "<tr><td>" .$row['Name'] . "</td>
<td>" . $row['Comment'] . "</td></tr>";
}
$output .= "</table>";

mysql_close($con);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
 <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
 <title>Guest Book</title>
</head>
<body>
 <?php echo $output; ?>
 <form action="test.php?action=submit" method="post">
  <p>Name:<br /><input type="text" name="name" /></p>
  <p>Comment:<br /><textarea name="comment" rows="5" cols="50"></textarea></p>
  <p><input type="submit" value="Submit" /></p>
 </form>
</body>
</html>
Posted in PHP | Tagged , , , | Leave a comment