Shift 2 Dev
MySQL Communication With Android
In this post I will show you how to implement Android communication with a remote MySQL server via PHP.
MySQL Communication With Android
January 26, 2025
Custom Functions

In this post I will show you how I created an app for MySQL communication with Android.

Key Notes

The test environment for this is a locally installed MySQL server on my machine running Windows with IIS. The IIS server script to access the MySQL server will be made with PHP.

For Android, MVI Android architecture as well as the Retrofit library for network calls.

MySQL communication with Android can be very straightforward. I will go through each part of the project step by step.

Code

Below is my project structure. I will show you each class for the project.

Image showing the project level breakdown for Android

build.gradle

Add dependencies

    //retrofit
    implementation(libs.retrofit)
    //GSON
    implementation(libs.converter.gson)
    
    implementation(libs.logging.interceptor)

libs.versions.toml

[versions]
....
loggingInterceptor = "4.11.0"
retrofit = "2.11.0"
converterGson = "2.10.0"
....

[libraries]
....
logging-interceptor = { module = "com.squareup.okhttp3:logging-interceptor", version.ref = "loggingInterceptor" }
retrofit = { module = "com.squareup.retrofit2:retrofit", version.ref = "retrofit" }
converter-gson = { module = "com.squareup.retrofit2:converter-gson", version.ref = "converterGson" }
....

UserData

This holds the data type for the register page.

data class UserData(
    @SerializedName("username")
    val username : String,
    @SerializedName("email")
    val email : String,
    @SerializedName("password")
    val password : String
)

AccessAPI

The interface to hold the call functions for Retrofit

interface AccessApi {

    @POST("users_list.php")
    fun getUsers() : Call<List<UsersType>>

    @POST("register.php")
    fun registerUser(@Body userdata : UserData) : Call<UsersType>
}

RetrofitHelper

The class that creates the Retrofit object for use in the interface call functions

object RetrofitHelper {
    val baseUrl = "http://<server_name_or_addr>/include/"
    fun getInstance(): Retrofit {
        val httpLoggingInterceptor: HttpLoggingInterceptor = HttpLoggingInterceptor()
        httpLoggingInterceptor.setLevel(HttpLoggingInterceptor.Level.BODY)

        val okHttpClient = OkHttpClient.Builder()
            .addInterceptor(httpLoggingInterceptor)
            .build()

        val gson = GsonBuilder()
            .setLenient().create()

        return Retrofit.Builder()
            .baseUrl(baseUrl)
            .addConverterFactory(nullOnEmptyConverterFactory)
            .addConverterFactory(GsonConverterFactory.create(gson))
            .client(okHttpClient)
            .build()
    }
}

val nullOnEmptyConverterFactory = object : Converter.Factory() {
    fun converterFactory() = this
    override fun responseBodyConverter(type: Type, annotations: Array<out Annotation>, retrofit: Retrofit) = object : Converter<ResponseBody, Any?> {
        val nextResponseBodyConverter = retrofit.nextResponseBodyConverter<Any?>(converterFactory(), type, annotations)
        override fun convert(value: ResponseBody) = if (value.contentLength() != 0L) nextResponseBodyConverter.convert(value) else null
    }
}

UsersType

The data type used for holding each user account info. I used this in the getUsers() function call as well as a single reference to hold the user when registration is successful.

data class UsersType(
    @SerializedName("id")
    val id : Int,
    @SerializedName("username")
    val username : String,
    @SerializedName("email")
    val email : String,
    @SerializedName("password")
    val password : String,
    @SerializedName("acc_token")
    val acc_token : String
)

RegisteredUsersList

A Composable function that is attached to the MainActivity. There is a button and a LazyRow. When button is clicked, it gets all the registered users from the MySQL database.

@Composable
fun RegisteredUsersList(
    results: List<UsersType>,
    userListRequestFail: Boolean,
    handleEvents: (ItemIntent) -> Unit
) {
    Button(onClick = {
        handleEvents(ItemIntent.updateUserList(emptyList()))
    }) {
        Text(text = "Update Data")
    }
    if (userListRequestFail) {
        Row(
            modifier = Modifier
                .fillMaxWidth()
                .padding(20.dp),
            horizontalArrangement = Arrangement.Start
        ) {
            Text(text = "Server Request Failed")
        }
    }
    LazyRow(
        modifier = Modifier
            .fillMaxWidth()
            .padding(10.dp),
        verticalAlignment = Alignment.CenterVertically
    ) {
        items(results) { user ->
            ElevatedCard(
                modifier = Modifier.padding(10.dp),
                colors = CardDefaults.cardColors(MaterialTheme.colorScheme.onPrimary)
            ) {
                Text(
                    modifier = Modifier.padding(10.dp), textAlign = TextAlign.Center,
                    text = "${user.id} : " +
                            "\n${user.username}" +
                            "\n${user.email}" +
                            "\n${user.password}" +
                            "\n${user.acc_token}"
                )
            }
        }
    }
}

RegisterUI

The main UI composable that is attached to the MainActivity to handle the user registration. This contains some form input validation.

Note

A better Android form validation can be viewed at the link below.

Android Compose Registration UI With Form Validation

Breakdown

Create the UI composable.

@Composable
fun RegisterUI(
    userRegisterFail: Int,
    isRegisterProcessing: Boolean,
    currentUserData: UsersType,
    handleEvents: (ItemIntent) -> Unit
) {

...

}

Initialize Variables

Add the necessary variables needed.

    var usernameValidator by remember { mutableStateOf(true) }
    var emailValidator by remember { mutableStateOf(true) }
    var passwordValidator by remember { mutableStateOf(true) }

    var passwordVisible by remember { mutableStateOf(false) }

    var username by remember { mutableStateOf("Winston") }
    var email by remember { mutableStateOf("winston@clowncollege.com") }
    var password by remember { mutableStateOf("Password01") }

Form Composable With Validation

Create the UI form with validation. The UI form is built from an ElevatedCard composable.

    ElevatedCard(
        modifier = Modifier
            .fillMaxWidth()
            .padding(25.dp),
        colors = CardDefaults.elevatedCardColors(MaterialTheme.colorScheme.primaryContainer)
    ) {
        Column(
            modifier = Modifier
                .fillMaxWidth()
                .padding(10.dp), horizontalAlignment = Alignment.CenterHorizontally
        ) {
            Spacer(modifier = Modifier.padding(10.dp))
            Text(text = "Register", style = MaterialTheme.typography.titleMedium)
            Spacer(modifier = Modifier.padding(10.dp))
            TextField(value = username, onValueChange = {
                username = it
                if (username.isEmpty()) {
                    usernameValidator = false
                } else {
                    usernameValidator = true
                }
            }, placeholder = { Text(text = "Choose your user name") })
            if (!usernameValidator) {
                Text(
                    text = "Invalid Username",
                    style = MaterialTheme.typography.bodySmall,
                    color = MaterialTheme.colorScheme.error
                )
            }
            Spacer(modifier = Modifier.padding(10.dp))
            TextField(value = email, onValueChange = {
                email = it
                if (email.isEmpty() || !android.util.Patterns.EMAIL_ADDRESS.matcher(email)
                        .matches()
                ) {
                    emailValidator = false
                } else {
                    emailValidator = true
                }
            }, placeholder = { Text(text = "Enter Your Email") })
            if (!emailValidator) {
                Text(
                    text = "Invalid Email",
                    style = MaterialTheme.typography.bodySmall,
                    color = MaterialTheme.colorScheme.error
                )
            }
            Spacer(modifier = Modifier.padding(10.dp))
            TextField(value = password, onValueChange = {
                password = it
                if (password.isEmpty() || password.length < 8 || !password.contains("[0-9]".toRegex()) || !password.contains("[A-Z]".toRegex()) || password.contains("[{('\"~\\[|\\]^)}]".toRegex())) {
                    passwordValidator = false
                } else {
                    passwordValidator = true
                }
            }, placeholder = { Text(text = "Enter Your Password") },
                visualTransformation = if (passwordVisible) VisualTransformation.None else PasswordVisualTransformation(),
                trailingIcon = {
                    IconButton(onClick = { passwordVisible = !passwordVisible }) {
                        Icon(
                            imageVector = if (passwordVisible) Icons.Filled.Visibility else Icons.Filled.VisibilityOff,
                            contentDescription = if (passwordVisible) "Hide Password" else "Show Password"
                        )
                    }
                }
            )
            if (!passwordValidator) {
                Text(
                    text = "Invalid Password" +
                            "\n-Needs to be at least 8 characters long" +
                            "\n-At least 1 number" +
                            "\n-At least 1 capital letter" +
                            "\n-Must Not contain one of the following " +
                            "special characters:" +
                            "\n { ( ' \" ~ [ |  ] ^ ) }",
                    style = MaterialTheme.typography.bodySmall,
                    color = MaterialTheme.colorScheme.error
                )
            }
            Spacer(modifier = Modifier.padding(10.dp))
            if (!isRegisterProcessing) {
                var check = listOf(usernameValidator, emailValidator, passwordValidator)
                Button(
                    enabled = if (check.any { !it }) false else true,
                    onClick = {
                        handleEvents(
                            ItemIntent.registerUserData(
                                UserData(
                                    username = username,
                                    email = email,
                                    password = password
                                )
                            )
                        )
                    }) {
                    Text(text = "Register")
                }
            } else {
                CircularProgressIndicator()
            }
            //add network response validation here
            
        }
    }

UI Updates From Network Response

Update the UI for the user based on network response.

            if (userRegisterFail != RegisterFailed.NO_ERROR) {
                Spacer(modifier = Modifier.padding(10.dp))
                when (userRegisterFail) {
                    RegisterFailed.FORM_DATA_ISSUE -> {
                        Text(
                            text = "Error, Something went wrong. Please validate form",
                            style = MaterialTheme.typography.bodySmall,
                            color = MaterialTheme.colorScheme.error
                        )
                    }
                    
                    RegisterFailed.CONNECTION_ISSUE -> {
                        Text(
                            text = "There is a connection issue...",
                            style = MaterialTheme.typography.bodySmall,
                            color = MaterialTheme.colorScheme.error
                        )
                    }

                    RegisterFailed.EMAIL_EXISTS -> {
                        Text(
                            text = "Error, Email already exists. Please login or choose another.",
                            style = MaterialTheme.typography.bodySmall,
                            color = MaterialTheme.colorScheme.error
                        )
                    }

                    RegisterFailed.PROBLEM_ADDING_CREDENTIALS -> {
                        Text(
                            text = "Credential Issue",
                            style = MaterialTheme.typography.bodySmall,
                            color = MaterialTheme.colorScheme.error
                        )
                    }
                    
                    RegisterFailed.ERROR_VALIDATING_CREDENTIALS -> {
                        Text(
                            text = "Please validate form",
                            style = MaterialTheme.typography.bodySmall,
                            color = MaterialTheme.colorScheme.error
                        )
                    }
                }
            }
            if (currentUserData.id >= 0) {
                Spacer(modifier = Modifier.padding(10.dp))
                Text(
                    text = "Welcome ${currentUserData.username}",
                    color = MaterialTheme.colorScheme.tertiary
                )
            }
        }

Data.kt

Part of the MVI architecture, Data.kt is the data state in my MVI ( I know my MVI could be better optimized, it grows then I readjust, then grows, etc... )

data class DataState(

    var userList : List<UsersType> = emptyList(),
    var didUserListRequestFail : Boolean = false,
    var didUserRegisterFail : Int = RegisterFailed.NO_ERROR,
    var isRegisterProcessing : Boolean = false,
    var currentUserData : UsersType = UsersType(RegisterFailed.NO_ERROR, "","","","")
)

data object RegisterFailed {
    val NO_ERROR : Int = -10
    val EMAIL_EXISTS : Int = -1
    val PROBLEM_ADDING_CREDENTIALS : Int = -2
    val ERROR_VALIDATING_CREDENTIALS : Int = -3
    val CONNECTION_ISSUE : Int = -4
    val FORM_DATA_ISSUE : Int = -5
}

ItemIntent

Part of the MVI architecture, ItemIntent is the intent/actions used in the ViewModel.

sealed interface ItemIntent {

    data class updateUserList(val userList: List<UsersType>) : ItemIntent
    data class updateDidUserListRequestFail(val didUserListRequestFail : Boolean) : ItemIntent
    data class updateDidUserRegisterFail(val didUserRegisterFail : Int) : ItemIntent
    data class updateIsRegisterProcessing(val isRegisterProcessing : Boolean) : ItemIntent
    data class registerUserData(val userData : UserData) : ItemIntent
    data class updateCurrentUserData(val currentUserData: UsersType) : ItemIntent
    data class onError(val error: String) : ItemIntent

}

ViewModel

Breakdown

Initialize ViewModel with the state object referenced in the Data.kt file.

class ViewModel() : ViewModel() {

    private val _mvistate: MutableStateFlow<DataState> = MutableStateFlow<DataState>(DataState())
    val mvistate: StateFlow<DataState> = _mvistate.asStateFlow()
 
 ...
    
}

Methods For MVI ItemIntent

Create a when() statement to reference the functions in the ItemIntent object.

    fun handleEvents(event: ItemIntent) {
        when (event) {

            is ItemIntent.updateCurrentUserData -> {
                updateCurrentUserData(event)
            }

            is ItemIntent.registerUserData -> {
                registerUserData(event)
            }

            is ItemIntent.updateIsRegisterProcessing -> {
                updateIsRegisterProcessing(event)
            }

            is ItemIntent.updateDidUserRegisterFail -> {
                updateDidUserRegisterFail(event)
            }

            is ItemIntent.updateUserList -> {
                updateUserList()
            }

            is ItemIntent.updateDidUserListRequestFail -> {
                updateDidUserListRequestFail(event)
            }

            is ItemIntent.onError -> {
                onError()
            }

        }
    }

//reference all the private functions stated above here

ViewModel Functions

Add all the functions to the ViewModel.

    private fun updateCurrentUserData(event : ItemIntent.updateCurrentUserData){
        viewModelScope.launch {
            _mvistate.update {
                _mvistate.value.copy(
                    currentUserData = event.currentUserData
                )
            }
        }
    }

    private fun updateIsRegisterProcessing(event : ItemIntent.updateIsRegisterProcessing){
        viewModelScope.launch {
            _mvistate.update {
                _mvistate.value.copy(
                    isRegisterProcessing = event.isRegisterProcessing
                )
            }
        }
    }

    private fun updateDidUserRegisterFail(event : ItemIntent.updateDidUserRegisterFail){
        viewModelScope.launch {
            _mvistate.update {
                _mvistate.value.copy(
                    didUserRegisterFail = event.didUserRegisterFail
                )
            }
            if(event.didUserRegisterFail != RegisterFailed.NO_ERROR){
                _mvistate.update {
                    _mvistate.value.copy(
                        currentUserData = UsersType(RegisterFailed.NO_ERROR, "","","","")
                    )
                }
            }
        }
    }

    private fun registerUserData(event : ItemIntent.registerUserData){
        updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(RegisterFailed.NO_ERROR))
        updateIsRegisterProcessing(ItemIntent.updateIsRegisterProcessing(true))
        val query = RetrofitHelper.getInstance().create(AccessApi::class.java)
        Log.i("Values", event.userData.toString())
        val call: Call<UsersType> = query.registerUser(event.userData)
        call.enqueue(object : Callback<UsersType> {
            override fun onFailure(call: Call<UsersType>, error: Throwable) {
                Log.d("onFailure", error.message.toString())
                updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(RegisterFailed.CONNECTION_ISSUE))
                updateIsRegisterProcessing(ItemIntent.updateIsRegisterProcessing(false))
            }

            override fun onResponse(call: Call<UsersType>, response: Response<UsersType>) {
                if(response.code() == 200){
                    val result = response.body()
                    Log.d("onResponse", response.code().toString())
                    if(result != null && result.id >= 0){
                        Log.i("Result", result.toString())
                        updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(result.id))
                        updateCurrentUserData(ItemIntent.updateCurrentUserData(result))
                    } else {
                        if (result != null) {
                            updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(result.id))
                        } else {
                            updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(RegisterFailed.FORM_DATA_ISSUE))
                        }
                    }
                } else {
                    updateDidUserRegisterFail(ItemIntent.updateDidUserRegisterFail(RegisterFailed.FORM_DATA_ISSUE))
                }
                updateIsRegisterProcessing(ItemIntent.updateIsRegisterProcessing(false))
            }
        })
    }

    private fun updateDidUserListRequestFail(event: ItemIntent.updateDidUserListRequestFail) {
        viewModelScope.launch {
            _mvistate.update {
                _mvistate.value.copy(
                    userList = emptyList(),
                    didUserListRequestFail = event.didUserListRequestFail
                )
            }
        }
    }

    private fun updateUserList() {
        val query = RetrofitHelper.getInstance().create(AccessApi::class.java)
        val call: Call<List<UsersType>> = query.getUsers()
        call.enqueue(object : Callback<List<UsersType>> {
            override fun onFailure(call: Call<List<UsersType>>, error: Throwable) {
                _mvistate.update {
                    _mvistate.value.copy(
                        userList = emptyList(),
                        didUserListRequestFail = true
                    )
                }
            }

            override fun onResponse(
                call: Call<List<UsersType>>,
                response: Response<List<UsersType>>
            ) {
                val result = response.body()
                Log.i("Result", result?.size.toString())
                if (result != null) {
                    if (result.isNotEmpty() && result[0].id != -1) {
                        _mvistate.update {
                            _mvistate.value.copy(
                                userList = result,
                                didUserListRequestFail = false
                            )
                        }
                    }
                    if (result.isNotEmpty() && result[0].id == -1) {
                        _mvistate.update {
                            _mvistate.value.copy(
                                userList = emptyList(),
                                didUserListRequestFail = false
                            )
                        }
                    }
                } else {
                    _mvistate.update {
                        _mvistate.value.copy(
                            userList = emptyList(),
                            didUserListRequestFail = true
                        )
                    }
                }
            }
        })
    }

    private fun onError() {

    }

ViewModelFactory

class ViewModelFactory() : ViewModelProvider.Factory {

    override fun <T : ViewModel> create(modelClass: Class<T>): T {
        return if(modelClass.isAssignableFrom(com.itgeek25.syncourlists.viewmodel.ViewModel::class.java)){
            ViewModel() as T
        } else {
            throw IllegalArgumentException("ViewModel Not Found!!")
        }
    }
}

MainActivity

Put it all together in the main UI.

class MainActivity : ComponentActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        val factory = ViewModelFactory()
        val viewModel = ViewModelProvider(
            this,
            factory
        )[ViewModel::class.java]

        setContent {
            SyncOurListsTheme {
                val state = viewModel.mvistate.collectAsStateWithLifecycle().value
                val results = state.userList
                val userListRequestFail = state.didUserListRequestFail
                val userRegisterFail = state.didUserRegisterFail
                val isRegisterProcessing = state.isRegisterProcessing
                val currentUserData = state.currentUserData

                Scaffold(modifier = Modifier.fillMaxSize() { innerPadding ->

                    Column(
                        modifier = Modifier
                            .fillMaxSize()
                            .padding(innerPadding),
                        horizontalAlignment = Alignment.CenterHorizontally,
                        verticalArrangement = Arrangement.Center
                    ) {
                        RegisterUI(userRegisterFail, isRegisterProcessing, currentUserData, viewModel::handleEvents)

                        RegisteredUsersList(results, userListRequestFail, viewModel::handleEvents)
                    }

                }
            }
        }
    }
}

I could see that is a lot but the MVI could be removed to simplify the process. I am still expanding on this so I am keeping it for ease of state changes in one place.

PHP Server Side

connect.php

Establishes a connection to the database

<?php

$con = new mysqli("localhost", "root", "my_password", "db_name");

?>

users_list.php

Used to get all the users in database

<?php

require_once 'connect.php';

if($con){
    $get_all_users = "SELECT * FROM users";
    $query = $con->query($get_all_users);
    if($query->num_rows > 0){
        $responses = array();
        while($row=$query->fetch_object())
        {
            $responses[] = $row;
        }			
        echo json_encode($responses);
        $query->close();
    } else {
    //error or empty list tracking
        echo json_encode(array(
                            array(
                                'id' => "-1",
                                )
                            )
                        );
    }
    mysqli_close($con);
}
?>

register.php

Used to get the data type sent from Android app, process data, then return back the user account created

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    require_once 'connect.php';

    if($con){
        $data = json_decode(file_get_contents('php://input'));
    
        $username = mysqli_real_escape_string($con, $data->{'username'});
        $email = mysqli_real_escape_string($con, $data->{'email'});
        $password = mysqli_real_escape_string($con, $data->{'password'});
    
    
        $check_email = $con->prepare("SELECT * FROM users WHERE email = ?");
        $check_email->bind_param("s", $email);
        $check_email->execute();
        $check_email->store_result();
        if($check_email->num_rows > 0){
            //error for email exists
            echo json_encode(array(
                'id' => "-1",
            )); 
        } else {
            $my_token = sha1($password);
            $pwd = password_hash($password, PASSWORD_DEFAULT); // to verify password on login ------ if(password_verify($password, $hashed_password)) { }
            $ins_stmt = $con->prepare("INSERT INTO users (username, email, password, acc_token) VALUES (?, ?, ?, ?)");
            $ins_stmt->bind_param("ssss", $username, $email, $pwd, $my_token);
            
            if($ins_stmt->execute()){
                $find_user = $con->prepare("SELECT * FROM users WHERE email = ?");
                $find_user->bind_param("s", $email);
                $find_user->execute();
                $result = $find_user->get_result();
                if($result->num_rows == 1){
                    $row = $result->fetch_array();
                    echo json_encode(array(
                            'id' => $row['id'],
                            'username' => $row['username'],
                            'email' => $row['email'],
                            'password' => $row['password'],
                            'acc_token' => $row['acc_token']
                    )); 
                } else {
                    //error for cant find user account
                    echo json_encode(array(
                        'id' => "-3",
                    ));
                }

                $find_user->close();
            } else {
                //error for inserting problem
                echo json_encode(array(
                    'id' => "-2",
                )); 
            }

            $ins_stmt->close();
    
        }
        $check_email->close();
        mysqli_close($con);
    }
} else {  
//send to 404 if request is not POST
    http_response_code(404);
}

?>

That is it. Hope this helps anyone.

NOTES

If you are having issues with the server receiving the data type UserData for the registration action, you can try the changes below.

ViewModel

Change the ViewModel to:

        //val call: Call<UsersType> = query.registerUser(event.userData)
        val call: Call<UsersType> = query.registerUser(
            event.userData.username,
            event.userData.email,
            event.userData.password
        )

AccessAPI

Change the AccessAPI to:

    /*@POST("register.php")
    fun registerUser(@Body userdata : UserData) : Call<UsersType>*/

    @FormUrlEncoded
    @POST("register.php")
    fun registerUser(
        @Field("username") username: String,
        @Field("email") email: String,
        @Field("password") password: String
    ): Call<UsersType>

register.php

Change the register.php on the PHP server to:

    //$data = json_decode(file_get_contents('php://input'));


    //$username = mysqli_real_escape_string($con, $data->{'username'});
    //$email = mysqli_real_escape_string($con, $data->{'email'});
    //$password = mysqli_real_escape_string($con, $data->{'password'});

    $username = mysqli_real_escape_string($con, $_POST['username']);
    $email = mysqli_real_escape_string($con, $_POST['email']);
    $password = mysqli_real_escape_string($con, $_POST['password']);

I had this issue in the beginning, my logcat kept giving me a HTTP error code of 500 stating the items in UserData object were null. The script was not properly using json_decode(). I figured I would post both ways in case someone needs it.

🔖Tags: android compose jetpack json kotlin mysql php project ui

Leave a Reply

Your email address will not be published. Required fields are marked *