Unable to update MYSQL database

sparky · August 12, 2003

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>

<?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\'];

}

?>

<tr>

Item Name

</td>

</td> </tr>

<tr>

Directory

</td>

</td></tr>

FileName

</td>

</td> </tr>

Inventory Number

</td>

</td> </tr>

Sale Price ($)

</td>

</td> </tr>

Classification

</td>

<OPTION>Select Classification

<OPTION>Bazzar

<OPTION>African path - Clothing

</SELECT>

</td>

</tr>

<td>

</center></td>

Discription<BR> (As much as you want)

</td>

</tr>

</td>

</tr>

</table>

</tr>

<tr>

</td>

</td>

</tr>

</table>

</form>

</body>

</html>

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

Thanks for any assistance on this.

michael yare · August 12, 2003

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;

sparky · August 12, 2003

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.

michael yare · August 12, 2003

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?

sparky · August 12, 2003

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.

sparky · August 12, 2003

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.

michael yare · August 13, 2003

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

sparky · August 13, 2003

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

michael yare · August 13, 2003

You\'re welcome. Well done.

What was missing?

Sign In

Unable to update MYSQL database

Recommended Posts

sparky

Link to comment

Share on other sites

michael yare

Link to comment

Share on other sites

sparky

Link to comment

Share on other sites

michael yare

Link to comment

Share on other sites

sparky

Link to comment

Share on other sites

sparky

Link to comment

Share on other sites

michael yare

Link to comment

Share on other sites

sparky

Link to comment

Share on other sites

michael yare

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information