PHP Mysql UPDATE failure?

drdrew · August 17, 2010

I will provide the code. Im working on my script which updates a row of data in a mysql DB, the update command doesnt have all of the fields.(I don't know if this is failing it or not?)

The code for the form is.

<?php
include("../scripts/dbconnect.php");
mysql_select_db("rdb")or die("cannot select DB");


$sql="SELECT * FROM wo WHERE won='1'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="updatewo1.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td align="center"> </td>
<td align="center"><strong>WON</strong></td>
<td align="center"><strong>CName</strong></td>
<td align="center"><strong>CEmail</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" id="name" value="<?php echo $rows['won']; ?>"></td>
<td align="center"><input name="lastname" type="text" id="lastname" value="<?php echo $rows['cname']; ?>" size="15"></td>
<td><input name="email" type="text" id="email" value="<?php echo $rows['cemail']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['won']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>


<?php
mysql_close();
?>

The page the action goes to is below.

<?php
include("../scripts/dbconnect.php");
mysql_select_db("rdb")or die("cannot select DB");

$sql="UPDATE wo SET won='$_POST[won]', cname='$_POST[cname]', cemail='$_POST[cemail]'
WHERE won = '$_POST[won]'";
$result = mysql_query($sql);

if($result){
echo "Successful";
}

else {
echo "ERROR";
}
?>

I am fairly new to PHP, any help would be greatly appreciated. I will list all of the Mysql table's fields below.

won,wopword,status,dws,problem,dfin,cost,service,coupon,lmao1,lmao2,cname,caddy,cemail,cphone,compinfo,techname,comments,storeloc,clerk,solution

Pikachu2000 · August 17, 2010

There is no $_POST['won'] being passed to the second script. There is $_POST['name'], $_POST['lastname'], $_POST['email'] and $_POST['id'].

drdrew · August 17, 2010

Wow that was an idiot move. But the DB still doesn't update?

drdrew · August 17, 2010

Wait NVM I forgot to change the name. WOW am I slow to day thanks for your help. I now feel like an idiot.

Pikachu2000 · August 17, 2010

Echo any errors and your query string to the screen.

$result = mysql_query($sql) or die('<br />Query: ' . $sql . '<br />Error: ' . mysql_error());

drdrew · August 17, 2010

It was before giving me the Success error(which aggravated me).

Pikachu2000 · August 17, 2010

On an UPDATE query, you're better off to use mysql_affected_rows() since the query can actually succeed but not change anything at all.

drdrew · August 17, 2010

Im wicked new to this how would I do that?

TY

Pikachu2000 · August 17, 2010

After the query execution . . .

$aff_rows = mysql_affected_rows($result);
if( $aff_rows == 0 ) {
     echo 'No rows were updated.'; // Or do something else, whatever . . .
} else {
     echo $aff_rows . ' were updated'; // Or whatever . . .
}

drdrew · August 17, 2010

Thanks, so now what does that do? And why is it better?

drdrew · August 17, 2010

I just tried it and get an error.

Warning: mysql_affected_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CBMS\wo\updatewo1.php on line 9

No rows were updated.

<?php
include("../scripts/dbconnect.php");
mysql_select_db("rdb")or die("cannot select DB");

$sql="UPDATE wo SET won='$_POST[won]', cname='$_POST[cname]', cemail='$_POST[cemail]'
WHERE won = '$_POST[won]'";
$result = mysql_query($sql);

$aff_rows = mysql_affected_rows($result);
if( $aff_rows == 0 ) {
     echo 'No rows were updated.'; // Or do something else, whatever . . .
} else {
     echo $aff_rows . ' were updated'; // Or whatever . . .
}
?>

Pikachu2000 · August 17, 2010

It tells you how many rows were affected by the UPDATE query. An update query can return success, even if it doesn't actually change anything, so if you want to know if it actually updated any records, that would be the way to do it.

drdrew · August 17, 2010

I just checked it didn't update even with the error.

Pikachu2000 · August 17, 2010

Post your current code . . .

drdrew · August 17, 2010

<?php
include("../scripts/dbconnect.php");
mysql_select_db("rdb")or die("cannot select DB");

$sql="UPDATE wo SET won='$_POST[won]', cname='$_POST[cname]', cemail='$_POST[cemail]'
WHERE won = '$_POST[won]'";
$result = mysql_query($sql);

$aff_rows = mysql_affected_rows($result);
if( $aff_rows == 0 ) {
     echo 'No rows were updated.'; // Or do something else, whatever . . .
} else {
     echo $aff_rows . ' were updated'; // Or whatever . . .
}
?>

The likely hood of me screwing up a simple task is more than 97.285%.

Pikachu2000 · August 17, 2010

If the form hasn't changed, there is still no $_POST['won'] being passed to this script. To see exactly what the script is getting from the form, put this at the top, then post the result of it here.

echo '<pre>';
print_r($_POST);
echo '</pre>';

drdrew · August 17, 2010

Array

(

[won] => 1

[wopword] => 1

[status] => xgdfg

[dws] => 2010-08-16

[dfin] => 0000-00-00

[problem] => Problem

[cost] => asd

[service] => Service

[coupon] => agd

[cname] => asdg

[caddy] => Customers Address

[cemail] => asd

[cphone] => ag

[compinfo] => Computer Information

[techname] => agfsd

[comments] => Comments

[storeloc] => afhg

[clerk] => agf

[solution] => Solution

[lmao1] => 1

[lmao2] => 1

[lmao3] => 1

[lmao4] => 1

)

Warning: mysql_affected_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CBMS\wo\updatewo1.php on line 13

No rows were updated.

Pikachu2000 · August 17, 2010

Ah, now we're getting somewhere. The query is failing altogether it seems, so let's do this and see what we get.

$result = mysql_query($sql) or die('Query string: ' . $sql . '<br />Error: ' . mysql_error() . '<br />');

drdrew · August 17, 2010

Array

(

[won] => 1

[wopword] => 1

[status] => asdgag

[dws] => 2010-08-16

[dfin] => 0000-00-00

[problem] => Problem

[cost] => asd

[service] => Service

[coupon] => agd

[cname] => asdg

[caddy] => Customers Address

[cemail] => asd

[cphone] => ag

[compinfo] => Computer Information

[techname] => agfsd

[comments] => Comments

[storeloc] => afhg

[clerk] => agf

[solution] => Solution

[lmao1] => 1

[lmao2] => 1

[lmao3] => 1

[lmao4] => 1

)

Warning: mysql_affected_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CBMS\wo\updatewo1.php on line 13

No rows were updated.

I left the print in there.

Pikachu2000 · August 17, 2010

Oh, hell. I hosed the syntax . . . It should be mysql_affected_rows() without the $result parameter . . . Duh.

drdrew · August 17, 2010

I changed it and got:

Array

(

[won] => 1

[wopword] => 1

[status] => sga

[dws] => 2010-08-16

[dfin] => 0000-00-00

[problem] => Problem

[cost] => asd

[service] => Service

[coupon] => agd

[cname] => asdg

[caddy] => Customers Address

[cemail] => asd

[cphone] => ag

[compinfo] => Computer Information

[techname] => agfsd

[comments] => Comments

[storeloc] => afhg

[clerk] => agf

[solution] => Solution

[lmao1] => 1

[lmao2] => 1

[lmao3] => 1

[lmao4] => 1

)

No rows were updated.

I then checked the DB and it didn't change?

Pikachu2000 · August 17, 2010

This really should be using a primary key id field to reference the record that needs to be updated, but let's get the update at least working first. Are you changing any of the form's values before submitting it? Try changing only the value of the last field to something different.

drdrew · August 17, 2010

Ty, I tryed to change lmao from 1 to 7

Array

(

[won] => 1

[wopword] => 1

[status] => Status

[dws] => 2010-08-16

[dfin] => 0000-00-00

[problem] => Problem

[cost] => asd

[service] => Service

[coupon] => agd

[cname] => asdg

[caddy] => Customers Address

[cemail] => asd

[cphone] => ag

[compinfo] => Computer Information

[techname] => agfsd

[comments] => Comments

[storeloc] => afhg

[clerk] => agf

[solution] => Solution

[lmao1] => 1

[lmao2] => 1

[lmao3] => 1

[lmao4] => 7

)

No rows were updated.

Pikachu2000 · August 17, 2010

Huh? That field isn't used in the UPDATE query according to the form and code you posted. If either of those have changed, please repost them so we're on the same page.

drdrew · August 17, 2010

Your right im way to off tonight sorry. Just a quick question I have this set up and the <td> adds for new rows but no data is displayed?

<?php
include("../scripts/dbconnect.php");

mysql_select_db("rdb")or die("cannot select DB");

$sql="SELECT * FROM wo";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Remove Wo</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">/#/</td>
<td align="center" bgcolor="#FFFFFF"><strong>Won</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>CName</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>DWS</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>CEmail</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['won']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['won']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['cname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['dws']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['cemail']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM wo WHERE id='$del_id'";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php
if($result){
echo "Yes";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Do you have an idea of what could cause that. It produces a table but the $row information doesn't show up.

Thanks so very much again.

Sign In

PHP Mysql UPDATE failure?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information