Jump to content


Photo

Unable to update MYSQL database


  • Please log in to reply
8 replies to this topic

#1 sparky

sparky
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationMemphis, TN, USA
  • Age:52

Posted 12 August 2003 - 01:03 PM

I feel like I am overlooking so ever minor, but unable to find it. What the problem is, when I edit a record, it either does not save, or it creates a duplicate. Make note, my insert code works perfectly, but my replace code does not. Note both:

works for inserting records
<?php
// open the connection
$conn = mysql_connect(\"localhost\", \"xxxx\", \"xxxx\");

// pick the database to use
mysql_select_db(\"galleria\",$conn);

// create the SQL statement

$sql = \"INSERT INTO newinv values (\'*\', \'$_POST[item]\', \'$_POST[dir]\', \'$_POST[fn]\', \'$_POST[price]\', \'$_POST[class]\', \'$_POST[dis]\', \'$_POST[inv]\')\";

// execute the SQL statement
if (mysql_query($sql, $conn)) {
echo \"Record Updated!\";
} else {
echo \"Something went Wrong\";
}
?>


Does NOT work for Editing/Replacing records
<?php
// open the connection
$conn = mysql_connect(\"localhost\", \"xxxx\", \"xxxx\");

// pick the database to use
mysql_select_db(\"galleria\",$conn);

// create the SQL statement

$sql = \"REPLACE INTO newinv (\'$_POST[id]\', \'$_POST[item]\', \'$_POST[dir]\', \'$_POST[fn]\', \'$_POST[price]\', \'$_POST[class]\', \'$_POST[dis]\', \'$_POST[inv]\') WHERE \'id\'=id\";

// execute the SQL statement
if (mysql_query($sql, $conn)) {
echo \"Record Updated!\";
} else {
echo \"Something went Wrong\";
}
?>

-------------------------

The following is the page following that generates the data to be saved.



<html>
<body bgcolor=#FCF8A0>

<?php
// open the connection
$conn = mysql_connect(\"localhost\", \"xxxx\", \"xxxx\");

// pick the database to use
mysql_select_db(\"galleria\",$conn);

// create the SQL statement
$ids = $_GET[ \'ids\' ];
$sql = \"SELECT * FROM newinv WHERE id=\'$ids\'\";

// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());

//go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {

// give a name to the fields
$id = $newArray[\'id\'];
$item = $newArray[\'item\'];
$dir = $newArray[\'dir\'];
$fn = $newArray[\'fn\'];
$price = $newArray[\'price\'];
$class = $newArray[\'class\'];
$dis = $newArray[\'dis\'];
$inv = $newArray[\'inv\'];

}


?>

<p align=center>
<table cellpadding=\"2\" cellspacing=\"2\" width=\"600\">
<form method=\"POST\" action=\"EditInsert.php\">
<CENTER>

<table width=\"100%\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\">
<tr>
<td width=\"50%\">
Item Name
</td>
<td width=\"50%\">
<input type=\"text\" name=\"item\" size=\"30\" value=\"<?php echo $item; ?>\">
</td> </tr>
<tr>
<td width=\"50%\">
Directory
</td>
<td width=\"50%\">
<input type=\"text\" name=\"dir\" size=\"30\" value=\"<?php echo $dir; ?>\">
</td></tr>
<td width=\"50%\">
FileName
</td>
<td width=\"50%\">
<input type=\"text\" name=\"fn\" size=\"30\" value=\"<?php echo $fn; ?>\">
</td> </tr>
<td width=\"50%\">
Inventory Number
</td>
<td width=\"50%\">
<input type=\"text\" name=\"inv\" size=\"15\" value=\"<?php echo $inv; ?>\">
</td> </tr>
<td width=\"50%\">
Sale Price ($)
</td>
<td width=\"50%\">
<input type=\"text\" name=\"price\" size=\"7\" value=\"<?php echo $price; ?>\">
</td> </tr>
<td width=\"50%\">
Classification
</td>
<td width=\"50%\">
<select name=\"class\">
<OPTION>Select Classification
<OPTION>Bazzar
<OPTION>African path - Clothing
</SELECT>
</td>
</tr>
<td>
<center>
<img src=\"http://www.galleriaonline.com/inv/<?php echo $dir; ?>/<?php echo $fn; ?>\">
</center></td>
<td width=\"50%\">
Discription<BR> (As much as you want)
<TEXTAREA NAME=\"dis\" \" COLS=\"60\" ROWS=\"12\" input type=\"text\"><?php echo $dis; ?></TEXTAREA>
</td>
</tr>
</td>
</tr>

</table>


</tr>
<tr>
<td width=\"50%\">
</td>
<td width=\"50%\">
<input type=\"submit\" value=\"Submit Item\" name=\"btnSubmit\">
</td>
</tr>
</table>
</form>
<HR><BR><BR>
</body>
</html>

-------------

Thanks for any assistance on this.

John Campbell
mailto:sparkie951@gmail.com


#2 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 12 August 2003 - 02:35 PM

You need a SELECT in your REPLACE statement or do the VALUES as a line:

1. REPLACE INTO table ()
VALUES ();

2. REPLACE INTO table ()
SELECT select_clause;

#3 sparky

sparky
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationMemphis, TN, USA
  • Age:52

Posted 12 August 2003 - 03:03 PM

You need a SELECT in your REPLACE statement or do the VALUES as a line:

1. REPLACE INTO table ()
VALUES ();

2. REPLACE INTO table ()
SELECT select_clause;


I am not understanding the SELECT clause on this. I am missing the big picture on this for some reason.

John Campbell
mailto:sparkie951@gmail.com


#4 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 12 August 2003 - 03:13 PM

REPLACE does exactly the same thing as INSERT INTO (even has the same syntax) except it replaces the old data instead of adding a new line.

You tell the DBMS what fields you are replacing then you need to specify which row you are replacing data with in the REPLACE clause, so:

REPLACE INTO table (field1, field2, field3)SELECT field1, field2, field3 FROM table WHERE tableID=$tableID

In the case of an on-the-fly operation, use:

REPLACE INTO table (fieldID, field2, field3)VALUES ($fieldID, $field2, $field3)

Is that better? :)

#5 sparky

sparky
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationMemphis, TN, USA
  • Age:52

Posted 12 August 2003 - 03:36 PM

REPLACE does exactly the same thing as INSERT INTO (even has the same syntax) except it replaces the old data instead of adding a new line.

You tell the DBMS what fields you are replacing then you need to specify which row you are replacing data with in the REPLACE clause, so:

REPLACE INTO table (field1, field2, field3)SELECT field1, field2, field3 FROM table WHERE tableID=$tableID

In the case of an on-the-fly operation, use:

REPLACE INTO table (fieldID, field2, field3)VALUES ($fieldID, $field2, $field3)

Is that better? :)


I am trying to use:

$sql = \"REPLACE INTO newinv (id, item, dir, fn, price, class, dis, inv) VALUES (\'$_POST[id]\', \'$_POST[item]\', \'$_POST[dir]\' , \'$_POST[fn]\', \'$_POST[price]\', \'$_POST[class]\',\'$_POST[dis]\',\'$_POST[inv]\')\";

Is there a problem with the \'$_POST[id]\', routine... WHen I use this, for some reason it is writing a new record that is blank.

John Campbell
mailto:sparkie951@gmail.com


#6 sparky

sparky
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationMemphis, TN, USA
  • Age:52

Posted 12 August 2003 - 03:48 PM

Minor goof on my end... Is writing data, but duplicating the record. I feel that I am pretty close, but not quite there. WHen it duplicates it, it also is incrementing the index counter.

John Campbell
mailto:sparkie951@gmail.com


#7 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 13 August 2003 - 11:08 AM

One question, why are you using replace, why not update? I think that would be easier and less confusing.

#8 sparky

sparky
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationMemphis, TN, USA
  • Age:52

Posted 13 August 2003 - 01:53 PM

Not sure.. I am quite a novice... I tried using replace command and really got lost with it... I finally got the replace command working and it is doing great now. Thanks for the comment. All of them are welcome.

sparky

John Campbell
mailto:sparkie951@gmail.com


#9 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 13 August 2003 - 02:32 PM

You\'re welcome. Well done. :)

What was missing?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users