Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in

[AltarofScience]

I got the error in the title. Here is my code:

<?php
mysql_connect("localhost", "admin", "admin") or die(mysql_error());
echo "Connected to MySQL<br />";
?>
<?php
   if($_POST) {
      $password = $_POST['password'];
      $confirm = $_POST['confirm'];   
      if($password != $confirm) { ?>
<span style='color:red'>Error: Passwords do not match!</span>      
<?php   } else {
         $dbhost = 'localhost';
         $dbuser = 'admin';
         $dbpass = 'admin';
         $dbname = 'aosdb';
         $conn = mysql_connect($dbhost,$dbuser,$dbpass)
            or die ('Error connecting to mysql');
         mysql_select_db($dbname);
         $query = sprintf("SELECT COUNT(id) FROM 'users' WHERE UPPER('username') = UPPER('%s')",
            mysql_real_escape_string($_POST['username']));
         $result = mysql_query($query);
         list($count) = mysql_fetch_row($result);
         if($count >= 1) { ?>
<span style='color:red'>Error: that username is taken.</span>
<?php      } else {
            $query = sprintf("INSERT INTO users('username','password') VALUES ('%s','%s');",
               mysql_real_escape_string($_POST['username']),
               mysql_real_escape_string(md5($password)));
            mysql_query($query);
         ?>
<span style='color:green'>Congratulations, you registered successfully!</span>
<?php
         }   
      }
   }
?>
<form method='post' action='index.php'>Username: <input type='text' name='username' /><br />
Password: <input type='password' name='password' /><br />
Confirm Password: <input type='password' name='confirm' /><br />
<input type='submit' value='Register!' />
</form>

 

MOD EDIT: code tags added.

[Pikachu2000]

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

 

Your query is failing and returning a boolean FALSE to mysql_fetch_row(). Echo the query string along with mysql_error() and see if you can spot a problem.

[AltarofScience]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users' WHERE UPPER('username') = UPPER('AltarofScience')' at line 1

 

i got this error so far, i am checking for others.

[web_craftsman]

change this:

SELECT COUNT(id) FROM 'users' WHERE UPPER('username')

on:

SELECT COUNT(id) FROM `users` WHERE UPPER(`username`)

[AltarofScience]

okay i know why i get a boolean value now.

 

also i have mysql server 5.5, and the code is for a registration page, if that helps. the code is supposed to check if a user name exists when you try to register.

 

 

[AltarofScience]

So I made these changes to the code and it does not throw an error anymore:

<?php
mysql_connect("localhost", "admin", "admin") or die(mysql_error());
echo "Connected to MySQL<br />";
?>
<?php
   if($_POST) {
      $password = $_POST["password"];
      $confirm = $_POST["confirm"];   
      if($password != $confirm) { ?>
<span style="color:red">Error: Passwords do not match!</span>      
<?php   } else {
         $dbhost = "localhost:3306";
         $dbuser = "root";
         $dbpass = "root";
         $dbname = "aosdb";
         $conn = mysql_connect($dbhost, $dbuser, $dbpass)
            or die ("Error connecting to mysql");
         mysql_select_db($dbname);
         $query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER('username') = UPPER('%s')",
            mysql_real_escape_string($_POST["username"]));
         $result = mysql_query($query);
         list($count) = mysql_fetch_row($result);
         if($count >= 1) { ?>
<span style="color:red">Error: that username is taken.</span>
<?php      } else {
            $query = sprintf("INSERT INTO users('username', 'password') VALUES ('%s', '%s');",
               mysql_real_escape_string($_POST["username"]),
               mysql_real_escape_string(md5($password)));
            mysql_query($query);
         ?>
<span style="color:green">Congratulations, you registered successfully!</span>
<?php
         }   
      }
   }
?>
<form method="post" action="index.php">Username: <input type="text" name="username" /><br />
Password: <input type="password" name="password" /><br />
Confirm Password: <input type="password" name="confirm" /><br />
<input type="submit" value="Register!" />
</form>

[Pandemikk]

I'm not sure if you're done here or what but I have something to add:

 

It is better to see if a result exists doing this:

<?php
if (mysql_query($query))
{
    // Result exists [username taken]
}
?>

 

It's shorter. Just a personal preference though.

[Pikachu2000]

It isn't throwing an error anymore because there is no logic in the code that would allow it to do so. MySQL database/table/field names don't get enclosed in quotes. If you feel compelled to enclose them in something, use `backticks`. When you're working with database queries, you should incorporate logic to check not only whether the query executed error free, but also that the results are what you would expect.

 

$query = "SELECT field_a FROM table WHERE field_b = '$condition'";
if( !$result = mysql_query( $query ) ) {
// query failed to execute
trigger_error( "DB query: $query failed with error: " . mysql_error() . " in " . __FILE__ . " on line: " . __LINE__ ); // can also use echo intstead of trigger_error() for debugging
} else {
if( mysql_num_rows($result) === 0 ) {  // INSERT/UPDATE queries use mysql_affected_rows()
// results set was empty, so handle that condition appropriately
} else {
	// query both executed successfully and returned results, so continue with the script, process the results
}
}

[AltarofScience]

Notice: DB query: SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('AltarofScience') failed with error: Unknown column 'username' in 'where clause' in C:\Server\www\myserver.dev\public_html\Registration.php on line: 24 in C:\Server\www\myserver.dev\public_html\Registration.php on line 24

 

i am certain i did everything right though. i made sure username was spelled correctly in the table. i exported the model to get the sql file. i opened the client and did the file load thing. i got the file in the command line client and picked a database and the show table function shows table users. i tested my connection in workbench and it worked.

[Pikachu2000]

As far as I can tell, it looks fine to me now. Copy and paste the query string into phpMyAdmin (or whatever tool you use) and see if you get the same error.

[AltarofScience]

i don't understand. what tool? is there somewhere in workbench where i can debug the code or something?

[Pikachu2000]

I just now noticed you have 2 calls to mysql_connect in that script, each having a different username and password. Which one is correct?

[AltarofScience]

oh the first one is a separate script that checks if i can access the database, i think. both of those user/password combos are users in the database. i switched em both to admin but, that doesn't affect the errors.

it still wants to claim that the column username doesn't exist...

[Pikachu2000]

Add the correct mysql_connect() and mysql_select_db() to the top of this, and let's see what that table really holds.

 

// add your DB connection credentials

$query = "DESCRIBE users";

if( $result = mysql_query($query) ) {
echo '<pre>';
while( $array = mysql_fetch_assoc($result) ) {
	print_r($array);
}
echo '</pre>';
} else {
echo mysql_error();
}

[AltarofScience]

it keeps returning no database selected.

the code i ran might have some errors but here it is:

<?php
$dbhost = "localhost:3306";
$dbuser = "admin";
$dbpass = "admin";
$dbname = "aosdb";
mysql_connect($dbhost, $dbuser, $dbpass)   
or die ("Error connecting to mysql");      
mysql_select_db($dbname);
$query= "DESCRIBE users";
if( $result = mysql_query($query) ) {
echo '<pre>';
while( $array = mysql_fetch_assoc($result) ) {
print_r($array);
}
echo '</pre>';
} else {
echo mysql_error();
}
?>

okay it was being whiny about using admin, here is the result if i use root root:

Array
(
    [Field] => id
    [Type] => int(11)
    [Null] => NO
    [Key] => PRI
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => usersname
    [Type] => varchar(50)
    [Null] => NO
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => password
    [Type] => varchar(25)
    [Null] => NO
    [Key] => 
    [Default] => 
    [Extra] => 
)

 

[AltarofScience]

and before you say anything, i see that it is returning usersname, but that doesn't make sense. i saw that in the table a while ago and i deleted all the files and renamed it and resaved everything. the workbench file and the sqlfile. i don't understand where it is grabbing that column name.

[Pikachu2000]

Well clearly the name of the field is still usersname, not username. Mystery solved, I guess .  .  .

[AltarofScience]

the test script now returns the arrays below, which is what i wanted. and after i changed all the things back to username, it appears that the login and register scripts work, seeing as the login error for not finding a name doesn't come up. thanks to everyone who helped out, even if i was a little slow. and also thanks for showing me all that error checking stuff.

<code>

Array

(

    [Field] => id

    [Type] => int(11)

    [Null] => NO

    [Key] => PRI

    [Default] =>

    [Extra] => auto_increment

)

Array

(

    [Field] => username

    [Type] => varchar(45)

    [Null] => NO

    [Key] =>

    [Default] =>

    [Extra] =>

)

Array

(

    [Field] => password

    [Type] => varchar(45)

    [Null] => NO

    [Key] =>

    [Default] =>

    [Extra] =>

)

Array

(

    [Field] => is_admin

    [Type] => tinyint(4)

    [Null] => NO

    [Key] =>

    [Default] => 0

    [Extra] =>

)

Array

(

    [Field] => last_login

    [Type] => timestamp

    [Null] => YES

    [Key] =>

    [Default] =>

    [Extra] =>

)

</code>