Page 1 of 1

Insert Statements gone mad

Posted: Wed Jul 23, 2008 6:59 pm
by Sephirangel
Right, I have a function which inserts a record of a new stock item into a table. I also have a function which checks whether the stock is already in the database before adding it.

Here are the functions:

Code: Select all

    function checkForDuplicates($iC){
        $mysqlDupSearch = "SELECT ItemCode, Description FROM stock WHERE ItemCode = '".$iC."'"; 
        
        $dupQuery = mysql_query($mysqlDupSearch);
        $num = mysql_num_rows($dupQuery);
                        
                if($num > 0){
                    return false;
                }else{
                    return true;
                }
    }

Code: Select all

function insertNewStock($stockArray){
        $mysqlAddStock = "INSERT IGNORE INTO stock (ItemCode, Product, Company, Brand, TypeOfProduct, Colour, Size, Shape, Length, Description, Quantity) values ('".$stockArray['itemCode']."','" .$stockArray['Product']."','" .$stockArray['Company']."','" .$stockArray['Brand']."','" .$stockArray['TypeOfProduct']."','" .$stockArray['Colour']."','" .$stockArray['Size']."','" .$stockArray['Shape']."','" .$stockArray['Length']."','" .$stockArray['Description']."','" .$stockArray['Quantity']."')";
        if(!checkForDuplicates($stockArray['itemCode'])){
            return 'The Record Already Exists!';
        }else{
            if(mysql_query($mysqlAddStock)){
                $response = "<font size=3>The following item has been saved successfully:</font>";
                $response = $response."<table border=1>";
                $response = $response."<tr><td align=right><b>Item Code: </b></td><td align=right>".$stockArray['itemCode']."</tr>";
                $response = $response."<tr><td align=right><b>Description: </b></td><td align=right>".$stockArray['Description']."</tr>";
                $response = $response."<tr><td align=right><b>Product: </b></td><td align=right>".$stockArray['Product']."</tr>";
                $response = $response."<tr><td align=right><b>Company: </b></td><td align=right>".$stockArray['Company']."</tr>";
                $response = $response."<tr><td align=right><b>Brand: </b></td><td align=right>".$stockArray['Brand']."</tr>";
                $response = $response."<tr><td align=right><b>Type Of Product: </b></td><td align=right>".$stockArray['TypeOfProduct']."</tr>";
                $response = $response."<tr><td align=right><b>Colour: </b></td><td align=right>".$stockArray['Colour']."</tr>";
                $response = $response."<tr><td align=right><b>Size: </b></td><td align=right>".$stockArray['Size']."</tr>";
                $response = $response."<tr><td align=right><b>Shape: </b></td><td align=right>".$stockArray['Shape']."</tr>";
                $response = $response."<tr><td align=right><b>Length: </b></td><td align=right>".$stockArray['Length']."</tr>";
                $response = $response."<tr><td align=right><b>Quantity: </b></td><td align=right>".$stockArray['Quantity']."</tr></table><br>";
                $response = $response."<a href='addNewStock.php'><button name='anotherStock'>Add Another</button></a>";
                $response = $response."<a href='mainpage.php'><button name='mainpage'>Mainpage</button></a>";
                return $response;
            }else{
                return "<p>Cannot insert new stock due to: ".mysql_error();
            
            }
        }
            
    }
Ok the problem is this: when the insertNewStock() function is called, it comes up with the error message "The Record Already Exists!". When i check the database, the record has been added successfully. Now, the confusing part is, if i didnt have that checkForDuplicates funtion, that same function (without the duplicate method call) will add the same record twice...even though i only called it once. If i make the ItemCode field unique, it will come up with a mysql error saying that it cant add the record because its already been entered (duplicate error).

I really am at the end of my tether here! ive totally ran out of ideas so if anyone can help me please please please dont hesitate to give advice!!

Many thanks in advance!

Re: Insert Statements gone mad

Posted: Wed Jul 23, 2008 7:58 pm
by manixrock
Is the ItemCode column set as the Primary key? If it's not, that would explain why you would be getting duplicates.

You can use the php function mysql_affected_rows() to check if the item was inserted into the database (returns 1 if the item was added, 0 otherwise; check http://php.net/mysql-affected-rows for more details).

Re: Insert Statements gone mad

Posted: Thu Jul 24, 2008 4:31 am
by Sephirangel
The itemCode column USED to be the primary key, but in that instance it gave me a duplicate key error. I have now added an auto_incrementing primary key instead.
But why would the record be entered twice when the syntax only calls it once??

Here is the SQL for the table:

Code: Select all

CREATE TABLE `stock` (
  `stock_num` int(10) unsigned NOT NULL auto_increment,
  `ItemCode` varchar(15) NOT NULL,
  `Product` varchar(25) NOT NULL,
  `Company` varchar(30) NOT NULL,
  `Brand` varchar(30) NOT NULL,
  `TypeOfProduct` varchar(30) NOT NULL,
  `Colour` varchar(30) NOT NULL,
  `Size` varchar(6) NOT NULL,
  `Shape` varchar(20) NOT NULL,
  `Length` varchar(6) NOT NULL,
  `Description` varchar(60) NOT NULL,
  `Quantity` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`stock_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
Hope that gives more insight!

Re: Insert Statements gone mad

Posted: Thu Jul 24, 2008 7:06 am
by manixrock
You are checking if the entry exists in the database after you try to insert it. The check should be done before, as after it will obviously always exist.

However I recommend using the INSERT IGNORE INTO statement followed by mysql_affected_rows() to check if it got inserted or a duplicated entry already existed.


If you've set ItemCode to be autoincremental, why when you insert a new item are you setting it manually? Normally an autoincrement column should have it's value set to NULL when inserting a new row, so it is assigned automatically by mysql.

Re: Insert Statements gone mad

Posted: Thu Jul 24, 2008 11:01 am
by Sephirangel
what im trying to do in that bit of code is check whether the record already exists and if it doesnt, then insert the record and return an error message if it hasnt succeeded, and a list of what has just been entered if it has succeeded.

The ItemCode field is not auto_incrementing, the stock_num is the primary key.

I could try taking the self incrementing key out of the table and make the itemcode field the primary key.

This sounds like a silly question, but if im only calling that insert query once, why would it be adding the same record twice anyway?

Thanks for the advice so far manixrock!