mysqli_fetch_assoc returns only 1 result

[MattL019]

Hey. I'm making a log in form and it creates a query that checks the database for any records with the inputted email by the user. That query looks like this: "SELECT uid FROM users WHERE email='$valid_email'"

I store that in a variable called $sql. I then perform the function mysqli_query, and store it in $result. Then I check for it having at least 1 row (email has been found in the database). If it has at least one row, now my thinking is to hash the password the user entered and then match it against the hashed one in the database. So, I need to retrieve the already hashed password in the database. I went ahead and done some googling and found mysqli_fetch_assoc, which returns an associative array for the result row. Sounds good, so i did: $row = mysqli_fetch_assoc($result); By my understanding, and other examples on the web, I should now be able to do $row['password'] to access the 'password' field in that record. Though, for some reason, when debugging this $row using a foreach loop it ONLY shows the UID. Also, when trying to use $row['password'] PHP gives me an error like this: Notice: Undefined index: password

So, I tried to debug this code. I created a separate file just to test out queries and such. It still doesn't work, and I get the same Undefined index: password error, or if I substitue 'password' for another field e.g 'email' or 'username', it's the same error though it says undefined index: username or whatever. The ONLY thing that works is $row['uid']; 

Here's that debug code:

<?php

$sql = "SELECT uid FROM users WHERE email='examplar@gmail.com'";

$result = mysqli_query($link, $sql);

$row = mysqli_fetch_assoc($result);


// Undefined Index: username

echo $row['username'] . '<br>';

// Undefined Index: password

echo $row['password'] . '<br>';

// Shows '7' - which is the CORRECT uid! so this works..

echo $row['uid'] . '<br>';


mysqli_free_result($result);

?>

Edited October 26, 2017 by MattL019

[Barand]

If your query only selects the uid column, why would you expect the password column to be there?

[MattL019]

Hmm, I did think that myself however when looking at other examples they all did the same thing. I figured the mysqli_fetch_assoc just grabbed the row/record the value is in and turned it into an associative array. What should I be doing?

 

Nevermind, I've been having a massive brainfart. Changed it to this:

SELECT * FROM users WHERE email='examplar@gmail.com'

Now works! Thank you for helping me

Edited October 26, 2017 by MattL019

[Barand]

How about trying:

 

SELECT uid, password FROM ....

[benanamen]

Never ever put variables in a query. You need to use Prepared Statements. I recommend you use PDO. Do not SELECT *, explicitly specify the columns you want. And of course, you have already set the email column as unique right? Right?

[MattL019]

Never ever put variables in a query. You need to use Prepared Statements. I recommend you use PDO. Do not SELECT *, explicitly specify the columns you want. And of course, you have already set the email column as unique right? Right?

The email column is not unique but my signup form does not allow you to sign up with the same email twice. I am not sure what a prepared statement is, so I am going to check out the link you provided (thank you). Why shouldn't I input variables into a query?

[benanamen]

I suggest you post your signup form. Whatever you are doing, you are doing it wrong. Variables in the query can be vulnerable to SQL Injection. A Prepared Statement protects you from it.

 

You need to set a unique constraint on the database. You attempt the insert and capture the duplicate error. You do not check the DB if the email exists first. That will create a Race Condition where simultaneous requests will both get the OK to insert. In your case, you would end up with duplicate emails.

[MattL019]

I suggest you post your signup form. Whatever you are doing, you are doing it wrong. Variables in the query can be vulnerable to SQL Injection. A Prepared Statement protects you from it.

 

You need to set a unique constraint on the database. You attempt the insert and capture the duplicate error. You do not check the DB if the email exists first. That will create a Race Condition where simultaneous requests will both get the OK to insert. In your case, you would end up with duplicate emails.

Alright. Keep in mind, I'm very new to PHP, and made this sign-up form from reading scraps from the PHP documentation and random bits of tutorials. I'm fully aware I'm doing some stuff the wrong way.

 

signup form = https://pastebin.com/CkY7GWx0

[benanamen]

I will start from the top down.

 

All the Php should be at the top, All the Html below that.

 

Remove the form action completely

 

The errors should be an array which you would initialize one time with an empty array. $error = [];

 

Do not depend on the name of a input for your script to work. Check the server REQUEST METHOD instead.

 

Do not create variables for nothing. You already have the POST array. Just use it.

 

You would be better off using PDO with prepared statements. As previously mentioned you do not check if the email exists. Just attempt the insert in the unique column and capture the duplicate error if any.

 

Do not use md5. You need to use password_hash and password_verify.

 

Variables do not ever go in the query. Use Prepared Statements

 

Feel free to use the short echo syntax. <?= $somevar ?>

 

Your password "validation" is messed up and redundant.

 

When you get down on the basics you should spend some time learning TWIG Templating. At the least, you could put the form portion in a separate file and include it. You will want to learn and understand "Separation of Concerns".

 

Post your code in the forum using the code tags.

Edited October 26, 2017 by benanamen

[MattL019]

Alright. I'm not too sure if this is entirely what you had meant, however these are the changes I have made.

<?php
$title = "The Last Laugh | Sign Up";

include "inc/header.php";

// Error variables
$errors = [
  'username' => false,
  'username_same' => false,
  'password' => false,
  'email' => false
];

if(isset($_POST['input-username'])) {
  // Username Validation
  if((trim($_POST['input-username']) !== $_POST['input-username']) || strlen($_POST['input-username']) < 5 || strlen($_POST['input-username']) > 20 || preg_match("[\W]", $_POST['input-username'])) {
    $errors['username'] = true;
  }

  $valid_username = mysqli_real_escape_string($link, $_POST['input-username']);
  $sql = "SELECT username FROM users WHERE username='$valid_username'";
  $result = mysqli_query($link, $sql);
  if(mysqli_num_rows($result) == 1) {
    $errors['username_same'] = true;
    $errors['username'] = true;
  }

  // Password Validation
  if((trim($_POST['input-pass']) !== $_POST['input-pass']) || $_POST['input-pass'] !== $_POST['input-repass'] || strlen($_POST['input-pass']) < 5) {
    $errors['password'] = true;
  }

  // Check for existing email...
  $valid_email = mysqli_real_escape_string($link, $_POST['input-email']);
  $sql = "SELECT email FROM users WHERE email='$valid_email'";
  $result = mysqli_query($link, $sql);
  if(mysqli_num_rows($result) == 1) {
    $errors['email'] = true;
  }

  if(!$errors['username'] && !$errors['password'] && !$errors['email']) {
    // No errors
    $valid_pass = password_hash($_POST['input-pass'], PASSWORD_DEFAULT);

    // Set session variables
    $_SESSION['username'] = "$valid_username";
    $_SESSION['email'] = "$valid_email";

    $query = mysqli_query($link, "INSERT INTO users (username, email, password) VALUES ('$valid_username', '$valid_email', '$valid_pass')");
    if($query) {
      header( 'Location: index.php' ) ;
    }

  }
}
?>
<!-- DIV: CONTENT -->

<div class="sign-up-form">
  <h1>Register an account</h1>
  <p>Already have an account? <a id="login-refer" href='login.php'>Log in.</a></p>
  <form method="POST">

    <!-- Username -->
    <label for='label_username'>Username</label><br>
    <input class='<?php if($errors['username']) echo "input-error"?>' type='text' id='label_username' name="input-username" value='<?php if(isset($_POST['input-username'])) echo $_POST['input-username'];?>'><br>
    <p class="input-rule">Must be between 5-20 characters and contain no special characters/spaces.</p>
    <?php if($errors['username_same']) echo '<p class="error-message">That username has been taken.</p>';?>

    <!-- Email -->
    <label for='label_email'>Email</label><br>
    <input class='<?php if($errors['email']) echo "input-error"?>' type='email' id='label_email' name="input-email" value='<?php if(isset($_POST['input-email'])) echo $_POST['input-email'];?>'><br>
    <p class="input-rule">Disposable emails will not work.</p>
    <?php if($errors['email']) echo '<p class="error-message">An account with that email has already been registered. <a id="login-refer" href="login.php">Log in.</a></p>';?>

    <!-- Password -->
    <label for='label_password'>Password</label><br>
    <input class='<?php if($errors['password']) echo "input-error"?>' type='password' id='label_password' name="input-pass" value='<?php if(isset($_POST['input-pass'])) echo $_POST['input-pass'];?>'><br>
    <p class="input-rule">Password must meet the following requirements:
      <ul class="input-rule-list">
        <li>5+ characters</li>
        <li>No special characters or whitespace</li>
      </ul>
    </p>

    <!-- Re-Type Password -->
    <label for='label_repassword'>Re-type Password</label><br>
    <input class='<?php if($errors['password']) echo "input-error"?>' type='password' id='label_repassword' name="input-repass" value='<?php if(isset($_POST['input-repass'])) echo $_POST['input-repass'];?>'><br>
    <p class="input-rule">Passwords must be matching.</p>

    <!-- Submit -->
    <input type='submit' value='Register'>

  </form>
</div>

<!-- END OF DIV CONTENT -->
<?php include "inc/footer.php" ?>

[benanamen]

Here is a quick and dirty of some of what I mentioned. There is a bit more to it to do it all correctly. Search the forum for posts by Jaques1 and myself. The detailed info is already in the forum.

<?php

$display_error = false;

if ($_SERVER['REQUEST_METHOD'] == 'POST')
    {
    if (empty($_POST['username']))
        {
        $error['username'] = 'Username Required.';
        }

    if (empty($_POST['email']))
        {
        $error['email'] = 'Email Address Required.';
        }

    if (empty($_POST['password']))
        {
        $error['password'] = 'Password Required.';
        }
    if (empty($_POST['password_confirm']))
        {
        $error['password_confirm'] = 'Confirm Password Required.';
        }
    elseif ($_POST['password'] != $_POST['password_confirm'])
        {
        $error['password_confirm'] = 'Passwords do not match.';
        }

    if ($error)
        {
        $display_error = true;

        }
    else
        {
        //Attempt insert. Capture duplicate email error if any.
        }
    }

if ($display_error)
    {
    foreach ($error as $v)
        {
        echo "$v<br>";
        }
    }
?>
<form method="POST">
<input type="submit" value="Submit">
</form>

[MattL019]

Ah okay, I see. Thank you very much for helping and explaining more on this. I will have a look at your posts and Jaques1.