Jump to content

Archived

This topic is now archived and is closed to further replies.

sparky

Unable to update MYSQL database

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.

Share this post


Link to post
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;

Share this post


Link to post
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.

Share this post


Link to post
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? :)

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.