rinteractive Posted December 22, 2007 Share Posted December 22, 2007 I want to insert data into mysql table using php Below coding for creating table member $sql="CREATE TABLE member ( id int(11) NOT NULL auto_increment, membership_no varchar(100) , refcode varchar(100), post varchar(100), photo varchar(100), cat int(11), name varchar(100), head int (11), date_of_birth date, Gender varchar(10), profession varchar(50), nationality varchar(30), education varchar(30), familydetails1 varchar(50), familydetails2 varchar(50), address1 varchar(100), address2 varchar(100), telephoner int , telephoneo int, mobile int, email varchar(80), policestation varchar (70), municipalityoffice varchar(100), detailsofbank varchar(100), date1 date, place varchar(30), PRIMARY KEY (id), KEY `membership_no` (`membership_no`) )"; The table is created. Here is the coding for inserting the insert data into table member but its giving error message “Err, Problem with the database....Column count doesn't match value count at row 1” <?php include_once ("../include/config.php"); include_once ("../include/functions.php"); $id = CleanVar(h_($_POST['id'])); $name = CleanVar(h_($_POST['name'])); $head = CleanVar(h_($_POST['head'])); $membership_no = CleanVar(h_($_POST['membership_no'])); $cat = CleanVar(h_($_POST['cat'])); $mobile = CleanVar(h_($_POST['mobile'])); $telephoner = CleanVar(h_($_POST['telephoner'])); $telephoneo = CleanVar(h_($_POST['telephoneo'])); $refcode = CleanVar(h_($_POST['refcode'])); $education = CleanVar(h_($_POST['education'])); $post = CleanVar(h_($_POST['post'])); $date_of_birth = CleanVar(h_($_POST['date_of_birth'])); $gender = CleanVar(h_($_POST['gender'])); $nationality = CleanVar(h_($_POST['nationality'])); $profession = CleanVar(h_($_POST['profession'])); $policestation = CleanVar(h_($_POST['policestation'])); $address1 = CleanVar(h_($_POST['address1'])); $address2 = CleanVar(h_($_POST['address2'])); $famillydetails1 = CleanVar(h_($_POST['familydetails1'])); $familydetails2 = CleanVar(h_($_POST['familydetails2'])); $municipalityoffice = CleanVar(h_($_POST['muncicipalityoffice'])); $place = CleanVar(h_($_POST['place'])); $detailsofbank = CleanVar(h_($_POST['detailsofbank'])); $date1 = CleanVar(h_($_POST['date1'])); $email = CleanVar(h_($_POST['email'])); $upload_path = "../images/"; // Folder where uploaded file will be stored $allowed_ext = "image/pjpeg"; // Allowed file extension $allowed_ext2 = "image/jpeg"; $allowed_ext3 = "image/gif"; $photo_name = $_FILES['photo_file']['name']; $photo_temp = $_FILES['photo_file']['tmp_name']; $photo_path = $site_path."images/".$photo_name; $small_file_path = $site_path."prod_images/".$small_file_name; $small_allowed_size = "10240"; // Max File size in bytes (10 KB) $small_allowed_size_kb = number_format($small_allowed_size/1024,2); // Allowed File size in KB $small_image_size = $_FILES['image_small_file']['size']; // Current File Size in bytes $small_current_size = number_format($small_image_size/1024,2); // Current Size in KB $small_width = "138"; $small_height = "134"; $error = "N"; if (!$name) $error_name = format_error ("Member Name cannot be left blank !!!"); if (!$mobile) $error_mobile = format_error ("Mobile number cannot be left blank !!!"); if (!$post) $error_name = format_error ("Post Name cannot be left blank !!!"); if (!$gender) $error_gender = format_error ("Gender cannot be left blank !!!"); if (!$profession) $error_profession = format_error ("Profession cannot be left blank !!!"); if (!$date_of_birth) $error_date_of_birth = format_error ("Date of Birth cannot be left blank !!!"); if (!$place) $error_name = format_error ("Place Name cannot be left blank !!!"); if (!$address1) $error_name = format_error ("Residence address cannot be left blank !!!"); if ($_FILES["image_small_file"]["error"] > 0) $error_image_small_file = format_error("Please specify Small Image"); $prod_sql = "SELECT id, membership_no from member WHERE upper(membership_no) = '".d_(strtoupper($membership_no))."'"; $prod_result = mysql_query ($prod_sql); if (mysql_num_rows ($prod_result) > 0) $error_product_code = format_error ("Product Code already exists !!!"); if ($error == "Y"){ include_once ("member-add.php"); exit; } else{ // time to add the stuff... $sql = "INSERT INTO member values ( null, '".d_($membership_no)."', '".d_($cat)."', '".d_($head)."', '".d_($refcode)."', '".d_($post)."', '', '".d_($name)."', '".d_($date_of_birth)."', '".d_($gender)."' '".d_($profession)."', '".d_($nationality)."', '".d_($education)."', '".d_($familydetails1)."', '".d_($familydetails2)."', '".d_($address1)."', '".d_($address2)."', '".d_($telephoner)."', '".d_($telephoneo)."', '".d_($email)."', '".d_($mobile)."', '".d_($policestation)."', '".d_($municipalityoffice)."', '".d_($detailsofbank)."', '".d_($date1)."', '".d_($place)."' )"; $result = mysql_query ($sql) or die ("Err, Problem with the database....".mysql_error()); $prod_id = mysql_insert_id(); if($photo_name && $photo_name != "none"){ // copy small image first $explode_file_name = explode (".",$photo_name); $small_image_name = $prod_id."-small"; $new_small_file_name = $photo_name.".".$explode_file_name[1]; move_uploaded_file($_FILES['image_small_file']['tmp_name'], $upload_path.$new_small_file_name); // generate update string for small image $update_small_image = "image_small = '".d_($new_small_file_name)."',"; } $sql_update = "UPDATE member set $update_small_image WHERE id = '$prod_id'"; //echo $sql_update; $result_update = mysql_query ($sql_update) or die ("Err, Problem with the database....".mysql_error()); header ("Location: member-list.php?cat=$cat&msg=1"); exit; } ?> Quote Link to comment Share on other sites More sharing options...
phpSensei Posted December 22, 2007 Share Posted December 22, 2007 // time to add the stuff... $sql = "INSERT INTO member values ( null, '".d_($membership_no)."', '".d_($cat)."', '".d_($head)."', '".d_($refcode)."', '".d_($post)."', '', '".d_($name)."', '".d_($date_of_birth)."', '".d_($gender)."' '".d_($profession)."', '".d_($nationality)."', '".d_($education)."', '".d_($familydetails1)."', '".d_($familydetails2)."', '".d_($address1)."', '".d_($address2)."', '".d_($telephoner)."', '".d_($telephoneo)."', '".d_($email)."', '".d_($mobile)."', '".d_($policestation)."', '".d_($municipalityoffice)."', '".d_($detailsofbank)."', '".d_($date1)."', '".d_($place)."' )"; Where is the columns defined in this INSERT query? http://www.w3schools.com/php/php_mysql_insert.asp Quote Link to comment Share on other sites More sharing options...
asmith Posted December 22, 2007 Share Posted December 22, 2007 as phpsensei said oyu must give the column name , then values . which and only which are equal in count ! you can't give 3 columns and 2 values or vice versa . don't just insert data too much clumsy , i rearranged some part of your code : (the inserting should be like this) $sql = "INSERT INTO member( membership_no, cat, head, refcode, post, name, date_of_birth, Gender, profession, nationality, education, familydetails1, familydetails2, address1, address2, telephoner, telephoneo, mobile, email, policestation, municipalityoffice, detailsofbank, date1, place, ) values ( '".d_($membership_no)."', '".d_($cat)."', '".d_($head)."', '".d_($refcode)."', '".d_($post)."', '".d_($name)."', '".d_($date_of_birth)."', '".d_($gender)."' '".d_($profession)."', '".d_($nationality)."', '".d_($education)."', '".d_($familydetails1)."', '".d_($familydetails2)."', '".d_($address1)."', '".d_($address2)."', '".d_($telephoner)."', '".d_($telephoneo)."', '".d_($mobile)."', '".d_($email)."', '".d_($policestation)."', '".d_($municipalityoffice)."', '".d_($detailsofbank)."', '".d_($date1)."', '".d_($place)."' )"; Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 22, 2007 Author Share Posted December 22, 2007 $sql = "INSERT INTO member( membership_no, cat, head, refcode, post, name, date_of_birth, Gender, profession, nationality, education, familydetails1, familydetails2, address1, address2, telephoner, telephoneo, mobile, email, policestation, municipalityoffice, detailsofbank, date1, place, ) values ( '".d_($membership_no)."', '".d_($cat)."', '".d_($head)."', '".d_($refcode)."', '".d_($post)."', '".d_($name)."', '".d_($date_of_birth)."', '".d_($gender)."' '".d_($profession)."', '".d_($nationality)."', '".d_($education)."', '".d_($familydetails1)."', '".d_($familydetails2)."', '".d_($address1)."', '".d_($address2)."', '".d_($telephoner)."', '".d_($telephoneo)."', '".d_($mobile)."', '".d_($email)."', '".d_($policestation)."', '".d_($municipalityoffice)."', '".d_($detailsofbank)."', '".d_($date1)."', '".d_($place)."' )"; I have tried the above code there is no error message shown but the data is not inserting in the table Quote Link to comment Share on other sites More sharing options...
phpSensei Posted December 22, 2007 Share Posted December 22, 2007 Add a or die(mysql_error()); after the query. Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 22, 2007 Author Share Posted December 22, 2007 ya i have added after that code $result = mysql_query ($sql) or die ("Err, Problem with the database....".mysql_error()); then also its not showing an error message and data is not inserting in the table Quote Link to comment Share on other sites More sharing options...
phpSensei Posted December 22, 2007 Share Posted December 22, 2007 Edit: one sec.. Quote Link to comment Share on other sites More sharing options...
asmith Posted December 22, 2007 Share Posted December 22, 2007 remove your own sentence : $result = mysql_query ($sql) or die (mysql_error()); by the way , i do believe your mysql_query needs a connection as the second argument. something like : $conn = mysql_connect("localhost","yourusername","yourpassword"); $result = mysql_query ($sql,$conn) or die (mysql_error()); Quote Link to comment Share on other sites More sharing options...
phpSensei Posted December 22, 2007 Share Posted December 22, 2007 remove your own sentence : $result = mysql_query ($sql) or die (mysql_error()); by the way , i do believe your mysql_query needs a connection as the second argument. something like : $conn = mysql_connect("localhost","yourusername","yourpassword"); $result = mysql_query ($sql,$conn) or die (mysql_error()); Sentence or not, it would die out the error... Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 24, 2007 Author Share Posted December 24, 2007 i have modified the code what your replied then also there is no error message and data is not inserted in the table Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted December 24, 2007 Share Posted December 24, 2007 $sql="CREATE TABLE member ( id int(11) NOT NULL auto_increment, membership_no varchar(100) , refcode varchar(100), post varchar(100), photo varchar(100), cat int(11), name varchar(100), head int (11), date_of_birth date, Gender varchar(10), profession varchar(50), nationality varchar(30), education varchar(30), familydetails1 varchar(50), familydetails2 varchar(50), address1 varchar(100), address2 varchar(100), telephoner int , telephoneo int, mobile int, email varchar(80), policestation varchar (70), municipalityoffice varchar(100), detailsofbank varchar(100), date1 date, place varchar(30), PRIMARY KEY (id), KEY `membership_no` (`membership_no`) )"; Uh, where's the part where you actually run this query? Just putting this in a var doesn't mean the table is created. Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 24, 2007 Author Share Posted December 24, 2007 hey guys!! The table is already created only the problem is the data is not inserting in the table. Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 26, 2007 Author Share Posted December 26, 2007 hey guys, Please help me Thanks in Advance Quote Link to comment Share on other sites More sharing options...
trq Posted December 26, 2007 Share Posted December 26, 2007 Post your current code. (Within tags). Quote Link to comment Share on other sites More sharing options...
rinteractive Posted December 27, 2007 Author Share Posted December 27, 2007 The table has been created already the only thing is to insert a data into table using php code. The current code is below: <?php include_once ("../include/config.php"); include_once ("../include/functions.php"); $id = CleanVar(h_($_POST['id'])); $name = CleanVar(h_($_POST['name'])); $head = CleanVar(h_($_POST['head'])); $membership_no = CleanVar(h_($_POST['membership_no'])); $cat = CleanVar(h_($_POST['cat'])); $mobile = CleanVar(h_($_POST['mobile'])); $telephoner = CleanVar(h_($_POST['telephoner'])); $telephoneo = CleanVar(h_($_POST['telephoneo'])); $refcode = CleanVar(h_($_POST['refcode'])); $education = CleanVar(h_($_POST['education'])); $post = CleanVar(h_($_POST['post'])); $date_of_birth = CleanVar(h_($_POST['date_of_birth'])); $gender = CleanVar(h_($_POST['gender'])); $nationality = CleanVar(h_($_POST['nationality'])); $profession = CleanVar(h_($_POST['profession'])); $policestation = CleanVar(h_($_POST['policestation'])); $address1 = CleanVar(h_($_POST['address1'])); $address2 = CleanVar(h_($_POST['address2'])); $famillydetails1 = CleanVar(h_($_POST['familydetails1'])); $familydetails2 = CleanVar(h_($_POST['familydetails2'])); $municipalityoffice = CleanVar(h_($_POST['municipalityoffice'])); $place = CleanVar(h_($_POST['place'])); $detailsofbank = CleanVar(h_($_POST['detailsofbank'])); $date1 = CleanVar(h_($_POST['date1'])); $email = CleanVar(h_($_POST['email'])); $images = uploadImage('photo', GALLERY_IMG_DIR); if ($images['image'] == '' ) { echo "Error uploading file"; exit; } $image = $images['image']; $sql = "INSERT INTO member (id, membership_no, cat, head, refcode, post, photo, name, date_of_birth, Gender, profession, nationality, education, familydetails1, familydetails2, address1, address2, telephoner, telephoneo, mobile, email, policestation, municipalityoffice, detailsofbank, date1, place) VALUES ($id, $membership_no, $cat, $head, $refcode, $post, $image, $name, $date_of_birth, $gender, $profession, $nationality, $education, $familydetails1, $familydetails2, $address1, $address2, $telephoner, $telephoneo, $mobile, $email, $policestation, $municipalityoffice, $detailsofbank, $date1, $place)"; $result = mysql_query ($sql) or die (mysql_error()); header ("Location: member-list.php"); exit; ?> It gives an error message "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 ' bvvbvbn, dfgfg, sdfggf, 24234, ' at line 40" hey guys, please help me Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.