ถามเรื่องการเพิ่ม ลบ แก้ไข ข้อมูลหน่อยค่ะ...You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
เราเอาโค้ดจากเว็บนี้ ไปปรับใช้กับของเรา phpMySQLAddEditDeleteRecord.php
แต่พอรันผลแล้ว ข้อมูลไม่สามารถอัพเดทได้ มันขึ้นว่า
Error Save [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 ''13-14',,'มกราคม',,'',,'ไทยแลนด์','แอลเอ' ,'นิด','021234567','02123456','123@hot' at line 1]
ซึ่งเราก็ปรับคุณสมบัติในตารางเป็นตัวอักษรหมดแล้ว เลยไม่รู้ว่าผิดตรงไหนนะค่ะ รบกวนผู้รู้ช่วยดูให้หน่อยนะค่ะ
มีฐานข้อมูลชื่อ exhibition
มีตารางชื่อ subject
โครงสร้างเป็นดังนี้
CREATE TABLE `subject` (
`subject_id` int(4) NOT NULL auto_increment,
`subject_country` varchar(27) default NULL,
`subject_date` int(27) default NULL,
`subject_month` varchar(27) default NULL,
`subject_year` varchar(27) NOT NULL,
`subject_name` varchar(27) character set utf8 collate utf8_unicode_ci NOT NULL,
`subject_place` varchar(27) character set utf8 collate utf8_unicode_ci NOT NULL,
`subject_contact` varchar(45) character set utf8 collate utf8_unicode_ci NOT NULL,
`subject_tel` int(15) NOT NULL,
`subject_fax` int(15) NOT NULL,
`subject_mail` varchar(27) character set utf8 collate utf8_unicode_ci NOT NULL,
`subject_web` varchar(27) character set utf8 collate utf8_unicode_ci NOT NULL,
`subject_note` varchar(120) character set utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
นี้คือตัวโค้ดที่เอาไปปรับค่ะ
<html>
<head>
<title>งานแสดงสินค้าและนิทรรศการ</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">
</head>
<body>
<p align="center"><img src="images/globe.gif" width="16" height="16"> <a href="admin_logout.php">log off</a></p>
<p align="center" class="style1">งานแสดงสินค้าและนิทรรศการ</p>
<p>
<?
$objConnect = mysql_connect("localhost","root","123456") or die("Error Connect to Database");
$objDB = mysql_select_db("exhibition");
//*** Add Condition ***//
if($_POST["hdnCmd"] == "Add")
{
$strSQL = "INSERT INTO subject ";
$strSQL .="(subject_id,subject_country,subject_date,subject_month,subject_year,subject_name,subject_place,subject_contact,subject_tel,subject_fax,subject_mail,subject_web,subject_note) ";
$strSQL .="VALUES ";
$strSQL .="('".$_POST["txtAddsubject_id"]."','".$_POST["txtAddsubject_country"]."',,'".$_POST["txtAddsubject_date"]."',,'".$_POST["txtAddsubject_month"]."',,'".$_POST["txtAddsubject_year"]."',,'".$_POST["txtAddsubject_name"]."','".$_POST["txtAddsubject_place"]."' ";
$strSQL .=",'".$_POST["txtAddsubject_contact"]."','".$_POST["txtAddsubject_tel"]."','".$_POST["txtAddsubject_fax"]."','".$_POST["txtAddsubject_mail"]."','".$_POST["txtAddsubject_web"]."','".$_POST["txtAddsubject_note"]."') ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Save [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
//*** Update Condition ***//
if($_POST["hdnCmd"] == "Update")
{
$strSQL = "UPDATE subject SET ";
$strSQL .="subject_id = '".$_POST["txtEditsubject_id"]."' ";
$strSQL .=",subject_country = '".$_POST["txtEditsubject_country"]."' ";
$strSQL .=",subject_date = '".$_POST["txtEditsubject_date"]."' ";
$strSQL .=",subject_month = '".$_POST["txtEditsubject_month"]."' ";
$strSQL .=",subject_year = '".$_POST["txtEditsubject_year"]."' ";
$strSQL .=",subject_name = '".$_POST["txtEditsubject_name"]."' ";
$strSQL .=",subject_place = '".$_POST["txtEditsubject_place"]."' ";
$strSQL .=",subject_contact = '".$_POST["txtEditsubject_contact"]."' ";
$strSQL .=",subject_tel = '".$_POST["txtEditsubject_tel"]."' ";
$strSQL .=",subject_fax = '".$_POST["txtEditsubject_fax"]."' ";
$strSQL .=",subject_mail = '".$_POST["txtEditsubject_mail"]."' ";
$strSQL .=",subject_web = '".$_POST["txtEditsubject_web"]."' ";
$strSQL .=",subject_note = '".$_POST["txtEditsubject_note"]."' ";
$strSQL .="WHERE subject_id = '".$_POST["hdnEditsubject_id"]."' ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Update [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
//*** Delete Condition ***//
if($_GET["Action"] == "Del")
{
$strSQL = "DELETE FROM subject ";
$strSQL .="WHERE subject_id = '".$_GET["subid"]."' ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Delete [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
$strSQL = "SELECT * FROM subject";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
?>
</p>
<form name="frmMain" method="post" action="<?=$_SERVER["PHP_SELF"];?>">
<input type="hidden" name="hdnCmd" value="">
<table width="100%" border="1">
<tr>
<th width="91"> <div align="center">รหัส subject_id </div></th>
<th width="48"> <div align="center">ประเทศ subject_country</div></th>
<th width="48">วันที่ subject_date</th>
<th width="98">เดือน subject_month</th>
<th width="98">ปี พ.ศ. subject_year</th>
<th width="98">ชื่องาน subject_name</th>
<th width="98">สถานที่ subject_place</th>
<th width="98">ติดต่อ subject_contact</th>
<th width="48">โทรศัพท์ subject_tel</th>
<th width="48"><p>โทรสาร subject_fax</p></th>
<th width="198"> <div align="center">E-mail subject_mailEmail </div></th>
<th width="97"> <div align="center">Website subject_web</div></th>
<th width="59"> <div align="center">หมายเหตุ subject_note</div></th>
<th width="30"> <div align="center">Edit </div></th>
<th width="30"> <div align="center">Delete </div></th>
</tr>
<?
while($objResult = mysql_fetch_array($objQuery))
{
?>
<?
if($objResult["subject_id"] == $_GET["subid"] and $_GET["Action"] == "Edit")
{
?>
<tr>
<td>
$strSQL .=",subject_web = '".$_POST["txtEditsubject_web"]."' ";
$strSQL .=",subject_note = '".$_POST["txtEditsubject_note"]."' ";
<input name="txtEditsubject_id" type="text" value="<?=$objResult["subject_id"];?>" size="5" maxlength="5">
<input type="hidden" name="hdnEditsubject_id" size="5" value="<?=$objResult["subject_id"];?>">
</td>
<td><input type="text" name="txtEditsubject_country" size="20" value="<?=$objResult["txtEditsubject_country"];?>"></td>
<td><input type="text" name="txtEditsubject_date" size="20" value="<?=$objResult["txtEditsubject_date"];?>"></td>
<td><input type="text" name="txtEditsubject_month" size="20" value="<?=$objResult["txtEditsubject_month"];?>"></td>
<td><input type="text" name="txtEditsubject_year" size="20" value="<?=$objResult["txtEditsubject_year"];?>"></td>
<td><input type="text" name="txtEditsubject_name" size="20" value="<?=$objResult["txtEditsubject_name"];?>"></td>
<td><input type="text" name="txtEditsubject_place" size="20" value="<?=$objResult["txtEditsubject_place"];?>"></td>
<td><input type="text" name="txtEditsubject_contact" size="20" value="<?=$objResult["txtEditsubject_contact"];?>"></td>
<td><input type="text" name="txtEditsubject_tel" size="20" value="<?=$objResult["txtEditsubject_tel"];?>"></td>
<td><input type="text" name="txtEditsubject_fax" size="20" value="<?=$objResult["txtEditsubject_fax"];?>"></td>
<td><input type="text" name="txtEditsubject_mail" size="20" value="<?=$objResult["txtEditsubject_mail"];?>"></td>
<td><input type="text" name="txtEditsubject_web" size="20" value="<?=$objResult["txtEditsubject_web"];?>"></td>
<td><input type="text" name="txtEditsubject_note" size="20" value="<?=$objResult["txtEditsubject_note"];?>"></td>
<td>
<input name="btnAdd" type="button" id="btnUpdate" value="Update" OnClick="frmMain.hdnCmd.value='Update';frmMain.submit();">
<input name="btnAdd" type="button" id="btnCancel" value="Cancel" OnClick="window.location='<?=$_SERVER["PHP_SELF"];?>';">
</td>
</tr>
<?
}
else
{
?>
<tr>
<td><?=$objResult["subject_id"];?></td>
<td><?=$objResult["subject_country"];?></td>
<td><?=$objResult["subject_date"];?></td>
<td><?=$objResult["subject_month"];?></td>
<td><?=$objResult["subject_year"];?></td>
<td><?=$objResult["subject_name"];?></td>
<td><?=$objResult["subject_place"];?></td>
<td><?=$objResult["subject_contact"];?></td>
<td><?=$objResult["subject_tel"];?></td>
<td><?=$objResult["subject_fax"];?></td>
<td><?=$objResult["subject_mail"];?></td>
<td><?=$objResult["subject_web"];?></td>
<td><?=$objResult["subject_note"];?></td>
<td><a href="<?=$_SERVER["PHP_SELF"];?>?Action=Edit&subid=<?=$objResult["subject_id "];?>">Edit</a></td>
<td><a href="JavaScript:if(confirm('Confirm Delete?')==true){window.location='<?=$_SERVER["PHP_SELF"];?>?Action=Del&subid=<?=$objResult["subject_id "];?>';}">Delete</a></td>
</tr>
<?
}
?>
<?
}
?>
<tr>
<td><input type="text" name="txtAddsubject_id " size="5"></td>
<td><input type="text" name="txtAddsubject_country" size="20"></td>
<td><input type="text" name="txtAddsubject_date" size="20"></td>
<td><input type="text" name="txtAddsubject_month" size="20"></td>
<td><input type="text" name="txtAddubject_year" size="20"></td>
<td><input type="text" name="txtAddsubject_name" size="20"></td>
<td><input type="text" name="txtAddsubject_place" size="20"></td>
<td><input type="text" name="txtAddsubject_contact" size="20"></td>
<td><input type="text" name="txtAddsubject_tel" size="20"></td>
<td><input type="text" name="txtAddsubject_fax" size="20"></td>
<td><input type="text" name="txtAddsubject_mail" size="20"></td>
<td><input type="text" name="txtAddsubject_web" size="20"></td>
<td><input type="text" name="txtAddsubject_note" size="20"> </td>
<td>
<input name="btnAdd" type="button" id="btnAdd" value="Add" OnClick="frmMain.hdnCmd.value='Add';frmMain.submit();">
</td>
</tr>
</table>
</form>
<?
mysql_close($objConnect);
?>
</body>
</html>Tag : - - - -
Date :
2010-01-18 17:54:32
By :
it53008
View :
1203
Reply :
4
ดู Syntax ของ SQL ให้ดีน่ะครับ
ปกติแล้วการ Update ควรใส่เครื่องหมาย 'string' ทุกครั้งครับ เพระาถ้าเกิดตัวแปรไม่มีค่าก็จะ error ในทันที
Date :
2010-01-18 18:15:39
By :
webmaster
ขอบคุณค่ะ ตอนนี้สามารถเพิ่มข้อมูลลงฐานข้อมูลได้แล้วค่ะ แต่ปัญหามันมีอยู่ที่ พอจะเข้าไปแก้ไขข้อมูลหรือว่าลบข้อมูล มันไม่ได้นะค่ะ พอเวลารันผล มันก็ไม่ขึ้นว่าผิดตรงไหนด้วยนะค่ะ เลยไม่รู้ว่าจะเข้าไปแก้ตรงจุดไหนนะค่ะ
++++++++++++++++++++++++++++++++++++++++++++++++++
<html>
<head>
<title>งานแสดงสินค้าและนิทรรศการ</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">
</head>
<body>
<p align="center"><img src="images/globe.gif" width="16" height="16"> <a href="admin_logout.php">log off</a></p>
<p align="center" class="style1">งานแสดงสินค้าและนิทรรศการ</p>
<p>
<?
$objConnect = mysql_connect("localhost","root","123456") or die("Error Connect to Database");
$objDB = mysql_select_db("exhibition");
//*** Add Condition ***//
if($_POST["hdnCmd"] == "Add")
{
$strSQL = "INSERT INTO subject ";
$strSQL .="(subject_id,subject_country,subject_date,subject_month,subject_year,subject_name,subject_place,subject_contact,subject_tel,subject_fax,subject_mail,subject_web,subject_note) ";
$strSQL .="VALUES ";
$strSQL .="('".$_POST["txtAddsubject_id"]."','".$_POST["txtAddsubject_country"]."','".$_POST["txtAddsubject_date"]."','".$_POST["txtAddsubject_month"]."','".$_POST["txtAddsubject_year"]."','".$_POST["txtAddsubject_name"]."','".$_POST["txtAddsubject_place"]."' ";
$strSQL .=",'".$_POST["txtAddsubject_contact"]."','".$_POST["txtAddsubject_tel"]."','".$_POST["txtAddsubject_fax"]."','".$_POST["txtAddsubject_mail"]."','".$_POST["txtAddsubject_web"]."','".$_POST["txtAddsubject_note"]."') ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Save [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
//*** Update Condition ***//
if($_POST["hdnCmd"] == "Update")
{
$strSQL = "UPDATE subject SET ";
$strSQL .="subject_id = '".$_POST["txtEditsubject_id"]."' ";
$strSQL .=",subject_country = '".$_POST["txtEditsubject_country"]."' ";
$strSQL .=",subject_date = '".$_POST["txtEditsubject_date"]."' ";
$strSQL .=",subject_month = '".$_POST["txtEditsubject_month"]."' ";
$strSQL .=",subject_year = '".$_POST["txtEditsubject_year"]."' ";
$strSQL .=",subject_name = '".$_POST["txtEditsubject_name"]."' ";
$strSQL .=",subject_place = '".$_POST["txtEditsubject_place"]."' ";
$strSQL .=",subject_contact = '".$_POST["txtEditsubject_contact"]."' ";
$strSQL .=",subject_tel = '".$_POST["txtEditsubject_tel"]."' ";
$strSQL .=",subject_fax = '".$_POST["txtEditsubject_fax"]."' ";
$strSQL .=",subject_mail = '".$_POST["txtEditsubject_mail"]."' ";
$strSQL .=",subject_web = '".$_POST["txtEditsubject_web"]."' ";
$strSQL .=",subject_note = '".$_POST["txtEditsubject_note"]."' ";
$strSQL .="WHERE subject_id = '".$_POST["hdnEditsubject_id"]."' ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Update [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
//*** Delete Condition ***//
if($_GET["Action"] == "Del")
{
$strSQL = "DELETE FROM subject ";
$strSQL .="WHERE subject_id = '".$_GET["subid"]."' ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
echo "Error Delete [".mysql_error()."]";
}
//header("location:$_SERVER[PHP_SELF]");
//exit();
}
$strSQL = "SELECT * FROM subject";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
?>
</p>
<form name="frmMain" method="post" action="<?=$_SERVER["PHP_SELF"];?>">
<input type="hidden" name="hdnCmd" value="">
<table width="100%" border="1">
<tr>
<th width="91"> <div align="center">รหัส subject_id </div></th>
<th width="48"> <div align="center">ประเทศ subject_country</div></th>
<th width="48">วันที่ subject_date</th>
<th width="98">เดือน subject_month</th>
<th width="98">ปี พ.ศ. subject_year</th>
<th width="98">ชื่องาน subject_name</th>
<th width="98">สถานที่ subject_place</th>
<th width="98">ติดต่อ subject_contact</th>
<th width="48">โทรศัพท์ subject_tel</th>
<th width="48"><p>โทรสาร subject_fax</p></th>
<th width="198"> <div align="center">E-mail subject_mailEmail </div></th>
<th width="97"> <div align="center">Website subject_web</div></th>
<th width="59"> <div align="center">หมายเหตุ subject_note</div></th>
<th width="30"> <div align="center">Edit </div></th>
<th width="30"> <div align="center">Delete </div></th>
</tr>
<?
while($objResult = mysql_fetch_array($objQuery))
{
?>
<?
if($objResult["subject_id"] == $_GET["subid"] and $_GET["Action"] == "Edit")
{
?>
<tr>
<td>
<input name="txtEditsubject_id" type="text" value="<?=$objResult["subject_id"];?>" size="5" maxlength="5">
<input type="hidden" name="hdnEditsubject_id" size="5" value="<?=$objResult["subject_id"];?>">
</td>
<td><input type="text" name="txtEditsubject_country" size="20" value="<?=$objResult["txtEditsubject_country"];?>"></td>
<td><input type="text" name="txtEditsubject_date" size="20" value="<?=$objResult["txtEditsubject_date"];?>"></td>
<td><input type="text" name="txtEditsubject_month" size="20" value="<?=$objResult["txtEditsubject_month"];?>"></td>
<td><input type="text" name="txtEditsubject_year" size="20" value="<?=$objResult["txtEditsubject_year"];?>"></td>
<td><input type="text" name="txtEditsubject_name" size="20" value="<?=$objResult["txtEditsubject_name"];?>"></td>
<td><input type="text" name="txtEditsubject_place" size="20" value="<?=$objResult["txtEditsubject_place"];?>"></td>
<td><input type="text" name="txtEditsubject_contact" size="20" value="<?=$objResult["txtEditsubject_contact"];?>"></td>
<td><input type="text" name="txtEditsubject_tel" size="20" value="<?=$objResult["txtEditsubject_tel"];?>"></td>
<td><input type="text" name="txtEditsubject_fax" size="20" value="<?=$objResult["txtEditsubject_fax"];?>"></td>
<td><input type="text" name="txtEditsubject_mail" size="20" value="<?=$objResult["txtEditsubject_mail"];?>"></td>
<td><input type="text" name="txtEditsubject_web" size="20" value="<?=$objResult["txtEditsubject_web"];?>"></td>
<td><input type="text" name="txtEditsubject_note" size="20" value="<?=$objResult["txtEditsubject_note"];?>"></td>
<td>
<input name="btnAdd" type="button" id="btnUpdate" value="Update" OnClick="frmMain.hdnCmd.value='Update';frmMain.submit();">
<input name="btnAdd" type="button" id="btnCancel" value="Cancel" OnClick="window.location='<?=$_SERVER["PHP_SELF"];?>';">
</td>
</tr>
<?
}
else
{
?>
<tr>
<td><?=$objResult["subject_id"];?></td>
<td><?=$objResult["subject_country"];?></td>
<td><?=$objResult["subject_date"];?></td>
<td><?=$objResult["subject_month"];?></td>
<td><?=$objResult["subject_year"];?></td>
<td><?=$objResult["subject_name"];?></td>
<td><?=$objResult["subject_place"];?></td>
<td><?=$objResult["subject_contact"];?></td>
<td><?=$objResult["subject_tel"];?></td>
<td><?=$objResult["subject_fax"];?></td>
<td><?=$objResult["subject_mail"];?></td>
<td><?=$objResult["subject_web"];?></td>
<td><?=$objResult["subject_note"];?></td>
<td><a href="<?=$_SERVER["PHP_SELF"];?>?Action=Edit&subid=<?=$objResult["subject_id "];?>">Edit</a></td>
<td><a href="JavaScript:if(confirm('Confirm Delete?')==true){window.location='<?=$_SERVER["PHP_SELF"];?>?Action=Del&subid=<?=$objResult["subject_id "];?>';}">Delete</a></td>
</tr>
<?
}
?>
<?
}
?>
<tr>
<td><input type="text" name="txtAddsubject_id " size="5"></td>
<td><input type="text" name="txtAddsubject_country" size="20"></td>
<td><input type="text" name="txtAddsubject_date" size="20"></td>
<td><input type="text" name="txtAddsubject_month" size="20"></td>
<td><input type="text" name="txtAddsubject_year" size="20"></td>
<td><input type="text" name="txtAddsubject_name" size="20"></td>
<td><input type="text" name="txtAddsubject_place" size="20"></td>
<td><input type="text" name="txtAddsubject_contact" size="20"></td>
<td><input type="text" name="txtAddsubject_tel" size="20"></td>
<td><input type="text" name="txtAddsubject_fax" size="20"></td>
<td><input type="text" name="txtAddsubject_mail" size="20"></td>
<td><input type="text" name="txtAddsubject_web" size="20"></td>
<td><input type="text" name="txtAddsubject_note" size="20"> </td>
<td>
<input name="btnAdd" type="button" id="btnAdd" value="Add" OnClick="frmMain.hdnCmd.value='Add';frmMain.submit();">
</td>
</tr>
</table>
</form>
<?
mysql_close($objConnect);
?>
</body>
</html>
Date :
2010-01-19 10:12:29
By :
it53008
ตอนนี้สามารถแก้ไขได้แล้วค่ะ แต่อยากสอบถามเพิ่มเติ่มค่ะ ในส่วนตรงที่เป็นเดือน ถ้าเราต้องการให้เป็นแบบลิสต์ เราต้องปรับตรงไหนค่ะ
คือจะให้มีลิสต์เลือกเดือนทั้ง 12 เดือน แล้วให้ผู้ใช้เลือกเดือน เพื่อความสะดวกนะค่ะ
ขอบคุณค่ะ
Date :
2010-01-19 11:05:53
By :
it53008
Load balance : Server 02