0

I am new to php and mysqli and have been going through and trying to understand how a lot of these built in functions work. I have read the documentation and many tutorials/examples.

I have something like:

$conn = new mysqli(vars); <br />
$sql = "Select * from Users where name ='$name'";

$result = $conn->query($sql);
$pass = result->fetch_assoc()['Pass'];
//check password stuff

that works fine, however if I then try to use $result->fetch_assoc()['ID'] it returns null. If I swap the order of ID and Pass then the id returns and pass comes back null.

I don't understand why this is the case. What I think (which is clearly wrong) is that result should store the whole row (which it does) and then when i fetch assoc I should just be pulling data from the row. However it seems to be overwriting result when I fetch assoc. What's up with that?

My work around is to call the query multiple times for each data point i need to start a session and store variables, and I know I can use prepared statements, but I feel like there's a better way and I'm missing it.

Can someone please explain?

Tyler
  • 35
  • 9
  • 3
    You really should learn parameterized queries. Your SQL query is vulnerable to SQL injection, which can get your data compromised and your site hacked. – ceejayoz Feb 07 '20 at 04:06
  • And what about multiple rows? What 8s a query returns more than one? – Your Common Sense Feb 07 '20 at 04:19
  • I am aware of prepared statements and escaping and whatnot, I just haven't gotten to that. This site will ultimately be taking cc #'s and other info that needs to be protected, and I really want to understand exactly what's going on as best I can. I ran into a ? so I spent a few hrs trying to find an answer. – Tyler Feb 07 '20 at 04:49
  • 1. Read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php. SQL injection is one of the most common security holes web developers make the mistake of leaving, and it's highly dangerous. 2. Consider a framework like Laravel that handles a lot of this security stuff for you internally. 3. **Don't store credit card info.** Storing credit card info means having to be fully PCI compliant, which is expensive and requires a lot of work. You'll want to look into something like https://stripe.com/ instead. – ceejayoz Feb 07 '20 at 14:23

1 Answers1

3

Every time you call fetch_assoc(), it fetches the next row. There'll presumably only be one user with a specific name, so the result of the second fetch_assoc() call will be null.

Store the value of $result->fetch_assoc() and you can do what you want with it afterwards.

$user = $result->fetch_assoc();

echo $user['ID'];
echo $user['Pass'];
ceejayoz
  • 176,543
  • 40
  • 303
  • 368
  • This makes a lot of sense. I actually had 2 accounts with the same name from testing my registration page, and ID was returning 3 instead of 1 like I expected. All of this null happened after I deleted the second identical row. – Tyler Feb 07 '20 at 04:51
  • @Tyler You may want to have a unique index on the `name` field, in that case, if it's supposed to be a unique identifier like a username. – ceejayoz Feb 07 '20 at 14:21
  • The plan is to make usernames unique, I just haven't gotten around to that yet. I also have an ID field that increments. Thanks. – Tyler Feb 08 '20 at 02:04