0

I have an issue with structuring my data correctly. I have over 6000 rows in it, but because of some error it is overwriting my existing rows! I think I have many values in the columns which are same, that is why he is overwriting my existing rows.

Datatable structure

Datatable info

My columns:

  • bid - primary key, which is auto increment
  • btyp - can have values 1,2 or 3 only
  • mid - can have values 1-300 only

  • monat - month - 1-12 only

  • jahr - year - 2016 upwards only

  • language - has values "de" or "ur" only

  • entry_date - date

  • entry_type - has values 1 or 2 only

  • entry_uid - 10-15 different values

  • upload_date - date

  • upload_uid - 10-15 different values

  • zusatz - some text

The columns mid,monat,jahr & btyp should be unique together in the datatable. So if I want to save a new record, I have to first check whether I have already that row in my database. If not, it should be inserted. If yes, updated. I am trying to do this:

    $checker2="SELECT * FROM berichte WHERE mid='".$_POST['mid']."' AND monat='".$_POST['monat']."' AND jahr='".$_POST['jahr']."' AND btyp='".$btyp."'";

    $checkresult2 = mysqli_query( $mysqli, $checker2 );

    if (mysqli_num_rows($checkresult2)>0) {
        $sql3 = "UPDATE berichte SET (entry_date = '" . $jetzt . "') WHERE mid='".$_POST['mid']."' AND monat='".$_POST['monat']."' AND jahr='".$_POST['jahr']."' AND btyp='".$btyp."'";
    } else {
        $sql3 = "INSERT INTO berichte(btyp, mid, monat, jahr, language, entry_date, entry_type, entry_uid, upload_date, upload_uid, zusatz) 
                VALUES (".$btyp.",'".$_POST['mid']."','".$_POST['monat']."','".$_POST['jahr']."','de','" . $jetzt . "','1','1','" . $jetzt . "','1','Online Eintragung von Majlis')";
    }
    mysqli_query( $mysqli, $sql3 );
}

But as I mentioned, there seems to be an issue I am not getting. Some rows are overwritten. I hope anybody can help here.

farahzeb
  • 1
  • 1
  • How many rows does `mysqli_num_rows` return? If your table already has duplicate rows then it will update all of them. You need to clean up your data first. Ideally, you could use a composite key to ensure those columns are unique. – waterloomatt Nov 06 '18 at 16:03
  • Thanks. I just cleaned up my table, which contained 17 duplicate rows. So I should just drop the primary key "bid"? (Do I need it still to auto-increment?) And instead add primary keys (mid, monat, jahr and btyp) ? Do I have to change my php code therefore? – farahzeb Nov 06 '18 at 16:23
  • There are a few ways to handle this. You could keep the auto-increment `primary key` and simply add a `unique index` on those columns - https://stackoverflow.com/q/635937/296555. Or you could use those columns as your primary key - called a `composite primary key`. Also, look into prepared statements; you're fully exposed to SQL injection. – waterloomatt Nov 06 '18 at 18:26
  • Okay. I will implement this and also prepared statements. I will let you know if the issue is solved. I was just wondering what the cardinality number 5215 of my primary key means: https://i.stack.imgur.com/pt25i.png I was thinking that maybe this is making the issue... – farahzeb Nov 07 '18 at 09:10

0 Answers0