I wrote a php script to edit a database that, to the best of my knowledge, worked just fine.  Now, it's not working.  What it's doing now is replacing EVERY row with the data for the LAST row.

So, for example, the database had this info before the edit script ran:

Row 1 - "this is row 1"
Row 2 - "this is row 2"
Row 3 - "this is row 3"


Now, it has this:

Row 1 - "this is row 3"
Row 2 - "this is row 3"
Row 3 - "this is row 3"

Like I said, to my knowledge, this script did work before.  Could have an update to the server (either PHP or MySQL) caused me some problem with this code?

Code:

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); @mysql_select_db("$DBName") or die("Unable to select database $DBName"); $usql = "UPDATE weather SET event='$_POST[event]', hrs='$_POST[hrs]', closed='$_POST[closed]', notes='$_POST[notes]' "; // compiles query $dosql = mysql_query($usql); // executes query  if ($dosql){    echo "<p>Thank You, your entry has been submitted!</p>        <meta http-equiv=Refresh content=1;url='http://www.kkbjam.com/weather/'>";  }  else{    echo mysql_errno().": ".mysql_error()."<BR>";  }  mysql_close (); ?>

The current versions on the server are:

PHP 4.3.10-16
MySQL 4.0.24

I don't know what versions they were when this script worked (or I thought it worked!)


An UPDATE command works on every row unless you add a WHERE clause, it which case it works on every matching row.

In the sample script you posted, there's no WHERE clause so every row will be changed. I would guess when you tested it that you had a single row in the table, so that every row was the same thing as the row you wanted - thus it appeared to work in the special case you happened to have.

It's not obvious to me which variable in your script and which column you want to use to select the row to be updated, so I can't give you a complete fix - but you'll want to add something that resolves to a piece of text such as
WHERE wid = 4
onto the end of your query.

P.S. The DELETE command works the same way - don't tell it which rows you want, and it deletes them all!

OK, then how would you design the layout of this?  What I'm doing now is echoing every row into an html table and displaying the contents for the person to see what is already entered, and then change what needs to be changed.  They then press the submit button, and the edit script is called in the form action.

I don't see where I can use a WHERE statement in this setup.  How should I design this application then?

Thanks Graham.

Brian

Hi Brian

Could you not echo into the html table a hidden field say the unique row number, so when it came to rewrite to the database you could use the unique row number as the where row = 'unique_row_number'?

Kind regards

Chris

PS. Please note I'm rusty (thats an understatement  ) so may be worth waiting for Graham's response  

I've been offline for 48 hours - ooops - will be back and answer in another 8 hours or so.   Basically, brian, you have to design your application so that - somehow - it can identify the resord you need to update.  Not so much a PHP or MySQL question - more a general design issue.   As I say, more to follow ...

thanks for the replys Chris and Graham.  I will await further advise.

Brian

Brian, it comes very much down to the design of your application - how do you tell which particular row of your table it is that you want to update?  Once you can answer that question, you'll need to to include the answer (written in the form of a where clause) in your update statement.

I was hoping to write a longer answer / piece on design to help you see your way through this, but I'm really running out of time (you don't want to know all that's been going on in the background - or if you do you can read the blog!).    Potentially, you may even have to add an extra column to your database that will help you tell the various rows apart.

I will have an SQL server in my hotel tonight and I'll try to find a chance to put up - at the least - a demonstration of updates.


I've added a field in the database called "row_number" that is auto incremented.  

To the edit script, I've added this code:

Code:

$DBhost = "localhost"; $DBuser = "user"; $DBpass = "password"; $DBName = "weather"; mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); @mysql_select_db("$DBName") or die("Unable to select database $DBName"); $usql = "UPDATE weather SET event='$_POST[event]', hrs='$_POST[hrs]', closed='$_POST[closed]', notes='$_POST[notes]' WHERE $_POST[row_number] = row_number"; // compiles query $dosql = mysql_query($usql); // executes query  if ($dosql){    echo "<p>Thank You, your entry has been submitted!</p>

...and nothing is being updated.  If I echo the row_number in the html form, it gives me the correct number from the database.  Any ideas?

Thanks.

If the row number is completed on the form, that looks correct. You are CERTAINLY headed in the right direction.

Have you tried echoing $usql onto the page to see what's actually in the query?  That would probably give you a very good clue.

Alright, I echoed $usql and it is only updating the last record in the database.  

There are currently 2 records:

row_number - 1
today - 2006-02-28
event - school 1
hrs - 2 Hours Late
closed - ""
notes - school 1 notes


row_number - 2
today - 2006-02-28
event - school 2
hrs - 2 Hours Late
closed - ""
notes - school 2 notes



The edit.php page echos both records into an html table.  The row_number is a hidden field.  If I try to edit record 1, and hit submit (calling edit_db.php) nothing happens to record 1...only record 2 is being changed, and since there weren't any changes to record 2, nothing changes.  If I change record 2, the script runs perfectly.  Can one not update several records at once in this manner?



The original problem, as I understood it, was that all records were being updates ... problem as you didn't want every record updated bust just specific ones.   That's fine - add a where clause.

Problem is that the where clause you have chosen is such that it only matches the last record, so that's the one being updated ...

You need to look back at the logic and say to yourself "how do I identify the particular records that need updting and only those records".   The answer to THAT question, coded into SQL, will be the Where clause you need.

Graham,

I'm sick today...and have NO logic!  I'll be back in a day or two when I feel better.  Thanks for the help.

got it...

Code:

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); @mysql_select_db("$DBName") or die("Unable to select database $DBName"); $event=$_POST[event]; $hrs=$_POST[hrs]; $closed=$_POST[closed]; $notes=$_POST[notes]; $row_number=$_POST[row_number]; for ($i=0;$i<count($_POST[event]);$i++){ $usql = "UPDATE weather SET event='$event[$i]', hrs='$hrs[$i]', closed='$closed[$i]', notes='$notes[$i]' WHERE row_number='$row_number[$i]'"; // compiles query $dosql = mysql_query($usql); // executes query } if ($dosql){ echo "<p>Thank You, your entry has been submitted!</p>      <meta http-equiv=Refresh content=1;url='http://www.kkbjam.com/weather/edit.php'>"; } else{ echo mysql_errno().": ".mysql_error()."<BR>"; } mysql_close (); ?>

thanks, Graham

---