#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    214
    Rep Power
    96

    Debugging mysqli prepared statement insert


    Sorry for what may be a really easy question. I've been out of the loop for nearly 3 years and am rusty as can be...

    The short question is I need to see the errors for why my insert statement isn't working. Here's my code. i'm including the first routine that reads the CSV file so you know the data is there.

    PHP Code:
    DCI 'Long_Desc_Master_File.csv';
    if (
    file_exists($DCI) && is_readable($DCI)) {
        
    $DCIImport file($DCI);
        
    //loop through array
        
    for ($i 0$i count($DCIImport); $i++) {
            
    //separate each element
            
    $tmp explode(","$DCIImport[$i]);
            
    //assign each element of temp array to new array
            
    $DCIImport[$i] = array(
                
    'PartNo' => trim($tmp[0]),
                
    'WebDescNote' => $tmp[1],
                
    'Comments' => $tmp[2],
                
    'Photo' => $tmp[3],
                
    'VehicleType' => $tmp[4],
                
    'VehicleMake' => $tmp[5],
                
    'VehicleModel' => $tmp[6],
                
    'VehicleBodyStyle' => $tmp[7],
                
    'VehicleDriveline' => $tmp[8],
                
    'VehicleYearStart' => $tmp[9],
                
    'VehicleYearEnd' => $tmp[10],
                
    'Trademark' => $tmp[11],
                
    'Series' => $tmp[12],
                
    'Category' => $tmp[13],
                
    'SubCategory' => $tmp[14],
                
    'Application' => $tmp[15],
                
    'ShortDesc' => $tmp[16],
                
    'LongDesc' => trim($tmp[41]),
                
    'JobberPrice' => $tmp[17],
                
    'JobberPriceEffDate' => $tmp[18],
                
    'MSRP' => $tmp[19],
                
    '2017EffDate' => $tmp[20],
                
    'DateUpdated' => $tmp[21],
                
    'UPC' => $tmp[22],
                
    'BoxDepth' => $tmp[23],
                
    'BoxWidth' => $tmp[24],
                
    'BoxHeight' => $tmp[25],
                
    'Weight' => $tmp[26],
                
    '24hrShip' => $tmp[27],
                
    'Color' => $tmp[28],
                
    'Carded' => $tmp[29],
                
    'ScheduleBCode' => $tmp[30],
                
    'CountryOfOrigin' => $tmp[31],
                
    'Feature1' => $tmp[32],
                
    'Feature2' => $tmp[33],
                
    'Feature3' => $tmp[34],
                
    'Feature4' => $tmp[35],
                
    'Feature5' => $tmp[36],
                
    'Feature6' => $tmp[37],
                
    'Feature7' => $tmp[38],
                
    'Feature8' => $tmp[38],
                
    'Feature9' => $tmp[40]);
        }
    }
    else {echo 
    "Can't open data file.";}

    $conn dbConnect('query');

    if (!empty(
    $DCIImport)) {
    //    echo "Made it into the Insert Loop!<br><br>";
        
        
    for ($j 1    $len count($DCIImport), $j $len$j++) {
    /*        echo "The Count equals $j<br><br>";
            print_r($DCIImport[$j]);
            echo "<br><br>";
    */
            
    echo $DCIImport[$j]['PartNo']."<br>";
            
    // insert into database
            
    $insert "INSERT INTO dci (LineItem, PartNo, WebDescNote, Comments, Photo, VehicleType, VehicleMake, VehicleModel, VehicleBodyStyle, VehicleDriveline, VehicleStartYear, VehicleEndYear, Trademark, Series, Category, SubCategory, Application, ShortDesc, LongDesc, JobberPrice, JobberPriceEffDate, MSRP, 2017EffDate, DateUpdated, UPC, BoxDepth, BoxWidth, BoxHeight, Weight, 24hrShip, Color, Carded, ScheduleBCode, CountryOfOrigin, Feature1, Feature2, Feature3, Feature4, Feature5, Feature6, Feature7, Feature8, Feature9) VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            if (
    $stmt $conn->prepare($insert)) {
                
    $stmt->bind_param('ssssssssssssssssssdsdssiiiiissssssssssssss'
                    
    $DCIImport[$j]['PartNo'],
                    
    $DCIImport[$j]['WebDescNote'],
                    
    $DCIImport[$j]['Comments'],
                    
    $DCIImport[$j]['Photo'],
                    
    $DCIImport[$j]['VehicleType'],
                    
    $DCIImport[$j]['VehicleMake'],
                    
    $DCIImport[$j]['VehicleModel'],
                    
    $DCIImport[$j]['VehicleBodyStyle'],
                    
    $DCIImport[$j]['VehicleDriveline'],
                    
    $DCIImport[$j]['VehicleYearStart'],
                    
    $DCIImport[$j]['VehicleYearEnd'],
                    
    $DCIImport[$j]['Trademark'],
                    
    $DCIImport[$j]['Series'],
                    
    $DCIImport[$j]['Category'],
                    
    $DCIImport[$j]['SubCategory'],
                    
    $DCIImport[$j]['Application'],
                    
    $DCIImport[$j]['ShortDesc'],
                    
    $DCIImport[$j]['LongDesc'],
                    
    $DCIImport[$j]['JobberPrice'],
                    
    $DCIImport[$j]['JobberPriceEffDate'],
                    
    $DCIImport[$j]['MSRP'],
                    
    $DCIImport[$j]['2017EffDate'],
                    
    $DCIImport[$j]['DateUpdated'],
                    
    $DCIImport[$j]['UPC'],
                    
    $DCIImport[$j]['BoxDepth'],
                    
    $DCIImport[$j]['BoxWidth'],
                    
    $DCIImport[$j]['BoxHeight'],
                    
    $DCIImport[$j]['Weight'],
                    
    $DCIImport[$j]['24hrShip'],
                    
    $DCIImport[$j]['Color'],
                    
    $DCIImport[$j]['Carded'],
                    
    $DCIImport[$j]['ScheduleBCode'],
                    
    $DCIImport[$j]['CountryOfOrigin'],
                    
    $DCIImport[$j]['Feature1'],
                    
    $DCIImport[$j]['Feature2'],
                    
    $DCIImport[$j]['Feature3'],
                    
    $DCIImport[$j]['Feature4'],
                    
    $DCIImport[$j]['Feature5'],
                    
    $DCIImport[$j]['Feature6'],
                    
    $DCIImport[$j]['Feature7'],
                    
    $DCIImport[$j]['Feature8'],
                    
    $DCIImport[$j]['Feature9']
                    );
                if (!
    $inserted $stmt->execute()) {
                    echo 
    "Error Line $j: ".$stmt->error."<br><br>";
                }
                
    $stmt->close();
                if (!
    $inserted) {$note[] = "There was a problem Uploading the DCI Sheet.";}
                else {
                    
    $note[] = '<span class="red bold">DCI Data Uploaded!</span>';
                }
            }
        }
    }
    else {
        echo 
    "There was a problem getting to the Insert Routine.";

    As you can see I've put in some generic checks to show that I'm actually moving through the routine as expected. My echo statements are working but I'm not getting anything from $stmt->error.

    So, if you can tell me why I'm not getting an error and how to get the error I may have better luck finding why it's not working.

    Thanks,

    Mike
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    give us a chance... echo the sql string that you execute
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    214
    Rep Power
    96
    I can't seem to find out HOW? According to this (https://stackoverflow.com/questions/...ared-statement) you can't because the statement is sent and then the variables are sent alone.

    I've tried echo and print_r is several places with no luck. If you tell me how I'm glad to do it...

    I guess I could try inserting one column at a time and see when it's starts to not work.

    Mike
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    82
    Rep Power
    4
    Are you stating that you get the echoed "Error Line $j: " value without anything for $stmt->error? Actually, that just reminded me, when you bind too many values for the number of placeholders, I have seen the database server/php throw no errors but not execute the query.

    Next, if you enable exceptions for the mysqli statements, the connection and the prepare() and execute() statement will throw an exception that if you let php catch, php will report and display the actual error information, providing that you have php's error_reporting set to E_ALL and display_errors set to ON. To enable exceptions for the php msyqli extension, add the following two lines of code before the point where you are making the database connection -
    PHP Code:
    // note: the $driver variable name used in the following two lines is unique to these two lines of code and don't have any relationship to any other variable used in your code
    $driver = new mysqli_driver();
    $driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT// MYSQLI_REPORT_ALL <- w/index checking; w/o index checking -> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; 
    Lastly, when you use a prepared query, you prepare it once, before the start of any looping. You would also bind an array consisting of empty values. The code inside the loop only populates the bound array variable with each set of data and executes the query.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    214
    Rep Power
    96
    Originally Posted by DSmabismad
    Next, if you enable exceptions for the mysqli statements, the connection and the prepare() and execute() statement will throw an exception that if you let php catch, php will report and display the actual error information, providing that you have php's error_reporting set to E_ALL and display_errors set to ON. To enable exceptions for the php msyqli extension, add the following two lines of code before the point where you are making the database connection -
    PHP Code:
    // note: the $driver variable name used in the following two lines is unique to these two lines of code and don't have any relationship to any other variable used in your code
    $driver = new mysqli_driver();
    $driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT// MYSQLI_REPORT_ALL <- w/index checking; w/o index checking -> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; 
    Lastly, when you use a prepared query, you prepare it once, before the start of any looping. You would also bind an array consisting of empty values. The code inside the loop only populates the bound array variable with each set of data and executes the query.
    Holy crap... I mentioned I was rusty... Seems my eyes are going, too...

    After moving my loop to the correct place and adding that MYSQLI_REPORT_ERROR it threw the error I was looking for: I had an incorrect column name in $insert.

    I think I'll keep that little snippet in ALL my connection files. That certainly helped!

    Pretty sure this issue is solved. I'll be back if I run into further issues.

    Thanks,

    Mike

    P.S. Is there some setting I'm missing. The compose box I'm typing in doesn't wrap lines of type. It gives me a huge left>right scroll bar. Seems pretty dumb...
  10. #6
  11. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    Originally Posted by big0mike
    P.S. Is there some setting I'm missing. The compose box I'm typing in doesn't wrap lines of type. It gives me a huge left>right scroll bar. Seems pretty dumb...

    It is an known issue. It can sometime been fixed by changing the template style at bottom of the page.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    192
    Rep Power
    17
    Originally Posted by DSmabismad
    .... Actually, that just reminded me, when you bind too many values for the number of placeholders, I have seen the database server/php throw no errors but not execute the query...
    Really? I didn't know this. Is it documented someplace? Is there some "max"? Or just some buggy thing?
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    192
    Rep Power
    17
    Originally Posted by MrFujin
    It is an known issue. It can sometime been fixed by changing the template style at bottom of the page.
    Sort of like how Slashdot comments still don't parse UTF-8 correctly.

IMN logo majestic logo threadwatch logo seochat tools logo