Integration of PHP and MYSQL with Android

ANDROID PHP

            In this chapter, we are going to explain, how you can integrate PHP and MYSQL with your android application. This is very useful in case you have a webserver, and you want to access its data on your android application.

            MYSQL is used as a database at the webserver and PHP is used to fetch data from the database. Our application will communicate with the PHP page with necessary parameters and PHP will contact MYSQL database and will fetch the result and return the results to us.

PHP - MYSQL
CREATING DATABASE
            MYSQL database can be created easily using this simple script. The CREATE DATABASE statement creates the database.

< ?php $con=mysqli _ connect( " example.com " , " username " , " password " ); $sql= " CREATE DATABASE my _ db " ; if (mysqli _ query($con,$sql)) { echo " Database my _ db created successfully " ; } ? >

CREATING TABLES:
            Once database is created, it’s time to create some tables in the database. The CREATE TABLE statement creates the database.

< ?php $con=mysqli _ connect( " example.com " , " username " , " password " , " my _ db " ); $sql= " CREATE TABLE table1(Username CHAR(30),Password CHAR(30),Role CHAR(30)) " ; if (mysqli _ query($con,$sql)) { echo " Table have been created successfully " ; } ? >

INSERTING VALUES IN TABLES:
            When the database and tables are created. Now it’s time to insert some data into the tables. The Insert Into statement creates the database.

< ?php $con=mysqli _ connect( " example.com " , " username " , " password " , " my _ db " ); $sql= " INSERT INTO table1 (FirstName, LastName, Age) VALUES ( ' admin ' , ' admin ' , ' adminstrator ' ) " ; if (mysqli _ query($con,$sql)) { echo " Values have been inserted successfully " ; } ? >

PHP - GET AND POST METHODS:
            PHP is also used to fetch the record from the MySQL database once it is created. In order to fetch record some information must be passed to PHP page regarding what record to be fetched.

            The first method to pass information is through GET method in which $ _ GET command is used. The variables are passed in the URL and the record is fetched. Its syntax is given below:

< ?php $con=mysqli _ connect( " example.com " , " username " , " password " , " database name " ); if (mysqli _ connect _ errno($con)) { echo " Failed to connect to MySQL: " . mysqli _ connect _ error(); } $username = $ _ GET[ ' username ' ]; $password = $ _ GET[ ' password ' ]; $result = mysqli _ query($con, " SELECT Role FROM table1 where Username= ' $username ' and Password= ' $password ' " ); $row = mysqli _ fetch _ array($result); $data = $row[0]; if($data) { echo $data; } ysqli _ close($con); ? >

            The second method is to use POST method. The only change in the above script is to replace $ _ GET with $ _ POST. In Post method , the variables are not passed through URL.

ANDROID-CONNECTING MYSQL
CONNECTING VIA GET METHOD
            There are two ways to connect to MYSQL via PHP page. The first one is called Get method. We will useHttpGet and HttpClient class to connect. Their syntax is given below:

URL url = new URL(link); HttpClient client = new DefaultHttpClient(); HttpGet request = new HttpGet(); request.setURI(new URI(link));

            After that you need to call execute method of HttpClient class and recieve it in a HttpResponse object. After that you need to open streams to recieve the data.

HttpResponse response = client.execute(request); BufferedReader in = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));

CONNECTING VIA POST METHOD:
            In the Post method, the URLEncoder,URLConnection class will be used. The urlencoder will encode the information of the passing variables. It's syntax is given below:

URL url = new URL(link); String data = URLEncoder.encode( " username " , " UTF - 8 " ) + ; " = " + ; URLEncoder.encode(username, " UTF - 8 " ); data + ;= " & " + ; URLEncoder.encode( " password " , " UTF - 8 " ) + ; " = " + ; URLEncoder.encode(password, " UTF - 8 " ); URLConnection conn = url.openConnection();

            The last thing you need to do is to write this data to the link. After writing , you need to open stream to receive the responded data.

OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream()); wr.write( data ); BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));

Example
            The below example is a complete example of connecting your android application with MYSQL database via PHP page. It creates a basic application that allows you to login using GET and POST method.

PHP - MYSQL PART:
            In this example a database with the name of temp has been created at 000webhost.com. In that database , a table has been created with the name of table1. This table has three fields. (Username, Password, Role). The table has only one record which is ("admin","admin","administrator").

The PHP page has been given below which takes parameters by post method.

< ?php $con=mysqli _ connect( " mysql10.000webhost.com " , " username " , " password " , " db _ name " ); if (mysqli _ connect _ errno($con)) { echo " Failed to connect to MySQL: " . mysqli _ connect _ error(); } $username = $ _ POST[ ' username ' ]; $password = $ _ POST[ ' password ' ]; $result = mysqli _ query($con, " SELECT Role FROM table1 where Username= ' $username ' and Password= ' $password ' " ); $row = mysqli _ fetch _ array($result); $data = $row[0]; if($data) { echo $data; } mysqli _ close($con); ? >

ANDROID PART:
            To experiment with this example , you need to run this on an actual device on which WIFI internet is connected.

Steps	Description
1	You will use Eclipse IDE to create an Android application and name it as PHPMYSQL under a package com.example.phpmysql. While creating this project, make sure you Target SDK and Compile With at the latest version of Android SDK to use higher levels of APIs.
2	Modify src/MainActivity.java file to add Activity code.
3	Create src/SiginActivity.java file to add PHPMYSQL code.
4	Modify layout XML file res/layout/activity_main.xml add any GUI component if required.
5	Modify res/values/string.xml file and add necessary string components.
6	Modify AndroidManifest.xml to add necessary permissions.
7	Run the application and choose a running android device and install the application on it and verify the results.

Here is the content of src/com.example.phpmysql/MainActivity.java.

package com.example.phpmysql; import android.app.Activity; import android.os.Bundle; import android.view.Menu; import android.view.View; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends Activity { private EditText usernameField,passwordField; private TextView status,role,method; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); usernameField = (EditText)findViewById(R.id.editText1); passwordField = (EditText)findViewById(R.id.editText2); status = (TextView)findViewById(R.id.textView6); role = (TextView)findViewById(R.id.textView7); method = (TextView)findViewById(R.id.textView9); } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.main, menu); return true; } public void login(View view) { String username = usernameField.getText().toString(); String password = passwordField.getText().toString(); method.setText( " Get Method " ); new SigninActivity(this,status,role,0).execute(username,password); } public void loginPost(View view) { String username = usernameField.getText().toString(); String password = passwordField.getText().toString(); method.setText( " Post Method " ); new SigninActivity(this,status,role,1).execute(username,password); } }

Here is the content of src/com.example.phpmysql/SigninActivity.java.

package com.example.phpmysql; import java.io.BufferedReader; import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.net.URI; import java.net.URL; import java.net.URLConnection; import java.net.URLEncoder; import org.apache.http.HttpResponse; import org.apache.http.client.HttpClient; import org.apache.http.client.methods.HttpGet; import org.apache.http.impl.client.DefaultHttpClient; import android.content.Context; import android.os.AsyncTask; import android.widget.TextView; public class SigninActivity extends AsyncTask < String,Void,String > { private TextView statusField,roleField; private Context context; private int byGetOrPost = 0; //flag 0 means get and 1 means post.(By default it is get.) public SigninActivity(Context context,TextView statusField, TextView roleField,int flag) { this.context = context; this.statusField = statusField; this.roleField = roleField; byGetOrPost = flag; } protected void onPreExecute() { } @Override protected String doInBackground(String... arg0) { if(byGetOrPost == 0){ //means by Get Method try { String username = (String)arg0[0]; String password = (String)arg0[1]; String link = " http://myphpmysqlweb.hostei.com/login.php?username= " + username + " &password= " + password; URL url = new URL(link); HttpClient client = new DefaultHttpClient(); HttpGet request = new HttpGet(); request.setURI(new URI(link)); HttpResponse response = client.execute(request); BufferedReader in = new BufferedReader(new InputStreamReader(response.getEntity().getContent())); StringBuffer sb = new StringBuffer( " " ); String line= " " ; while ((line = in.readLine()) != null) { sb.append(line); break; } in.close(); return sb.toString(); } catch(Exception e) { return new String( " Exception: " + e.getMessage()); } } else { try { String username = (String)arg0[0]; String password = (String)arg0[1]; String link="http://myphpmysqlweb.hostei.com/loginpost.php"; String data = URLEncoder.encode( " username " , " UTF - 8 " ) + " = " + URLEncoder.encode(username, " UTF - 8 " ); data + = " & " + URLEncoder.encode( " password " , " UTF - 8 " ) + " = " + URLEncoder.encode(password, " UTF - 8 " ); URL url = new URL(link); URLConnection conn = url.openConnection(); conn.setDoOutput(true); OutputStreamWriter wr = new OutputStreamWriter (conn.getOutputStream()); wr.write( data ); wr.flush(); BufferedReader reader = new BufferedReader (new InputStreamReader(conn.getInputStream())); StringBuilder sb = new StringBuilder(); String line = null; // Read Server Response while((line = reader.readLine()) != null) { sb.append(line); break; } return sb.toString(); } catch(Exception e) { return new String( " Exception: " + e.getMessage()); } } } @Override protected void onPostExecute(String result) { this.statusField.setText( " Login Successful " ); this.roleField.setText(result); } }

Here is the content of activity_main.xml.

< RelativeLayout xmlns:android= " http://schemas.android.com/apk/res/android " xmlns:tools= " http://schemas.android.com/tools " android:layout _ width= " match _ parent " android:layout _ height= " match _ parent " android:paddingBottom= " @dimen/activity _ vertical _ margin " android:paddingLeft= " @dimen/activity _ horizontal _ margin " android:paddingRight= " @dimen/activity _ horizontal _ margin " android:paddingTop= " @dimen/activity _ vertical _ margin " tools:context= " .MainActivity " > < EditText android:id= " @ + id/editText2 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignRight= " @ + id/editText1 " android:layout _ below= " @ + id/editText1 " android:layout _ marginTop= " 25dp " android:ems= " 10 " android:inputType= " textPassword " > < /EditText > < EditText android:id= " @ + id/editText1 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignParentRight= " true " android:layout _ alignParentTop= " true " android:layout _ marginTop= " 44dp " android:ems= " 10 " > < requestFocus android:layout _ width= " wrap _ content " / > < /EditText > < TextView android:id= " @ + id/textView1 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignBottom= " @ + id/editText1 " android:layout _ alignParentLeft= " true " android:text= " @string/Username " / > < TextView android:id= " @ + id/textView3 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignParentTop= " true " android:layout _ centerHorizontal= " true " android:text= " @string/App " android:textAppearance= " ?android:attr/textAppearanceLarge " / > < TextView android:id= " @ + id/textView7 " android:layout _ height= " wrap _ content " android:layout _ alignBottom= " @ + id/textView5 " android:layout _ alignLeft= " @ + id/textView6 " android:text= " @string/Role " android:textAppearance= " ?android:attr/textAppearanceMedium " android:textSize= " 10sp " / > < TextView android:id= " @ + id/textView5 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ below= " @ + id/textView6 " android:layout _ marginTop= " 27dp " android:layout _ toLeftOf= " @ + id/editText1 " android:text= " @string/LoginRole " / > < TextView android:id= " @ + id/textView8 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ above= " @ + id/textView6 " android:layout _ alignLeft= " @ + id/textView5 " android:layout _ marginBottom= " 27dp " android:text= " @string/method " / > < TextView android:id= " @ + id/textView4 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignLeft= " @ + id/textView8 " android:layout _ below= " @ + id/button1 " android:layout _ marginTop= " 86dp " android:text= " @string/LoginStatus " / > < TextView android:id= " @ + id/textView6 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignTop= " @ + id/textView4 " android:layout _ centerHorizontal= " true " android:text= " @string/Status " android:textAppearance= " ?android:attr/textAppearanceMedium " android:textSize= " 10sp " / > < TextView android:id= " @ + id/textView9 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignBottom= " @ + id/textView8 " android:layout _ alignLeft= " @ + id/textView6 " android:text= " @string/Choose " android:textAppearance= " ?android:attr/textAppearanceMedium " android:textSize= " 10sp " / > < Button android:id= " @ + id/button2 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ centerVertical= " true " android:layout _ toRightOf= " @ + id/textView6 " android:onClick= " loginPost " android:text= " @string/LoginPost " / > < Button android:id= " @ + id/button1 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignBaseline= " @ + id/button2 " android:layout _ alignBottom= " @ + id/button2 " android:layout _ alignLeft= " @ + id/textView2 " android:onClick= " login " android:text= " @string/LoginGet " / > < TextView android:id= " @ + id/textView2 " android:layout _ width= " wrap _ content " android:layout _ height= " wrap _ content " android:layout _ alignBaseline= " @ + id/editText2 " android:layout _ alignBottom= " @ + id/editText2 " android:layout _ alignParentLeft= " true " android:text= " @string/Password " / > < /RelativeLayout >

Here is the content of Strings.xml.

< ?xml version= " 1.0 " encoding= " utf-8 " ? > < resources > < string name= " app _ name " > PHPMYSQL < /string > < string name= " action _ settings " > Settings < /string > < string name= " hello _ world " > Hello world! < /string > < string name= " Username " > Username < /string > < string name= " Password " > Password < /string > < string name= " LoginGet " > Login - Get < /string > < string name= " LoginPost " > Login - Post < /string > < string name= " App " > Login Application < /string > < string name= " LoginStatus " > Login Status < /string > < string name= " LoginRole " > Login Role < /string > < string name= " Status " > Not login < /string > < string name= " Role " > Not assigned < /string > < string name= " method " > Login Method < /string > < string name= " Choose " > Choose Method < /string > < /resources >

Here is the content of AndroidManifest.xml.

< ?xml version= " 1.0 " encoding= " utf-8 " ? > < manifest xmlns:android= " http://schemas.android.com/apk/res/android " package= " com.example.phpmysql " android:versionCode= " 1 " android:versionName= " 1.0 " > < uses-sdk android:minSdkVersion= " 8 " android:targetSdkVersion= " 17 " / > < uses-permission android:name= " android.permission.INTERNET " / > < uses-permission android:name= " android.permission.ACCESS _ NETWORK _ STATE " / > < application android:allowBackup= " true " android:icon= " @drawable/ic _ launcher " android:label= " @string/app _ name " android:theme= " @style/AppTheme " > < activity android:name= " com.example.phpmysql.MainActivity " android:label= " @string/app _ name " > < intent-filter > < action android:name= " android.intent.action.MAIN " / > < category android:name= " android.intent.category.LAUNCHER " / > < /intent-filter > < /activity > < /application > < /manifest >

            Let's try to run your PHPMYSQL application. I assume you have connected your actual Android Mobile device with your computer. To run the app from Eclipse, open one of your project's activity files and click Run icon from the toolbar. Before starting your application, Eclipse will display following window to select an option where you want to run your Android application.

            Select your mobile device as an option and then check your mobile device .Now just type in your username and password. In my case i am typing admin as username and password. 

Now press the Get button and wait a few seconds and response will be downloaded and will be shown to you. In this case, the response is the ROLE that is fetched in case of admin as username and password.Now again press the POST button and same result woud appear. All the four screens are shown below

PHP

1. What does a special set of tags <?= and ?> do in PHP? - The output is displayed directly to the browser.
2. What’s the difference between include and require? - It’s how they handle failures. If the file is not found by require(), it will cause a fatal error and halt the execution of the script. If the file is not found by include(), a warning will be issued, but execution will continue.
3. I am trying to assign a variable the value of 0123, but it keeps coming up with a different number, what’s the problem? - PHP Interpreter treats numbers beginning with 0 as octal. Look at the similar PHP interview questions for more numeric problems.


4. Would I use print "$a dollars" or "{$a} dollars" to print out the amount of dollars in this example? - In this example it wouldn’t matter, since the variable is all by itself, but if you were to print something like "{$a},000,000 mln dollars", then you definitely need to use the braces.
5. How do you define a constant? - Via define() directive, like define ("MYCONSTANT", 100);
6. How do you pass a variable by value? - Just like in C++, put an ampersand in front of it, like $a = &$b
7. Will comparison of string "10" and integer 11 work in PHP? - Yes, internally PHP will cast everything to the integer type, so numbers 10 and 11 will be compared.
8. When are you supposed to use endif to end the conditional statement? - When the original if was followed by : and then the code block without braces.
9. Explain the ternary conditional operator in PHP? - Expression preceding the ? is evaluated, if it’s true, then the expression preceding the : is executed, otherwise, the expression following : is executed.
10. How do I find out the number of parameters passed into function? - func_num_args() function returns the number of parameters passed in.
11. If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? - 100, it’s a reference to existing variable.
12. What’s the difference between accessing a class method via -> and via ::? - :: is allowed to access methods that can perform static operations, i.e. those, which do not require object initialization.
13. Are objects passed by value or by reference? - Everything is passed by value.
14. How do you call a constructor for a parent class? - parent::constructor($value)
15. What’s the special meaning of __sleep and __wakeup? - __sleep returns the array of all the variables than need to be saved, while __wakeup retrieves them.
16. Why doesn’t the following code print the newline properly?    <?php
                $str = ‘Hello, there.nHow are you?nThanks for visiting TechInterviews’;
                print $str;
        ?>
    Because inside the single quotes the n character is not interpreted as newline, just as a sequence of two characters - and n.
17. Would you initialize your strings with single quotes or double quotes? - Since the data inside the single-quoted string is not parsed for variable substitution, it’s always a better idea speed-wise to initialize a string with single quotes, unless you specifically need variable substitution.
18. How come the code <?php print "Contents: $arr[1]"; ?> works, but <?php print "Contents: $arr[1][2]"; ?> doesn’t for two-dimensional array of mine? - Any time you have an array with more than one dimension, complex parsing syntax is required. print "Contents: {$arr[1][2]}" would’ve worked.
19. What is the difference between characters 23 and x23? - The first one is octal 23, the second is hex 23.
20. With a heredoc syntax, do I get variable substitution inside the heredoc contents? - Yes.
21. I want to combine two variables together:

     $var1 = 'Welcome to ';
     $var2 = 'TechInterviews.com';
    

    What will work faster? Code sample 1:
    

    $var 3 = $var1.$var2;
    

    Or code sample 2:
    
    

    $var3 = "$var1$var2";
    

    Both examples would provide the same result - $var3 equal to "Welcome to TechInterviews.com". However, Code Sample 1 will work significantly faster. Try it out with large sets of data (or via concatenating small sets a million times or so), and you will see that concatenation works significantly faster than variable substitution.
22. For printing out strings, there are echo, print and printf. Explain the differences. - echo is the most primitive of them, and just outputs the contents following the construct to the screen. print is also a construct (so parentheses are optional when calling it), but it returns TRUE on successful output and FALSE if it was unable to print out the string. However, you can pass multiple parameters to echo, like:

     <?php echo 'Welcome ', 'to', ' ', 'TechInterviews!'; ?>

    and it will output the string "Welcome to TechInterviews!" print does not take multiple parameters. It is also generally argued that echo is faster, but usually the speed advantage is negligible, and might not be there for future versions of PHP. printf  is a function, not a construct, and allows such advantages as formatted output, but it’s the slowest way to print out data out of echo, print and printf.
23. I am writing an application in PHP that outputs a printable version of driving directions. It contains some long sentences, and I am a neat freak, and would like to make sure that no line exceeds 50 characters. How do I accomplish that with PHP? - On large strings that need to be formatted according to some length specifications, use wordwrap() or chunk_split().
24. What’s the output of the ucwords function in this example?

     $formatted = ucwords("TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS");
     print $formatted;

    What will be printed is TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS.
    ucwords() makes every first letter of every word capital, but it does not lower-case anything else. To avoid this, and get a properly formatted string, it’s worth using strtolower() first.
25. What’s the difference between htmlentities() and htmlspecialchars()? - htmlspecialchars only takes care of <, >, single quote ‘, double quote " and ampersand. htmlentities translates all occurrences of character sequences that have different meaning in HTML.
26. What’s the difference between md5(), crc32() and sha1() crypto on PHP? - The major difference is the length of the hash generated. CRC32 is, evidently, 32 bits, while sha1() returns a 128 bit value, and md5() returns a 160 bit value. This is important when avoiding collisions.
27. So if md5() generates the most secure hash, why would you ever use the less secure crc32() and sha1()? - Crypto usage in PHP is simple, but that doesn’t mean it’s free. First off, depending on the data that you’re encrypting, you might have reasons to store a 32-bit value in the database instead of the 160-bit value to save on space. Second, the more secure the crypto is, the longer is the computation time to deliver the hash value. A high volume site might be significantly slowed down, if frequent md5() generation is required.
28. How do you match the character ^ at the beginning of the string? - ^^