MySql Insert error and PHP Coding

Hi Folks -

  I have a insert statement that is just giving me grief! I am using MySQL 5.6 and using PHP. I echo the actual statement so I can view it. I can copy and paste it into  workbench query form and it inserts just fine. But not in my PHP coding.
I have verified the values of the variables exist and have correctly picked up what they should have from the form. All are integer except for the very last field, which contains HTML coding. It is to be inserted into the table and is stored in the variable $soffer
By right it should insert since it does in the query form regardless of the HTML formatting. I think the last time I did this was years ago and I actually had to insert a record, return the unique id and then update the field in the record. But thats a long way around it.
Below is the echo results and the error message.
Below that is the coding I use. And below that is that table structure.


Results of echo statement when executed:
INSERT INTO camplibrary
(SupplierRecId,MsSqlSupplierId,CountryRecId,BrandRecId,RatingRecId,TravelRecId,CampMsgBody) values(3,2,1,4,1,7,'link rel="shortcut icon" href="//experts.cachefly.net/images/experts-exchange/favicon.ico" type="image/x-icon" /> ')

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1


 PHP coding in page:
   $sql = "INSERT INTO camplibrary (SupplierRecId,MsSqlSupplierId,CountryRecId,BrandRecId,RatingRecId,TravelRecId,CampMsgBody)
               values($supplerid,$sqlsupplierid,$country,$brands,$rating,$travel,$soffer)";

               if ($databaseConnection->query($sql == TRUE))
                  {
                        echo "RECORD SAVED SUCCESFULLY";
                   } else {
                         echo "ERROR FAILED TO INSERT RECORD";
                         echo "<BR><BR>";
                         echo "ERROR<BR>" .$sql ."<BR><BR>" .$databaseConnection->error;
                   }
             
                mysqli_close($databaseConnection);


Table structure:

Table: camplibrary
Columns:
RecId int(11) AI PK
SupplierRecId int(11)
MsSqlSupplierId int(11)
CountryRecId int(11)
BrandRecId int(11)
RatingRecId int(11)
TravelRecId int(11)
CampMsgBody varchar(5000)
RecCreateDate datetime


Any help would be appreciated...thanks!
OverthereAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
>> values($supplerid,$sqlsupplierid,$country,$brands,$rating,$travel,$soffer)";

what's the value of $soffer ? can you give us an example? does it comes with the single quotes at the front and back?
0
Dave BaldwinFixer of ProblemsCommented:
You might try this.  I often have to put in a NULL value for the AI int.  And some spaces to make it easier to read.
$sql = "INSERT INTO camplibrary (RecId, SupplierRecId, MsSqlSupplierId, CountryRecId, BrandRecId, RatingRecId, TravelRecId, CampMsgBody) VALUES (NULL, $supplerid, $sqlsupplierid, $country, $brands, $rating, $travel, $soffer)";

Open in new window

0
Ray PaseurCommented:
I set up a test case using this code and it worked correctly for me.  Some examples showing how to use MySQL with the currently supported PHP extensions can be found in this article.  Most of them should work with copy / paste.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

<?php // demo/temp_overthere.php

/**
 * http://www.experts-exchange.com/questions/28693884/MySql-Insert-error-and-PHP-Coding.html
 *
 * Demonstrate some of the basics of MySQLi
 *
 * References for using PHP and MySQL(i)
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */


// RAISE THE ERROR REPORTING LEVEL TO THE HIGHEST POSSIBLE SETTING
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE camplibrary
( RecId            INT            NOT NULL AUTO_INCREMENT PRIMARY KEY
, SupplierRecId    INT            NOT NULL DEFAULT 0
, MsSqlSupplierId  INT            NOT NULL DEFAULT 0
, CountryRecId     INT            NOT NULL DEFAULT 0
, BrandRecId       INT            NOT NULL DEFAULT 0
, RatingRecId      INT            NOT NULL DEFAULT 0
, TravelRecId      INT            NOT NULL DEFAULT 0
, CampMsgBody      VARCHAR(5000)  NOT NULL DEFAULT ''
, RecCreateDate    DATETIME
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
$supplerid     = 3;
$sqlsupplierid = 2;
$country       = 1;
$brands        = 4;
$rating        = 1;
$travel        = 7;
$raw_soffer    = 'link rel="shortcut icon" href="//experts.cachefly.net/images/experts-exchange/favicon.ico" type="image/x-icon" /> ';

// ESCAPE THE STRING VARIABLE FOR SAFE USE IN A QUERY
$soffer = $mysqli->real_escape_string($raw_soffer);

// CONSTRUCT THE QUERY USING THE VARIABLES
$sql
=
"
INSERT INTO camplibrary
( SupplierRecId
, MsSqlSupplierId
, CountryRecId
, BrandRecId
, RatingRecId
, TravelRecId
, CampMsgBody
)
VALUES
( '$supplerid'
, '$sqlsupplierid'
, '$country'
, '$brands'
, '$rating'
, '$travel'
, '$soffer'
)
"
;
echo $sql . PHP_EOL;

// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id  = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
echo PHP_EOL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

OverthereAuthor Commented:
Thank you for responding. I am making progress, but still no go.
The statement:
"$soffer = $mysqli->real_escape_string($raw_soffer);
 caused my coding to abort.

I used the alternative: mysqli_escape_string($raw_soffer);

But neither worked.
 I did verify once again that I am retrieving values from my form and they are correct.
I believe the problem is centered around single and double quotes within the html coding.
That being my thought, I constructed the following line of coding :

    $goodoffer = htmlentities($rawoffer,ENT_QUOTES,"UTF-8",TRUE);

which does, when I view the source code of my page,  what it is suppose to: replace single and double quotes with &quote etc. I
 have attached a screen shot of the source after executing my page.
When I enclosed my variables with single quotes in my insert statement it places double quotes around the numbers and when copying it and pasting into query form (workbench), it blows, remove double quotes and no problem. I am using PHPv5.3 if that helps.
here is the coding: (and I used the code button!)
<?php 
    require_once ("Includes/db-config.php"); 
    require_once ("Includes/connectDB.php");
    include("Includes/session.php");
    $baderr= "";

if (isset($_POST['submit']))
  
    {
      
        // intialize 
        $baderr= "";

  // section to pull values from form  
       $country = $_POST["country"];
       
       $travel = $_POST["travel"];
  
       $rating = $_POST["rating"];
       
       $brands = $_POST["brands"];
 
   // pulling html code pasted into textarea

    $rawoffer = $_POST['supoffers'];
 
   $goodoffer = htmlentities($rawoffer,ENT_QUOTES,"UTF-8",TRUE);
    echo "<BR><BR>";
    echo "value of goodoffer after htmlentities commanmd";
    echo "<BR><BR>";
    echo  $goodoffer;
    echo "<BR><BR>";

    $supplerid = $_SESSION["SupplierRecId"];
 
    $sqlsupplierid = $_SESSION["MsSqlSupplierId"];
     
// section coded out for now 
      //check to make sure they have chosen at least one tag
     // if ($country == 0 and $travel == 0 and $rating == 0 and $brands == 0)
      //    {  
        //       $baderr = "YOU MUST SELECT AT ATLEAST ONE TAG FOR SEARCHING";
        //  }
 // end of section



 //  insert a record section
           // if they have chosen an option then insert record
               $sql = "insert into camplibrary(SupplierRecId,MsSqlSupplierId,CountryRecId,BrandRecId,RatingRecId,TravelRecId,CampMsgBody)
               values($supplerid,$sqlsupplierid,$country,$brands,$rating,$travel,'$goodoffer')";

               if ($databaseConnection->query($sql == TRUE))
                  {
                        echo "RECORD SAVED SUCCESFULLY";
                   } else {
                         echo "ERROR FAILED TO INSERT RECORD";
                         echo "<BR><BR>";
                         echo "ERROR<BR>" .$sql ."<BR><BR>" .$databaseConnection->error;
                   }
              
                mysqli_close($databaseConnection);
               
    } else {
           
           // echo "<h7 style='color:'red';alignment-adjust: 'middle'>$baderr</h7>";
    }
?>
  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
    
   <head>
      <title>Save Offer to Library</title>
     <link href="Styles/StyleSheet.css" rel="stylesheet" type="text/css" /> 
    </head>
     
    <body class="bodybg">
<form action="savecampaign.php" method="post">
    <br><br>
        <table align="center">
           <tr><td>Select any of the following tags to be for searching later.<br>You MUST choose atleast one.</br:>:</td></tr>
            <tr><td colspan="2">  <?php if (isset($_POST['submit']))
                       echo "<h7 style='color:#e80c4d;'>$baderr</h7>"; ?></td></tr>
            <tr><td align="right">Country:</td>
                <td align="left">
                <select name="country">
                <option value="0" selected>Choose One</option>
        
                      <?php
                         $query = "SELECT * FROM Countries";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?php echo $row["RecId"];?>"><?php echo $row["Country"];?></option>

                             <?php  }  ?>
                 </select>
                                 <?php// mysqli_close($databaseConnection);?>
           
                                     
                 </td>                                
                 <td align="right">Travel Type:</td>
                 <td align="left">
                 <select name="travel">
                <option value="0" selected>Choose One</option>
        
                      <?php
                         $query = "SELECT * FROM traveltypes order by TravelDesc";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?php echo $row["RecId"];?>"><?php echo $row["TravelDesc"];?></option>

                             <?php  }  ?>
                               </select>
                                 <?php //mysqli_close($databaseConnection);?>
             </td></tr>
            <tr><td colspan="2">&nbsp;</td></tr>
            <tr><td align="right">Response Rating:</td>
                     <td align="left">
                      <select name="rating">
                     <option value="0" selected>Choose One</option>
        
                      <?php
                         $query = "SELECT * FROM ratings";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?php echo $row["RecId"];?>"><?php echo $row["Rating"];?></option>

                             <?php  }  ?>
                               </select>
                                 <?php //mysqli_close($databaseConnection);?>
             </td>
            
            <td align="right">Brands:</td></td>
                     <td align="left">
                     <select name="brands">
                    <option value="0" selected>Choose One</option>
        
                      <?php
                         $query = "SELECT * FROM brands order by BrandName";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?php echo $row["RecId"];?>"><?php echo $row["BrandName"];?></option>

                             <?php  }  ?>
                               </select>
                                 <?php mysqli_close($databaseConnection);?>
            </td></tr>
        </table>
        <br><br>
        <table align="center">
            <tr><td align="center">Paste HTML Code in textbox below</td>
            <td align="left">
                <input type="submit" name="submit" value="Save" src="images/Save.jpg"/>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <input type="submit" name="cancel" value="Cancel" src="images/Cancel.jpg"/>
                </td>
            </tr>
            <tr><td colspan="2"><textarea name="supoffers" cols="80" rows="20"></textarea></td></tr>
        </table>

    </form>

    
    </body>
</html>

Open in new window


I am entirely frustrated at this problem.
Insert-error.JPG
0
Ray PaseurCommented:
OK, couple of things.

PHP 5.3 is obsolete.  You can find the current versions of PHP on the home page.  Look on the right where it says, Download.
http://php.net/

PHP has comparison operators that are specific to certain data types.  The double-equal sign is not appropriate for comparisons to TRUE and FALSE; you want to use the triple equal sign for these.

The statement:
"$soffer = $mysqli->real_escape_string($raw_soffer);
 caused my coding to abort.
PHP scripts do not "abort" without a reason, and PHP usually produces a message of some sort.  We would be better able to help if you show us that message and the line of code that generated it.  If that statement worked correctly in my script but failed in your script, there may be some other differences in the data.  Here are a couple of things you can do to visualize the data and understand how it is being used.

You can use var_dump() to print out the contents of variables.  You may want to use "view source" to confirm what you see in the output from var_dump().

You can test the return values from function calls and raise a notice when the values are not as expected.  If you're not 100% sure what to expect from a function call, use PHP.net to look up the function, and read what it does.

It might also be useful to see the contents of these scripts:

    require_once ("Includes/db-config.php");
    require_once ("Includes/connectDB.php");
    include("Includes/session.php");
0
OverthereAuthor Commented:
You are so good and I am so proud of myself but embarrassed to tell you what I did! And I know better - I ain't a newbie at developing! But it's been awhile...a tad bit rusty here...
   Here's what the problem was: further down in my coding after loading the listboxes, I had closed my database connection - duh!  
    I did upgrade my PHP to PHP 5.5 -  and tore my page apart - I have made only the database  name, username and etc. as an include. And now on each page that needs a  database connection, I  create it along with a more detailed error message which is what I always used to do in previous languages etc.
     You also pointed out something to me - I need to study more on PHP and study harder.  Your willingness to share knowledge is appreciated.
0
OverthereAuthor Commented:
Thank you ...much appreciated...
0
Ray PaseurCommented:
Thanks for the points and thanks for using E-E.

If you want to see the best thing to come along in the last two decades of PHP, learn about the Laravel framework.  It replaces configuration with convention in many wonderful ways.  You will find your web application development is done in a fraction of the time it takes to write and debug PHP scripts.
http://laravel.com/

Best regards, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.