Page 1 of 1

Table in Database is not being updated.

Posted: Sun Dec 07, 2008 1:02 pm
by Greg19
Hello everyone, I'm having a bit of a problem, I've populated to drop down menus with information from two different tables (customer and product_id). I'm trying to take that information and stick it in another table. The drop down menus work fine, but once i select some combination a customer and product_id and hit update its says update was successful, but there is no new information in the database. I'm a newbie at php so its probably something pretty obvious.

Here is my code, any help would great. -Thanks

Code: Select all

<?php
session_start(); 
if(!isset($_SESSION['adminctrl'])){ 
    header('Location: admin.php'); die('<a href="admin.php">Login first!</a>');
   }
$query = mysql_connect("************.net", "*****", "***********") or die(mysql_error());
mysql_select_db('*******', $query) or die(mysql_error());
 
$error = array();
if(isset($_POST['company'])) {
 
$result = @mysql_query('SELECT company FROM `customers_products` WHERE company = \''.mysql_real_escape_string($_POST['company']).'\'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']);
 
@mysql_query('INSERT INTO `customers_products` (company, product_id) VALUES (\''.mysql_real_escape_string($_POST['company']).'\', \''.mysql_real_escape_string($_POST['product_id']).'\')');
if(!$error) {
echo"Update was successful.";
}
}
php?> 
 
<!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='company'>\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>
 

Re: Table in Database is not being updated.

Posted: Sun Dec 07, 2008 3:20 pm
by Peter Anselmo
Try removing the '@' before your insert query - This is the error suppression operator. It's your friend when the site is in production. It's a hindrance when trying to find bugs. After removing it, it should spit out a helpful error.

Re: Table in Database is not being updated.

Posted: Fri Dec 19, 2008 7:05 pm
by Greg19
Sorry it took me so long to respond (finals week), anyway I did as Peter Anselmo said and removed the @ before the insert query but the same thing is still happening.

Code:

Code: Select all

<?php
session_start(); 
if(!isset($_SESSION['adminctrl'])){ 
    header('Location: admin.php'); die('<a href="admin.php">Login first!</a>');
   }
$query = mysql_connect("*****************.net", "********", "*********") or die(mysql_error());
mysql_select_db('*******', $query) or die(mysql_error());
 
$error = array();
if(isset($_POST['company'])) {
 
$result = @mysql_query('SELECT company FROM `customers_products` WHERE company = \''.mysql_real_escape_string($_POST['company']).'\'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']);
 
mysql_query('INSERT INTO `customers_products` (company, product_id) VALUES (\''.mysql_real_escape_string($_POST['company']).'\', \''.mysql_real_escape_string($_POST['product_id']).'\')');
if(!$error) {
echo"Update was successful.";
}
}
php?> 
 
<!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='company'>\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>
 
 
 

Re: Table in Database is not being updated.

Posted: Fri Dec 19, 2008 10:52 pm
by califdon
There are several things wrong there. Let's start with the closing tag for a PHP block -- it's ?>, not php?>.
Then it looks like you're expecting your array variable $error to contain a boolean value that indicates whether or not an error has occurred, but your code never assigns such a value, so it's always going to echo your "Update was successful" message.
You should insert an "or die(mysql_error())" after your queries so that you will generate an error message if the query fails.
As a recommendation, I suggest that you always form your SQL string as a string variable and then use that variable in the mysql_query() function, rather than trying to form all the pieces of the statement within the function argument. This allows you to echo back your SQL string during debugging, to see what you really have. If you had done that here, I think you would see that you are not getting a valid SQL string, due to improper use of quotation marks. Do this:

Code: Select all

 
$sql = "INSERT INTO `customers_products` (company, product_id) VALUES (";
$sql .= mysql_real_escape_string($_POST['company']) . ", " . mysql_real_escape_string($_POST['product_id']) . ")";
// during debugging, uncomment this:  echo $sql;
mysql_query($sql) or die(mysql_error());
 

Re: Table in Database is not being updated.

Posted: Sat Dec 20, 2008 1:41 am
by Greg19
Thanks! Okay so I did what you said and know I'm getting this message:
INSERT INTO `customers_products` (customer, product_id) VALUES (hunts, 1314A)Unknown column 'hunts' in 'field list'
(hunts is a random name I chose for a customer and 1314A would be the product ID)

So my code thinks what is in the drop-down menu is a column in my table?

Code: Select all

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

Re: Table in Database is not being updated.

Posted: Sat Dec 20, 2008 1:02 pm
by califdon
That's because you didn't enclose the values in quotation marks. I failed to put them in my suggested code--sorry. That's why forming the string is so useful, it becomes immediately clear what's wrong. The first part,
INSERT INTO `customers_products` (customer, product_id) VALUES (hunts, 1314A)
comes from the echo $sql statement, which shows that there are no quote marks around hunts and 1314A, so MySQL is looking for fields by those names, just as it did for customer and product_id. The second part,
Unknown column 'hunts' in 'field list'
tells you the same thing even more specifically. Here's how your string statement should look:

Code: Select all

$sql = "INSERT INTO `customers_products` (customer, product_id) VALUES ('";
$sql .= mysql_real_escape_string($_POST['customer']) . "', '" . mysql_real_escape_string($_POST['product_id']) . "')";
It is essential that you get the single and double quotes correct. The double quotes enclose the string that you are forming, the single quotes need to be in the string so that MySQL interprets them as strings, not field names.

Re: Table in Database is not being updated.

Posted: Sat Dec 20, 2008 2:41 pm
by Greg19
Thanks a ton for your help, got the thing working now.