Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple insert with one query problem, new here , need help

    Hello, I am currently having some issue on inserting in a database.
    my idea is to have a couple of input boxes to allow user to enter data and then submit, where this data will be inserted into the corresponding table. some of the code i did is by referring to other person coding and understand how it works.

    for example in my html
    i have 3 input text has name tag with has array elements
    <form action =" somescript.php">
    <input type="text" name="id[]"/>
    <input type="text" name="name[]"/>
    <input type="text" name="date[]"/>
    </form>

    i then have a for loop when the condition is true
    i call the sql insert statement by assigning to a variable
    $sql = ' insert into (id, name, date) values ($id[$i],$name[$i],$date[$i])';
    before that in order to check whether the insert works i did like
    $sql = ' insert into id,name, date) values ("2","ben","1999-1-1"); and it works fine, but when assign to an array element it fails to insert data.

    Any advise or is it my understanding is wrong ?

    Thank you.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Show us your whole PHP script (as you have it now).
    The script that processes the form.

  • #3
    New Coder
    Join Date
    Jan 2006
    Posts
    73
    Thanks
    2
    Thanked 3 Times in 3 Posts
    I suggest simply echoing what is in those arrays, it could be inserting an empty row (nothing) because it isn't retrieving any real values out of those keys.

  • #4
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the code mlseim,

    There are two parts
    the enter data in the input box will be insert into the table

    // here is the testconnection.php
    -----------------------------------
    // here to let you know the table has 3 data field id, name and date
    $sql = 'create TABLE if not exists test (id INT NOT NULL AUTO_INCREMENT primary key, name TEXT, mydate DATE NOT NULL)DEFAULT CHARACTER SET UTF8';
    // check if create table works
    if(!mysqli_query($link,$sql)){
    $output = " cannot create table";
    include 'output.html.php';
    exit();
    }
    $output = "successfully created table";
    include 'output.html.php';

    // looping
    for($i=0;$i<count($name);$i++){
    if($name=="")
    continue;
    // i did a print out out the data to check the values as XGIHavoc says and
    //they show the correct values i enter in the input box
    echo "<br>";
    echo $id[$i];
    echo "<br>";
    echo $name[$i];
    echo "<br>";
    echo $date[$i];
    echo "<br>";
    echo count($name);
    echo "<br>";

    // here is the statement where i insert the data in the the test table
    $sql = 'insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])';
    // i did a testing with the below code to check whether my insert code is working and it works.
    //$sql = 'insert into test(id, name, mydate) values("2","drew","2002-10-2")';

    // check if insert works
    if (!mysqli_query($link,$sql)){
    $output = " cannot insert data table";
    include 'output.html.php';
    exit();
    }

    // here is the html part

    <form action="testconnection.php">
    <input type="text" name="id[]" />
    <input type="text" name="name[]" />
    <input type="text" name="date[]"/>
    <input type="submit" value="enter"/>
    </form>


    }

    //for some reason it keeps on goes to handle error and display the cannot insert data data output.

  • #5
    New Coder
    Join Date
    Jan 2006
    Posts
    73
    Thanks
    2
    Thanked 3 Times in 3 Posts
    I believe it is because of this line:
    PHP Code:
    $sql 'insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])'
    PHP does not process strings inside of single quotes, but it does inside of double quotes. Change occurrences of ' to ".

    So,
    PHP Code:
    $sql "insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])"

  • #6
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    message deleted
    Last edited by awong82; 06-20-2010 at 12:56 AM. Reason: delete message

  • #7
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i tried switching the double quote and the single quote but it is still the same, cannot insert data in the table

  • #8
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    the reason its not working is because you have it checking $name when $name hasn't even been set.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #9
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hello again, i manage to debug the code and found out that the problem was the insert statement, where instead of using

    insert into tablename(field1, field2, field3) values (array[],array[],array[]);

    it is suppose to be

    insert into tablename(field1,field2,field3) values("'.array[].'","'.array[].'","'array[].'");

    even so i m still trying to understand what does "'.'" means?

    Thank you for your help again.

  • #10
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    Why not just do this

    PHP Code:
    $sql mysql_query("INSERT INTO test(id,name,mydate) VALUES('{$id[$i]}','{$name[$i]}','{$date[$i]}')"); 
    And the '..' is only used to pass php vars into a string when using single quotes around the string but if your using double quotes you dont need to do that.

    for example.
    print 'hello $name'; Wrong.
    print 'hello '.$name; Right.

    and with double quotes and array values.
    print "hello $items['name']"; Wrong.
    print "hello $items[name]"; Right.
    or
    print "hello {$items['name']}"; Right.
    Last edited by DJCMBear; 06-20-2010 at 08:00 PM.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #11
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks djmbear


  •  

    Posting Permissions

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