Check for duplicates

Discuss MySQL Ajax Table Editor

Check for duplicates

Postby jwebber » Tue Nov 17, 2009 4:06 pm

I have an ItemCode field that is essentially a product SKU and needs to be unique for each product. How do I check for duplicates when a product is added or edited?

thanks!
jwebber
 
Posts: 1
Joined: Tue Nov 17, 2009 4:05 pm

Re: Check for duplicates

Postby admin » Tue Nov 17, 2009 4:21 pm

You can use the val_fun callback for this
Code: Select all
$tableColumns['last_name'] = array('display_text' => 'Last Name', 'perms' => 'EVCTAXQSHO', 'val_fun' => array(&$this,'validateFun'));

   function validateFun($col,$val,$row)
   {
      if(value does not exist)
      {
         return true;
      }
      else
      {
         $this->Editor->retArr[] = array('where' => 'javascript', 'value' => 'alert("Sorry this value already exists in the db");');
         return false;
      }
   }
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Check for duplicates

Postby gdknight » Tue Jan 26, 2010 4:16 pm

Hello again!

I get a parse error on line:

if(value does not exist)

I am new to the PHP so please forgive me!
gdknight
 
Posts: 4
Joined: Tue Jan 26, 2010 3:48 pm

Re: Check for duplicates

Postby alphalamont » Wed Mar 17, 2010 4:18 pm

I have the same error, and can't figure out a way to handle it, and just present a polite alert box to the user.
Any clues would be greatly appreciated.
alphalamont
 
Posts: 20
Joined: Fri Mar 12, 2010 5:55 pm

Re: Check for duplicates

Postby admin » Wed Mar 17, 2010 4:33 pm

"if(value does not exist)" is sudo code. You will need to do a query and see if the value already exists in the database. For example
Code: Select all
$query = "select id from table name where id = '$val'";
$result = mysql_query($query);
if(mysql_num_rows($result) == 0)
{
return true;
}
else
{
$this->Editor->retArr[] = array('where' => 'javascript', 'value' => 'alert("Sorry this value already exists in the db");');
return false;
}
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Check for duplicates

Postby alphalamont » Wed Mar 17, 2010 5:06 pm

Before all, many thanks for your quick response admin :)

Let's see a bit deeper: I have a table, with only two columns: id (primary key), and marcas (unique index).
I've tried your code example as follows:

Code: Select all
function initiateEditor()
   {
      $tableColumns['id'] = array('display_text' => 'ID', 'perms' => 'TVQSXO');
      $tableColumns['marca'] = array('display_text' => 'Marca', 'perms' => 'EVCTAXQSHO', 'val_fun' => array(&$this,'validateFun'));
      
                  function validateFun($col,$val,$row)
                   {
                      $query = "select id from marcas where id = '$val'";
                      $result = mysql_query($query);

                      if (mysql_num_rows($result) == 0)
                      {
                         return true;
                      }
                      else
                      {
                         $this->Editor->retArr[] = array('where' => 'javascript', 'value' => 'alert("Sorry this value already exists in the db");');
                         return false;
                      }
                   }

      $tableName = 'marcas';
      $primaryCol = 'id';
      $errorFun = array(&$this,'logError');
      $permissions = 'EAVIDQS';

      $this->Editor = new AjaxTableEditor($tableName,$primaryCol,$errorFun,$permissions,$tableColumns);
      $this->Editor->setConfig('tableInfo','cellpadding="1" width="1000" class="mateTable"');
      $this->Editor->setConfig('orderByColumn','marca');
      $this->Editor->setConfig('addRowTitle','Agregar');
      $this->Editor->setConfig('editRowTitle','Editar');
   }


But I still get the infamous "There was a problem with the response text - string(267) "An error showed in script C:\xampplite\htdocs\mate\php\AjaxTableEditor.php on line 211: <br />There was an error executing the following query: insert into marcas set `marca` = 'Nike' - mysql said: Duplicate entry 'Nike' for key 2""

I guess the code is checking a wrong variable or so, but after a lot of changes/permutations can't get it to work :cry:

In absence of callback functions' docs, I'm trying to understand the "inner gears" of MATE, since I'm sold - it's a terrific piece of software, and I intend to use it extensively.

Again, many thanks in advance.
Last edited by alphalamont on Wed Mar 17, 2010 5:36 pm, edited 1 time in total.
alphalamont
 
Posts: 20
Joined: Fri Mar 12, 2010 5:55 pm

Re: Check for duplicates

Postby admin » Wed Mar 17, 2010 5:10 pm

Try changing
Code: Select all
$query = "select id from marcas where id = '$val'";

to
Code: Select all
$query = "select id from marcas where marca = '$val'";
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Check for duplicates

Postby alphalamont » Wed Mar 17, 2010 5:15 pm

I've tryed that, same error alert message.
Just for the record, also tryed deleting the UNIQUE INDEX in the db - and saw a duplicate entry happily inserting (!!!)
I'm persuaded that we are "almost there" - but can't understand the origin of the error.
Is this additional data useful to track it?
Really screwed-up :evil:
alphalamont
 
Posts: 20
Joined: Fri Mar 12, 2010 5:55 pm

Re: Check for duplicates

Postby alphalamont » Fri Mar 19, 2010 6:27 pm

Just a bump ;)

I'm very interested in the validation aspect of MATE (data sanitization, referential integrity on myISAM, and so on), did anybody get a solution? Or similar problems?

Would love to see any examples - and if I could, I'd love to help too.
alphalamont
 
Posts: 20
Joined: Fri Mar 12, 2010 5:55 pm

Re: Check for duplicates

Postby admin » Fri Mar 19, 2010 8:31 pm

If you post the sql to create your table and your config file I'll see if I can help trouble shoot your problem.
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Next

Return to Open Discussion

Who is online

Users browsing this forum: No registered users and 8 guests

cron