VALIDATION. no duplicates on column. php/mysql

[imagined]

I have the following query:
select * from shoppertrack;

on column SELECTEDITEM, the result is:

g45, LDR01, TYU

I want to do some validation before adding a new item so there wont be any duplicates. I have hyperlinks that pass the variable to the page where the item is added. How would I create a script that echoes the message "Item already selected" when for example, someone selects g45?

This is what I have so far but doesn't work:



$sessionid = session_id();

//PREVENT DUPLICATION
$checkSession = "SELECT sessionid FROM SHOPPERTRACK";
if(!$checkSession){ exit("<p class='alert'>Could not get database information during duplicate prevention.</p>"); }
$checkSessionResult = mysql_query($checkSession) or die("<p class='alert'>Could not get database information during duplicate prevention.</p>");
while($rowSession = mysql_fetch_array($checkSessionResult))
{
$rowSessionid = $rowSession["sessionid"];

//PREVENT DUPLICATION
$check = "SELECT selecteditem FROM SHOPPERTRACK WHERE sessionid = '$sessionid'";
if(!$check){ exit("<p class='alert'>Could not get database information during duplicate prevention.</p>"); }
$checkResult = mysql_query($check) or die("<p class='alert'>Could not get database information during duplicate prevention.</p>");

while($row = mysql_fetch_array($checkResult))
{
$productidCheck = $row["selecteditem"];
if($productidCheck == $productid AND $sessionid == $rowSessionid)
{
$productidCheck = strtoupper($productidCheck);
exit("<p class='alert'>Product: " . $productidCheck . " has already been added to shopping cart. Please go back and continue.</p>");
} }
}

$checkSession = "SELECT * FROM SHOPPERTRACK";
if(!$checkSession){ exit("<p class='alert'>Could not get database information during duplicate prevention.</p>"); }
$checkSessionResult = mysql_query($checkSession) or die("<p class='alert'>Could not get database information during duplicate prevention.</p>");

$number_of_rows = mysql_num_rows($checkSessionResult);

while($rowSession = mysql_fetch_array($checkSessionResult))
{
$rowSessionid = $rowSession["sessionid"];
if($sessionid == $rowSessionid)
{
$selecteditemRow = $rowSession["selecteditem"];
$quantityRow = $rowSession["quantity"];
$priceRow = $rowSession["PRICE"];

echo "quantityRow: " . $quantityRow . "<p>";
$quantity = 1;
$updateSelecteditem = $selecteditemRow . ", " . $productid;
$updateQuantity = $quantityRow . ", " . $quantity;
$updatePrice = $priceRow . ", " . $price;

echo "item: " . $updateSelecteditem . "<BR>";
echo "quantity: " . $updateQuantity . "<BR>";
echo "price: " . $updatePrice . "<BR>";

$sql = "UPDATE shoppertrack SET " .
"selecteditem = '$updateSelecteditem', " .
"PRICE = '$updatePrice', " .
"quantity = '$updateQuantity', " .
"timeadded = CURTIME(), " .
"dateadded = CURDATE() " .
"WHERE sessionid = '$sessionid'";
$result = mysql_db_query('magalys', $sql);
if($result)
{ echo("<p class='success'>62: Item successfully added to database.</p><p><a href='showCart.php?customerid=" .
$customerid . "'>Click here to view your shopping cart.</a></p>"); }
else { echo("<p class='alert'>Error. Please go back and try again. " . __line__ . "</p>"); }
}
else
{
$number_of_rows++;
echo "Number of rows: " . $number_of_rows . "<p>";
if($number_of_rows == 1)
{
echo("<p>INSERT</p>");
$sql = "INSERT INTO shoppertrack SET " .
"sessionid = '$sessionid', " .
"selecteditem = '$productid', " .
"customerid = '$customerid', " .
"quantity = 1, " .
"price = '$price', " .
"dateadded = curdate(), " .
"timeadded = curtime()" ;
$result = mysql_db_query('magalys', $sql);
if($result)
{ echo("<p class='success'>101: Item successfully added to database.</p><p><a href='showCart.php?customerid=" .
$customerid . "'>Click here to view your shopping cart.</a></p>"); }
else { echo("<p class='alert'>Error. Please go back and try again.103</p>"); }
}
} // END WHILE
}

[Enigmatic]

Heres a validation code I use for user signup forms. If you look at it you should be able to adapt it with ease to fit into your script. I`d normaly do it but I`m tired right now and you know what your looking to do better than me anyway :


$sql_email_check = mysql_query("SELECT email FROM users WHERE email='$email'");
$sql_username_check = mysql_query("SELECT username FROM users WHERE username='$username'");

$email_check = mysql_num_rows($sql_email_check);
$username_check = mysql_num_rows($sql_username_check);

if(($username_check > 0) || ($email_check > 0)){
echo "Please correct the following errors ... <br />";
if($username_check > 0) {
echo "Username is already in use. Please select another !! <br />";
}
if($email_check > 0) {
echo "Email is already in our database. Please enter a different email !! <br />";
}
exit();
}

[imagined]

I had a script like that that checked for a single item. But I changed the scripts to enter multiple items in one row.

Like this:
selecteditem
g45
THIS ONE WAS EASY TO VALIDATE

selecteditem
g45, LDR01, TYU
SINCE THE COMMAS, THE SPACES AND 3 DIFFERENT ITEMS ITS HARDER TO VALIDATE FOR DUPLICATION

I think I need a regular expresion or something like that. Im a begginner, not sure.

Please help,

Leo :hair:

[Enigmatic]

Hmmm, something like :


SELECT selecteditem FROM table WHERE selecteditem='$item1' OR '$item2' OR '$item3'

That any closer ?

[Enigmatic]

OK, I made an effort here :P


$sql_selecteditem_check = mysql_query("SELECT selecteditem FROM table WHERE selecteditem = '$item1' OR '$item2' OR '$item3'");
$selecteditem_check = mysql_num_rows($sql_selecteditem_check);

if(($selecteditem_check > 0)) {
echo "Please do something else ... <br />";
}

[imagined]

I found the answer!



if(strstr($selecteditemRow, $productid))
{
exit("<p class='alert'>Product: " . $productid . " has already been added to shopping cart. Please go back and continue. " . __line__ . "</p>");
}


Thanks a loooooot guys! :beer: