Moving data derived from MySQL from one PHP to another

Question

I am creating an Android app where I have a login page and then a page showing data based on who logged in.

The login page is as follows:

    <?php
    include('listview.php');
    $hostname_localhost ="URL";
    $database_localhost ="project_test";
    $username_localhost ="user";
    $password_localhost ="pass";
    $localhost = mysql_connect($hostname_localhost,$username_localhost,$password_localhost)
    or
    trigger_error(mysql_error(),E_USER_ERROR);

    mysql_select_db($database_localhost, $localhost);

    $username = $_POST['username'];
    $password = $_POST['password'];
    $query_search = "select * from Employee where Emp_Email = '".$username."' AND Emp_Password = '".$password. "'";
    $userid = mysql_query("SELECT Group_ID FROM Employee where Emp_Email = '".$username."' AND Emp_Password = '".$password. "'");

    $query_exec = mysql_query($query_search) or die(mysql_error());
    $rows = mysql_num_rows($query_exec);
    //echo $rows;
     if(($rows == 0) || ($username == NULL)) { 
     echo "No Such User Found"; 
     }
     else  {
        echo "User Found";  
    }
    ?>

And my listview page is:

<?php 
$con = $con = mysql_connect("URL","user","pass");

if (!$con)   {   die('Could not connect: ' . mysql_error());   }
mysql_select_db("project_nfc", $con);

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

while($row = mysql_fetch_assoc($result))   {    $output[]=$row;   }

print(json_encode($output));

mysql_close($con); 
?>

In the variable $result I am trying to show only the employees that have the same GroupID as the person who logged in.

So:

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

Should be something like

$result = mysql_query("SELECT * FROM Employee where Group_ID = '".$userid."'");

where $userid is derived from the login PHP page and sent to the list view page.

So basically how do I send the Group_ID from the user who logged in to the listview? I only want to show the employees that have the same Group_ID as the logged in user.

Here is the Android side of the code:

Login view:

package dbtesting.example.com.testexternaldb;

import android.app.Activity;

import org.apache.http.client.ResponseHandler;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.ResponseHandler;
import org.apache.http.client.entity.UrlEncodedFormEntity;
        import org.apache.http.client.methods.HttpPost;
        import org.apache.http.impl.client.BasicResponseHandler;
        import org.apache.http.impl.client.DefaultHttpClient;
        import org.apache.http.message.BasicNameValuePair;
        import android.app.Activity;
        import android.app.AlertDialog;
        import android.app.ProgressDialog;
        import android.content.DialogInterface;
        import android.content.Intent;
        import android.os.Bundle;
        import android.view.View;
        import android.view.View.OnClickListener;
        import android.widget.Button;
        import android.widget.EditText;
        import android.widget.TextView;
        import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class LoginPage extends Activity {
    Button b;
    EditText et,pass;
    TextView tv;
    HttpPost httppost;
    StringBuffer buffer;
    HttpResponse response;
    HttpClient httpclient;
    List<NameValuePair> nameValuePairs;
    ProgressDialog dialog = null;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.login_layout);

        b = (Button)findViewById(R.id.Button01);
        et = (EditText)findViewById(R.id.username);
        pass= (EditText)findViewById(R.id.password);
        tv = (TextView)findViewById(R.id.tv);

        b.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                dialog = ProgressDialog.show(LoginPage.this, "",
                        "Validating user...", true);
                new Thread(new Runnable() {
                    public void run() {
                        login();
                    }
                }).start();
            }
        });
    }

    void login(){
        try{

            httpclient=new DefaultHttpClient();
            httppost= new HttpPost("http://URL/Android/check.php"); // make sure the url is correct.
            //add your data
            nameValuePairs = new ArrayList<NameValuePair>(2);
            // Always use the same variable name for posting i.e the android side variable name and php side variable name should be similar,
            nameValuePairs.add(new BasicNameValuePair("username",et.getText().toString().trim()));  // $Edittext_value = $_POST['Edittext_value'];
            nameValuePairs.add(new BasicNameValuePair("password",pass.getText().toString().trim()));
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            //Execute HTTP Post Request
            response=httpclient.execute(httppost);
            // edited by James from coderzheaven.. from here....
            ResponseHandler<String> responseHandler = new BasicResponseHandler();
            final String response = httpclient.execute(httppost, responseHandler);
            System.out.println("Response : " + response);
            runOnUiThread(new Runnable() {
                public void run() {
                    tv.setText("Response from PHP : " + response);
                    dialog.dismiss();
                }
            });

            if(response.equalsIgnoreCase("User Found")){
                runOnUiThread(new Runnable() {
                    public void run() {
                        Toast.makeText(LoginPage.this,"Login Success", Toast.LENGTH_SHORT).show();
                    }
                });


                startActivity(new Intent(LoginPage.this, MainActivity.class));

            }else{
                showAlert();
            }

        }catch(Exception e){
            dialog.dismiss();
            System.out.println("Exception : " + e.getMessage());
        }
    }
    public void showAlert(){
        LoginPage.this.runOnUiThread(new Runnable() {
            public void run() {
                AlertDialog.Builder builder = new AlertDialog.Builder(LoginPage.this);
                builder.setTitle("Login Error.");
                builder.setMessage("User not Found.")
                        .setCancelable(false)
                        .setPositiveButton("OK", new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog, int id) {
                            }
                        });
                AlertDialog alert = builder.create();
                alert.show();
            }
        });
    }
}

List view (2 pages):

Apiconector:

package dbtesting.example.com.testexternaldb;

        import android.util.Log;
        import org.apache.http.HttpEntity;
        import org.apache.http.HttpResponse;
        import org.apache.http.client.ClientProtocolException;
        import org.apache.http.client.methods.HttpGet;
        import org.apache.http.impl.client.DefaultHttpClient;
        import org.apache.http.util.EntityUtils;
        import org.json.JSONArray;
        import org.json.JSONException;

        import java.io.IOException;

public class ApiConnector {


    public JSONArray GetAllCustomers()
    {
        // URL for getting all customers


        String url = "http://URL/Android/listview.php";

        // Get HttpResponse Object from url.
        // Get HttpEntity from Http Response Object

        HttpEntity httpEntity = null;

        try
        {

            DefaultHttpClient httpClient = new DefaultHttpClient();  // Default HttpClient
            HttpGet httpGet = new HttpGet(url);

            HttpResponse httpResponse = httpClient.execute(httpGet);

            httpEntity = httpResponse.getEntity();



        } catch (ClientProtocolException e) {

            // Signals error in http protocol
            e.printStackTrace();

            //Log Errors Here



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


        // Convert HttpEntity into JSON Array
        JSONArray jsonArray = null;

        if (httpEntity != null) {
            try {
                String entityResponse = EntityUtils.toString(httpEntity);

                Log.e("Entity Response  : ", entityResponse);

                jsonArray = new JSONArray(entityResponse);

            } catch (JSONException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return jsonArray;


    }


}

Actual listviewpage:

package dbtesting.example.com.testexternaldb;

        import android.app.Activity;
        import android.os.AsyncTask;
        import android.support.v7.app.ActionBarActivity;
        import android.support.v7.app.ActionBar;
        import android.support.v4.app.Fragment;
        import android.os.Bundle;
        import android.view.LayoutInflater;
        import android.view.Menu;
        import android.view.MenuItem;
        import android.view.View;
        import android.view.ViewGroup;
        import android.os.Build;
        import android.widget.BaseAdapter;
        import android.widget.ListView;
        import android.widget.TextView;

        import org.json.JSONArray;
        import org.json.JSONException;
        import org.json.JSONObject;

public class MainActivity extends ActionBarActivity {

    private ListView GetAllCustomerListView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        this.GetAllCustomerListView = (ListView) this.findViewById(R.id.GetAllCustomerListView);

        new GetAllCustomerTask().execute(new ApiConnector());


    }

    private void setListAdapter(JSONArray jsonArray)
    {
        this.GetAllCustomerListView.setAdapter(new GetAllCustomerListViewAdapter(jsonArray,this));
    }


    private class GetAllCustomerTask extends AsyncTask<ApiConnector,Long,JSONArray>
    {
        @Override
        protected JSONArray doInBackground(ApiConnector... params) {

            // it is executed on Background thread

            return params[0].GetAllCustomers();
        }

        @Override
        protected void onPostExecute(JSONArray jsonArray) {

            setListAdapter(jsonArray);



        }
    }
}

(1) Your mysql_query string is missing quotes, check the syntax highlighting. (2) What is the question? — delatbabel, Feb 18 '15 at 15:02
what you should do is save the data in the cookie, in the session, or send it through post/get methods — , Feb 18 '15 at 15:24
I tried the post get and it did not work, got a null for group_id — ThaNerd, Feb 18 '15 at 15:32
**Don't use `mysql_` functions.** Use `mysqli_` or `PDO`. And I would recommend going with the Object Oriented approach (of `mysqli_` if you use that and not pdo) because it promotes good coding practices in general compared to using the non-OO approach — Reed, Feb 18 '15 at 17:24
And you should either sanitize paramaters (using `real_escape_string`) or use prepared statements, which are better, but take more work. And hashing should be used for passwords in your DB instead of using plaintext passwords. — Reed, Feb 18 '15 at 17:28

score 0 · Answer 1 · answered Feb 18 '15 at 15:05

I dont know how you have your DB, try this:

    <?php 
$con = $con = mysql_connect("URL","user","pass");

if (!$con)   {   die('Could not connect: ' . mysql_error());   }
mysql_select_db("project_nfc", $con);

$result = mysql_query("SELECT * FROM Employee where id_group=".$id_group." ");

while($row = mysql_fetch_assoc($result))   {    $output[]=$row;   }

print(json_encode($output));

mysql_close($con); 
?>

Make sure you have a column name id_group and have a variable $id_group

Yes but how do I get the id_group for the user who logged in via the login page and send it to the listview page? — ThaNerd, Feb 18 '15 at 15:24

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

In your first login file add this code so it puts group_id into session var:

$userid = mysql_query("SELECT Group_ID FROM Employee where Emp_Email = '".$username."' AND Emp_Password = '".$password. "'");
if ($row = mysql_fetch_assoc($userid)) {
    session_start();
    $_SESSION['group_id'] = $row['Group_ID'];
}

And in second one you can do something like:

session_start();    
if (isset($_SESSION['group_id'])) {
    $result = mysql_query("SELECT * FROM Employee where group_id=".$_SESSION['group_id']." ");
    }else{
    $result = mysql_query("SELECT * FROM Employee");
    }

But please stop use mysql_ it is deprecated, switch development to PDO or mysqli

And here is a link to how to handle sessions in java for Android https://stackoverflow.com/a/6054828/4421474

so in your login view you can change:

HttpClient httpclient;

to

public static DefaultHttpClient httpclient;

and in apiconnector change:

DefaultHttpClient httpClient = new DefaultHttpClient();  // Default HttpClient
            HttpGet httpGet = new HttpGet(url);

            HttpResponse httpResponse = httpClient.execute(httpGet);

to:

HttpGet httpGet = new HttpGet(url);
HttpResponse httpResponse = LoginPage.httpClient.execute(httpGet);

edited May 23 '17 at 12:12

Community

1
1

answered Feb 18 '15 at 15:38

Alex

16,739
1
28
51

What I find strange is in order to remove the actual user who logged in from the list I have to do: mysql_query("SELECT * FROM Employee where Group_ID='".$_SESSION['group_id']."' AND Emp_Email='".$_SESSION['useremail']."'") instead of: mysql_query("SELECT * FROM Employee where Group_ID='".$_SESSION['group_id']."' AND Emp_Email<>'".$_SESSION['useremail']."'") Shouldn't it be <> to the users email??? – ThaNerd Feb 18 '15 at 18:12
I wanted to show all user except the one that logged in. So I added to the SQL statement AND Emp_Email<>'".$_SESSION['useremail']. But it did not work. The only thing that worked was this: AND Emp_Email='".$_SESSION['useremail']. Which is strange since I want it to show every email except the one from the login...anyhow it works so no worries. – ThaNerd Feb 18 '15 at 19:43
don't you forget to save useremail in `$_SESSION` on login.php ? – Alex Feb 18 '15 at 19:49
I did, I forgot to add the useremail in the mysql_query select statment. All works now! Thanks again for your help!! – ThaNerd Feb 18 '15 at 22:11

Moving data derived from MySQL from one PHP to another

2 Answers2