SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query failed: Column count doesn't match value count at row 1

    I'm tring to insert data into a database. The code is below and the printout
    showing the database fields - and I get this message:
    data inserted
    Query failed: Column count doesn't match value count at row 1
    PHP Code:
    <?php
    mysql_connect
    (localhost,root,"");
    mysql_select_db(oodb) or die( "Unable to select database"); 
    $query "
    INSERT INTO oocust (payrec,invnum,date,time,name,name,street,state,zip,terms,fob,ordernum,dateord,datecomp,datepaid,charges,paidamt,tax,amtdue)
    VALUES('
    $payrec','$invnum','$date','$time','$name','$street','$state','$zip','$terms','$fob','$ordernum','$dateord','$datepaid','$charges','$paidamt','$tax','$amtdue')";
    echo 
    "data inserted</font><br /><br />"
    $stat mysql_query($query) or die('Query failed: ' mysql_error());  
    if (isset( 
    $_POST['payrec']) ) 
    {
    $pd=$_POST['pd'];
    $payrec=$_POST['payrec'];
    $ordernum=$_POST['ordernum'];
    $invnum=$_POST['invnum'];  
    $bname=$_POST['bname'];  
    $bstreet=$_POST['bstreet'];  
    $bstate=$_POST['bstate'];
    $bcity=$_POST['bcity'];
    $bzip=$_POST['bzip'];
    $sname=$_POST['sname'];  
    $sstreet=$_POST['sstreet'];  
    $sstate=$_POST['sstate'];
    $scity=$_POST['scity'];
    $szip=$_POST['szip'];
    $contact=$_POST['contact'];
    $terms=$_POST['terms'];
    $fob=$_POST['fob'];
    $shipdate=$_POST['shipdate'];
    $shipamt=$_POST['shipamt'];  
    $dateord=$_POST['dateord'];
    $datecomp=$_POST['datecomp'];
    $duedate=$_POST['duedate'];  
    $datepaid=$_POST['datepaid'];
    $qty=$_POST['qty'];  
    $desc=$_POST['desc'];  
    $charges=$_POST['charges'];  
    $paidamt=$_POST['paidamt'];
    $dayslate=$_POST['dayslate'];  
    $tax=$_POST['tax'];
    $amtdue=$_POST['amtdue'];
    $paidsum=$_POST['paidsum'];
    }
    mysql_connect(localhost,root,""); 
    mysql_select_db(numbersdb) or die( "Unable to select database"); 
    if(!empty(
    $_POST["submit"])) 

    $invnum $_POST['invnum']; 

    mysql_query("UPDATE numbdata SET invnum=invnum+1"); 
    $result=mysql_query("select number from numbdata") or die ("Error - could not retrieve receipt number from database");
    $data=mysql_fetch_assoc($result);
    echo 
    ".Invoice number ".$data['invnum'];
    mysql_close();
    ?>
    HTML Code:
    <HTML><HEAD>
    <SCRIPT>
    changed = 0;
    function change(field)
    {
    for (var node = 0; node < field.attributes.length; node++)
    {
    if (field.attributes[node].nodeName == 'class')
    {
    if (field.defaultValue == field.value)
    {
    if (field.attributes[node].nodeValue != 'bgwhite')
    { 
    field.attributes[node].nodeValue = 'bgwhite';
    changed--;
    } 
    }
    else
    {
    if (field.attributes[node].nodeValue != 'bgyellow')
    { 
    field.attributes[node].nodeValue = 'bgyellow';
    changed++;
    } 
    }
    field.Form.reset.disabled = ! changed;
    break;
    }
    }
    }
    
    function changeall(Form)
    {
    Form.reset.disabled = true;
    changed = 0;
    for (var el = 0; el < Form.elements.length; el++)
    {
    if (Form.elements[el].name != 'reset') /* Some way needed of knowing which to reset. */ 
    {
    for (var node = 0; node < Form.elements[el].attributes.length; node++)
    {
    if (Form.elements[el].attributes[node].nodeName == 'class')
    {
    Form.elements[el].attributes[node].nodeValue = 'bgwhite';
    break; 
    } 
    }
    
    }
    }
    } 
    </SCRIPT>
    
    <STYLE type=text/css>.highlight {
    	BACKGROUND: #ff6699
    }
    .text {
    	COLOR: #ffffff
    }
    .both {
    	BACKGROUND: black; COLOR: white
    }
    </STYLE>
    
    <STYLE>INPUT.bgyellow {
    	BACKGROUND-COLOR: yellow
    }
    </STYLE>
    <script type="text/javascript">
    var today = new Date();
    var date = today.getMonth() + " / " + today.getDate() + " / " + today.getFullYear();
    </script>
    <!--increment date to create due date-->
    </head>
    <body>
    <script type="text/javascript">document.write(date);
    </script>
    <center>
    <B><font size=+2>Work Order Input Form</font></b></center>
    <P><FORM name="Form">
    Paid? If so - enter"P":<input type=text size=1 name=pd><p>
    
    AR/AP: <SELECT name=payrec>
    <OPTION class=highlight value=ar selected>R
    <OPTION class=highlight value=ap>P
    </OPTION></SELECT><br>
    
    Order No: <INPUT TYPE="text" size=5 name="ordernum"><br>
    Invoice No: <INPUT TYPE="text" size=5 name="invnum" MAXLENGTH=5
    onKeyUp="if(this.value.length==this.size)document.form.bname.focus();"><p>
    
    <b>Bill</b> - Name: <INPUT TYPE="text" name=bname>
    Street: <INPUT TYPE="text" name=bstreet>
    State: <SELECT name=bstate>
    <OPTION class=highlight value=fl selected>Florida
    <OPTION class=highlight value=ga>Georgia
    <OPTION class=highlight value=al>Alabama
    <OPTION class=highlight value=la>Louisiana
    <OPTION class=highlight value=sc>South Carolina
    </OPTION></SELECT>
    City: <INPUT TYPE="text" name="bcity">
    
    </OPTION></SELECT>
    Zip: <INPUT TYPE="text" size=5name="bzip" MAXLENGTH=5
    onKeyUp="if(this.value.length==this.size)document.form.contact.focus();"><br>
    <b>Contact: </b><INPUT TYPE="text" name=contact><BR>
    
    <b>Ship</b> - Name: 
    <INPUT TYPE="text" name=sname>
    Street <INPUT TYPE="text" name=sstreet>
    
    State <SELECT name=sstate>
    <OPTION class=highlight value=fl selected>Florida
    <OPTION class=highlight value=ga>Georgia
    <OPTION class=highlight value=al>Alabama
    <OPTION class=highlight value=la>Louisiana
    <OPTION class=highlight value=sc>South Carolina
    </OPTION></SELECT>
    City: <INPUT TYPE="text" name="scity">
    
    </OPTION></SELECT>
    Zip: <INPUT TYPE="text" size=5name="szip" MAXLENGTH=5
    onKeyUp="if(this.value.length==this.size)document.form.terms.focus();"><p>
    
    Terms: <SELECT name=terms>
    <OPTION class=highlight value=net 10 selected>Net 10
    <OPTION class=highlight value=1_10_30ga>1 10 Net 30
    <OPTION class=highlight value=2_10_30>2 10 Net 30
    <OPTION class=highlight value=cash>Cash
    </OPTION></SELECT>
    
    FOB: <SELECT name=fob>
    <OPTION class=highlight value=Destination selected>Destination
    <OPTION class=highlight value=Origin>Origin
    </OPTION></SELECT><p>
    
    <b>Ship</b> - Amt:<INPUT TYPE="text" size=10 name="shipamt">
    Date: <INPUT TYPE="text" size=7 name="shipamt" MAXLENGTH=10
    onKeyUp="if(this.value.length==this.size)document.form.dateord.focus();"><br>
    <b>Dates</b> - Ordered: 
    <INPUT TYPE="text" size=10 name="dateord" MAXLENGTH=10
    onKeyUp="if(this.value.length==this.size)document.form.datecomp.focus();">
    Completed: <INPUT TYPE="text" size=10 name="datecomp" MAXLENGTH=10
    onKeyUp="if(this.value.length==this.size)document.form.duedate.focus();">
    Due: <INPUT TYPE="text" size=10 name="duedate" MAXLENGTH=10
    onKeyUp="if(this.value.length==this.size)document.form.datepaid.focus();">
    Paid: <INPUT TYPE="text" size=10 name="datepaid" MAXLENGTH=10
    onKeyUp="if(this.value.length==this.size)document.form.qty.focus();"><p>
    Qty: <INPUT TYPE="text" size=5 name=qty>
    Description: <INPUT TYPE="text" size=25 name=desc>
    Charges: <INPUT TYPE="text" size=7 name=charges>
    Paid Amt: <INPUT TYPE="text" size=7 name=paidamt>
    Tax: <INPUT TYPE="text" size=6 name tax><p>
    <b>Shipping</b> - Date: <INPUT TYPE="text" size=10 name shipdate>
    Amt:<INPUT TYPE="text" size=7 name shipamt><p>
    <b>Amount Due</b>: <INPUT TYPE="text" size=7 name=amtdue><p>
    
    <INPUT type="submit" value="submit data" />
    </p></form></body></html>
    oocust
    Field Type Null Default Comments MIME
    id int(5) No
    pd varchar(1) No
    payrec varchar(1) No
    ordernum int(5) No
    invnum int(5) No
    bname varchar(25) No
    bstreet varchar(25) No
    bstate varchar(15) No
    bcity varchar(15) No
    bzip int(5) No
    sname varchar(25) No
    sstreet varchar(25) No
    sstate varchar(15) No
    scity varchar(15) No
    szip int(5) No
    contact varchar(15) No
    terms varchar(12) No
    fob varchar(11) No
    shipdate varchar(10) No
    shipamt decimal(7,2) No
    dateord varchar(10) No
    datecomp varchar(10) No
    duedate varchar(10) No
    datepaid varchar(10) No
    qty int(5) No
    desc varchar(25) No
    charges decimal(7,2) No
    paidamt decimal(7,2) No
    dayslate int(4) No
    tax decimal(6,2) No
    amtdue decimal(7,2) No
    paidsum decimal(7,2) No

  2. #2
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The column count doesn't match the value count! You're looking to insert into ~ 19 columns, and you have variables for around 16.

    You also mention the name column twice once after each other. Figure out what columns you want data to be going into, and what values match those columns. Then look over your insert query.

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help Zurev. I was neglecting this section:
    Code:
    INSERT INTO oocust (pd,payrec,ordernum,invnum,bname,bstreet,bstate,bcity,bzip,sname,sstreet,sstate,scity,szip,contact,terms,fob,shipdate,shipamt,dateord,datecomp,duedate,datepaid,qty,desc,charges,paidamt,
    dayslate,tax,amtdue,paidsum)
    VALUES('$pd','$payrec','$ordernum','$invnum','$bname','$bstreet','$bstate','$bcity','$bzip','$sname','$sstreet','$sstate','$scity','$szip','$contact','$terms','$fob','$shipdate','$shipamt','$dateord','$datecomp','$duedate','$datepaid','$qty','$desc',
    '$charges','$paidamt','$dayslate','$tax','$amtdue','$paidsum')";
    I made the above corrections but still got the below message:

    data inserted
    Query failed: 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 'desc,charges,paidamt, dayslate,tax,amtdue,paidsum) VALUES('','','','','','',''' at line 1
    I've spent the longest pouring over this and do not see the error in the above code ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    DESC is a reserved word

    just put those cute little old backticks around it

    better yet, remane it to something like descr

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You were right & I finally got the form - but no insert. I trimmed the code way down to simplify until I get it working. I think I have to rearrange some of the code but unsure ? A printout of the table is at the bottom(the last 3 are the result of the insert attempt), only the numeric fields are shown?
    Code:
     <?php
    mysql_connect(localhost,root,"");
    mysql_select_db(oodb) or die( "Unable to select database"); 
    $query = "
    INSERT INTO oocust (pd,payrec,ordernum,invnum)
    VALUES('$pd','$payrec','$ordernum','$invnum')";
    
    echo "data inserted</font><br /><br />"; 
    $stat = mysql_query($query) or die('Query failed: ' . mysql_error());  
    if (isset( $_POST['payrec']) ) 
    {
    $pd=$_POST['pd'];
    $payrec=$_POST['payrec'];
    $ordernum=$_POST['ordernum'];
    $invnum=$_POST['invnum'];  
    
    }
    mysql_connect(localhost,root,""); 
    mysql_select_db(numbersdb) or die( "Unable to select database"); 
    if(!empty($_POST["submit"])) 
    { 
    $invnum = $_POST['invnum']; 
    } 
    mysql_query("UPDATE numbdata SET invnum=invnum+1"); 
    $result=mysql_query("select invnum from numbdata") or die ("Error - could not retrieve receipt number from database");
    $data=mysql_fetch_assoc($result);
    mysql_close();
    ?>
    <HTML><HEAD>
    <SCRIPT>
    changed = 0;
    function change(field)
    {
    for (var node = 0; node < field.attributes.length; node++)
    {
    if (field.attributes[node].nodeName == 'class')
    {
    if (field.defaultValue == field.value)
    {
    if (field.attributes[node].nodeValue != 'bgwhite')
    { 
    field.attributes[node].nodeValue = 'bgwhite';
    changed--;
    } 
    }
    else
    {
    if (field.attributes[node].nodeValue != 'bgyellow')
    { 
    field.attributes[node].nodeValue = 'bgyellow';
    changed++;
    } 
    }
    field.Form.reset.disabled = ! changed;
    break;
    }
    }
    }
    
    function changeall(Form)
    {
    Form.reset.disabled = true;
    changed = 0;
    for (var el = 0; el < Form.elements.length; el++)
    {
    if (Form.elements[el].name != 'reset') /* Some way needed of knowing which to reset. */ 
    {
    for (var node = 0; node < Form.elements[el].attributes.length; node++)
    {
    if (Form.elements[el].attributes[node].nodeName == 'class')
    {
    Form.elements[el].attributes[node].nodeValue = 'bgwhite';
    break; 
    } 
    }
    
    }
    }
    } 
    </SCRIPT>
    
    <STYLE type=text/css>.highlight {
    	BACKGROUND: #ff6699
    }
    .text {
    	COLOR: #ffffff
    }
    .both {
    	BACKGROUND: black; COLOR: white
    }
    </STYLE>
    
    <STYLE>INPUT.bgyellow {
    	BACKGROUND-COLOR: yellow
    }
    </STYLE>
    <script type="text/javascript">
    var today = new Date();
    var date = today.getMonth() + " / " + today.getDate() + " / " + today.getFullYear();
    </script>
    <!--increment date to create due date-->
    </head>
    <body><br>
    <script type="text/javascript">document.write(date);
    </script>
    
    <center>
    <B><font size=+2>Work Order Input Form</font></b></center>
    <P><FORM name="Form">
    Paid? If so - enter"P":<input type=text size=1 name=pd><p>
    
    AR/AP: <SELECT name=payrec>
    <OPTION class=highlight value=ar selected>R
    <OPTION class=highlight value=ap>P
    </OPTION></SELECT><br>
    
    Order No: <INPUT TYPE="text" size=5 name="ordernum"><br>
    Invoice No: <INPUT TYPE="text" size=5 name="invnum"><p>
    
    <INPUT type="submit" value="submit data" />
    </p></form></body></html>
    id pd payrec ordernum invnum bname bstreet bstate bcity bzip sname sstreet sstate scity szip contact terms fob shipdate shipamt dateord datecomp duedate datepaid qty descr charges paidamt dayslate tax amtdue paidsum
    1 R 150 150 Design Concept Builders The last house on the lef Florida Miami 33301 Design Concept Builders The last house on the lef Florida Miami 33301 Bubba net 10 destination 12/25/2011 0.00 01/02/2011 01/07/2011 02/07/2011 1 replaced kitchen sink 160.00 0.00 0 9.60 169.60 0.00
    2 R 250 151 Design Concept Builders The last house on the lef Florida Miami 33301 Design Concept Builders, The last house on the lef Florida Miami 33301 Bubba net 10 destination 17.00 3/12/2011 3/15/2011 4/15/2011 0 routed out toilet 88.00 93.28 0 5.28 0.00 93.28
    3 P R 350 152 my own house 9810 E. Hogan Road Dumas Florida 33063 jobsite Down the road Alaska Juneau 99901 Lorrie 2 10 net 30 origin 0.00 3/29/2011 12/25/2010 1/25/2011 1 just came to visit 10.00 0.00 0 0.00 0.00 0.00
    4 R 450 153 All Broward Realty 4325 N. Sunrise Blvd. Florida Opalocka 33313 All Broward Realty 4325 N. Sunrise Blvd Florida Opalocka 33313 Dawn net 10 destination 0.00 2/15/2011 3/15/2011 4/15/2011 3/29/2011 0 inspected water damage 300.00 0.00 0 18.00 318.00 0.00
    5 P 0 0 iou name iou street iou state iou city 99990 0 0.00 2/23/2011 3/23/2011 10 bathroom faucets 89.52 0.00 0 5.37 94.87 0.00
    6 p P 0 0 apname apstreet apstate apcity 0 0 0.00 2/15/2011 3/15/2011 4 kitchen faucets 102.78 0.00 0 6.53 110.31 0.00
    16 0 0 0 0 0.00 0 0.00 0.00 0 0.00 0.00 0.00
    15 0 0 0 0 0.00 0 0.00 0.00 0 0.00 0.00 0.00
    14 0 0 0 0 0.00 0 0.00 0.00 0 0.00 0.00 0.00

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what is this --
    Code:
    UPDATE numbdata SET invnum=invnum+1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2010
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using a database to increment the invoice number for each work order input. I use this same code in other programs.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    do a search for race condition

    you will either have to use the built-in auto_increment feature, or else use START TRANSACTION and COMMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •