SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting array values with foreach

    Hi there,
    Forgive me if my question has been answered somewhere else in the forum(I haven't found it yet though):
    I am trying to insert array values into mysql with a foreach loop. The table events contains service events (student reporting that his laptop screen is broken and that the computer is virus infected. I want to insert an event into the event table, and insert the errors (one or more) in an error table, using the newly created eventid in the error table AND giving each error its own field in the table. In other words, I do not want to implode the array into a string an insert it in one field.
    Both inserts run ok and the array prints out fine with var_dump, but only the last value in the array gets inserted into the database. What am I doing wrong?


    Regards, terjew


    This is my script:
    $sql = "INSERT INTO events SET
    serial='$serial,
    studid='$studid',
    descr='$descr,
    startdato=CURDATE()";

    if (isset($_POST[error])) {
    $error= $_POST[error];
    } else {
    $error = array();
    }

    foreach ($error as $et => $etype) {
    $sql2 = "insert into errors
    set eventid = last_insert_id(),
    errortype= '$etype'";


    DROP TABLE IF EXISTS `comp`.`errors`;
    CREATE TABLE `comp`.`errors` (
    `errorid` int(10) unsigned NOT NULL auto_increment,
    `eventid` varchar(45) NOT NULL,
    `descr` varchar(45) default NULL,
    `errortype` varchar(45) NOT NULL,
    PRIMARY KEY USING BTREE (`errorid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


    DROP TABLE IF EXISTS `comp`.`events`;
    CREATE TABLE `comp`.`events` (
    `eventid` int(10) unsigned NOT NULL auto_increment,
    `startdate` datetime default NULL,
    `serial` varchar(50) default NULL,
    `studid` int(10) unsigned default NULL,
    `descr` varchar(500) default NULL,
    PRIMARY KEY (`eventid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1188 DEFAULT CHARSET=latin1;

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    After you insert each error into the errors table, last_insert_id() contains the ID of the error you just inserted, not the event. You should store it in a variable before the loop so you can use it with each error.

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The rest of the code is missing, can you post the complete code?

    By the way, here's an idea of what you can do:
    PHP Code:
    $InsertEvent SPrintF("INSERT INTO events SET serial='%s', studid='%s', descr='%s', startdato=CURDATE()"MySQL_Real_Escape_String($serial), MySQL_Real_Escape_String($studid), MySQL_Real_Escape_String($descr));
    mysql_query($InsertEvent);
    if(
    array_key_exists('error'$_POST) && is_array($_POST['error'])){
        
    $Last mysql_insert_id();
        
    $InsertErrors "INSERT INTO errors (eventid, errortype) VALUES";
        
    $Delimiter '';
        foreach (
    $_POST['error'] as $EventType){
            
    $InsertErrors .= SPrintF('%s (%s, %s)'$Delimiter$LastMySQL_Real_Escape_String($EventType));
            
    $Delimiter ',';
        }
        
    mysql_query($InsertErrors);

    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    SitePoint Member
    Join Date
    May 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    arkinstall - your suggestion was perfect. It worked the way I wanted it to.
    Thank you both for ultra fast response:-)
    terjew

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again,
    working a little too good, my script now makes two identical inserts in the error table. This runs twice:
    "INSERT INTO feil (eventid, feiltype) VALUES (1224, 1), (1224, 2)"

    The event insert is ok. Any ideas why this happens?



    terjew


    Here's the script (without the form, though). I did'nt translate it, hope it doesn't matter:


    <?php require 'secure.inc.php';
    $page_title='New event';
    include ('dbconn.inc.php');
    include ('meny.inc.php');


    if (isset($_POST['tilstand'])):

    $serienr= $_POST['serienr'];
    $elevid= $_POST['elevid'];
    $kommentar=$_POST['kommentar'];
    $tilstand=$_POST['tilstand'];
    $ekstern=$_POST['ekstern'];
    $beskrivelse=$_POST['beskrivelse'];
    $garanti=$_POST['garanti'];
    $faktura=$_POST['faktura'];
    $forsikring=$_POST['forsikring'];
    $feil=$_POST['feil[]'];

    $InsertEvent = SPrintF("INSERT INTO hendelser SET
    serienr='%s',
    elevid='%s',
    kommentar='%s',
    tilstand='%s',
    ekstern='%s',
    garanti='%s',
    faktura='%s',
    forsikring='%s',
    startdato=CURDATE()",

    MySQL_Real_Escape_String($serienr),
    MySQL_Real_Escape_String($elevid),
    MySQL_Real_Escape_String($kommentar),
    MySQL_Real_Escape_String($tilstand),
    MySQL_Real_Escape_String($ekstern),
    MySQL_Real_Escape_String($garanti),
    MySQL_Real_Escape_String($forsikring),
    MySQL_Real_Escape_String($faktura));

    mysql_query($InsertEvent);

    if(array_key_exists('feil', $_POST) && is_array($_POST['feil'])){
    $Last = mysql_insert_id();
    $InsertErrors = "INSERT INTO feil (eventid, feiltype) VALUES";
    $Delimiter = '';
    foreach ($_POST['feil'] as $feiltype){
    $InsertErrors .= SPrintF('%s (%s, %s)', $Delimiter, $Last, MySQL_Real_Escape_String($feiltype));
    $Delimiter = ',';
    }

    mysql_query($InsertErrors);
    }


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
  •