I want to update, not overwrite my database!

[info261]

Morning all,

In Dreamweaver CS4 - I've managed to insert data from my Repeating Region using Dynamic Checkboxes and Text Fields into my MySql database (thanks again to www.DwFAQ.info) , but when I go to insert another/new record, the checkboxes are already selected and the text from the previous entry is showing in the text fields.

Obviously the dynamic fields are reading the data that is already in the database, but how do I send the data from the form to the database, then clear the form for the next use?

I want to update my database with football player stats, and display the running total of games played, goals scored etc on another page, so I need to update the database each time I input data, not overwrite the previous entry.

Should I be looking at the Dynamic input from Dreamweaver, or the MySql data/functions side of things?

Any help or general pointers would be much appreciated!

Many Thanks

Andy

[Guest]

hello,

I think this will do the job

first you need to know how many goals player has in database, then you add to existent the new posted value and update your database.

I'm no expert, so this may be wrong, ok?

$sql = "SELECT ID, goals_scored FROM database_table WHERE player_name LIKE '".$_get['player']."'";

$result=mysql_query($sql) or die("Error!: " . mysql_error()." : ".$sql);
if (mysql_num_rows($result) == 1) {
        while($row = mysql_fetch_assoc($result)) {

               $n_goals = $row['goals_scored'];

               $new_score = $n_goals + $_get['goals'];

               $sql = "UPDATE database_table SET goals_scored = '".$new_score."' WHERE player_id = '$player'";

          }

}

[info261]

Thanks for this, I'll try and look into it later.

Do you know if this will stop the goals total from the previous entry showing up in the 'Enter Goals' field in the new form? The problem I'm having is getting the form to show up empty when I go in to add/update the new details

Cheers

Andy

[bregent]

If you are inserting new rows, then use the insert form behavior. If you are updating exising rows, use the update behavior. If you are doing a combination, then you must hand code this.

[info261]

I don't need to insert any more rows, all the rows and columns etc for the database are set up fine. The column with the player names will stay as it is, but I need to update the games played/red card/yellow card etc with checkboxes and the goals scored with text fields.

I can insert data from dreamweaver into the mysql database ok, but when I go into the form again to update it (ie add another match played, another 4 goals scored), then the data that is already in the database is there, so some of the checkboxes are already ticked, and the previous goals scored are already in the text entry field.

How do I 'clear' the form for the next use, and have dreamweaver/mysql update the database? I need to add 2 goals to the previous 3 that were scored in the match before, not replace the 2 goals with 3

Is this to do my the mysql functions etc, or dreamweaver? Can anyone help please?!

[bregent]

>I can insert data from dreamweaver into the mysql database ok, but when I go into the form again to update it

>(ie add another match played, another 4 goals scored), then the data that is already in the database is there,

>so some of the checkboxes are already ticked, and the previous goals scored are already in the text entry field

When you use an update behavior, then the fields should be populated with the row that is selected based on the key field. If you are adding another match played, shouldn't you be using an insert behavior? In that case, the fields should not be populated.

>I need to add 2 goals to the previous 3 that were scored in the match before, not replace the 2 goals with 3

If you are adding details for a new match, shouldn't you be inserting a new row? To be of any real help, we really need to see your database design. Can you give us a list of tables, fields and relations? We can then help sort this out.

[info261]

At the moment, I'm just working on the 'Player' table, I haven't even started on the 'Match' one yet! I just want to be able to add/increment the player details.

The player table has just got Player Name (which I'm displaying in a Repeat Region from the DB) then Checkboxes for if they've played, come on as a sub or didn't show up, as well as checkboxes for Red & Yellow cards. The 'goals scored' entry is a dynamic text field.

Am I missing something basic? Do I need to send the data from the input form somewhere else, which will then update the database, and then the form will be clear again when I go into it, or is it to do with the Update/Insert functions in Mysql

Maybe I've just come at it the wrong way?!

[bregent]

I am still not clear on the total design and workflow.

I would envision a design like this with 3 tables

Player

Match

Player_Match

The Player table stores details about the player not related to any specific match

The Match table stores details about the match not related to a specific player

The Player_Match stores details about a player for a specific match. This would include foreign keys to the other two tables and include attributes like cards, goals_scored, assists, time_played, no_show, etc.

Is this your concept? Are you updating any stats during the game or after the game is complete. If the latter, then you won't really be updating those rows.  If the former, the workflow would be:

Before each game: Insert a new Match row for a new Match. Insert new Player_Match rows for each player as they enter the game using insert behavior. Use the update behavior to update the Player_Match row when there is a goal, card, etc.

>Do I need to send the data from the input form somewhere else,

>which will then update the database, and then the form will be clear

>again when I go into it, or is it to do with the Update/Insert functions in Mysql

Again, if you are using the DW Insert behavior, all of the fields should be cleared. If using the update behavior, they should be populated. We may still not be understanding each other.

[bregent]

And so we are clear on this, are you actually using Dreamweavers server behaviors for insert and update, or coding by hand?

[info261]

Hi Bregent,

Yeah, I'm using Dreamweaver for all the insert/update features. Coding's not my stong point really, but then again neither is Dreamweaver at the moment!

Thanks for all your help, I think I was just approaching the setting up of the databases the wrong way. I'll let you know how I get on

Andy