Delete one column from table, Update another with PHP MYSQL

Question

I have two tables. One table is the matches table (e2wedstrijden) and another table is my scoring table with the points earned etc. (e2teams).

Now I have that I can delete a match from the e2wedstrijden table. And this is working fine.

But I want that if I delete a match from that table. It also add or decrease points to the table ("e2teams"). I tried to compare the tables but this is not working.

So I want for example:

If($row['thuisscore'] == $row['uitscore']) what are to row names in my e2wedstrijden table. So if these two are the same (like 0-0 or 1-1 or something) Than it needs to decrease 1 point from the table e2teams. But only by the teams that are the same as the rows "Thuisteam" and "Uitteam" in my e2wedstrijden table. So the Row Thuisteam (in "e2wedstrijden") Needs to find the same result in ("e2teams") row Team. And this needs to be done the same with the Row Uitteam (in "e2wedstrijden") Needs to find the same result in ("e2teams")

Thuisteam and Uitteam = Dutch for hometeam and awayteam. I think my fault is that the system can't link the 'Thuisteam' from e2wedstrijden to the Team in e2teams but don't know how to solve it

This is my deletematches.php, It deletes the match but doesn't decrease or adds points:

    <?php
if(!isset($_COOKIE['E2ingelogd'])) {
header("location:../../index.php");
}
include "../../connect.php";
$dbhandle = mysql_connect($hostname, $username, $password) or die("Could not connect to database");
$selected = mysql_select_db("login", $dbhandle);
$result = mysql_query("SELECT * FROM e2wedstrijden WHERE ID = ".$_GET['del']."");

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

if( $row['thuisscore'] == $row['uitscore']){
      echo $row['thuisscore'];
      mysql_query("UPDATE e2teams SET Punten = Punten-1 WHERE Team ='".$row['Thuisteam']."'");
      mysql_query("UPDATE e2teams SET Gespeeld = Gespeeld-1 WHERE Team = ('".$row['Thuisteam']."'");
      mysql_query("UPDATE e2teams SET Verloren = Gelijk-1 WHERE Team ='".$row['Uitteam']."'");
      echo "Team is deleted";
    }else{
       echo 'Update Error!';
    }
}


$table_1_delete = mysql_query("DELETE FROM e2wedstrijden WHERE ID = ".$_GET['del']."");


    ?>

This is my e2teams table: E2teamstable

And this is my E2wedstrijden table: E2wedstrijdentable

So i need something like:

UPDATE e2teams SET Punten = Punten-1 WHERE Team = Look in table ("e2wedstrijden) deleted Thuisteam and deleted Uitteam

Hope you can help

Funk Forty Niner · Accepted Answer · 2014-03-18 14:08:41Z

3

You've placed an extra parentheses in the 2nd query for "gespeeld" right after the equal sign:

mysql_query("UPDATE e2teams SET Gespeeld = Gespeeld-1  
WHERE Team = ('".$row['Thuisteam']."'");

Is this what isn't updating?

edited Mar 18, 2014 at 14:08

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Mar 18, 2014 at 13:37

gtr1971

2,7382 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gtr1971 Over a year ago

Sometimes it takes an extra pair of eyes :-)

Community · Accepted Answer · 2020-06-20 09:12:55Z

Without being 100% sure on how your data model works, it might make sense at refactoring what you have. Something that might be useful would be to create a view of the summary table and just update the data from the child/master table.... aggregating in the view layer. Views in mysql can be seen here.

If you are stuck with the data model you have (legacy application, etc.) you can possibly look at triggers if you have to modify data in two tables you might want to consider stored procedures or triggers, discussed here and here.

The third thing that comes to mind, is around correlated sub-queries and how you could reference the another table in a sort of update-from. However, you're ID's aren't surrogate keys in this situation.

Also, have a look at sql injection; I haven't looked at PHP in a while but those sql statements kind of look like they are created with sting composition

Good luck,

Collectives™ on Stack Overflow

Delete one column from table, Update another with PHP MYSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related