Duplicate Record Query Problem

[j1100]

I am writing a script which allows users of my site to create albums so that they can share photo albums with other users. However I am having a problem writing a function which queries the database and checks to see if that specific user already has an album under a certain name.

This is how I set it up:

CREATE TABLE IF NOT EXISTS `albums` (
  category varchar(255) NOT NULL,
  albumname varchar(50) NOT NULL default '',
  description varchar(255) NOT NULL,
  visibility tinyint(1) NOT NULL default '1',
  username varchar(30) primary key,
  time int(11) NOT NULL
);

This is the function that I have so far:

   function albumnameTaken($albumname, $username){
      if(!get_magic_quotes_gpc()){
         $albumname = addslashes($albumname);
         $username = addslashes($username); 
      $q = "SELECT albumname FROM albums WHERE albumname = '$albumname' AND username = '$username'";  

      $result = mysql_query($q, $this->connection);
      return (mysql_numrows($result) > 0);
   } 

I am not very familiar with MySQL, so I am not sure what is wrong with the query. When I had it set as: "SELECT albumname FROM albums WHERE albumname = '$albumname'" it worked great. But now that I added the rest on it just returns my scripts error array. I was planning on adding all albums from all users collectively to this table. So I need to be able to check whether each specific user already has an album with a specific name.

Any help would be greatly appreciated.

[igorlopez]

Your query looks alright as far as just looking at the syntax.
Add what type of error you get and also how the corresponding query string looks like before the error throws you off.
The easiest way is just to echo out the string:

function albumnameTaken($albumname, $username){
      if(!get_magic_quotes_gpc()){
         $albumname = addslashes($albumname);
         $username = addslashes($username);
      $q = "SELECT albumname FROM albums WHERE albumname = '$albumname' AND username = '$username'"; 
      echo "<br/>QueryString:<br/>" . $q . "<br/>"; 
      $result = mysql_query($q, $this->connection);
      return (mysql_numrows($result) > 0);
   }

Just one question out of curiosity, why do to need to check the users album names?

[j1100]

Thanks for help.

I included the echo and it is echoing the exact info that I need, its just not returning the result.

QueryString:
SELECT albumname, username FROM albums WHERE albumname = 'test' AND username = 'j1100'

igorlopez wrote:

Just one question out of curiosity, why do to need to check the users album names?

The script is checking the users album names as the user creates them, so that they do not have multiple albums with the same name.

[igorlopez]

Can you access the database and manually enter the query string?

Also provide us with the error you are getting.

[j1100]

Entering the info manually wouldn't work for what I am trying to do.

It doesn't really send back any errors that would help. It is processing the data, it just doesn't seem to be returning it as $result. I have the script set up so that it returns the error to the form that the album is being created on, or it just returns a failure if there is an error. Which is what its doing, and I don't know why it is. If I just have it as:
$q = "SELECT albumname FROM albums WHERE albumname = '$albumname'";

it works perfect, but when: AND username = '$username'
is added it returns a failure.

[igorlopez]

Too bad you can't enter the query manually.
My experience (the little i have) is that it is very good to work directly at the mysql prompt to learn how the querys work.
I know it works with the AND condition (at least on my server) and I hope somebody with more knowledge can help you solve your problem.

[j1100]

igorlopez wrote:

Too bad you can't enter the query manually.
My experience (the little i have) is that it is very good to work directly at the mysql prompt to learn how the querys work.
I know it works with the AND condition (at least on my server) and I hope somebody with more knowledge can help you solve your problem.

Thanks for trying!

[nemonoman]

using some of my handbuilt utilities for debugging...here's the test code.

<?

include 'sqlbase.php';
   echosql("CREATE TABLE IF NOT EXISTS `albums` (
  category varchar(255) NOT NULL,
  albumname varchar(50) NOT NULL default '',
  description varchar(255) NOT NULL,
  visibility tinyint(1) NOT NULL default '1',
  username varchar(30) primary key,
  time int(11) NOT NULL
); ");
echosql("DELETE FROM albums WHERE username='test' AND albumname='test'");
if(albumnameTaken('test','test')){
    $x='YES';
}
else{
    $x='No';
}

echobig(  $x);
if($x=='No'){
    echosql("INSERT INTO albums SET username='test',albumname='test'");
    if(albumnameTaken('test','test')){
    $x='YES';
}
else{
    $x='No';
}

echobig( "This time it's $x");

}

function albumnameTaken($albumname, $username){
      if(!get_magic_quotes_gpc()){
         $albumname = addslashes($albumname);
         $username = addslashes($username);
      }

  $q = "SELECT albumname FROM albums WHERE albumname = '$albumname' AND username = '$username'";
  echo "<br/>QueryString:<br/>" . $q . "<br/>";
  $result =echosql($q, $this->connection);
  echobig(' mysql_numrows($result) is '. mysql_numrows($result) );
  return (mysql_numrows($result) > 0);
   }
?>

Here's the results:

CREATE TABLE IF NOT EXISTS albums ( category varchar(255) NOT NULL, albumname varchar(50) NOT NULL default '', description varchar(255) NOT NULL, visibility tinyint(1) NOT NULL default '1', username varchar(30) primary key, time int(11) NOT NULL );

DELETE FROM albums WHERE username='test' AND albumname='test'

QueryString:
SELECT albumname FROM albums WHERE albumname = 'test' AND username = 'test'
SELECT albumname FROM albums WHERE albumname = 'test' AND username = 'test'

Echoing big: mysql_numrows($result) is 0

Echoing big: No

INSERT INTO albums SET username='test',albumname='test'

QueryString:
SELECT albumname FROM albums WHERE albumname = 'test' AND username = 'test'
SELECT albumname FROM albums WHERE albumname = 'test' AND username = 'test'

Number of rows selected: 1.

Echoing big: mysql_numrows($result) is 1

Echoing big: This time it's YES

==============================

Don't understand your error.

[Sxooter]

Just a couple of points.

1: If you want to avoid duplicates, use a unique index on the field(s) that need to be unique. It's still ok to then check for a record that exists ahead of time and tell the user that there's already one there, but by having a unique index you avoid the race condition of having two people insert the same album at the same time and having two records in your database with the same album.

2: I don't think these two declarations are doing what you think they are doing:

visibility tinyint(1)
time int(11)

In MySQL, precision values after ints affect padding when you retrieve the values. They do no constrain / lengthen the size of those types. I.e. an int(20) cannot hold a 20 character wide int. It will pad them with spaces on output to be 20 chars wide. Same goes for tinyint.

If you need a numeric type that can hold 11 digits of precision, you need to use a numeric or bigint. The maximum for an int is 2147483647, unsigned it's 4294967295.

Often if you need a boolean in mysql, it's more efficient to use something like char(1) or an enum type.