I always get problem or error like this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near `'kunci=1 WHERE date='2013-09-01' AND user_id='2'' at line 1
Here is my php coding:
if(isset($_POST['mc'])) {
$mcdate = $_POST['mcdate'];
$mcstaff = $_POST['mcstaff'];
$sql = mysql_query("
SELECT *
FROM timetable
WHERE
date ='" . $mcdate . "'
AND shift='O'
AND user_id='" . $mcstaff . "'");
$counto = mysql_num_rows($sql);
echo $counto;
if ($counto == 0) {
$sql1 = "
UPDATE timetable
SET
shift='A'
kunci=1
WHERE
date='" . $mcdate . "'
AND user_id='" . $mcstaff . "'";
mysql_query($sql1) or die(mysql_error());
$sql2 = mysql_query("
SELECT *
FROM timetable
WHERE date ='" . $mcdate . "'
AND shift='M'");
$countm=mysql_num_rows($sql2);
//echo $countm;
$sql3 = mysql_query("
SELECT *
FROM timetable
WHERE
date ='" . $mcdate . "'
AND shift='E'");
$counte=mysql_num_rows($sql3);
//echo $counte;
//balance shift
if($counte==$countm) {
$rand=rand(0, 2);
if($rand==0) {
$shift = 'M';
} else {
$shift = 'E';
}
} else if ($countm>$counte) {
$shift = 'E';
} else if ($counte>$countm) {
$shift = 'M';
}
} else if ($counto == 1) {
echo "Staff already Off";
}
}
I also have try enclose database, table and column names in backticks to prevent such conflicts but it still come out with this error.
I also realized that in my MySQL table for timetable got collation for 'shift' structure. I don't know if it can effect.
My table = timetable:
name: shift, type:varchar(10), collation:latin1_swedish_ci.
Can somebody help me? Thanks you so much.
$sql1=("UPDATE timetable SET shift='A' kunci=1 WHERE date='".$mcdate."' AND user_id='".$mcstaff."'");
should be
$sql1=("UPDATE timetable SET shift='A', kunci=1 WHERE date='".$mcdate."' AND user_id='".$mcstaff."'");
^------ missing comma