Jump to content

Unable to update MYSQL database


sparky

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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? :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.