Inserting HTML into MySQL

Hi,

I've got a dictionary script. You type a word into it and in an ajax dropdown it gives you the correct translation. All the words and their respective translations are stored in the database. I'd like to change some of the translation results and format the output with HTML. When I do that the HTML code won't be parsed.

How can I use HTML (or format the text) in the database?

Here is the sql code that came with the script:

CREATE TABLE `wn_gloss` (
  `synset_id` decimal(10,0) NOT NULL default '0',
  `gloss` varchar(255) default NULL,
  PRIMARY KEY  (`synset_id`),
  FULLTEXT KEY `gloss` (`gloss`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



INSERT INTO `wn_gloss` (`synset_id`, `gloss`) VALUES 
(100001740, 'that which is perceived or known or inferred to have its own distinct existence (living or nonliving)'),
(100002056, 'a separate and self-contained entity'),
(100002342, 'a thing of any kind; "do you have anything to declare?"'),
(100002452, 'a thing of some kind; "is there something you want?"'),
(100002560, 'a nonexistent thing'), and so on....

Code (markup):

and

CREATE TABLE `wn_synset` (
  `synset_id` decimal(10,0) NOT NULL default '0',
  `w_num` decimal(10,0) NOT NULL default '0',
  `word` varchar(50) default NULL,
  `ss_type` char(2) default NULL,
  `sense_number` decimal(10,0) NOT NULL default '0',
  `tag_count` decimal(10,0) default NULL,
  PRIMARY KEY  (`synset_id`,`w_num`),
  KEY `synset_id` (`synset_id`),
  KEY `w_num` (`w_num`),
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 

INSERT INTO `wn_synset` (`synset_id`, `w_num`, `word`, `ss_type`, `sense_number`, `tag_count`) VALUES 
(100001740, 1, 'entity', 'n', 1, 11),
(100002056, 1, 'thing', 'n', 12, 0),
(100002342, 1, 'anything', 'n', 1, 0),
(100002452, 1, 'something', 'n', 1, 0),
(100002560, 1, 'nothing', 'n', 2, 0), and so on

Code (markup):

And this is the lookup .php file for the script:

<?php 


include 'script/config.php';
mysql_connect($sqlhost, $sqluser, $sqlpass);
mysql_select_db($sqldb);
$q = mysql_query('SELECT synset_id FROM wn_synset WHERE word="'.str_replace(' ', '_', $_GET['w']).'" ORDER BY w_num ASC');
$n = mysql_num_rows($q);
if($n) {
  if($n > 1)
    print '<ol>';
  while($r = mysql_fetch_array($q)) {
    if($n > 1)
      print '<li>';
    $q2 = mysql_query('SELECT gloss FROM wn_gloss WHERE synset_id='.$r['synset_id']);
    $r2 = mysql_fetch_array($q2);
    print htmlspecialchars($r2['gloss']);
    $q2 = mysql_query('SELECT synset_id_2 FROM wn_similar WHERE synset_id_1='.$r['synset_id']); 
    $n2 = mysql_num_rows($q2);
    if($n2) {
      print ' <em>';
      $i = 0;
      $syns = array();
      while($r2 = mysql_fetch_array($q2)) {
        $q3 = mysql_query('SELECT word FROM wn_synset WHERE synset_id='.$r2['synset_id_2']);
        $r3 = mysql_fetch_array($q3);
        if(!in_array($r3['word'], $syns)) {
          print ($i?', ':'').str_replace('_', ' ', $r3['word']);
          array_push($syns, $r3['word']);
          $i++;
        }
      }
      print '</em>';
    }

    print '</li>'; 
    
  }
  if($n > 1)
      print '</ol>';
} else
  print '<strong>'.htmlspecialchars(stripslashes($_GET['w'])).'</strong> is not a word.'; 
?>

PHP:

You can see a demo of the script here: http://www.dictionaryscript.com/

Any help is appreciated!

 

Inserting or mixing HTML in the db is not a good choice, I believe. Instead, do it at the client side. What kind of formatting do you need to apply ?

You could also try using tags like bbcode and parse them at client side using javascript.