LinuxQuestions.org - PHP with MySQL INSERT problem

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - PHP with MySQL INSERT problem (https://www.linuxquestions.org/questions/programming-9/php-with-mysql-insert-problem-713176/)

PHP with MySQL INSERT problem

Hello, I am looking for advice on some code I am writing. I am trying to ceat a database driven page for my company website where we will list all of the used equipment we are wanting to sell. What I am currently trying to do is make the page where I will ad the equipment into the database table. This page has a few text boxes for the equipments make, model, engine size, price, etc. It also has two drop down boxes. The first drop down box is to pick which category of equipment the pices being added is in and the second box is to pick the manufacturer (make) of the piece of equipment. As of now everything seems to work except that no matter which category I choos from the list, when I go back and check the database manually it is always category one listed in that field. I have tried a few different methods, but I always get the same results. If anyone has any suggestions I would greatly appreciate your input. I am including the code for your review:

PHP Code:




<?php 
$page_title = 'Add Equipment'; 
include ('header.html'); 
require_once ('mysql_connect1.php'); 
 
if (isset($_POST['submit'])) { 
 
if (!empty($_POST['equipname'])) { 
    $en = (($_POST['equipname'])); 
} else { 
$en = FALSE; 
echo '<p><font color="red">Please enter Equipment Name!</font></p>'; 
} 
 
if (!empty($_POST['equipmodel'])) { 
$em = (($_POST['equipmodel'])); 
} else { 
$em = ' '; 
} 
 
if (!empty($_POST['equipengine'])) { 
$ee = ($_POST['equipengine']); 
} else { 
$ee = ' '; 
} 
 
if (!empty($_POST['equipunit'])) { 
$eu = ($_POST['equipunit']); 
} else { 
$eu = ' '; 
} 
 
if (!empty($_POST['lprice'])) { 
$lp = ($_POST['lprice']); 
} else { 
$lp = ' '; 
} 
 
if (!empty($_POST['sprice'])) { 
$sp = ($_POST['sprice']); 
} else { 
$sp = ' '; 
} 
 
if ($_POST['sellcategory'])  { 
    $sellcategory = TRUE; 
} else { 
$sellcategory = FALSE; 
echo '<p><font color="red">Please select a category!</font></p>'; 
} 
 
if ($_POST['makecategory'])  { 
    $makecategory = TRUE; 
} else { 
$makecategory = FALSE; 
 echo '<p><font color="red">Please select a make!</font></p>'; 
} 
 
if ($en && $sellcategory && $makecategory) { 
$query = "INSERT INTO ForSale.Product (ForSale.Product.Product, ForSale.Product.Model, ForSale.Product.Engine, ForSale.Product.Unit, ForSale.Product.ListPrice, ForSale.Product.SellPrice, ForSale.Product.Category, ForSale.Product.Make) VALUES ('$en', '$em', '$ee', '$eu', '$lp', '$sp', '$sellcategory', '$sellmake')"; 
$result = @mysql_query ($query); 
$tid = @mysql_insert_id(); 
 
##if ($cid) { 
##$query = 'INSERT INTO ForSale.Product (ForSale.Product.Category) VALUES '; 
##if ($_POST['sellcategory'] > 0) { 
##$query .= "($cid, {$_POST['sellcategory']}), "; 
##} 
##$query = substr ($query, 0, -2); 
 
##if ($mid) { 
##$query = 'INSERT INTO ForSale.Product (ForSale.Product.Make) VALUES '; 
##if ($_POST['makecategory'] > 0) { 
##$query .= "($mid, {$_POST['makecategory']}), "; 
##} 
##$query = substr ($query, 0, -2); 
 
$result = @mysql_query 
($query); 
if ($result) { 
echo '<p><b>Thank you for your submission!</b></p>'; 
} else { 
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'.'; 
}} else { 
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'..'; 
}} else { 
echo '<p><font color="red">Please try again!</font></p>'. mysql_error() .'...'; 
} 
 
 
$query = "SELECT * FROM ForSale.Category ORDER BY ForSale.Category.CatID ASC"; 
$result = @mysql_query ($query); 
$catpulldown = '<option>Select One...</option> 
'; 
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) { 
$catpulldown .= "<option value=\"{$row['CatID']}\">{$row['CatName']}</option>\n"; 
} 
 
$query = "SELECT * FROM ForSale.Make ORDER BY ForSale.Make.MakeID ASC"; 
$result = @mysql_query ($query); 
$makepulldown = '<option>Select One...</option> 
'; 
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) { 
$makepulldown .= "<option value=\"{$row['MakeID']}\">{$row['Make']}</option>\n"; 
} 
?> 
<style type="text/css"> 
<!-- 
.aligntop { 
    vertical-align: top; 
} 
--> 
</style> 
 
 
 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
<fieldset> 
<legend>Fill out the form to submit a piece of equipment for sale: </legend> 
<br> 
Equipment Description:  
 
 
 
<p><b>Equipment Name:</b> 
  <input type="text" name="equipname" size="60" maxlength="60" value="<?php if (isset($_POST['equipname'])) echo $_POST['equipname']; ?>" /> 
</p> 
<p><b>Equipment Model #:</b> 
  <input type ="text" name="equipmodel" size="60" maxlength="60" value="<?php if (isset($_POST['equipmodel'])) echo $_POST['equipmodel']; ?>" / > 
  </p> 
<p><b>Equipment Engine:</b> 
  <input type ="text" name="equipengine" size="60" maxlength="60" value="<?php if (isset($_POST['equipengine'])) echo $_POST['equipengine']; ?>" / > 
</p> 
<p><b>Equipment Unit #:</b> 
  <input type ="text" name="equipunit" size="60" maxlength="60" value="<?php if (isset($_POST['equipunit'])) echo $_POST['equipunit']; ?>" / > 
</p> 
<hr> 
<p><br> 
  Equipment Price:</p> 
<p>  <b>List Price:</b> 
  <input type ="text" name="lprice" size="20" maxlength="10" value="<?php if (isset($_POST['lprice'])) echo $_POST['lprice']; ?>" / > 
</p> 
<p><b>Sell Price:</b> 
  <input type ="text" name="sprice" size="20" maxlength="10" value="<?php if (isset($_POST['sprice'])) echo $_POST['sprice']; ?>" / > 
</p> 
<hr> 
<p><b>Equipment Category:</b>  
  <select name="sellcategory"><?php echo $catpulldown; ?></select> 
</p> 
<p><b>Equipment Make:</b>  
  <select name="sellmake"><?php echo $makepulldown; ?></select> 
</p> 
</fieldset> 
<div align="center"> 
<input type="submit" name="submit" value="Submit" /> 
</div> 
 
<?php mysql_close(); 
include ('footer.html'); 
?> 
<p><a href="http://www.rentalcityomaha.com/manage/">Return Admin Page</a></p> 
<p> 
<a href="http://mysql.rentalcityomaha.com"  
  onclick="window.open(this.href, 'popupwindow',  
  'scrollbars=1,width=950,height=650');  
  return false;" 
> 
Go to MySQL 
</a> 
</p> 
<p><a href="http://www.rentalcityomaha.com">Go to Home Page</a></p>

The part in the code that is commented out is a piece of code that I had from a different page and at first I modified for this page, until I realized that I no longer needed it. If you think that I do need that let me know.

Thank you, in advance, for your help!

Some classical debugging should be done here.

First look at the HTML which is produced for the drop down lists. Does it have the correct option values? If not, look at the loops to create the drop down list.

Then print out the POST array you are receiving back. Are the values for the drop down lists correct in this array? If not, check the form in the HTML code.

If the post array has the correct contents, print out the INSERT SQL statement. You must see there an error in the value for the category, at least something else than what you expect.

jlinkels

A little debugging

Thank you for your ideas jlinkels. I looked at my HTML source and all of the values in my drop down list are correct. So I checked my SQL Query and I noticed that the variable for the product make was set incorrectly. I changed that to the correct value and now I get ones for both the category and the make in the database. What is weird is when I had the incorrect value for the make it INSERTed the correct value into the database. That soesn't make any sense to me. If you could help me out with some debugging steps I would really appreciate it. I am pretty unfamiliar with how to do it though. Here is what I updated my code to be:

PHP Code:




<?php 
  
$page_title = 'Add Equipment';  
include ('header.html');  
require_once ('mysql_connect1.php');  
 
if (isset($_POST['submit'])) {  
 
if (!empty($_POST['equipname'])) {  
    $en = (($_POST['equipname']));  
} else {  
$en = FALSE;  
echo '<p><font color="red">Please enter Equipment Name!</font></p>';  
}  
 
if (!empty($_POST['equipmodel'])) {  
$em = (($_POST['equipmodel']));  
} else {  
$em = ' ';  
}  
 
if (!empty($_POST['equipengine'])) {  
$ee = ($_POST['equipengine']);  
} else {  
$ee = ' ';  
}  
 
if (!empty($_POST['equipunit'])) {  
$eu = ($_POST['equipunit']);  
} else {  
$eu = ' ';  
}  
 
if (!empty($_POST['lprice'])) {  
$lp = ($_POST['lprice']);  
} else {  
$lp = ' ';  
}  
 
if (!empty($_POST['sprice'])) {  
$sp = ($_POST['sprice']);  
} else {  
$sp = ' ';  
}  
 
if ($_POST['sellcategory'])  {  
    $sellcategory = TRUE;  
} else {  
$sellcategory = FALSE;  
echo '<p><font color="red">Please select a category!</font></p>';  
}  
 
if ($_POST['makecategory'])  {  
    $makecategory = TRUE;  
} else {  
$makecategory = FALSE;  
 echo '<p><font color="red">Please select a make!</font></p>';  
}  
 
if ($en && $sellcategory && $makecategory) {  
$query = "INSERT INTO ForSale.Product (ForSale.Product.Product, ForSale.Product.Model, ForSale.Product.Engine, ForSale.Product.Unit, ForSale.Product.ListPrice, ForSale.Product.SellPrice, ForSale.Product.Category, ForSale.Product.Make) VALUES ('$en', '$em', '$ee', '$eu', '$lp', '$sp', '$sellcategory', '$makecategory')";  
$result = @mysql_query ($query);  
$tid = @mysql_insert_id();  
 
##if ($cid) {  
##$query = 'INSERT INTO ForSale.Product (ForSale.Product.Category) VALUES ';  
##if ($_POST['sellcategory'] > 0) {  
##$query .= "($cid, {$_POST['sellcategory']}), ";  
##}  
##$query = substr ($query, 0, -2);  
 
##if ($mid) {  
##$query = 'INSERT INTO ForSale.Product (ForSale.Product.Make) VALUES ';  
##if ($_POST['makecategory'] > 0) {  
##$query .= "($mid, {$_POST['makecategory']}), ";  
##}  
##$query = substr ($query, 0, -2);  
 
$result = @mysql_query  
($query);  
if ($result) {  
echo '<p><b>Thank you for your submission!</b></p>';  
} else {  
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'.';  
}} else {  
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'..';  
}} else {  
echo '<p><font color="red">Please try again!</font></p>'. mysql_error() .'...';  
}  
 
 
$query = "SELECT * FROM ForSale.Category ORDER BY ForSale.Category.CatID ASC";  
$result = @mysql_query ($query);  
$catpulldown = '<option>Select One...</option>  
';  
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {  
$catpulldown .= "<option value=\"{$row['CatID']}\">{$row['CatName']}</option>\n";  
}  
 
$query = "SELECT * FROM ForSale.Make ORDER BY ForSale.Make.MakeID ASC";  
$result = @mysql_query ($query);  
$makepulldown = '<option>Select One...</option>  
';  
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {  
$makepulldown .= "<option value=\"{$row['MakeID']}\">{$row['Make']}</option>\n";  
}  
?>  
<style type="text/css">  
<!--  
.aligntop {  
    vertical-align: top;  
}  
-->  
</style>  
 
 
 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">  
<fieldset>  
<legend>Fill out the form to submit a piece of equipment for sale: </legend>  
<br>  
Equipment Description:   
 
 
 
<p><b>Equipment Name:</b>  
  <input type="text" name="equipname" size="60" maxlength="60" value="<?php if (isset($_POST['equipname'])) echo $_POST['equipname']; ?>" />  
</p>  
<p><b>Equipment Model #:</b>  
  <input type ="text" name="equipmodel" size="60" maxlength="60" value="<?php if (isset($_POST['equipmodel'])) echo $_POST['equipmodel']; ?>" / >  
  </p>  
<p><b>Equipment Engine:</b>  
  <input type ="text" name="equipengine" size="60" maxlength="60" value="<?php if (isset($_POST['equipengine'])) echo $_POST['equipengine']; ?>" / >  
</p>  
<p><b>Equipment Unit #:</b>  
  <input type ="text" name="equipunit" size="60" maxlength="60" value="<?php if (isset($_POST['equipunit'])) echo $_POST['equipunit']; ?>" / >  
</p>  
<hr>  
<p><br>  
  Equipment Price:</p>  
<p>  <b>List Price:</b>  
  <input type ="text" name="lprice" size="20" maxlength="10" value="<?php if (isset($_POST['lprice'])) echo $_POST['lprice']; ?>" / >  
</p>  
<p><b>Sell Price:</b>  
  <input type ="text" name="sprice" size="20" maxlength="10" value="<?php if (isset($_POST['sprice'])) echo $_POST['sprice']; ?>" / >  
</p>  
<hr>  
<p><b>Equipment Category:</b>   
  <select name="sellcategory"><?php echo $catpulldown; ?></select>  
</p>  
<p><b>Equipment Make:</b>   
  <select name="makecategory"><?php echo $makepulldown; ?></select>  
</p>  
</fieldset>  
<div align="center">  
<input type="submit" name="submit" value="Submit" />  
</div>  
 
<?php mysql_close();  
include ('footer.html');  
?>  
<p><a href="http://www.rentalcityomaha.com/manage/">Return Admin Page</a></p>  
<p>  
<a href="http://mysql.rentalcityomaha.com"   
  onclick="window.open(this.href, 'popupwindow',   
  'scrollbars=1,width=950,height=650');   
  return false;"  
>  
Go to MySQL  
</a>  
</p>  
<p><a href="http://www.rentalcityomaha.com">Go to Home Page</a></p>

Thank you.

Okay, almost fixed it...

Alright, I got it to post the correct values, but now it is placing the record into the database twice. Why would it do this? I can't figure it out at all. Here is the code, let me know if you have any ideas.

PHP Code:




<?php 
  
$page_title = 'Add Equipment';  
include ('header.html');  
require_once ('mysql_connect1.php');  
 
if (isset($_POST['submit'])) {  
 
if (!empty($_POST['equipname'])) {  
    $en = (($_POST['equipname']));  
} else {  
$en = FALSE;  
echo '<p><font color="red">Please enter Equipment Name!</font></p>';  
}  
 
if (!empty($_POST['equipmodel'])) {  
$em = (($_POST['equipmodel']));  
} else {  
$em = ' ';  
}  
 
if (!empty($_POST['equipengine'])) {  
$ee = ($_POST['equipengine']);  
} else {  
$ee = ' ';  
}  
 
if (!empty($_POST['equipunit'])) {  
$eu = ($_POST['equipunit']);  
} else {  
$eu = ' ';  
}  
 
if (!empty($_POST['lprice'])) {  
$lp = ($_POST['lprice']);  
} else {  
$lp = ' ';  
}  
 
if (!empty($_POST['sprice'])) {  
$sp = ($_POST['sprice']);  
} else {  
$sp = ' ';  
}  
 
if (!empty($_POST['sellcategory']))  {  
    $sc = ($_POST['sellcategory']);  
} else {  
$sc = FALSE;  
echo '<p><font color="red">Please select a category!</font></p>';  
}  
 
if (!empty($_POST['makecategory']))  {  
    $mc = ($_POST['makecategory']);  
} else {  
$mc = FALSE;  
 echo '<p><font color="red">Please select a make!</font></p>';  
}  
 
if ($en && $sc && $mc) {  
$query = "INSERT INTO ForSale.Product (ForSale.Product.Product, ForSale.Product.Model, ForSale.Product.Engine, ForSale.Product.Unit, ForSale.Product.ListPrice, ForSale.Product.SellPrice, ForSale.Product.Category, ForSale.Product.Make) VALUES ('$en', '$em', '$ee', '$eu', '$lp', '$sp', '$sc', '$mc')";  
$result = @mysql_query ($query);  
  
 
  
 
$result = @mysql_query  
($query);  
if ($result) {  
echo '<p><b>Thank you for your submission!</b></p>';  
} else {  
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'.';  
}} else {  
echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'. mysql_error() .'..';  
}} else {  
echo '<p><font color="red">Please try again!</font></p>'. mysql_error() .'...';  
} 
 
 
$query = "SELECT * FROM ForSale.Category ORDER BY ForSale.Category.CatID ASC";  
$result = @mysql_query ($query);  
$catpulldown = '<option>Select One...</option>  
';  
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {  
$catpulldown .= "<option value=\"{$row['CatID']}\">{$row['CatName']}</option>\n";  
}  
 
$query = "SELECT * FROM ForSale.Make ORDER BY ForSale.Make.MakeID ASC";  
$result = @mysql_query ($query);  
$makepulldown = '<option>Select One...</option>  
';  
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {  
$makepulldown .= "<option value=\"{$row['MakeID']}\">{$row['Make']}</option>\n";  
}  
?>  
<style type="text/css">  
<!--  
.aligntop {  
    vertical-align: top;  
}  
-->  
</style>  
 
 
 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">  
<fieldset>  
<legend>Fill out the form to submit a piece of equipment for sale: </legend>  
<br>  
Equipment Description:   
 
 
 
<p><b>Equipment Name:</b>  
  <input type="text" name="equipname" size="60" maxlength="60" value="<?php if (isset($_POST['equipname'])) echo $_POST['equipname']; ?>" />  
</p>  
<p><b>Equipment Model #:</b>  
  <input type ="text" name="equipmodel" size="60" maxlength="60" value="<?php if (isset($_POST['equipmodel'])) echo $_POST['equipmodel']; ?>" / >  
  </p>  
<p><b>Equipment Engine:</b>  
  <input type ="text" name="equipengine" size="60" maxlength="60" value="<?php if (isset($_POST['equipengine'])) echo $_POST['equipengine']; ?>" / >  
</p>  
<p><b>Equipment Unit #:</b>  
  <input type ="text" name="equipunit" size="60" maxlength="60" value="<?php if (isset($_POST['equipunit'])) echo $_POST['equipunit']; ?>" / >  
</p>  
<hr>  
<p><br>  
  Equipment Price:</p>  
<p>  <b>List Price:</b>  
  <input type ="text" name="lprice" size="20" maxlength="10" value="<?php if (isset($_POST['lprice'])) echo $_POST['lprice']; ?>" / >  
</p>  
<p><b>Sell Price:</b>  
  <input type ="text" name="sprice" size="20" maxlength="10" value="<?php if (isset($_POST['sprice'])) echo $_POST['sprice']; ?>" / >  
</p>  
<hr>  
<p><b>Equipment Category:</b>   
  <select name="sellcategory"><?php echo $catpulldown; ?></select>  
</p>  
<p><b>Equipment Make:</b>   
  <select name="makecategory"><?php echo $makepulldown; ?></select>  
</p>  
</fieldset>  
<div align="center">  
<input type="submit" name="submit" value="Submit" />  
</div>  
 
<?php mysql_close();  
include ('footer.html');  
?>  
<p><a href="http://www.rentalcityomaha.com/manage/">Return Admin Page</a></p>  
<p>  
<a href="http://mysql.rentalcityomaha.com"   
  onclick="window.open(this.href, 'popupwindow',   
  'scrollbars=1,width=950,height=650');   
  return false;"  
>  
Go to MySQL  
</a>  
</p>  
<p><a href="http://www.rentalcityomaha.com">Go to Home Page</a></p>

Thanks again everyone!

Come on... what do you see if you print the SQL statements?... you insert it twice by executing the SQL statement twice.

jlinkels

Not to be stupid, but when you say to print the SQL statements what do you mean? I am brand new at debugging and I'm not sure what you mean. If you could explain it to me I would appreciate it. I know debugging is important and I would like your help in learning what I need to do. I did figure it out though. In the lines directly below where my query is in the code I had two $results variables. The second $result variable was a remnant of using code from one of my other pages that required to SQL statements to place values from two forms into multiple tables. I can't believe I missed that one, but at least I know what to look for if it happens again. I would still like to hear your thoughts on printing SQL statements though. Thank you.

Quote:

Originally Posted by althage (Post 3483708)

In the lines directly below where my query is in the code I had two $results variables. The second $result variable was a remnant of using code from one of my other pages that required to SQL

PHP Code:




if ($en && $sc && $mc) {   
$query = "INSERT INTO ForSale.Product (ForSale.Product.Product, ForSale.Product.Model, ForSale.Product.Engine, ForSale.Product.Unit, ForSale.Product.ListPrice, ForSale.Product.SellPrice, ForSale.Product.Category, ForSale.Product.Make) VALUES ('$en', '$em', '$ee', '$eu', '$lp', '$sp', '$sc', '$mc')";   
$result = @mysql_query ($query);   
   
 
   
 
$result = @mysql_query   
($query);

This was the error. I am not sure "having two $results variables" describes correctly the error.

And debugging is done by inserting this statement in front your mysql_query statement:

PHP Code:




if ($en && $sc && $mc) {   
$query = "INSERT INTO ForSale.Product (ForSale.Product.Product, ForSale.Product.Model, ForSale.Product.Engine, ForSale.Product.Unit, ForSale.Product.ListPrice, ForSale.Product.SellPrice, ForSale.Product.Category, ForSale.Product.Make) VALUES ('$en', '$em', '$ee', '$eu', '$lp', '$sp', '$sc', '$mc')"; 
echo "inserting: $query<br>" // <<<<<<<<<<<<<<<< 
$result = @mysql_query ($query);   
   
 
   
echo "inserting: $query<br>" // <<<<<<<<<<<<<<< 
$result = @mysql_query   
($query);

jlinkels