Jump to content

Checking database for Combination

boblee

By boblee
in PHP Coding Help

Recommended Posts

boblee Newbie

boblee

Posted
Posted

Hello everyone,

I'm having a bit of a problem, I'm inserting two items from two drop-down menus into a database, this works fine, however I don't want duplicates of any particular combination.

 

Here is the the section of my code that is supposed to prevent that from happening:

 

$error = array();
if(isset($_POST['customer'])) {

$result = @mysql_query('SELECT customer FROM `customers_products` WHERE customer = \''.mysql_real_escape_string($_POST['customer']).'\'AND product_id = \''.mysql_real_escape_string($_POST['product_id']).'\'');
if($row = @mysql_fetch_row($result)) {
array_push($error, 'Combination already found.');
}

 

And the entire thing:

<?php
session_start(); 
if(!isset($_SESSION['adminctrl'])){ 
header('Location: admin.php'); die('<a href="admin.php">Login first!</a>');
   }
$query = mysql_connect("****", "****", "****") or die(mysql_error());
mysql_select_db('JohnPiatt', $query) or die(mysql_error());

$error = array();
if(isset($_POST['customer'])) {

$result = @mysql_query('SELECT customer FROM `customers_products` WHERE customer = \''.mysql_real_escape_string($_POST['customer']).'\'AND product_id = \''.mysql_real_escape_string($_POST['product_id']).'\'');
if($row = @mysql_fetch_row($result)) {
array_push($error, 'Combination already found.');
}	

$len = strlen($_POST['company']);
$len = strlen($_POST['product_id']);

$sql = "INSERT INTO `customers_products` (customer, product_id) VALUES ('";
$sql .= mysql_real_escape_string($_POST['customer']) . "', '" . mysql_real_escape_string($_POST['product_id']) . "')";
echo Update Was Successful
mysql_query($sql) or die(mysql_error());
}
?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Customer Update</title>
</head>
<html>
<body>
<form method="post" action="customer_info.php">
<?php
         $conn ="SELECT company FROM `users`" ;
         $result = mysql_query($conn,$query);
  
         while($row=mysql_fetch_row($result))
          {
           $user[] = $row[0];
          }
         
         echo "<select name='customer'>\n" ;
         foreach( $user as $v  )
                {
                 echo "<option value='$v'>\n" .$v."</option>\n";
                }
         echo "</select>\n";



echo "<br/><br/>";

         $conn ="SELECT product_id FROM `products`" ;
         $result = mysql_query($conn,$query);
  
         while($row=mysql_fetch_row($result))
          {
           $product[] = $row[0];
          }
         
         echo "<select name='product_id'>\n";
         foreach( $product as $y  )
                {
                 echo "<option value='$y'>\n" .$y."</option>\n";
                }
         echo "</select>\n";


php?> 
<br/><br/>
<input type="submit" name="submit" value="Update" />

</form>
</body>
</html>

 

Thanks for any help.

Link to comment
https://forums.phpfreaks.com/topic/137942-checking-database-for-combination/
Share on other sites
rhodesa Newbie

rhodesa

Posted
Posted

you need a space for AND. your SQL is probably failing:

 

$result = @mysql_query('SELECT customer FROM `customers_products` WHERE customer = \''.mysql_real_escape_string($_POST['customer']).'\' AND product_id = \''.mysql_real_escape_string($_POST['product_id']).'\'') or die(mysql_error());
if($row = @mysql_fetch_row($result)) {
array_push($error, 'Combination already found.');
}

rhodesa Newbie

rhodesa

Posted
Posted

now that i look it over again, the logic was a little off. you were testing for the entry, but not stopping the insert if you found it. and if you found it, you would put it into the $error array, but you never displayed the error array. try this out:

 

<?php
session_start();
if (!isset ($_SESSION['adminctrl'])) {
  header('Location: admin.php');
  die('<a href="admin.php">Login first!</a>');
}
$query = mysql_connect("hostname", "username", "password") or die(mysql_error());
mysql_select_db('JohnPiatt', $query) or die(mysql_error());

$error = array ();
if (isset ($_POST['customer'])) {
  //Find duplicate first
  $sql = sprintf(
    "SELECT customer FROM `customers_products` WHERE customer = '%s' AND product_id = '%s'",
    mysql_real_escape_string($_POST['customer']),
    mysql_real_escape_string($_POST['product_id'])
  );
  if($result = @mysql_query($sql)){
    if(!@mysql_num_rows($result)){
      //These two lines weren't doing anything
      //$len = strlen($_POST['company']);
      //$len = strlen($_POST['product_id']);

      //No duplicate, so add it
      $sql = sprintf(
        "INSERT INTO `customers_products` (customer, product_id) VALUES ('%s','%s')",
        mysql_real_escape_string($_POST['customer']),
        mysql_real_escape_string($_POST['product_id'])
      );
      if(@mysql_query($sql))
        echo 'Update Was Successful';
      else
        $error[] = 'Insert Failed: '.mysql_error();
    }else
      $error[] = 'Combination already found';
  }else
    $error[] = 'Select Failed: '.mysql_error();
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Customer Update</title>
</head>
<html>
<body>
<?php
  if(count($error)){
    print '<ul><b>Errors:</b><li>'.implode('</li><li>',$error).'</li></ul>';
  }
?>
<form method="post" action="customer_info.php">
<?php

$conn = "SELECT company FROM `users`";
$result = mysql_query($conn, $query);

while ($row = mysql_fetch_row($result)) {
  $user[] = $row[0];
}

echo "<select name='customer'>\n";
foreach ($user as $v) {
  echo "<option value='$v'>\n" . $v . "</option>\n";
}
echo "</select>\n";

echo "<br/><br/>";

$conn = "SELECT product_id FROM `products`";
$result = mysql_query($conn, $query);

while ($row = mysql_fetch_row($result)) {
  $product[] = $row[0];
}

echo "<select name='product_id'>\n";
foreach ($product as $y) {
  echo "<option value='$y'>\n" . $y . "</option>\n";
}
echo "</select>\n";

php
?>
<br/><br/>
<input type="submit" name="submit" value="Update" />

</form>
</body>
</html>

 

p.s. don't post usernames/passwords with your code. i removed it from my post above, but you should probably change your password on your server now

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.