use mysql database to parse words in javascript/jquery routine

i have a nifty auto tag routine, that parses while you type words it finds and then highlights the words and displays under the div the tag name when it finds a word (see my code so far for Political example - as is). id like to use the full set of tag_names as each section and look all of them up ie political and religious words so if a person types God or politics it will say Religious Political in the (found div) area below the typing div

how can i make this work?


Thank you in advance for any code or help you may provide
Johnny

my code so far
<?
session_start();  // needed for sessions.

include ('../site_config.php');
include ("mysqli_ctx_functions.php");

//****************************************************************
// Filename..: __.php
// Author....: __
// Date......: mm/dd/yyyy
// Purpose...: __
// SQL.......: server/database/tables/tablename
//****************************************************************
// include("filename.php");

//die('stop');

/*
echo "<pre>";
//var_dump($GLOBALS);
var_dump($result);
echo "</pre>";

die('stop');
*/

// load mysqli table of auto_tags
if (connectDb()) {
   $rs = mysqli_query($conn, "SELECT * FROM `auto_tags` limit 0,100");
   //echo getHtmlTable($rs);
}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Mark certain words while typing</title>

<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="http://code.jquery.com/ui/1.11.0/jquery-ui.js"></script>

  <style type='text/css'>
    [contenteditable] {
    padding: 10px;
    border: dotted 1px #aaa;

}
[contenteditable] > div {
    margin: 10px 0;
}
[contenteditable] strong {
    font-weight: normal;
    padding: 2px;
    /*background: red;*/
    color: black;
    border: 3px solid #BADA55;
	/* Safari 3-4, iOS 1-3.2, Android 1.6- */
	-webkit-border-radius: 12px;

	/* Firefox 1-3.6 */
	-moz-border-radius: 12px;

	/* Opera 10.5, IE 9, Safari 5, Chrome, Firefox 4, iOS 4, Android 2.1+ */
	border-radius: 12px;
}

  </style>



<script type='text/javascript'>//<![CDATA[
$(window).load(function(){
var changed,
    lastValue = '',
    div = $('#ce'),
    words = ['bipartisan', 'government', 'politics', 'republican','democratic','president'];

function markWords() {
    var found_words ='';
    var found_words_tag ='';
    var html = div.html().replace(/<\/?strong>/gi, ''),
        text = html.replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' '),
        exp;
    $.each(words, function(i, word) {
        exp = new RegExp('\\b(' + word + ')\\b', 'gi');
        html = html.replace(exp, function(m) {
			//console.log('WORD MATCH:', m);
			//found_words = found_words+m+"<br>";
			found_words_tag="Tag: Political";

            return '<strong>' + m + '</strong>';
        });
    });
    //html = html.replace('&nbsp;', ' ').replace(/\s+/g, ' ');
//console.log('HTML:', html);
//console.log('----');
    div.html(html);
//$("#found").html(found_words+"<br>");
$("#found").html(found_words_tag+"<br>");
}

setInterval(function() {
    var html = div.html();
    if ( lastValue != html && html ) {
//console.log(lastValue);
//console.log(html);
//console.log('----');
        lastValue = html;
        markWords();
        setEndOfContenteditable(div[0]);
    }
}, 500);

function setEndOfContenteditable(contentEditableElement)
{
    var range,selection;
    if(document.createRange)//Firefox, Chrome, Opera, Safari, IE 9+
    {
        range = document.createRange();//Create a range (a range is a like the selection but invisible)
        range.selectNodeContents(contentEditableElement);//Select the entire contents of the element with the range
        range.collapse(false);//collapse the range to the end point. false means collapse to end rather than the start
        selection = window.getSelection();//get the selection object (allows you to change selection)
        selection.removeAllRanges();//remove any selections already made
        selection.addRange(range);//make the range you have just created the visible selection
    }
    else if(document.selection)//IE 8 and lower
    {
        range = document.body.createTextRange();//Create a range (a range is a like the selection but invisible)
        range.moveToElementText(contentEditableElement);//Select the entire contents of the element with the range
        range.collapse(false);//collapse the range to the end point. false means collapse to end rather than the start
        range.select();//Select the range (make it the visible selection
    }
}

});//]]>

</script>


</head>
<body>
display auto tags <br>
<br>
<? 
//if (connectDb()) {
//   $rs = mysqli_query($conn, "SELECT * FROM `auto_tags` limit 0,100");
   echo getHtmlTable($rs);
//}
echo "\n<br>";
if (connectDb()) {
$tag_words = getRow($conn, "select tag_words from `auto_tags` WHERE tag_name='Political' limit 0,1");
echo "Political: ";
print_r ($tag_words);
echo "<br>";
}
?>
<br><br><hr><hr>

  <!--type in a political word from below in a sentence and it will chnage to a tag and display Political as a tag-->
  <div id="ce" contenteditable></div>
<!--Test words: <span>bipartisan, government, politics, republican, democratic, president</span>-->
<p><a href="javascript:void(0);" onclick="$('#ce').html('');">clear</a></p>
<div id="found" ></div>
</body>


</html>

Open in new window

JohnnyAsked:
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.

GaryCommented:
Just to be clear if someone types politics you want all these
'bipartisan', 'government', 'politics', 'republican','democratic','president'

to show below
0
JohnnyAuthor Commented:
@Gary,
if you just copy and paste to a html file and run it locally it runs and you can see what happens.
example of outputno please see image attached.
i just need how to check mysql rows for the tag word and tag name returned back.
also i just realized - i have another huge problem its in javascript i need this all in jquery.
thanks for the interest so far.
0
JohnnyAuthor Commented:
oh this maybe helpful duh
CREATE TABLE IF NOT EXISTS `auto_tags` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(50) NOT NULL,
  `tag_words` text NOT NULL,
  `tag_color` varchar(7) NOT NULL COMMENT '#000000 format',
  `notes` text NOT NULL,
  `admin_notes` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `auto_tags`
--

INSERT INTO `auto_tags` (`id`, `tag_name`, `tag_words`, `tag_color`, `notes`, `admin_notes`) VALUES
(1, 'Political', '''bipartisan'', ''government'', ''politics'', ''republican'',''democratic'',''president''', '#BADA55', '', ''),
(2, 'Religious or Spiritual', '''God'', ''Church'', ''Clergy'', ''Pastor'', ''Priest'', ''Christian'', ''Catholic'', ''baptize'', ''baptism'', ''Baptists'', ''Bar Mitzvah'', ''bible'', ''Biblical'', ''Bishop'', ''Lutheran'', ''Lutheranism'','' Liturgy'', ''Lord'', ''Lucifer'', ''Satan'', ''Passover'' ', '#BADA55', '', '');

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GaryCommented:
Are you hardcoding the words in the page or making an ajax call for each word
0
JohnnyAuthor Commented:
@gary the code you see is what i have so far
i guess i was unclear here i though the post caption said it all but i never placed that in the posts them selves. im looking to parse the words from a mysql saved field set the ones i have posted in the sql set sorry for the misunderstanding.
i was unsure of how to look up the words in the word = blah blah so that it looked for all the words ie political and God form both record sets of political and religious. then i farther had a problem of how to make it output like it does for the key word when any word is found.

this is basically a person types in a (simulated text box) and it auto tags for them, adding tags listing under the div. at one point im going to add this to the post save to mysql, the tags names it generates, i will have a bunch of tag name lookups for things like bad words, and others too with the political and religious look ups there the test ones, so i had something to work with.
i am also going to save the div content, and the tag names to a database at one point so i can use it

thanks again for your interest so far
0
GaryCommented:
It's an unusual method of tagging, normally you just add your tags and that is it - you don't normally have a parent tag that you use

Will have to think on this.
0
JohnnyAuthor Commented:
@gary we have a need for this as people are not tagging the sets correctly and we have a filter on our posts that if a person does not wish to see political posts then they dont see them if they are taged that way so im trying to automate some of them. hence the reason for this. we have multiple tags so the reason for the sets of tag names.

i am open to completely rewriting this i have put this together from items i found on the net, id much rather have it in jquery too.
im not in any rush, i been playing with it for a few days now.
thanks
0
GaryCommented:
Ok, in your db have your parent tag table so Political, God etc
Then have a sub tag table that has all the bipartisan, government etc linked to the parent tag by an ID.

When typing in your sentence you only worry about the sub tags. Ignore the parent tag.

In your posts filter you use the Parent tag - then when you show the posts you filter by the associated sub tags.

Does that make sense?
0
JohnnyAuthor Commented:
@ gary
note: i also posted the sql for reference and use http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28489511.html#a40237471 (wont let me make it a link in edit its broke)
yes a user types and it auto finds the tag name(political) from words like politics etc or pastor is typed it would have a tag name of religious, so a post can have political and religious tags etc etc, i thought it was nifty that it did all this live as they typed and highlighted the word (im not so worried of the highlight but would like it to add the tag name below live as they type). then later on these will be posted and in a post a user can have a flag that they do not want to see any posts with a tag name of political (as an example).
0
GaryCommented:
Your current table set up is not really conducive to what you want and is not really how you should be storing the data
I have the code below but this is based on a single entry for each keyword e.g.

ID	tag_name	tag_words
1	Political	bipartisan
2	Political	government
3	Political	politics
...and so on

Open in new window

Then this code will work (it doesn't highlight the word in the sentence but that can follow) and it probably could do with some tweaking
<?
session_start();  // needed for sessions.

// Add database connection values
$database_name="";
$username="";
$password="";

$conn = new PDO('mysql:host=localhost;dbname='.$database_name, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$rows=$conn->query("select id,tag_name,tag_words from auto_tags");
$rows=$rows->fetchAll();
?>

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Mark certain words while typing</title>

<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="http://code.jquery.com/ui/1.11.0/jquery-ui.js"></script>

  <style type='text/css'>
    [contenteditable] {
    padding: 10px;
    border: dotted 1px #aaa;

}
[contenteditable] > div {
    margin: 10px 0;
}
[contenteditable] strong {
    font-weight: normal;
    padding: 2px;
    /*background: red;*/
    color: black;
    border: 3px solid #BADA55;
	/* Safari 3-4, iOS 1-3.2, Android 1.6- */
	-webkit-border-radius: 12px;

	/* Firefox 1-3.6 */
	-moz-border-radius: 12px;

	/* Opera 10.5, IE 9, Safari 5, Chrome, Firefox 4, iOS 4, Android 2.1+ */
	border-radius: 12px;
}

  </style>

<script type='text/javascript'>//<![CDATA[
$(window).load(function(){
	
a = (<?=json_encode($rows);?>)

function findtag(tagname){
	for (index = 0; index < a.length; ++index) {
		entry = a[index];
		if (entry.tag_words == tagname) {
			return entry.tag_name
			break;
		}
	}
}

$("#ce").keyup(function(e){
	if(e.keyCode == 32){
		str=$(this).html().substring(0, $(this).html().length - 5);
		myWord=str.substring(str.lastIndexOf(" "))
		foundtag=findtag(myWord)
		if(foundtag!="" && foundtag!=undefined && $('#found:contains("'+foundtag+'")').length < 1){
			$("#found").html($("#found").html()+ '<strong>' + foundtag + '</strong>');
		}
   }
})

});//]]>

</script>

</head>
<body>
display auto tags <br>
<br>
<br><br><hr><hr>
  <div id="ce" contenteditable></div>
<p><a href="javascript:void(0);" onclick="$('#ce').html('');">clear</a></p>
<div id="found" ></div>
</body>
</html>

Open in new window

And this is using PDO - MySQL_ library is deprecated - you shouldn't be using it.
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
JohnnyAuthor Commented:
@gary
hrrrm its not doing anything at all
it does display the page and allows typing but nothing happens
im gonna have to go soon its my girls b-day i may continue this tomorrow.


i changed my database info and cut copied pasted code to a  new file and uploaded to server so should work
heres my sql
CREATE TABLE IF NOT EXISTS `auto_tags` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(50) NOT NULL,
  `tag_words` varchar(50) NOT NULL,
  `tag_color` varchar(7) NOT NULL COMMENT '#000000 format',
  `notes` text NOT NULL,
  `admin_notes` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `auto_tags`
--

INSERT INTO `auto_tags` (`id`, `tag_name`, `tag_words`, `tag_color`, `notes`, `admin_notes`) VALUES
(1, 'Political', 'politics', '#BADA55', '', ''),
(2, 'Religious or Spiritual', 'God', '#BADA55', '', ''),
(3, 'Political', 'government', '', '', ''),
(4, 'Religious or Spiritual', 'Church', '', '', ''),
(5, 'Religious or Spiritual', 'Pastor', '', '', '');

Open in new window

0
JohnnyAuthor Commented:
@gary
heres the a= json it is finding it
a = ([{"id":"1","0":"1","tag_name":"Political","1":"Political","tag_words":"politics","2":"politics"},{"id":"2","0":"2","tag_name":"Religious or Spiritual","1":"Religious or Spiritual","tag_words":"God","2":"God"},{"id":"3","0":"3","tag_name":"Political","1":"Political","tag_words":"government","2":"government"},{"id":"4","0":"4","tag_name":"Religious or Spiritual","1":"Religious or Spiritual","tag_words":"Church","2":"Church"},{"id":"5","0":"5","tag_name":"Religious or Spiritual","1":"Religious or Spiritual","tag_words":"Pastor","2":"Pastor"}])

Open in new window

0
JohnnyAuthor Commented:
@gary

think i found the problem
alert of tag name and entry tag wordsi added
entry = a[index];
		alert(tagname+" / "+entry.tag_words);
		if (entry.tag_words == tagname) {

Open in new window


not yet sure how to fix it tho
0
JohnnyAuthor Commented:
@gary
also does this match as case insensitive?
0
JohnnyAuthor Commented:
@gary
ok i got it to work by doing

entry = a[index];
		tagname = str.slice(0,-1);
		alert(tagname+" / "+entry.tag_words);
		if (entry.tag_words == tagname) {

Open in new window

as str.slice(0,-1); removes last charter ie the & symbol

but now i need to have it be case insensitive and to look up more then one word as its always doing everything you tpype not last word typed see image plz
example
0
GaryCommented:
It is case sensitive to change it replace

foundtag=findtag(myWord)

with
foundtag=findtag(myWord.toLowerCase())

Where's the & coming from
0
JohnnyAuthor Commented:
@gary
ok solved lowercase lookup also did if (entry.tag_words.toLowerCase() == tagname) {

thank you
still have multi lookup problem in image in my last post
0
GaryCommented:
still have multi lookup problem in image in my last post
Not following you
0
JohnnyAuthor Commented:
it does not look up multi tags so if a person types in a political word and a religious word it does not do both as tag names.
thank you so much for your help so far im vary grateful
0
GaryCommented:
I see
Replace
foundtag=findtag(myWord.toLowerCase())

with
foundtag=findtag(myWord.toLowerCase().trim())
0
JohnnyAuthor Commented:
@ gary
no its searching the complete typed in text
exampleif someone types in god and politics is should do both religous and political tags
0
GaryCommented:
It searches the last word entered
Post the js you have now.
0
JohnnyAuthor Commented:
<?
session_start();  // needed for sessions.

// Add database connection values
$database_name="";
$username="";
$password="";

$conn = new PDO('mysql:host=localhost;dbname='.$database_name, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$rows=$conn->query("select id,tag_name,tag_words from auto_tags");
$rows=$rows->fetchAll();
?>

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Mark certain words while typing</title>

<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="http://code.jquery.com/ui/1.11.0/jquery-ui.js"></script>

  <style type='text/css'>
    [contenteditable] {
    padding: 10px;
    border: dotted 1px #aaa;

}
[contenteditable] > div {
    margin: 10px 0;
}
[contenteditable] strong {
    font-weight: normal;
    padding: 2px;
    /*background: red;*/
    color: black;
    border: 3px solid #BADA55;
	/* Safari 3-4, iOS 1-3.2, Android 1.6- */
	-webkit-border-radius: 12px;

	/* Firefox 1-3.6 */
	-moz-border-radius: 12px;

	/* Opera 10.5, IE 9, Safari 5, Chrome, Firefox 4, iOS 4, Android 2.1+ */
	border-radius: 12px;
}

  </style>

<script type='text/javascript'>//<![CDATA[
$(window).load(function(){
	
a = (<?=json_encode($rows);?>)

function findtag(tagname){
	for (index = 0; index < a.length; ++index) {
		entry = a[index];
		tagname = str.slice(0,-1);
		alert(tagname+" / "+entry.tag_words.toLowerCase());
		if (entry.tag_words.toLowerCase() == tagname) {
			return entry.tag_name
			break;
		}
	}
}

$("#ce").keyup(function(e){
	if(e.keyCode == 32){
		str=$(this).html().substring(0, $(this).html().length - 5);
		myWord=str.substring(str.lastIndexOf(" "))
		foundtag=findtag(myWord.toLowerCase().trim())
		if(foundtag!="" && foundtag!=undefined && $('#found:contains("'+foundtag+'")').length < 1){
			$("#found").html($("#found").html()+ '<strong>' + foundtag + '</strong>');
		}
   }
})

});//]]>

</script>

</head>
<body>
display auto tags <br>
<br>
<br><br><hr><hr>
  <div id="ce" contenteditable></div>
<p><a href="javascript:void(0);" onclick="$('#ce').html('');">clear</a></p>
<div id="found" ></div>
</body>
</html>

Open in new window

0
JohnnyAuthor Commented:
thank you for the pdo part (i just started to play with it the last few days) - fyi if you look at my first posted code it is mysqli (note the i) and not mysql.

im accepting this as i have to go and cant view this till tomorrow-ish and i believe we answered it just needs tweaking some.
0
GaryCommented:
You're right I missed the i
0
GaryCommented:
Why did you add this

tagname = str.slice(0,-1);

That will prevent a match since it's cutting off the last letter.
0
JohnnyAuthor Commented:
@Gary
Because if you look at post it had a & at the end of the string. I posted a pic and how I fixed it in a post above
0
GaryCommented:
But I don't follow why you would have God&
If that is the case then you need to do a replace for the character
0
JohnnyAuthor Commented:
@gary
I don't know why it was there too but it was. It works fine for single pulls but as I said I need it to pull multiple tag names right now it looks at complete string only matches one match then stops I need it to display both Religious and political and others too if found as multiple tag words but only one so if politics and God and Church where typed both politics and religious would be printed once
0
GaryCommented:
Thats what it does but not with this line in the code
tagname = str.slice(0,-1);

Your going to have a hard time making matches if the words are not exactly as in the database, unless you add an entry for example God& in the database.

Where is the string coming from - I assumed this was for people to type in
0
JohnnyAuthor Commented:
No idea
0
JohnnyAuthor Commented:
I'll be home in a few maybe we can find out in like 10 mins
0
JohnnyAuthor Commented:
@gary

found it it was the html
str=$(this).html().substring(0, $(this).html().length - 5);

Open in new window

to this
str=$(this).text().substring(0, $(this).text().length - 5);

Open in new window

and taking out my slice and it works fine now
when you said it should not be placed there the & i was like why is it.

thanks again for all your help
0
GaryCommented:
Ahhh, yes my mistake, I was grabbing the HTML and not the text and going back to remove the <br> that gets added in FF
You can replace that whole function with this

$("#ce").keyup(function(e){
	if(e.keyCode == 32){
		str=$(this).text().trim()
		myWord=str.substring(str.lastIndexOf(" "))
		foundtag=findtag(myWord.toLowerCase().trim())
		if(foundtag!="" && foundtag!=undefined && $('#found:contains("'+foundtag+'")').length < 1){
			$("#found").html($("#found").html()+ '<strong>' + foundtag + '</strong>');
		}
   }
})

Open in new window

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
JavaScript

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.