[RESOLVED] Accessing MySQL

@ZexanimaDec 06.2011

I’m trying to grab data from MySQL database I made. IT connects, I checked that. But when it gets to this part of the code nothing happens.

[code=php] $query = “SELECT ‘id’ FROM ‘users’ WHERE ‘username’=’$username’ AND ‘password’=’$password_hash'”;

if($query_run = mysql_query($query)){ echo ‘WORKS!’; $query_num_rows = mysql_num_rows($query_run);

if($query_num_rows == 0){

}else if($query_num_rows == 1){ $user_id = mysql_result($query_run, 0, ‘id’); $_SESSION[‘user_id’]=$user_id; }

[/code]

I made it echo ‘Works!’ to see if the code made it that far, but it didn’t. what am I doing wrong?

It’s this part in particular where it decides to just do nothing.

[code=php]if($query_run = mysql_query($query))[/code]

to post a comment

PHP

9 Comments(s) _↴

@bionoidDec 06.2011 — #You don't need to put quotes around the column/table names, but if you do use the quote next to the 1 key `

[CODE]$query = "SELECT id FROM users WHERE username='$username' AND password='$password_hash'";[/CODE]

I would recommend safeguarding your inputs when dealing with user submitted data... http://php.net/manual/en/function.mysql-real-escape-string.php

[CODE]$query = 'SELECT id FROM users WHERE username="' . mysql_real_escape_string($username, $conn) . '" AND password="' . mysql_real_escape_string($password_hash, $conn) . '"';[/CODE]

@ZexanimaauthorDec 06.2011 — #Ok, thanks for the security tip. I had seen that before but forgot. I changed what you showed me but it still didn't do anything. It doesn't get past that if() statement:

[code=php]if($query_run = mysql_query($query)) [/code]

Because I put [code=php]echo 'WORKS!';[/code] so I know how far the code was making it. It never printed 'works'.

@criterion9Dec 07.2011 — #Try adding a little error checking before you assume the query ran successfully.

[code=php]
 if(!$query_run){
 die($query.'<br />'.mysql_error());
 }
 [/code]

@ZexanimaauthorDec 07.2011 — #(I'm new so you'll have to pardon things I miss doing like error checking) Criterion9 I did what he/she said, and I returned 'No Database Selected'. Now my connect.inc.php which I have error checking for returns that it connected. I require the connect.inc.php (with the correct database to get the tables from)but still it say no database selected. What is going on here? If you need to see a certain part of my code just ask. Here is my connect code

[code=php]<?php
 $con_error = 'Could not connect.';
 $mysql_host = 'localhost';
 $mysql_user = 'root';
 $mysql_pass ='' ;
 $mysql_db = 'rpmeiste_users';
 
 if(!@mysql_connect($mysql_host, $mysql_user, $mysql_pass)&& !@mysql_select_db($mysql_db)){
 die($con_error);
 
  }else{
 
  }
 
 
 ?>[/code]

@criterion9Dec 07.2011 — #(I'm new so you'll have to pardon things I miss doing like error checking) Criterion9 I did what he/she said, and I returned 'No Database Selected'. Now my connect.inc.php which I have error checking for returns that it connected. I require the connect.inc.php (with the correct database to get the tables from)but still it say no database selected. What is going on here? If you need to see a certain part of my code just ask. Here is my connect code

[code=php]<?php
 $con_error = 'Could not connect.';
 $mysql_host = 'localhost';
 $mysql_user = 'root';
 $mysql_pass ='' ;
 $mysql_db = 'rpmeiste_users';
 
 if(!@mysql_connect($mysql_host, $mysql_user, $mysql_pass)&& !@mysql_select_db($mysql_db)){
 die($con_error);
 
  }else{
 
  }
 
 
 ?>[/code]

[/QUOTE]
I usually do it a little differently...but if I were to do a procedural version I would probably break out the different cases where the error might occur so I could troubleshoot a little better.

[code=php]
 $con = mysql_connect($host,$user,$pass);
 if(!$con){
 die("Connection failed: ".mysql_error());
 }
 if(!mysql_select_db($db)){
 die("DB selection failed: ".mysql_error());
 }
 [/code]

@aj_nscDec 07.2011 — #You don't need to put quotes around the column/table names, but if you do use the quote next to the 1 key


 
 <CODE>[CODE]$query = "SELECT id FROM users WHERE username='$username' AND password='$password_hash'";[/CODE]</CODE>
 
 I would recommend safeguarding your inputs when dealing with user submitted data... <URL url="http://php.net/manual/en/function.mysql-real-escape-string.php"><a href="http://php.net/manual/en/function.mysql-real-escape-string.php">http://php.net/manual/en/function.mysql-real-escape-string.php</a></URL>
 
 <CODE>[CODE]$query = 'SELECT id FROM users WHERE username="' . mysql_real_escape_string($username, $conn) . '" AND password="' . mysql_real_escape_string($password_hash, $conn) . '"';[/CODE]</CODE>[/QUOTE]</QUOTE>
 
 For the record, even though you don't need to use backticks (

) around table/column names, you'll save yourself headaches if you make sure you ALWAYS place table/column names in backticks. Chances are, sooner or later, you're going to use a reserved SQL word and spend at least an extra 30 minutes debugging a simple query that would've never occurred if you always use backticks.

Best practice.

@ZexanimaauthorDec 07.2011 — #For the record, even though you don't need to use backticks (

) around table/column names, you'll save yourself headaches if you make sure you ALWAYS place table/column names in backticks. Chances are, sooner or later, you're going to use a reserved SQL word and spend at least an extra 30 minutes debugging a simple query that would've never occurred if you always use backticks.
 
 Best practice.[/QUOTE]</QUOTE>
 
 
 Yeah, I decided to do that because I figured I'd run into a problem like that. Thanks a lot for the tip.
 
 
 
 <QUOTE author="criterion9;1183409">I usually do it a little differently...but if I were to do a procedural version I would probably break out the different cases where the error might occur so I could troubleshoot a little better.
 
 <CODE lang="php">[code=php]<i>
 </i>$con = mysql_connect($host,$user,$pass);
 if(!$con){
 die("Connection failed: ".mysql_error());
 }
 if(!mysql_select_db($db)){
 die("DB selection failed: ".mysql_error());
 }
 [/code]</CODE>[/QUOTE]</QUOTE>
 
 Ok,I did as you suggested.It helps a lot with your suggestions on error handling because that's something I'm not in a habit of yet. It connected to both fine and didn't return an error though. My first problem still remains 
 
 <QUOTE>
 Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:xampphtdocsWDWPloginform.inc.php on line 12[/QUOTE]</QUOTE>
 
 In this part of the code I still get that error. I don't understand, because I've connected to the database and got the user's id. So shouldn't it return number of rows as 1? On addition to that, even if I enter incorrect user information it still returns the id of 1. I'm doing something majorly wrong, but I can't seem to find where.
 
 <CODE lang="php">[code=php]$query = "SELECT

id FROM users WHERE username='$username' AND password`='$password_hash'";

if($query_run = mysql_query($query) || die($query.'<br />'.mysql_error())){
$query_num_rows = mysql_num_rows($query_run);

if($query_num_rows == 0){

}else if($query_num_rows == 1){
$user_id = mysql_result($query_run, 0, 'id');
$_SESSION['user_id']=$user_id;
}
} else if(!$query_run){
die($query.'<br />'.mysql_error());
}[/code]

@criterion9Dec 08.2011 — #Looks like you have a little logic error there:

if($query_run = mysql_query($query) || die($query.'<br />'.mysql_error())){

$query_num_rows = mysql_num_rows($query_run);
[/quote]
Probably meant something more like:

[code=php]
 $query_run = mysql_query($query);
 if(!$query_run){ die($query.'<br />'.mysql_error())} else {
 $query_num_rows = mysql_num_rows($query_run);
 [/code]

@ZexanimaauthorDec 08.2011 — #Looks like you have a little logic error there:

Probably meant something more like:

[code=php]
 $query_run = mysql_query($query);
 if(!$query_run){ die($query.'<br />'.mysql_error())} else {
 $query_num_rows = mysql_num_rows($query_run);
 [/code]

[/QUOTE]

Thanks, that was the problem! Thank you all for your help.

Also in #PHP _↴

how to allow only one user to connect my site. using same username and password!Widgets in sidebar appear at the bottom.Need help with user input

Success!

Help @Zexanima spread the word by sharing this article on Twitter...

Tweet This

[RESOLVED] Accessing MySQL

9 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

[RESOLVED] Accessing MySQL

9 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

9 Comments(s) _↴

Also in #PHP _↴