SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Form not adding data MySQL Database:more than a couple of characters or using spaces

    Hi,

    I cannot understand why data is not being correctly added to my database from a form in my admin pages. The problem is if I enter something like:
    1
    2
    y
    8

    this works fine.

    If I enter in the textarea (the last box in the form) something like:
    1 3
    o p
    7 8
    988786

    (ie. there is a space in between the data entered, or there is more than a couple of characters or numbers.) So it is very restrictive.

    this does not work. The error I get is:

    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 '10\r\n)' at line 1

    It basically seems to say 'because you have a space in between the entries, or because you are using more than 2 characters in a row when entering data, there is a problem'

    WHY!!!???


    I am using this form

    PHP Code:
    <form method="post" action="CMS-home.php">

    <
    input type="hidden" name="add" value="true" />

    <
    div>
    <
    label for="productid">ProductID</label>
    <
    input type="text" name="productid" id="productid" />
    </
    div>

    <
    div>
    <
    label for="categoryid">CategoryID</label>
    <
    input type="text" name="categoryid" id="categoryid" />
    </
    div>


    <
    div>
    <
    label for="name">Name</label>
    <
    input type="text" name="name" id="name" />
    </
    div>

    <
    div>
    <
    label for="order">Order</label>
    <
    input type="text" name="order" id="order" />
    </
    div>

    <
    div>
    <
    label for="price">Price</label>
    <
    input type="text" name="price" id="price" />
    </
    div>

    <
    div>
    <
    label for="shortname">Short name</label>
    <
    input type="text" name="shortname" id="shortname" />
    </
    div>

    <
    div>
    <
    label for="extendedname">Extended name</label>
    <
    input type="text" name="extendedname" id="extendedname" />
    </
    div>

    <
    div>
    <
    label for="code">Code</label>
    <
    input type="text" name="code" id="code" />
    </
    div>

    <
    div>
    <
    label for="manucode">Manufacturers code</label>
    <
    input type="text" name="manucode" id="manucode" />
    </
    div>

    <
    div>
    <
    label for="text">Text</label>
    <
    textarea name="text" id="text" rows="20" cols="100"></textarea>
    </
    div>

    <
    input type="submit" name="submit" value="Add Content" />
    </
    form

    This is the code that enters the code into the database

    PHP Code:
    function add_content($p) {
            
    $productid mysql_real_escape_string($p['productid']);
            
    $categoryid mysql_real_escape_string($p['categoryid']);
            
    $name mysql_real_escape_string($p['name']);
            
    $order mysql_real_escape_string($p['order']);
            
    $price mysql_real_escape_string($p['price']);
            
    $shortname mysql_real_escape_string($p['shortname']);
            
    $extendedname mysql_real_escape_string($p['extendedname']);
            
    $code mysql_real_escape_string($p['code']);
            
    $manucode mysql_real_escape_string($p['manucode']);
            
    $text mysql_real_escape_string($p['text']);
        
        
        
        
        
    $sql "INSERT INTO products (productid, categoryid, name, `order`, price, shortname, extendedname, code, manucode, `text`) VALUES ('$productid', '$categoryid', '$name', '$order', $price$shortname$extendedname$code$manucode$text)";
        
    $res mysql_query($sql) or die(mysql_error());
        
        } 

  2. #2
    SitePoint Enthusiast
    Join Date
    Sep 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's because you haven't got quotes around all your values in the INSERT, you have quoted some but for some reason then have stopped after '$order'.

  3. #3
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To easily debug a query, just echo it out before running it so you can see the actual query being run.
    PHP Code:
    $sql 'insert blah blah';

    echo 
    $sql;

    die(); 
    When you echo out the actual query, the errors are usually obvious.

    And you don't need the back-ticks so you can remove them.

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's right - I have added them and it is working now. Cheers,

    Matt.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    $p = array('name'=>'this','email'=>'that');  // go on, go on...

    $keys = array();
    $values = array();
    foreach( 
    $p as $k=>$v ){
    $keys[]   = "`{$k}`";
    $values[] = "'"mysql_real_escape_string($v) ."'"//**
    }
    $sql ='insert into mytable ( 'join(','$keys) . ') values ('join(','$values) .');'

    // echo $sql
    // insert into mytable ( `name`,`email`) values ('this','that'); 
    ** had to guess that bit, I don't have mysql_* installed

    Better that having to type any new field you may need to add 4 times, imagine the chance of an error...

    Code is arrays too read loops are good!.


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
  •