1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

php mysql form insert more than one record at once whats the best way?

Discussion in 'PHP' started by xbat, Jun 9, 2015.

  1. #1
    I have a form.

    Name
    SEMrush
    option1
    option2
    option3
    etc... all the way up to option 100

    When I insert the data into mysql I want to create a separate record for each option filled out. And if no option is filled out for option2 option3 ect.. it does not insert a row for that.

    What is my best choice of going about this a foreach?
     
    Solved! View solution.
    xbat, Jun 9, 2015 IP
    SEMrush
  2. #2
    You could append the values you want to insert into a sql statement in your loop and then run your statement after that.

    
    INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ),( Value1, Value2 ),...
    
    PHP:
     
    Anveto, Jun 9, 2015 IP
    ThePHPMaster likes this.
  3. xbat

    xbat Active Member

    Messages:
    280
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #3
    Is that more efficient?

    If it helps any... Pretty much its going to look like this in the database.. I just wasn't sure what the most effcient or best way was. Thanks

    name - option1
    "" - option2
    "" - option3
    nextname - option1
    nextname -option2
     
    xbat, Jun 9, 2015 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,602
    Likes Received:
    711
    Best Answers:
    150
    Trophy Points:
    420
    #4
    You should rethink your database. I dunno what you're registering, but a better way would be separate tables for users (if you have more than just name) and one for all the options in one row(1-100) linked via an unique id.
     
    PoPSiCLe, Jun 9, 2015 IP
    Anveto likes this.
  5. xbat

    xbat Active Member

    Messages:
    280
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #5




    I completely agree with you. You have noooooo idea how bad I agree with you. And after reading your post a few days a quote stuck with me " you are the warden of the prison lets face you run the show but even though they are the people who pay you, you can either let the prisoners run the show or you can be the warden" So I have thought about it for a few days and I think I am going to go over the project managers head and just do things correctly." Because in all honesty what happens is they know somethings but they do not understand everything, and there not exactly the type that will ask for help when they should. And they have been calling the shots and get so upset because the bandied fixes or building, and how long it takes because of how they wanted things built before(In other words not to much future planning). But in the long run it takes so much longer because of all the half **sing. (In other words use the horrible piece of software or use that and them copy and paste sort of thing) I am sure you get the idea. Then what happens so much of the time is someone will make a mistake then I end up spending hours of trying to find out what went wrong because the first person who made the mistake trys to delete it. It was the same concept of logging all the records when someone inserts a record. Ha! anyways I am doing to do it that way, because it will save time in the future they are just not focused enough to see into the next few months once there databases get so bloated and they get mad because they are slowing down because of the type of the structure they wanted in the first place. (Its been like that for years at this one place and it drives me nuts!) I am going to take charge. Thank you if you wouldn't have said that I would probably have never thought of the warden story.
     
    xbat, Jun 13, 2015 IP
  6. xbat

    xbat Active Member

    Messages:
    280
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #6
    I went over the project managers head and just said screw so we can avoid stuff down the road. And then I used this code.

    
    block=$_POST['block'];
    $num=$_POST['num'];
    $num2id=$_POST['num2id'];
    
    for($i=0;$i<=count($block);$i++) {
    if (!empty($block[$i])){
    $query3 = $dbi->prepare('INSERT INTO blockit (`num`,`num2ud`,`block_#`)  VALUES (?,?,?)');
    $query3->bindValue(1, $num);
    $query3->bindValue(2, $num);
    $query3->bindValue(3, $block[$i]);
    
    $query3->execute();
    }
    }
    
    
    PHP:
     
    xbat, Jun 16, 2015 IP
  7. deathshadow

    deathshadow Acclaimed Member

    Messages:
    8,389
    Likes Received:
    1,495
    Best Answers:
    220
    Trophy Points:
    515
    #7
    You actually missed a fun detail of how prepared queries work. Prepare and bind can be done BEFORE the loop. Also, shouldn't that second bind be $num2id? I'd also suggest NOT wasting time on "variables for NOTHING" and use bindParam instead of bindValue. BindValue is static, it stores the value fixed. BindParam lets you change that variable's value and it will follow-through in the query. Also, foreach? USE IT.

    I'm assuming you're using PDO since there is no bindValue in mysqli.

    
    $stmt = $dbi->prepare('
    	INSERT INTO blockit (
    		num, num2ud, block_#
    	) VALUES (
    		?, ?, ?
    	)
    ');
    $stmt->bindValue(1, $_POST['num']);
    $stmt->bindValue(2, $_POST['num2id']);
    $value = ''; // must be non-null / set for bind to work
    $stmt->bindParam(3, $value);
    
    foreach ($_POST['block'] as $value) if (!empty($value)) $stmt->execute();
    Code (markup):
    Untestest, but should work. Using bindParam makes PDO look at $value every time instead of using a fixed value.

    That's the beauty of prepared queries and bound parameters, you prepare and bind once, then execute multiple times.

    -- edit -- I'd probably also throw a check to make sure $_POST['block'] isset and is_array

    -- edit edit -- Might also be good to cross-check against a whitelist of valid values since one shouldn't blindly trust $_POST.
     
    Last edited: Jun 17, 2015
    deathshadow, Jun 17, 2015 IP
  8. xbat

    xbat Active Member

    Messages:
    280
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #8
    thank you very much even better! :)
     
    xbat, Jul 14, 2015 IP