Update not updating db

[crmamx]

This executes with no errors but it is not updating the db. I have previously verified that the record exists.

 

<?php // update  database

// Connect to database =====================================================

include("connect_db.php");

// retrieve form data from form2.html  ==========================================

$id = $_POST['id']; // do not change
$id = $_POST['ama']; // do not change
$id = $_POST['model_name'];
$id = $_POST['model_mfg'];
$id = $_POST['wingspan'];
$id = $_POST['engine'];
$id = $_POST['decibels'];

// Send query ===========================================================

$query = "UPDATE airplanes SET model_name = '$model_name' WHERE id = '$id'";

//if (!mysql_query($query)){
//die('Error :' .mysql_error());
//}
   
echo '<p>Return to <a
href="members_menu.html">Members Menu</a></p>';

?>

 

Thanks

[Jessica]

You're not actually running the code to query the DB you know, right?

print the query and show what it says.

[crmamx]

Tried that. Tried everything I know.

 

The $query line just happens to be the last thing I tried. Can't see where it would do any good to post the many different lines I have tried because none of them worked.

 

These doesn't work either.

 

"UPDATE airplanes SET model_name = '$model_name' WHERE id = '$id'";
UPDATE airplanes SET model_name = '$model_name' WHERE id = '$id';

 

Was just reading Reed's page. Sure is a clean design.

[Jessica]

Echo $query and copy and paste it here.

 

Was just reading Reed's page. Sure is a clean design.

you mean my dog's blog? Thanks, it's a wordpress template. Credit is ...somewhere on there.

[Maq]

Show us the exact code that's not working because the part that executes the actual query is commented out.

[gizmola]

Most probably it's because id is an integer and you're using WHERE id = '$id' rather than WHERE id = $id.

 

Follow this advice, will help you figure it out.

[Maq]

Most probably it's because id is an integer and you're using WHERE id = '$id' rather than WHERE id = $id.

 

Follow this advice, will help you figure it out.

 

AFAIK that shouldn't cause any problems.

[Jessica]

Most probably it's because id is an integer and you're using WHERE id = '$id' rather than WHERE id = $id.

 

Follow this advice, will help you figure it out.

 

AFAIK that shouldn't cause any problems.

 

In MySQL maybe, I am pretty sure it could cause problems in MSSQL. *shrug*

[crmamx]

ID is the auto-incremented key. However, it still doesn't update the db. What kills me is my programs to add or delete records in the db work fine but I can't figure this one out.

 

<?php // change airplane in database

// Connect to database =====================================================

include("connect_db.php");

// retrieve form data from form2.html  ==========================================

$id = $_POST['id']; // do not change
$id = $_POST['ama']; // do not change
$id = $_POST['model_name'];
$id = $_POST['model_mfg'];
$id = $_POST['wingspan'];
$id = $_POST['engine'];
$id = $_POST['decibels'];

// Send query ===========================================================

mysql_query("DELETE FROM airplanes WHERE id=$id");

echo '<p>Return to <a
href="members_menu.html">Members Menu Here</a></p>';
?>

 

I'm thinking maybe I shouldn't have volunteered for this job.

 

 

[Maq]

Maybe there's an error?  Try changing this line to:

mysql_query("DELETE FROM airplanes WHERE id=$id") or die(mysql_error());

 

Do you see the echo?

[gizmola]

Most probably it's because id is an integer and you're using WHERE id = '$id' rather than WHERE id = $id.

 

Follow this advice, will help you figure it out.

 

AFAIK that shouldn't cause any problems.

 

Hmm, yeah that is something that probably was an issue a long time ago, but mysql doesn't seem to care about that anymore, although it would certainly not work if you were to omit the quotes around a character column comparison.

[gizmola]

So the obvious thing that jumps out is that you have a problem in your SET columname for the update query.  Again this would jump out if ($result) is false and you check the mysql_error().

 

Do a describe on the table (or use phpMyAdmin) to check the table structure. 

[crmamx]

I am at my wit's end. My programs to add and delete records in the db work fine, but I cannot get the update program to work.

 

I have completely rewritten the whole thing. The if statements are my version of seeing if a record is found. If not found, it prints. If found it also prints but does not update.

 

<?php // Update airplane in database

// Connect to database =====================================================

include("connect_db.php");

$id = $_POST['id']; // Retrieve form id

// Send query ====================================================

$query = "SELECT * FROM airplanes WHERE id='$id'";
if (!mysql_query($query)){
die('Error :' .mysql_error());
}
$result = mysql_query ($query);
$num = mysql_num_rows($result);

// Check to see if record exists =======================================

if (mysql_num_rows($result) == 1)
    {
    "UPDATE airplanes SET model_name='$model_name', model_mfg='$model_mfg', wingspan='$wingspan', engine='$engine', decibels='$decibels' WHERE id='$id'";
        print 'Database successfully updated';
    } 
    
// Record not in db  ===============================================
    if (mysql_num_rows($result) == 0)
    {
    print '<big style="color: red;"><span style="font-weight: bold;">
    ERROR MESSAGE:</span></big> ID number not found'; 
    echo "<br />";
    echo "<br />";
    print 'Use the BACK button on your browser to try again'; 
    echo "<br />";
    } 
    
echo "<br />"; 
echo '<p>Return to <a
href="members_menu.html">Members Menu Here</a></p>';

?>

[Pikachu2000]

You never execute the UPDATE query with mysql_query(); it's just a string in the middle of nowhere.

 

I suspect the SELECT query isn't really necessary; you can simply execute the UPDATE query, then if the query returns an error, or mysql_affected_rows() == 0, no record was updated.

 

I assume that $_POST['id'] is expected to be an integer? If so, it should be cast as one, and left unquoted in the query string.

 

None of the other variables ($model_name, $model_mfg, $wingspan, $engine and $decibels) in the query string will have values, because they've not been assigned.

 

All form data, even from hidden fields, select boxes, radio buttons, etc. is subject to user manipulation, and should be validated and sanitized before being allowed into a query string.

[crmamx]

You never execute the UPDATE query with mysql_query(); it's just a string in the middle of nowhere.

Just didn't know how. Tried everything I could find in the tutorials.

I suspect the SELECT query isn't really necessary; you can simply execute the UPDATE query, then if the query returns an error, or mysql_affected_rows() == 0, no record was updated.

Of course you are right, I did this so I could visually see when it did or if it didn't. Will change the final code.

I assume that $_POST['id'] is expected to be an integer? If so, it should be cast as one, and left unquoted in the query string.

Yep!

None of the other variables ($model_name, $model_mfg, $wingspan, $engine and $decibels) in the query string will have values, because they've not been assigned.

I did that just to reduce the clutter while I tested. Let me ask, as is will those values just be null and write nothing?

 

All form data, even from hidden fields, select boxes, radio buttons, etc. is subject to user manipulation, and should be validated and sanitized before being allowed into a query string.

Shouldn't those values be validated in the preceeding form program?

 

By the way, many thanks....it's working. Now let me see if I can finish it and capture, validate and update everything changed in the form.

[Pikachu2000]

All form data, even from hidden fields, select boxes, radio buttons, etc. is subject to user manipulation, and should be validated and sanitized before being allowed into a query string.

Shouldn't those values be validated in the preceeding form program?

 

 

Anything that arrives via the $_POST/$_GET/$_REQUEST/$_COOKIE arrays should be validated and sanitized, since the user has the opportunity to jack with the data.

 

[crmamx]

I got the update to the db working.

 

Now I am trying to get the form input that feeds the update program to work.

 

This code works in two other programs but it doesn't work here. The only difference is in the other two programs I am outputing a table rather than a form. It displays the ID in the form but doesn't display the AMA.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Change data</title>
</head>

<?php

// Connect to db  =============================================================

include("connect_db.php");

// Retrieve form data ========================================================

$id = $_POST['id'];

// Send query ===========================================================

$result = mysql_query("SELECT * FROM airplanes
WHERE id='$id'") or die(mysql_error());  

if (!$result) {
    die("Query to show fields from table failed");
}

?>

<h3> Change an airplane</h3>
<form action="update_db_airplane.php" method="post">
          
 
    
ID #: <input name="id" type="text" size="3" maxlength="3" value="<?php echo $id; ?>"><br>
AMA #:<input name="ama" size="6" maxlength="6" type="text" value="<?php echo $ama; ?>"><br>
        
Model Name: <input name="model_name" size="6" maxlength="6" type="text"><br>
           
Model Mfg: <input name="model_mfg" size="30" maxlength="30" type="text"><br>
        
    
Wingspan: <input name="wingspan" size="3" maxlength="3" type="text">
    In inches<br>
Engine Mfg & Size: <input name="engine" size="30" maxlength="30" type="text"><br>
     
Sound Reading: <input name="decibels" size="3" maxlength="3" type="text">
  
Leave blank if you don't know it.<br>
       
       
       
       
       
       
       
       
     <input value="Change" type="submit"></form>
</body>
</html>

 

While I am at it what is a better way to align the boxes in the form?

 

Thanks

[Pikachu2000]

$id should echo into the field, but $ama isn't set. You aren't doing anything with the result resource from the query to get the values from it. You need to use a fetch function like mysql_fetch_assoc to access them.

 

$query = "SELECT `field` FROM `table` WHERE `id` = 1";
if( $result = mysql_query($query) ) {
     $array = mysql_fetch_assoc($result);
} else {
     // query errored out, handle the error
}

[crmamx]

Thanks Pikachu, you are most helpful as always.

 

I will use your code which is more correct, but just for the record, this statement from the other two programs that work (and output to a table) I left out of this program. Damn!!!

while($row = mysql_fetch_array( $result )) {

// and then of course to table

echo "<tr><td>";
echo $row['id'];

[Pikachu2000]

The can't possibly work (at least not in the way you may think they do) without getting the values from the result resource in one way or another . . .

[crmamx]

I never could get the thing to work completely. So I found something else and tried it and so far it appears to work ok. Never used FETCH before. I like it.

 

<html>
<head>
</head>
<body>
<?php
// Connect to database=====================================================

include("connect_db.php"); 

// retrieve form data ======================================================

$id = $_POST['id']; 

// sending query ===========================================================

$fetch = mysql_fetch_array(mysql_query("SELECT ama,model_name,model_mfg,wingspan,engine,decibels FROM airplanes
WHERE id='$id'"));

// Output form with retrieved data ==========================================
?>
<form name="myForm" action="delete_airplanes.php" onsubmit="return validateForm()" method="post">
AMA #:<input type="text" name="ama" value="<?=$fetch[ama]?>" /><br>
Model Name:<input type="text" name="model_name" value="<?=$fetch[model_name]?>" /><br>
Model Mfg:<input type="text" name="model_mfg" value="<?=$fetch[model_mfg]?>" /><br>
Wingspan:<input type="text" name="wingspan" value="<?=$fetch[wingspan]?>" /><br>
Engine:<input type="text" name="engine" value="<?=$fetch[engine]?>" /><br>
Decibels:<input type="text" name="decibels" value="<?=$fetch[decibels]?>" /><br>
<input type="Submit">
</form>

<br>
<body>
</html>

 

Any comments or advice on this?

 

Now I am just praying that my update_db.php that you fixed for me will pick up the data and update the db.

[Pikachu2000]

Nesting everything like that is going to cause you headaches in the long run. It'll usually work, but it makes debugging difficult at best. the better method is to keep the logic separated so you can handle errors and control what happens if they occur.

 

$query = "SELECT ama,model_name,model_mfg,wingspan,engine,decibels FROM airplanes WHERE id='$id'";
if( !$result = mysql_query($query) ) {
         echo "<br>Query $query<br>Failed with error: " . mysql_error() . '<br>';
} else {
         $fetch = mysql_fetch_array( $result );
}

[crmamx]

With only about 30 days exposure to php, I knew there was a better way.

 

Hallelujah! I'm off and running.

 

Thanks again.