Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

PHP duplicate checking

M

mikepro44

macrumors member
Original poster
May 18, 2008
86
0
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());
 
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
 
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());

		}
 
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...
 
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.
 
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?
 
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
 
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
 
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_card, 0, '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());
}
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back