[SOLVED] Problem adding html data to a mysql table using php

dkost · April 24, 2008

I have a demo site that I want to periodically reset the DB. I have 2 problems using the data that was exported from the MYSQL db. The first problem is that I'm getting syntax errors, specifically unexpected T_STRING and also the # signs used in the color statements are acting like comments.

I'm pretty new to this so any help will be greatly appreciated.

d...

Here's a snippet of the code:

/* add a table */
   $query = " CREATE TABLE IF NOT EXISTS _announcement (
   `ID` int(11) NOT NULL auto_increment,
  `Date` datetime default NULL,
  `Announcement` mediumtext,
  `EmailNotification` varchar(50) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error.....<p>" .mysql_error() );
   
   /* add data to table */
$query = "INSERT INTO _announcement (`ID`, `Date`, `Announcement`,  `EmailNotification`) VALUES
(1, '2007-08-21 00:00:00', '<div align="center"><span style="font-size: 18pt; color: rgb(255, 69, 0);"><span style="font-size: 12pt; color: rgb(255, 0, 0); font-family: Arial;"><span style="font-size: 12pt; color: rgb(255, 0, 0); text-decoration: underline;"><strong>Announcements will go here....</strong></span></span></span></div>\r\n<div align="center"><span style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Arial;"> </span></div>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">To modify this page simply select the "<strong>CCS</strong>" button above and select "<strong>Announcements</strong>" then select edit. A graphical editor will display this page within the edit window and your ready to make your updates!</font></p>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">[<a href="http://xyz.com/flash_demos.htm" target="_blank"><em><strong><font color="#0000ff">Flash Demo Page</font></strong></em></a>]</font></p>', '0')";

	if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error.....<p>" .mysql_error() );

April 24, 2008

To fix the # part, just adda \ in front of it: \#, making it a literal character.

Check your <div align="center"> and the code after that.

EDIT: Figured I would fix it for you:

$query = <<<ENDOFTEXT
INSERT INTO _announcement (`ID`, `Date`, `Announcement`,  `EmailNotification`) VALUES
(1, '2007-08-21 00:00:00', '<div align="center"><span style="font-size: 18pt; color: rgb(255, 69, 0);"><span style="font-size: 12pt; color: rgb(255, 0, 0); font-family: Arial;"><span style="font-size: 12pt; color: rgb(255, 0, 0); text-decoration: underline;"><strong>Announcements will go here....</strong></span></span></span></div>\r\n<div align="center"><span style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Arial;"> </span></div>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">To modify this page simply select the "<strong>CCS</strong>" button above and select "<strong>Announcements</strong>" then select edit. A graphical editor will display this page within the edit window and your ready to make your updates!</font></p>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">[<a href="http://xyz.com/flash_demos.htm" target="_blank"><em><strong><font color="#0000ff">Flash Demo Page</font></strong></em></a>]</font></p>', '0')
ENDOFTEXT;

Try that. When you do it like that, you do not have to make the (") literal characters.

dkost · April 24, 2008

Thanks, that fixed the # part, but now it seems I get an unexpected $end error on the closing ?>. Also I'm using phpDesigner to write this code and all the comments and code after the ENDOFTEXT is not highlighted anymore. Comments and functions should be different colors.

thanks.

April 24, 2008

Instead of doing the ENDOFTEXT, just put a \ in front of every quote. I do not know why that messed up the code, but this would be the easy solution.

dkost · April 25, 2008

still no luck. The data that's being inserted already has \'s because of the html markup. Is there a way to escape the whole query so that php doesn't pay attention to the content but mysql will?

soycharliente · April 25, 2008

Try bringing out the HTML to see if the error moves to it instead of the query.

<?php
$html = '<div align="center"><span style="font-size: 18pt; color: rgb(255, 69, 0);">
<span style="font-size: 12pt; color: rgb(255, 0, 0); font-family: Arial;">
<span style="font-size: 12pt; color: rgb(255, 0, 0); text-decoration: underline;">
<strong>Announcements will go here....</strong></span></span></span></div>\r\n
<div align="center"><span style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Arial;"> </span>
</div>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">
To modify this page simply select the "<strong>CCS</strong>" button above and select
"<strong>Announcements</strong>" then select edit. A graphical editor will display this
page within the edit window and your ready to make your updates!</font></p>\r\n<p>
<font size="2" face="Lucida Sans Unicode" color="#666666">
[<a href="http://xyz.com/flash_demos.htm" target="_blank">
<em><strong><font color="#0000ff">Flash Demo Page</font></strong></em></a>]</font></p>';

$query = "INSERT INTO `_announcement`
(`ID`,`Date`,`Announcement`,`EmailNotification`)
VALUES (1, '2007-08-21 00:00:00', '$html', '0')";
?>

Also, is your date field actually of datatype DATE? Because you have a DATETIME trying to be passed in.

dkost · April 25, 2008

When I bring it out I now get a T_CONSTANT_ENCAPSED_STRING error.

The date field is set as DATETIME.

soycharliente · April 25, 2008

Which line?

soycharliente · April 25, 2008

T_CONSTANT_ENCAPSED_STRING parse errors suggest that you forgot to put a string inside quotes. The thing to remember is that although error messages can be very helpful, the line number they give indicates where the parser first realized that there was a problem. This may or may not be the line that contains the actual error.

It might help to post a bit more code if you can. Looking at the code you've posted and the code that has been provided, I don't see any errors.

Also, when saying that you've got an error, even after changing your code to something someone has suggested, it's always a good idea to repost what code you're working with. We don't know exactly how you took the advice that was given.

dkost · April 25, 2008

Ok, here's the section of code I've been playing with. There are quite a few more tables that have html data in them, so I cut the code down to only deal with one of them till I can figure it out. This version has the html code pulled out into the variable $html as suggested.

Again the purpose of this code is to reset a db being used as a demo. Some tables can be truncated, while some will need the original data restored.

Thanks for your help.

<?php

/**
* @author Don Kost
* @copyright 2008
*/
  
  // connect local for testing
   $link = mysql_connect("localhost", "root","") or die("Could not connect : " . mysql_error());

/* select db */
   $DB = "ccs100_demo";
   mysql_select_db($DB) or die ("Database $DB not selected.." . mysql_error());

   
   /* truncate a table */
   /* table array to truncate */
   $truncate_table = array("calendar", "_email_notification", "_fields", "_main", "_sms_notification", "_teams");
   foreach ($truncate_table as $table){
    $query = "TRUNCATE $table";
    if ( ! mysql_query( $query, $link) )
    die ( "MySQL error.....<p>" .mysql_error() );
   }
   
   /* drop tables */
   /* table array to drop */
   $drop_table = array("category","choices", "globals", "poll", "_announcement", "_config", "_contact_us", "_frontpage", "_header", "_links", "_photo_album", "_player_bio", "_player_progress", "_progress_report_settings", "_recognition", "_site_info", "_staff", "_users");
   foreach ($drop_table as $table){
    $query = "DROP TABLE IF EXISTS $table";
    if ( ! mysql_query( $query, $link) )
    die ( "<p>MySQL error.....<p>" .mysql_error() );
   }
   
   /* add a table */
   $query = " CREATE TABLE IF NOT EXISTS category (
  `id` int(11) NOT NULL auto_increment,
  `Category` varchar(50) default NULL,
  `Color` varchar(255) default NULL,
  `GroupID` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4";

   if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 1.....<p>" .mysql_error() );
   
   /* add data to table */
$query = "INSERT INTO category (`id`, `Category`, `Color`, `GroupID`) VALUES 
(1, 'Game', '#EE1C25', 'admin'),
(2, 'Practice', '#0054A5', 'admin'),
(3, 'Fund Raiser', '#FFC20F', 'admin')";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 2.....<p>" .mysql_error() );
  
  /* add a table */
   $query = " CREATE TABLE IF NOT EXISTS choices (
   `id` int(11) NOT NULL auto_increment,
  `pollid` int(11) NOT NULL default '0',
  `choice` varchar(255) default NULL,
  `barcolor` varchar(20) default NULL,
  `votes` int(11) default '0',
  PRIMARY KEY  (`id`),
  KEY `pollid` (`pollid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Simple Poll' AUTO_INCREMENT=7";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error.....<p>" .mysql_error() );
   
   /* add data to table */
$query = "INSERT INTO choices (`id`, `pollid`, `choice`, `barcolor`, `votes`) VALUES 
(1, 1, 'Baseball', 'Red', 0),
    (2, 1, 'Basketball', 'Aqua', 0),
    (3, 1, 'Soccer', 'Blue', 0),
    (4, 1, 'Lacrosse', 'Black', 0),
    (5, 1, 'Football', 'Orange', 0),
    (6, 1, 'Volleyball', 'Green', 0)";
    
    if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 3.....<p>" .mysql_error() );
   
   /* add a table */
   $query = " CREATE TABLE IF NOT EXISTS globals (
   `id` int(11) NOT NULL auto_increment,
  `TimePeriod` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2";

   if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 4.....<p>" .mysql_error() );
   
   /* add data to table */
$query = "INSERT INTO globals (`id`, `TimePeriod`) VALUES 
(1, 15)";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 5.....<p>" .mysql_error() );

/* add a table */
   $query = " CREATE TABLE IF NOT EXISTS poll (
   `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `question` varchar(255) default NULL,
  `bgcolor` varchar(20) default NULL,
  `fontsize` varchar(10) default NULL,
  `font` varchar(50) default NULL,
  `fontcolor` varchar(20) default NULL,
  `orientation` varchar(100) default NULL,
  `tbgcolor` varchar(20) default NULL,
  `tfontsize` varchar(10) default NULL,
  `tfontcolor` varchar(20) default NULL,
  `tfont` varchar(50) default NULL,
  `votes` int(11) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Simple Poll' AUTO_INCREMENT=2";

   if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 6.....<p>" .mysql_error() );
   
   /* add data to table */
$query = "INSERT INTO poll (`id`, `name`, `question`, `bgcolor`, `fontsize`, `font`, `fontcolor`, `orientation`, `tbgcolor`, `tfontsize`, `tfontcolor`, `tfont`, `votes`) VALUES 
(1, 'Favorite Sport', 'What is your favorite sport?', '#dfe6ef', '4', '', '#336699', '', '#6598b7', '3', 'White', '', 0)";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 7.....<p>" .mysql_error() );
   
   /* add a table */
   $query = " CREATE TABLE IF NOT EXISTS _announcement (
   `ID` int(11) NOT NULL auto_increment,
  `Date` datetime default NULL,
  `Announcement` mediumtext,
  `CreatedByPHPRunner` int(11) default NULL,
  `EmailNotification` varchar(50) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2";

if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error 8.....<p>" .mysql_error() );
   
   $html = '<div align="center"><span style="font-size: 18pt; color: rgb(255, 69, 0);"><span style="font-size: 12pt; color: rgb(255, 0, 0); font-family: Arial;"><span style="font-size: 12pt; color: rgb(255, 0, 0); text-decoration: underline;"><strong>Announcements will go here....</strong></span></span></span></div>\r\n<div align="center"><span style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Arial;"> </span></div>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">To modify this page simply select the "<strong>Coach''s Corner</strong>" button above and select "<strong>Announcements</strong>" then select edit. A graphical editor will display this page within the edit window and your ready to make your updates!</font></p>\r\n<p><font size="2" face="Lucida Sans Unicode" color="#666666">[<a href="http://coachscornersports.com/flash_demos.htm" target="_blank"><em><strong><font color="#0000ff">Flash Demo Page</font></strong></em></a>]</font></p>';
   
         
   /* add data to table */
$query = "INSERT INTO _announcement (`ID`, `Date`, `Announcement`, `CreatedByPHPRunner`, `EmailNotification`) VALUES
(1, '2007-08-21 00:00:00','$html' , NULL, '0')";



	if ( ! mysql_query( $query, $link) )
   die ( "<p>MySQL error.....<p>" .mysql_error() );


?>

soycharliente · April 25, 2008

Can you show the whole error message as well is distinguish which line is the one from the error message?

soycharliente · April 25, 2008

Try this where it is:

Coach\'s Corner

dkost · April 25, 2008

thanks!

Since there were 2 quotes i had to double escape it:

Coach\'\'s Corner

Also this only works if the html data is broken out into a variable. If left in the query still has syntax issues.

I hope the data for the rest of the tables have similar problems.

Thanks again for your help.

soycharliente · April 27, 2008

Why are there 2 quotes? Did you get the data from a MySQL dump? I'm just saying because grammatically 2 quotes don't make sense.

dkost · April 28, 2008

If you look at the code you'll see this:

"<strong>Coach''s Corner</strong>"

Everywhere that there is a ' there are always 2.

Yes this was a Mysql dump.

Once I double escaped the ', it works great. The demo is being reset every night.

Thanks for your help, much appreciated.

Sign In

[SOLVED] Problem adding html data to a mysql table using php

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information