asked on

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 " ";
echo "ERROR " .$sql ." " .$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!

Ryan Chong

>> 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?

Dave Baldwin

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

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Overthere

ASKER

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

Ray Paseur

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");

Overthere

ASKER

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.

Overthere

ASKER

Thank you ...much appreciated...

Ray Paseur

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