?
Solved

use mysql database to parse words in javascript/jquery routine

Posted on 2014-08-03
34
Medium Priority
?
277 Views
Last Modified: 2014-08-03
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

0
Comment
Question by:Johnny
  • 20
  • 14
34 Comments
 
LVL 58

Expert Comment

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

to show below
0
 

Author Comment

by:Johnny
ID: 40237435
@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
 

Author Comment

by:Johnny
ID: 40237471
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:Gary
ID: 40237474
Are you hardcoding the words in the page or making an ajax call for each word
0
 

Author Comment

by:Johnny
ID: 40237490
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40237500
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
 

Author Comment

by:Johnny
ID: 40237509
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40237516
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
 

Author Comment

by:Johnny
ID: 40237532
@ 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
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40237592
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
 

Author Comment

by:Johnny
ID: 40237693
@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
 

Author Comment

by:Johnny
ID: 40237718
@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
 

Author Comment

by:Johnny
ID: 40237723
@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
 

Author Comment

by:Johnny
ID: 40237727
@gary
also does this match as case insensitive?
0
 

Author Comment

by:Johnny
ID: 40237736
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40237737
It is case sensitive to change it replace

foundtag=findtag(myWord)

with
foundtag=findtag(myWord.toLowerCase())

Where's the & coming from
0
 

Author Comment

by:Johnny
ID: 40237745
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40237750
still have multi lookup problem in image in my last post
Not following you
0
 

Author Comment

by:Johnny
ID: 40237755
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
 
LVL 58

Expert Comment

by:Gary
ID: 40237762
I see
Replace
foundtag=findtag(myWord.toLowerCase())

with
foundtag=findtag(myWord.toLowerCase().trim())
0
 

Author Comment

by:Johnny
ID: 40237767
@ 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
 
LVL 58

Expert Comment

by:Gary
ID: 40237774
It searches the last word entered
Post the js you have now.
0
 

Author Comment

by:Johnny
ID: 40237776
<?
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
 

Author Closing Comment

by:Johnny
ID: 40237781
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
 
LVL 58

Expert Comment

by:Gary
ID: 40237786
You're right I missed the i
0
 
LVL 58

Expert Comment

by:Gary
ID: 40237791
Why did you add this

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

That will prevent a match since it's cutting off the last letter.
0
 

Author Comment

by:Johnny
ID: 40238024
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40238028
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
 

Author Comment

by:Johnny
ID: 40238081
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40238085
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
 

Author Comment

by:Johnny
ID: 40238089
No idea
0
 

Author Comment

by:Johnny
ID: 40238090
I'll be home in a few maybe we can find out in like 10 mins
0
 

Author Comment

by:Johnny
ID: 40238113
@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
 
LVL 58

Expert Comment

by:Gary
ID: 40238134
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question