CODEWITHSUNDEEP
phpmysql
Bha
Bha

Reputation: 11

how to update the mysql database in php

$query="update attendance 
SET 
    `day1`='$day1', `day2`='$day2',  `day3`='$day3', `day4`='$day4', 
    `day5`='$day5', `day6`='$day6', `day7`='$day7', `day8`='$day8', 
    `day9`='$day9', `day10`='$day10', `day11`='$day11', `day12`='$day12', 
    `day13`='$day13', `day14`='$day14', `day15`='$day15', `day16`='$day16', 
    `day17`='$day17', `day18`='$day18', `day19`='$day19', `day20`='$day20', 
    `day21`='$day21', `day22`='$day22', `day23`='$day23', `day24`='$day24', 
    `day25`='$day25', `day26`='$day26', `day27`='$day27', `day28`='$day28', 
    `day29`='$day29', `day30`='$day30', `day31`='$day31', `accident`='$a1', 
    `disiplinary`='$d1', `family_death`='$fd1', `family_illness`='$fi1', 
    `holiday`='$h1', `illness`='$i1', `jury_duty`='$j1', `leave`='$l1', 
    `layoff`='$lo1', `personal`='$p1', `tardy`='$t1', `vacation`='$v1' 
WHERE `month`='January'";

$result=mysql_query($query);

Here the day1 value updated (value as 1), next to update the value day2 is 1, in that time the day2 value is updated(1), but day1 value changed to zero.

What i do?

Upvotes: 1

Views: 130

Answers (1)

MvG
MvG

Reputation: 60868

The way you phrased your query, all the variables like $day1 have to be set inside PHP. If they are not, then an empty string gets pasted into the request, which in turn gets converted to zeros by the MySQL server.

So if you only want to modify day2, then do just that:

UPDATE attendance SET day2 = ? WHERE month = 'January'

That said, there are a number of ways how you could improve your current approach.

  • You should not use mysql_query as it is deprecated.
  • You should not directly include variables into the query, as this opens the door to both bugs and SQL injection vulnerabilities. Use placeholders like ? instead, and provide the actual values when executing this query.
  • Having one column for every day looks like a pretty bad database schema. Instead have a single table with one row for each day. You can compute monthly overviews from that for your output, but in general such a request will be far easier to handle on the DB level. If you want some columns per month, you could have those in an extra table.
  • You probably should use the DATE type to describe each day, along the lines just described. If you really have to have a table with one row per month, then you might still use a DATE to describe it, using the first day of the month to identify the whole month. This will allow you to easily use date and time functions on that column. If you want separate year and month columns, I guess I'd still identify the month by number, not name.

Upvotes: 2

Related Questions