By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,729 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,729 IT Pros & Developers. It's quick & easy.

php@mysql problem, using MySQL reserved words

100+
P: 127
Below is my php code which i need to save the jobseeker resume in database. But does not function and show the message: Column count doesn't match value count at row 1 after i add a field name resume_ID as a primary key in phpMyadmin. i dont know what is the error i done. Thanks.

do_resume.php
[PHP]<?php
include_once("database.php");


class user {


function linkid ()
{
$db = new db();
$link_id = $db->dbconnect();

return $link_id;
} // end function linkid

function add_user($arr)
{


$link_id = $this->linkid();


$fullName = $arr['fullName'];
$nric = $arr['nric'];
$gender = $arr['gender'];
$day = $arr['day'];
$month = $arr['month'];
$year = $arr['year'];
$maritalStatus = $arr['maritalStatus'];
$nationality = $arr['nationality'];
$emailAdd = $arr['emailAdd'];
$contactNum = $arr['contactNum'];
$contactAdd = $arr['contactAdd'];
$languages1 = $arr['languages1'];
$languages2 = $arr['languages2'];
$education = $arr['education'];
$workExp = $arr['workExp'];
$curricularAct = $arr['curricularAct'];
$skills = $arr['skills'];
$references = $arr['references'];


if($fullName==""){
$message="Opps.. You forgot enter your Full Name!<br>";
include("resume.php");
}


else


if($nric==""){
$message="Opps.. You forgot enter your NRIC!<br>";
include("resume.php");
}


else


if($gender==""){
$message="Please select your Gender!<br>";
include("resume.php");
}


else


if($maritalStatus==""){
$message="Please select your Marital Status!<br>";
include("resume.php");
}


else


if($nationality==""){
$message="Opss.. You forgot enter your Nationality!<br>";
include("resume.php");
}


else


if($emailAdd==""){
$message="Opps.. You forgot enter your Email Address!<br>";
include("resume.php");
}


else


if($contactNum==""){
$message="Opps.. You forgot enter your Contact Number!<br>";
include("resume.php");
}


else


if($contactAdd==""){
$message="Opps.. You forgot enter your Contact Address!<br>";
include("resume.php");
}


else


if($languages1==""){
$message="Please select your Languages(oral)!<br>";
include("resume.php");
}


else


if($languages2==""){
$message="Please select your Languages(written)!<br>";
include("resume.php");
}


else


if($education==""){
$message="You need to fill in your Education Level!<br>";
include("resume.php");
}


else


if($workExp==""){
$message="You need to fill in your Work Experiences!<br>";
include("resume.php");
}


else


if($references==""){
$message="You need to fill in your References!<br>";
include("resume.php");
}

else

{


$myquery = "INSERT INTO resume
VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', '$contactAdd', '$languages1', '$languages2', '$education', '$workExp', '$curricularAct', '$skills', '$references' )";

$result = mysql_query($myquery,$link_id) or die(mysql_error());

include_once("resume_success.php");


}
}
}



?>[/PHP]

do_resume_Pre.php
[PHP]<?php
include_once("do_resume.php");


$act = $_REQUEST['act'];


if ($act == 'adduser') {

$arr = $_REQUEST;
$usr = new user();
$usr->add_user($arr);


exit();
}


?>[/PHP]


database.php

[PHP]<?php
class db {

var $sqlserver = "localhost";
var $sqldatabase = "ums e-job portal";
var $sqluser = "root";
var $sqlpass = "";
var $link_id;

function dbconnect () {
$link_id = mysql_connect($this->sqlserver, $this->sqluser, $this->sqlpass);
$dbh = mysql_select_db($this->sqldatabase);

return $this->link_id = $link_id;
}
}

?>[/PHP]


resume_success.php
[PHP]<?php
include("index.php");
?>[/PHP]
Jan 21 '07 #1
Share this Question
Share on Google+
18 Replies


ronverdonk
Expert 2.5K+
P: 4,258
You don;'t have to show all code in this case. The MySQL message simply means that you have not specified the exact number of table columns in the INSERT statement.

Since you use the VALUES attribute you must specify all values for all columns. When you don't want to use that, it is better to define the columns names first, like
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tablename (col1, col2 ,.... coln) VALUES(val1, val2, .... valn);
Ronald :cool:
Jan 21 '07 #2

100+
P: 127
You don;'t have to show all code in this case. The MySQL message simply means that you have not specified the exact number of table columns in the INSERT statement.

Since you use the VALUES attribute you must specify all values for all columns. When you don't want to use that, it is better to define the columns names first, like
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tablename (col1, col2 ,.... coln) VALUES(val1, val2, .... valn);
Ronald :cool:


Thanks, but when i do like that, as php code shown below:
[PHP]
$myquery = "INSERT INTO resume ( fullName, nric, gender, day, month, year, maritalStatus, nationality, emailAdd, contactNum, contactAdd, languages1, languages2, education, workExp, curricularAct, skills, references )
VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', '$contactAdd', '$languages1', '$languages2', '$education', '$workExp', '$curricularAct', '$skills', '$references' )"; [/PHP]



it will shown message 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 'references ) VALUES ( 'bb', '881028-11-5555', 'Female', '27',


i have try many way, but din't success, i have no idea.
Jan 21 '07 #3

ronverdonk
Expert 2.5K+
P: 4,258
Show all your table columns with their data types. You can use the command
Expand|Select|Wrap|Line Numbers
  1. DESCRIBE tablename;
to get that information. Then show it here.

Ronald :cool:
Jan 21 '07 #4

100+
P: 127
Show all your table columns with their data types. You can use the command
Expand|Select|Wrap|Line Numbers
  1. DESCRIBE tablename;
to get that information. Then show it here.

Ronald :cool:

This is my table and datatype:
Tablename: resume

resume_ID int(10) PRI NULL auto_increment
fullName varchar(30)
nric varchar(14)
gender varchar(20)
day int(2) 0
month varchar(20)
year year(4) 0000
maritalStatus varchar(20)
nationality varchar(30)
emailAdd varchar(50)
contactNum varchar(11)
contactAdd varchar(100)
languages1 varchar(10)
languages2 varchar(10)
education blob
workExp blob
curricularAct blob
skills blob
references blob


Thanks..
Jan 21 '07 #5

ronverdonk
Expert 2.5K+
P: 4,258
Hold on a second! Did you specify the query exactly as you showed it, i.e. split over 2 lines without closing quotes and concatenating them? Then that is the error.

Ronald :cool:
Jan 21 '07 #6

100+
P: 127
This is my table and datatype:
Tablename: resume

resume_ID int(10) PRI NULL auto_increment
fullName varchar(30)
nric varchar(14)
gender varchar(20)
day int(2) 0
month varchar(20)
year year(4) 0000
maritalStatus varchar(20)
nationality varchar(30)
emailAdd varchar(50)
contactNum varchar(11)
contactAdd varchar(100)
languages1 varchar(10)
languages2 varchar(10)
education blob
workExp blob
curricularAct blob
skills blob
references blob


Thanks..
What is the problem with my php code?? or it is problem of mysql??
i don't know what to do.. Need to delete the Resume table and create new one??
Jan 21 '07 #7

100+
P: 127
i don't really understand, you means in the php code or mysql, so what should i do now?? Thanks..
Jan 21 '07 #8

ronverdonk
Expert 2.5K+
P: 4,258
i don't really understand, you means in the php code or mysql, so what should i do now?? Thanks..
You most probably split your query over 2 lines without concatenating them. This is how you must define the query in PHP.
[php]$myquery = "INSERT INTO resume ( fullName, nric, gender, day, month, year, maritalStatus, nationality, emailAdd, contactNum, contactAdd, languages1, languages2, education, workExp, curricularAct, skills, references ) ".
" VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', '$contactAdd', '$languages1', '$languages2', '$education', '$workExp', '$curricularAct', '$skills', '$references' )";
[/php]Ronald :cool:
Jan 21 '07 #9

100+
P: 127
if i code like this:
[PHP]$myquery = "INSERT INTO resume ( fullName, nric, gender, day, month, year, maritalStatus, nationality, emailAdd, contactNum, contactAdd, languages1, languages2, education, workExp, curricularAct, skills, references )"
"VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', '$contactAdd', '$languages1', '$languages2', '$education', '$workExp', '$curricularAct', '$skills', '$references' )"; [/PHP]

then, will show the message:
Parse error: parse error, unexpected '"' in C:\Apache2\Apache2\htdocs\do_resume.php on line 162

Fatal error: Cannot instantiate non-existent class: user in C:\Apache2\Apache2\htdocs\do_resumePre.php on line 11


do_resumePre.php
[PHP]<?php
include_once("do_resume.php");


$act = $_REQUEST['act'];


if ($act == 'adduser') {

$arr = $_REQUEST;
$usr = new user();
$usr->add_user($arr);


exit();
}


?>[/PHP]


i don't know what is the error, i have done my registration form, it is not problem, the resume above is edit the code from my registration form, should be no problem.. i really don't know..
Jan 21 '07 #10

ronverdonk
Expert 2.5K+
P: 4,258
You made the same error again! You did NOT copy the code statement exactly as I showed you previously. You cannot construct the MySQL statement like you did. You must concatenate the strings when you use a multi-line construct.

So copy the statement I show you here and use that. I shortened the strings on each line so you can see what I am doing.

[php]$myquery = "INSERT INTO resume ( fullName, nric, gender, day, ".
" month, year, maritalStatus, nationality, emailAdd, contactNum, ".
" contactAdd, languages1, languages2, education, workExp, ".
" curricularAct, skills, references ) ".
" VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', ".
" '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', ".
" '$contactAdd', '$languages1', '$languages2', '$education', ".
" '$workExp', '$curricularAct', '$skills', '$references' )"; [/php]
Ronald :cool:
Jan 22 '07 #11

100+
P: 127
sorry, it can not work.. i have copy the code from you as below:
[PHP]$myquery = "INSERT INTO resume ( fullName, nric, gender, day, ".
" month, year, maritalStatus, nationality, emailAdd, contactNum, ".
" contactAdd, languages1, languages2, education, workExp, ".
" curricularAct, skills, references ) ".
" VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', '$year', ".
" '$maritalStatus', '$nationality', '$emailAdd', '$contactNum', ".
" '$contactAdd', '$languages1', '$languages2', '$education', ".
" '$workExp', '$curricularAct', '$skills', '$references' )";[/PHP]

but the message as below shown:
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 'references ) VALUES ( 'tan mei ling', '880505-10-5432', 'Femal
Jan 22 '07 #12

ronverdonk
Expert 2.5K+
P: 4,258
Well, I am baffled. I tried it and it gave me the same error. Untill I took some columns off from he end. And when the column count was 16, I did not get the error any longer. I have no clue as to why this is happening.

I promise, I will look into it tomorrow. Maybe a bug in MySQL, I have no idea yet.

Ronald :cool:
Jan 22 '07 #13

100+
P: 127
thanks a lot.. i really no idea on it, i check the code again and again, but i can not find the error.. thanks..
Jan 22 '07 #14

ronverdonk
Expert 2.5K+
P: 4,258
I found it! It appears that references is a reserved word! (see also this link MySQL reserved words).

The only way to get this bypassed is either to change the column name or enclose the column name references within so-called 'back ticks', as follows:
Expand|Select|Wrap|Line Numbers
  1. `references`
.
So your select statement becomes[php]$myquery = "INSERT INTO resume ( fullName, nric, gender, day, ".
" month, year, maritalStatus, nationality, emailAdd, contactNum, ".
" contactAdd, languages1, languages2, education, workExp, ".
" curricularAct, skills, `references` ) ".
" VALUES ( '$fullName', '$nric', '$gender', '$day', '$month', ".
" '$year', '$maritalStatus', '$nationality', '$emailAdd', ".
" '$contactNum', '$contactAdd', '$languages1', '$languages2', ".
" '$education', '$workExp', '$curricularAct', '$skills', ".
" '$references' )"; [/php]
Ronald :cool:
Jan 22 '07 #15

100+
P: 127
Yes!! i'm success.. Really thanks for your helps, if not, i will checking again and again and don't where i code wrongly.. Haha.. although i checking the code again and again before i paste it in forum, but it is worth, because i got the answer now and learn one more thing, thanks a lot.. :)

And may i ask if i got foreign key (jobseeker_ID) in my resume table(table name) where resume table got primary key(resume_ID). Is it just put it like others data type in mysql? What is the function of foreign key? How if i want to call the database from the foreign key?
From example, the jobseeker have register as a member in my system and he/she want to create a resume and save it and my system. He/she need to fill in some field like personal particular, education and so on.. And his/her personal detail is already in my database when he/she make a registration as a member. Thus, what should i do if i want to display their personal particular in their resume so they no need to re-type their personal detail, and they only need to fill in education, skills and others.. Thanks..
Jan 22 '07 #16

ronverdonk
Expert 2.5K+
P: 4,258
Yes, it took some sweat to find, but I managed it by removing the columns in your update query 1 by 1. And the error stayed on until I got to the last one, the references. Anyway, the problem was found. And I also learned from that.

About the foreign key: usually a foreign key is a 'normal' column in a table that points to a primary key in another table (relation). See the following wikipedia paragraph about foreign keys Wikipedia Foreign Key

Ronald :cool:
Jan 22 '07 #17

100+
P: 127
Thanks a lot.. I learn many things from you and this forum.. Thanks..:)
Jan 22 '07 #18

ronverdonk
Expert 2.5K+
P: 4,258
You are always welcome to this and the other forums at this site.

Ronald :cool:
Jan 22 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.