[RESOLVED] mysql result handling

[system_critical]

Hi, I've been learning PHP from a very poor source (PHP Visual Quickstart Guide by Larry Ullman). There is almost no talk of how to use data returned from mysql queries. So, I have a database with a table called login_stuff with columns of prime_key, user_id and password and one row, which has values as follows: 1 master and 12345. I want to check to see if a username is already taken. So, this is what I tried:

	if($database_pointer = mysql_connect('localhost','my_username','my_password')){
	   if(mysql_select_db('my_dbname')){
	   $query = 'SELECT user_id FROM login_info WHERE user_id = "$user_id"';
	   if($result = mysql_query("$query")){
	   $r = mysql_fetch_row($result);
	   print_r($r); // place holder for print function.
	   }
	   } 
	}
mysql_close();

Is there a better way to access the contents of a query or to compare form data to db data? Thanks.

[HalfaBee]

The best way is to see how many rows are returned using www.php.net/mysql_num_rows
You also should indent on every open brace.
You will also have issues with quotes in the query.

    if($database_pointer = mysql_connect('localhost','my_username','my_password')){
      if(mysql_select_db('my_dbname')){
           $query = 'SELECT user_id FROM login_info WHERE user_id = "' . $user_id . '" ';
           if($result = mysql_query("$query")){
               $r = mysql_num_rows($result);
               echo $r; //print_r($r); // place holder for print function.
           }
       }
    }
mysql_close(); 

[NogDog]

For something like creating a new user name, I generally find it simplest to (a) define the username field as unique in the DB table, (b) just go ahead and do the insert query, and (c) if the query fails, check to see if it was a duplicate key error.

$sql = "INSERT INTO users blah...blah...blah";
$result = mysql_query($sql);
if($result == false)
{
   if(mysql_errno() == 1062) // duplicate key error
   {
      // go back to form and tell user to try a different user name
   }
   else
   {
      // log error here and tell user some unknown DB error occurred
   }
}
else
{
   // success, tell user they're good to go
}

[system_critical]

How do you create a table column that does not allow duplicate entries?

[laserlight]

How do you create a table column that does not allow duplicate entries?

You declare it as unique (or a primary key, which should be implicitly unique), e.g.,

username VARCHAR(30) UNIQUE

[system_critical]

I tried creating a table with UNIQUE. I tried declaring columns like this :

user_id VARCHAR(15) UNIQUE NOT NULL

The table wouldn't get created. I figured I must be using the syntax improperly. Do you see why this should not work? Here is the whole create table.

$query = 'CREATE TABLE my_table(login_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(15) UNIQUE NOT NULL, password VARCHAR(15) NOT NULL, private_id TEXT UNIQUE NOT NULL, score INT NOT NULL, level INT NOT NULL)';

Thanks.

[Weedpacket]

If the table already exists then you won't be able to create it (and it's not clear when or where you're using that $query, so the problem may be there). If the table exists then you'll be wanting to ALTER it.

[system_critical]

I dropped the table before I tried creating it with said UNIQUEs included. Then when I tried to recreate the table with the uniques, it wouldn't be created. After removing the UNIQUEs, it created perfectly. I'm going to try and figure out what error number it throws.

[NogDog]

I believe the UNIQUE should come after the NOT NULL, not before it. (I'm not 100% sure it matters, but the manual appears to indicate that sequence.)

[HalfaBee]

If you look at the error, you will see what is wrong

MySQL said:
#1170 - BLOB/TEXT column 'private_id' used in key specification without a key length

[system_critical]

Here is the error I got when I put the UNIQUE after the NOT NULL on two entries:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/dbtest.php on line 20

I must be doing something wrong.

[HalfaBee]

Have a look at this page http://dev.mysql.com/doc/refman/5.0/en/blob.html

Does privateId have to be type TEXT ?

[Weedpacket]

system_critical wrote:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/westwar1/public_html/php/dbtest.php on line 20

That's the error message from PHP; mysql_error() will tell you the error message from MySQL.

[system_critical]

I actually was using mysql_error(), but with the wrong syntax. So, that is what that error was. Now However, I'm using it properly and got the following error:

BLOB/TEXT column 'private_id' used in key specification without a key length

I'm not sure, but I think I may still be using UNIQUE incorrectly. Here is my current code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
if($database_pointer = mysql_connect('localhost','my_name','my_pass')){
print("<p>Connection Sucessful</p>");
if(mysql_select_db('my_db')){
print("<p>and I found the right database.</p>");
$query = 'CREATE TABLE my_Table(login_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id VARCHAR(15) NOT NULL UNIQUE, password VARCHAR(15) NOT NULL, private_id BLOB NOT NULL UNIQUE, score INT NOT NULL, level INT NOT NULL)';
if (mysql_query("$query")){
print("<p>table created.</p>");
} 
else{
print("<p>table creation failed.</p>");
$error = mysql_error();
echo $error;
}
}
else{
print("<p>But I couldn't find the right database.</p>");
}
}
else{
print("<p>Connection Failure</p>");
};
mysql_close($database_pointer);
?>
</body>
</html>

Also, when I add a length to the TEXT column private_id. I still get the error. Here is the revised private_id declaration:

private_id TEXT(35) NOT NULL UNIQUE

Thank you all for your patience and help. I really appreciate it.

[HalfaBee]

Use varchar(35) instead of TEXT(35)

[system_critical]

That got it uniques and all. So maybe text/blobs (which have undefined lengths) can't have the UNIQUE trait. Even descriptive data types like TINYBLOB threw the error, so perhaps that is the source of the problem... Now on to get the INSERT INTO test running. Thanks.