-1
\$\begingroup\$

I'm new to MySQL database design. My question is this database is correctly built or not. We used the users table to login and get account type enter image description here

and here the code to login

 $result = mysqli_query($db->_connect()," SELECT * FROM users WHERE nam = $_n AND pas = $_p ");

if (!empty($result)) {
    // check for empty result
    if (mysqli_num_rows($result) > 0) {
        $response["userInfo"] = array(); 
        while ($row = mysqli_fetch_array($result)) {
            $userInfo = array();
            $userInfo["id"] = $row["id"];
            $userInfo["id_school"] = $row["id_school"]; 
            $userInfo["states"] = $row["states"];
            $userInfo["a_type"] = $row["a_type"];               
            array_push($response["userInfo"], $userInfo);
        }
\$\endgroup\$
  • 1
    \$\begingroup\$ Welcome to CodeReview! I find it slightly unsettling to be presented code with unmatched braces or parentheses. \$\endgroup\$ – greybeard Jul 22 at 3:52
  • \$\begingroup\$ I have several questions: 1. What is an user? In other words: Why does an user need id_school, states and a_type. The last two don't mean anything to me. 2. Why do you abbreviate names? $_n and nam, really? Why not $username and username? Code should not be a puzzle. 3. You insert PHP variables directly into your query string. That's like opening the door to your palace and invite the riff-raff. Please read up on SQL-injection. 4. Why do you, seemingly, allow multiple users to log in at the same time? \$\endgroup\$ – KIKO Software Jul 22 at 6:27
  • \$\begingroup\$ @KIKOSoftware the 1-user need id_school to add the user to that school case we have multiple school and same login screen to student and school so we make the users table states if the account is disable on not a_type account type school or student 2- this php script for android app not web app and i'am new to php \$\endgroup\$ – SARA Jul 22 at 8:27
  • \$\begingroup\$ @KIKOSoftware for php insert i will use this $stmt = $db->_connect()->prepare(" SELECT * FROM users WHERE nam = ? AND pas = ?"); $stmt->bind_param("ss", $_n,$_p); $result = $stmt->execute(); \$\endgroup\$ – SARA Jul 22 at 10:21
  • \$\begingroup\$ That is indeed better. However, the problem with the names remains. How will anyone know what $_n or $_p stand for? Also, if I had to guess I would say that id_std is a student id, but why not call it student_id or student_number? similarly sc_nam should probably be called school_name. This will make your code a lot easier to read. 30 years ago we used abbreviations to save memory, but nowadays most computers have enough memory to store a normal sensible name. \$\endgroup\$ – KIKO Software Jul 22 at 10:37
-1
\$\begingroup\$

This code work for me

$query = 'SELECT * FROM users WHERE username = ? AND _password= ?';

if ($stmt = $con->prepare($query)) {
    $stmt->bind_param('ss', $_username, $_password); // 's' specifies the variable type => 'string'
    $stmt->execute();
    $result = $stmt->get_result();

    if (mysqli_num_rows($result) > 0) {
        $response["userInfo"] = array();

        while ($row = $result->fetch_assoc()) {

            // Do something with $row
            $userInfo = array();
            $userInfo["id"] = $row["id"];
            $userInfo["id_school"] = $row["id_school"];
            $userInfo["states"] = $row["states"];
            $userInfo["account_type"] = $row["account_type"];
            array_push($response["userInfo"], $userInfo);
        }

        $response["success"] = 1;
        $response["message"] = "Login Done";
        // echoing JSON response
        echo json_encode($response, JSON_UNESCAPED_UNICODE);
    } else {
        $response["success"] = 0;
        $response["message"] = "Fail to Login";
        // echoing JSON response
        echo json_encode($response, JSON_UNESCAPED_UNICODE);
    }
    $stmt->close();
}
\$\endgroup\$
  • \$\begingroup\$ You are not supposed to post an edited code as an answer. I would suggest to delete the current question and to ask a new one, adding the code you posted here. \$\endgroup\$ – Your Common Sense Jul 23 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.