dcsimg
www.webdeveloper.com
Results 1 to 11 of 11

Thread: create an initial row in mysql

  1. #1
    Join Date
    Sep 2016
    Posts
    85

    create an initial row in mysql

    Hello,
    in phpmyadmin I created an auto-increment table. It looks like this in
    phpmyadmin. This has no initial id, just a blank. Now how can I add the initial row in this table without using codes like this:
    id, col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10, col_11 ......
    Code:
    $sql = "INSERT INTO table (id, col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10, col_11, col_12, col_13, col_14,col_15, col_16, col_17, col_18, col_19, col_20, col_21, col_22, col_23, col_24, col_25, col_26, col_27, col_28, col_29, col_30, col_31, col_32, col_33, col_34, col_35, col_36, col_37, col_38, col_39, col_40, col_41, col_42, col_43, col_44, col_45, col_46, col_47, col_48, col_49, col_50,col_51, col_52, col_53, col_54, col_55, col_56, col_57, col_58, col_59, col_60, col_61, col_62, col_63, col_64, col_65, col_66, col_67, col_68, col_69, col_70, col_71, col_72, col_73, col_74, col_75, col_76, col_77, col_78, col_79, col_80)
    It's just too long to type. Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    21,982
    You can leave out the id column -- assuming that's the auto-incremented primary key. You'll need the rest, however, as it's a good practice to explicitly list the columns, as you should not depend on the database always having the same ordering of the columns.

    If you truly have 80 other columns, my first thought would be to determine if that actually makes sense, or if the DB design needs some re-thinking. If you really do need them for some strange reason, you can programmatically make that list of column names if they're actually all named like that.
    PHP Code:
    $cols = array();
    for(
    $i=1$i<=80$i++) {
      
    $cols[] = "col_$i";
    }
    $sql 'INSERT INTO table '.(implode(', '$cols).') -- rest of query...'
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Sep 2016
    Posts
    85
    " to determine if that actually makes sense" Actually, I am wondering about that, I have a large form in pdf, my thought is that each column represents a blank n the form. I am not sure if there a better way to deal with this.
    And the array is a good idea, as always thanks NogDog.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    21,982
    My first thought is that there would be one table for the general form details, possibly things like timestamp, user ID, anything similar of a general nature. Then there would be a separate relational table that would be for "answers" (or whatever you want to call the form inputs). That table would have a foreign key to the forms table to relate each answer to a specific submission, an identifier for which field is being answered, and then the user-supplied value. The field identifier could, in fact, be a foreign key to yet another "questions" table that is a (mostly) static table that defines information about each field in the form.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  5. #5
    Join Date
    Sep 2016
    Posts
    85
    Hi NogDog, I try to run your code, apparently there is an error on this $sql line
    $sql = 'INSERT INTO table '.(implode(', ', $cols).') -- rest of query...';
    I tried a few different combinations such as
    $sql = 'INSERT INTO table '.(implode(', ', $cols).') .';
    $sql = 'INSERT INTO table '.(implode(', ', $cols).') ';
    and so on. I keep getting Parse error: parse error... on my browser.
    I think it is the .' business but I don't seem to get it right.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    21,982
    Err...the idea was that you would put in the rest of the query that follows the column list. If using PHP PDO, you could use the implode for the value place-holders, as well (warning, totally untested):
    PHP Code:
    $sql "insert into your_table_name_here (
    implode(', '$cols) . "VALUES (
    :" 
    implode(', :'$cols) . ")";
    $stmt $pdo->prepare($sql); 
    Then you would bind all your parameter to the ":xxxx" place-holders, and then finally execute() the statement.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  7. #7
    Join Date
    Sep 2016
    Posts
    85
    My trouble is does a mysql query even allows a php statement?

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    21,982
    Quote Originally Posted by rich1812 View Post
    My trouble is does a mysql query even allows a php statement?
    If you're trying to put it right into the MySQL command line or phpmyadmin SQL screen, then no. I was just brainstorming on an approach within an actual PHP script to do the insert if you have all those columns.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  9. #9
    Join Date
    Sep 2016
    Posts
    85
    Hi Nog Dog, I followed your code, it seems to work fine untill $stmt->execute(); I got an error on php: "Error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound" I can't figur out why that is, the source_tb structur is simple:
    Code:
      id  |   col_1 |   col_2   |......|   col_80   |   dt   |
    From the output I can see all col_1, col_2,...col_80 are in place, I I kept getting an error on php: "Error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound" I can't figur out why that is. I don't understand why "no parameters were bound"

    Code:
    $cols = array();
    for($i=1; $i<=80; $i++) {
      $cols[$i] = "col_$i";
      echo "col is ".$cols[$i]."<br/>";
    }
    
    $sql="INSERT INTO source_tb (
    " . implode(', ', $cols) . "VALUES (
    :" . implode(', :', $cols) . ")";
    $stmt = $conn->prepare($sql);  
    $stmt->execute();
    Thanks again.
    Last edited by rich1812; 03-21-2017 at 09:15 AM.

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    21,982
    Yep, each of those ":xxxxxx" placeholders will need to have a value bound to it (presumably from your form input). It could probably be done using the same looping idea:
    PHP Code:
    $cols = array();
    for(
    $i=1$i<=80$i++) {
      
    $cols[$i] = "col_$i";
      echo 
    "col is ".$cols[$i]."<br/>";
    }
    $values = array();
    foreach(
    $cols as $col) {
      
    $values[":$col"] = isset($_POST[$col]) ? $_POST[$col] : null;

    (I'm assuming your form is using the POST method?)

    Then when you do your execute():
    PHP Code:
    $stmt->execute($values); 
    (Untested )
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  11. #11
    Join Date
    Sep 2016
    Posts
    85
    Hi NogDog, base on your code, I made this code to insert into multiple columns Looks funky but it works!
    Code:
    try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $password);
    //$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, PDO::ERRMODE_EXCEPTION);
    //$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    $min=1;
    $max=5;
    
    $cols=array();
    $colv=array();
    
    for($i=$min; $i<=$max; $i++) {
    
    $cols[$i] = " col_$i, "; if($i==$max) $cols[$i]=" col_$i ";
    $colv[$i] = ":col_$i, "; if($i==$max) $colv[$i]=" :col_$i ";
    $colk[$i] = "\$col_$i ";
    }
    $sql="INSERT INTO $target_tb (".implode('',$cols).")VALUES (".implode('', $colv).");";
    $stmt = $conn->prepare($sql);
    for($i=$min; $i<=$max; $i++) {
    $stmt->bindParam(':col_'.$i, $colk[$i]);
    }
    $colk[1]="hello";
    $colk[2]="my";
    $colk[3]="name";
    $colk[4]="is";
    $colk[5]="mamamia!";
    
    $stmt->execute();
    // use exec() if there is no binding() and no prepare() involved
    $conn->exec($sql);
    
    } // end of try

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles