PHP duplicate checking

Discussion in 'Web Design and Development' started by mikepro44, Mar 10, 2010.

  1. mikepro44 macrumors member

    Joined:
    May 18, 2008
    #1
    Have a problem that I'm not sure how to tackle yet.

    So here is the dilemma:
    I'm inserting hockey cards into my database. What I want to do for example is say I enter 'Wayne' 'Gretzky' '1986' and my stock number is '4'.Then I enter it again but change the stock number to '6', I don't want it to insert a duplicate card. I want it to just keep one card and have the stock number change to 6.

    So heres my form:

    HTML:
    <body>
    <div id="add_back">
    
    <blockquote>
      <blockquote>
        <p> </p>
        <p> </p>
      </blockquote>
    </blockquote>
    <form method="post" name="addcard" >
      <blockquote>
        <blockquote>
          <p><b>First Name:</b>
            <input type="text" name="name" id="name"/>
          </p>
           <p><b>Last Name:</b>
            <input type="text" name="name2" id="name2"/>
          </p>
          <p>
            <b>Brand:</b>
            
            <select name="brand" id="brand">
              <option value="0">Pick a Brand</option>
              <option value="1">Donruss</option>
              <option value="2">O'Pee-Chee</option>
              <option value="3">Topps</option>
              <option value="4">Fleer</option>
              <option value="5">Upper-Deck</option>
              <option value="6">Pacific</option>
              <option value="7">Parkhurst</option>
            </select>
            
          </p>
          <p><b>Year:</b>
            
    		<input type="text" name="year" id="year"/>
            
          </p>
          <p><b>Stock:</b>
            <input type="text" name="stock" id="stock"/>
            
          </p>
          <p>
            <input type="submit" value="Add Card" onclick="add_card()"/>
          </p>
        </blockquote>
        </blockquote>
    </form>
    
    
    </div>
    </body>
    Then the form goes to my Javascript function:

    Code:
    function add_card(new_card,new_added)
    
    {
    	var first_name = document.addcard.name;
    	var last_name = document.addcard.name2;
        var card_brand = document.addcard.brand;
    	var card_year = document.addcard.year;
        var card_stock = document.addcard.stock;
    
        if (first_name.value == "")
        {
            window.alert("Please enter a First Name.");
            first_name.focus();
            return false;
        }
    	
    	
    	if (last_name.value == "")
        {
            window.alert("Please enter a Last Name.");
            last_name.focus();
            return false;
        }
    	
    	
    	if (card_brand.value == 0)
        {
    		
            window.alert("Please provide a Brand.");
            brand.focus();
            return false;
        }
    	
    	
    	
    	if (card_year.value == "")
        {
            
    		window.alert("Please provide a Year.");
            year.focus();
            return false;
        }
        
    
        if (card_stock.value == "")
        {
            window.alert("Please provide a Stock number.");
            stock.focus();
            return false;
        }
    	else
    	{
    	
    	if(new_card == new_added)
    	
    	{
    		
    	var name = document.getElementById("name").value;	
    	var name2 = document.getElementById("name2").value;	
    	var brand = document.getElementById("brand").value;
    	var year = document.getElementById("year").value;
    	var stock = document.getElementById("stock").value;
    	
    	if(new_card == new_added)
    	
    	{
    	
    	xmlDoc.load("scripts/insert.php?name="+name+"&name2="+name2+"&brand="+brand+"&year="+year+"&stock="+stock);
    	alert("New Card Added");
    
    	}
    	
    	
    	}
    
    	}
    	
    	
    	
    		
    }


    Then the values go to my php:


    Code:
    $select="INSERT INTO card (first_name,last_name,brand_id_fk,year)
    VALUES
    ('$_GET[name]','$_GET[name2]','$_GET[brand]','$_GET[year]')
    ";
    
    
    
    
    $result_card = mysql_query($select) or die(mysql_error());
    
    $id= mysql_insert_id();
    
    $stock="INSERT INTO stocks (card_id_fk,stock_num)
    VALUES
    ($id,'$_GET[stock]')
    ";
    $result_stock = mysql_query($stock) or die(mysql_error()); 
    
     
  2. needlnerdz macrumors regular

    needlnerdz

    Joined:
    Jun 10, 2006
    Location:
    switzerland
    #2
    I just wrote something similar to this for a users online script, and I'll try to make it as generic as possible. Essentially I have the query first check to see if the record exists, if so, update it, otherwise, create the new record with INSERT:


    Code:
    	// check to see if it already exists
    		if(mysql_num_rows(mysql_query("SELECT field1 FROM yourtable WHERE field1 = '$field1_var'"))){
    
    	// if yes, then update the record
    			$updatefield1 = mysql_query("UPDATE yourtable SET stock_num = '$stock_var' WHERE field1 = '$field1_var'");					
    		}else{
    
    	// create a new record for them
    			$insert = mysql_query ("INSERT INTO yourtable(field1, field2) VALUES ('$field1_var', '$field2_var')");
    			}
    
    goodluck
     
  3. mikepro44 thread starter macrumors member

    Joined:
    May 18, 2008
    #3
    So I have been messing around with the you provided. I plugged my insert code before and that all works, but I can't seem to get the if statement to work.. this is what I've been working with. Like I said I can get all my info inserted but when I try to put the same card in, it will duplicate.

    Code:
    if(mysql_num_rows(mysql_query("SELECT card.card_id,card.first_name, card.last_name,card.brand_id_fk,card.year 
    									  FROM card 
    									  WHERE first_name = ('$_GET[name]'), last_name = ('$_GET[name2]'),brand_id_fk = 		                                      ('$_GET[brand]'),year = ('$_GET[year]')")))
    
    	
    	
    	
    		{$updatefield1 = mysql_query("UPDATE stocks SET stock_num = ('$_GET[stock]') WHERE stock.card_id_fk = ('$_GET[card_id]')");}
    		
    		else{
    
    		$select="INSERT INTO card (first_name,last_name,brand_id_fk,year)
    		VALUES
    		('$_GET[name]','$_GET[name2]','$_GET[brand]','$_GET[year]')
    		";
    		
    		$result_card = mysql_query($select) or die(mysql_error());
    		
    		
    		
    		
    		$id= mysql_insert_id();
    		
    		$stock="INSERT INTO stocks (card_id_fk,stock_num)
    		VALUES
    		($id,'$_GET[stock]')
    		";
    		
    		
    		$result_stock = mysql_query($stock) or die(mysql_error());
    
    		}
     
  4. needlnerdz macrumors regular

    needlnerdz

    Joined:
    Jun 10, 2006
    Location:
    switzerland
    #4
    I think you really just need to google 'mysql php update' and 'mysql php existing' - and you should find a plethora of tutorials and tips for what to do... that said, from quickly looking at how you filled in the gaps - I think the biggest problem is that initial query to see if the record exists... it would be ideal if you could have the search performed with just one value.. an ID - but that might be not be the case for such a database.. anyways- before you get all the insert or update functions plugged in, just do some simple echo tests... try to recreate the existing record query on its own and say "I'm here" let you know that the record was found... then you can start using the update.. or the insert on an else statement...
     
  5. Bostonaholic macrumors 6502

    Bostonaholic

    Joined:
    Aug 21, 2009
    Location:
    Columbus, Ohio
    #5
    I don't necessarily condone doing queries like this but this should work for you. I found several issues with your most recent if/else, but I forget at the moment what they were exactly (2 glasses of wine at the moment). Wait, now I remember

    1) In your if-clause, no update was being run. So it would find the existing entry, but not update the entry.
    2) Actually, your if-clause wouldn't find an entry because you included the new brand in the query to find an existing entry.
    3) In your if-clause, it was inserting into the stocks table but not the card table. Mentioned this in #1

    PHP:
    if(mysql_num_rows(mysql_query("
            SELECT card.brand_id_fk
                FROM card
                WHERE first_name = '
    $_GET[name]' AND last_name = '$_GET[name2]' AND year = '$_GET[year]'"))) {

        
    $update_card mysql_query("UPDATE card SET brand_id_fk = '$_GET[brand]' WHERE first_name = '$_GET[name]' AND last_name = '$_GET[name2]' AND year = '$_GET[year]'");
        
    $update_stocks mysql_query("UPDATE stocks SET stock_num = '$_GET[stock]' WHERE stock.card_id_fk = '$_GET[card_id]'");

    } else {

        
    $select="INSERT INTO card (first_name,last_name,brand_id_fk,year)
            VALUES ('
    $_GET[name]','$_GET[name2]','$_GET[brand]','$_GET[year]')";

        
    $result_card mysql_query($select) or die(mysql_error());
        
    $id mysql_insert_id();

        
    $stock="INSERT INTO stocks (card_id_fk,stock_num) 
        VALUES (
    $id,'$_GET[stock]')";

        
    $result_stock mysql_query($stock) or die(mysql_error());
    }
    Let us know how this works.
     
  6. mikepro44 thread starter macrumors member

    Joined:
    May 18, 2008
    #6
    Nothing happens when I run it.


    This error showed up in the console..

    Error: junk after document element
    Source File: http://localhost/scripts/insert.php?name=Wayne&name2=Gretzky&brand=2&year=1987&stock=8
    Line: 2, Column: 1
    Source Code:
    <b>Notice</b>: Undefined index: card_id in <b>C:\wamp\www\scripts\insert.php</b> on line <b>18</b><br />

    Also, could you explain why you are selecting the brand_id_fk? and setting the brand_id_fk?
     
  7. mikepro44 thread starter macrumors member

    Joined:
    May 18, 2008
    #7
    Quick update, it works if I change the brand. If I put the same card in but different brand it switches the brand in the database. What I'm going for is to just switch if the stock changes...


    ________________________________________________

    So I switched up the code and it does update the stock now, but it switched every cards stock num to whatever I put in..


    Heres the new code:

    Code:
    if(mysql_num_rows(mysql_query("
            SELECT card.card_id
                FROM card
                WHERE first_name = '$_GET[name]' 
    			AND last_name = '$_GET[name2]' 
    			AND brand_id_fk = '$_GET[brand]'
    			AND year = '$_GET[year]'"))) {
    	
    	
    
       
       
       $update_stocks = mysql_query("UPDATE stocks
    								JOIN (card)
    								SET stock_num = '$_GET[stock]' 
    								WHERE stocks.card_id_fk = card.card_id");
       
       
        
    
    } else
    
     
  8. Bostonaholic macrumors 6502

    Bostonaholic

    Joined:
    Aug 21, 2009
    Location:
    Columbus, Ohio
    #8
    So I have a better understanding of your DB, could you give us the table definitions? Is this correct?

    Code:
    card
    ===
    card_id
    first_name
    last_name
    brand_id_fk
    year
    
    Code:
    stocks
    ===
    stock_num
    card_id_fk
    
     
  9. Bostonaholic macrumors 6502

    Bostonaholic

    Joined:
    Aug 21, 2009
    Location:
    Columbus, Ohio
    #9
    I've done a litte extra for you. I'm not sure if you're stripping the inputs before the queries, you should be just so people can't inject into your DB and cause some serious issues. I've also adjusted the queries a bit and made variables for the inputs so you're not calling $_GET over and over. This should work.

    PHP:
    $first_name $_GET['name'];
    $last_name $_GET['name2'];
    $brand $_GET['brand'];
    $year $_GET['year'];
    $stock $_GET['stock'];

    //un-quote
    if(get_magic_quotes_gpc()){
        
    $first_name stripslashes($first_name);
        
    $last_name stripslashes($last_name);
        
    $brand stripslashes($brand);
        
    $year stripslashes($year);
        
    $stock stripslashes($stock);
    }
    //escape special characters for SQL
    $first_name mysql_real_escape_string($first_name);
    $last_name mysql_real_escape_string($last_name);
    $brand mysql_real_escape_string($brand);
    $year mysql_real_escape_string($year);
    $stock mysql_real_escape_string($stock);

    //strip html and php
    $first_name strip_tags($first_name);
    $last_name strip_tags($last_name);
    $brand strip_tags($brand);
    $year strip_tags($year);
    $stock strip_tags($stock);

    $select_card mysql_query("
            SELECT card_id
                FROM card
                WHERE first_name = '
    $first_name'
                    AND last_name = '
    $last_name'
                    AND brand_id_fk = '
    $brand'
                    AND year = '
    $year'");

    if(
    mysql_num_rows($select_card)) {
        
    $card_id mysql_result($select_card0'card_id');
        
    $update_stocks mysql_query("UPDATE stocks SET stock_num = '$stock' WHERE stocks.card_id_fk = '$card_id'");
    } else {
        
    $insert_card "INSERT INTO card (first_name,last_name,brand_id_fk,year)
                    VALUES ('
    $first_name','$last_name','$brand','$year')";
        
    $result_card mysql_query($insert_card) or die(mysql_error());
        
    $insert_card_id mysql_insert_id();

        
    $insert_stock "INSERT INTO stocks (card_id_fk,stock_num)
                    VALUES (
    $insert_card_id,'$stock')";

        
    $result_stock mysql_query($insert_stock) or die(mysql_error());
    }
     
  10. mikepro44 thread starter macrumors member

    Joined:
    May 18, 2008

Share This Page