Javascript - Remove URL from thing

I have some code which pulls tweets from a database, removes any URLs and highlights the hashtag from the string.
I am getting an error on this 1 tweet, and im not sure how to fix it.

Hilarious!! “@kellyfurneaux: Which one is Jodie?! #Sony https://t.co/sAdxCv3bDe�

Open in new window


It appears to be the â character which is 3rd from the end, because when I remove that character it works

	String.prototype.parseURL = function() {
		return this.replace(/[A-Za-z]+:\/\/[A-Za-z0-9-_]+\.[A-Za-z0-9-_:%&~\?\/.=]+/g, function(url) {
			return url.link(url);
		});
	};
	data.text = (data.text.parseURL());

	data.text = data.text.replace(/(\#[a-zA-Z0-9\-\_]+)/g,"<span class='hashtag'> $1 </span>");

Open in new window



Console Error
Uncaught TypeError: Cannot read property 'parseURL' of null
    at createTweet (createTweet.js:8)
    at Object.<anonymous> (twitterwall.js:127)
    at Function.each (jquery-3.1.1.min.js:2)
    at appendTweets (twitterwall.js:109)
    at Object.success (twitterwall.js:77)
    at i (jquery-3.1.1.min.js:2)
    at Object.fireWith [as resolveWith] (jquery-3.1.1.min.js:2)
    at A (jquery-3.1.1.min.js:4)
    at XMLHttpRequest.<anonymous> (jquery-3.1.1.min.js:4)

Open in new window



Could someone help here??
Steve TinsleyAsked:
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.

Leonidas DosasCommented:
Decode the database tweets into UTF-8 format when you fetch them. ie if you are doing this via  php you can use utf8_decode function
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
Julian HansenCommented:
Can you show us your code that pulls the data from the DB?
0
Steve TinsleyAuthor Commented:
<?php
	// Database connection config
	$dbData["dbHost"] = 'localhost';
	$dbData["dbUser"] = 'user';
	$dbData["dbPass"] = 'password';
	$dbData["dbName"] = 'twitterwall';

	try {
		$db = new PDO("mysql:host=".$dbData["dbHost"].";dbname=".$dbData["dbName"], $dbData["dbUser"], $dbData["dbPass"]);
		$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
	}
	catch(PDOException $e) {
	    echo $e->getMessage();
	    die;
	}
?>


<?php
		try
		{
			$sql = $db->prepare("SELECT * FROM tweets
				WHERE updated > '$lastUpdatedDT' AND moderate = 1 AND lang = 'en'
				$where
				ORDER BY tweet_id $orderByDirection
				LIMIT $noOfTweets");
			$sql->execute(array(0));
			$row_count = $sql->rowCount();
		}
		catch (PDOException $e) {
		    print "Error(101): " . $e->getMessage();
		    //die();
		}
?>

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.

Julian HansenCommented:
Ok but where are you pulling the data - all I see is a query and then getting the number of rows returned?
0
Steve TinsleyAuthor Commented:
FULL PHP FUNCTION
	function newTweets() {
		global $ajaxResponse;
		global $db;
		$newTweets = Array();

		//$noOfTweets = (is_numeric($_POST['noOfTweets']) ? (int)$_POST['noOfTweets'] : 6);

		$lastId = $_POST['lastId'];
		$lastUpdatedDT = $_POST['lastUpdatedDT'];

		$randomTweets = $_POST['randomTweets']; //settings
		$hideRetweet = $_POST['hideRetweet']; //settings

		if ($hideRetweet) {
			$where = " AND retweet_count = 0 ";
		}


		if ($lastId == 0) {
			$orderByDirection = "ASC"; //DESC
			$noOfTweets = 3;
		} else {
			$orderByDirection = "ASC";
			$noOfTweets = $_POST['noOfTweets'];
		}


		//WHERE tweet_id > $lastId
		try
		{
			$sql = $db->prepare("SELECT * FROM tweets
				WHERE updated > '$lastUpdatedDT' AND moderate = 1 AND lang = 'en'
				$where
				ORDER BY tweet_id $orderByDirection
				LIMIT $noOfTweets");
			$sql->execute(array(0));
			$row_count = $sql->rowCount();
		}
		catch (PDOException $e) {
		    print "Error(101): " . $e->getMessage();
		    //die();
		}

		while($r = $sql->fetch(PDO::FETCH_ASSOC)){
			$r['type'] = "new";
			$r['profile_image_url'] = str_replace("_normal", "_bigger", $r['profile_image_url']);
			$newTweets[] = $r;
			//$ajaxResponse['newTweets'] = $r;
		}

		//if ($row_count != $noOfTweets && $randomTweets == 1) {
		if ($randomTweets == 1) {
			$extraTweets = $noOfTweets - $row_count;
			randomTweets($extraTweets);
		}
		
		$ajaxResponse['newTweets'] = $newTweets;
	}

Open in new window





JS AJAX
function getTweets(state) {
	noOfTweets = state['num_tweets'];
	anim_class = state['anim_class'];

	$.ajax({
		type: 'POST',
		url: '../api/wall.php',
		data: 'moderate='+moderate+'&hideRetweet='+hideRetweet+'&randomTweets='+randomTweets+'&hashtag='+hashtag+'&action=newTweets&noOfTweets='+noOfTweets+'&last3Ids='+last3Ids+'&lastId='+lastId+'&lastUpdatedDT='+lastUpdatedDT,
		dataType: 'json',
		cache: false,
		success: function(result) {

			result = result.newTweets.concat(result.randomTweets);

				if (state['clear_each_loop'])
					clearTweets('.twitterFeed .tweet');
				appendTweets(result, anim_class);

				main_timer = setTimeout(function(){
					getTweets(state);

				}, state['loop_every'] * 1000);

		},
	    error: function(xhr, textStatus, errorThrown){
	       console.log('request failed... retrying');

	       main_timer = setTimeout(function(){
				getTweets(state);

			}, state['loop_every'] * 2000);

	    }
	});
}

Open in new window



appendTweets runs a function which sorts the html data:
var htmlData = createTweet(element,size);

then create tweet builds the html:
function createTweet(data,size) {

	String.prototype.parseURL = function() {
		return this.replace(/[A-Za-z]+:\/\/[A-Za-z0-9-_]+\.[A-Za-z0-9-_:%&~\?\/.=]+/g, function(url) {
			return url.link(url);
		});
	};
	data.text = (data.text.parseURL());

	data.text = data.text.replace(/(\#[a-zA-Z0-9\-\_]+)/g,"<span class='hashtag'> $1 </span>");


	// htmlData = "<div class=\"tweet " + ((data.media_url) ? ("withimage") : ("withoutimage")) + " "+size+"\" style=\"background-size: cover; background-repeat: no-repeat; background-position: center center; background-image: url('"+data.media_url+"');\">";
	htmlData = "<div class=\"clearfix tweet " + ((data.media_url) ? ("withimage") : ("withoutimage")) + " "+size+"\">";
	htmlData += "<div class=\"clearfix tweet-inside\">";

	htmlData += "<div class=\"thumb-container\"><div class=\"thumb\"><img src=\""+data.profile_image_url+"\" onerror=\"this.src='images/twitter_square.png'\"></div></div>";


	htmlData += "<div class=\"tweet-body\">";
	htmlData += "<div class=\"top-data\"><span class=\"name\">"+data.name+"</span> <span class=\"screen-name\">@"+data.screen_name+"</span> <i class=\"fa fa-twitter\"></i></div>";
	htmlData += "<div class=\"text\">"+data.text+"</div>";

	//htmlData += "<div class=\"image-wrapper\"><div class=\"landscape image\" style=\"background-image: url('http://placehold.it/150x150');\"></div></div>";

	htmlData += "<div class=\"bottom-data\">";
	htmlData += "<span class=\"date\">posted "+ moment(data.created_at, 'ddd MMM DD HH:mm:ss Z YYYY').fromNow() +"</span> ";
	//htmlData += " &nbsp; <span>"+data.retweet_count+" retweets</span>";
	//htmlData += "<span>"+data.favorite_count+" favourites</span>";
	htmlData += "</div>";
	htmlData += "</div>";

	if(data.media_url)
		htmlData += "<div class=\"clearfix animate slideInDown tweet-image\"><img src=\""+data.media_url+"\"></div>";

	htmlData += "</div></div>";

	// var hashtag_target = '#SmarterEconomy';
	// htmlData = htmlData.replace(hashtag_target, "<span class='hashtag'>"+hashtag_target+"</span>");

	return htmlData;
}

Open in new window

0
Julian HansenCommented:
What does the source URL look like in the DB and
What is the name of that field?
0
Steve TinsleyAuthor Commented:
Thanks for digging... Heres a screenshot which I think may help:

Screen-Shot-2018-01-17-at-14.30.14.png
it's the text field
0
Julian HansenCommented:
The data appears to contain the offending char in the DB so we need to look at how it is getting there.

When you get the source data (from which your DB data is derived) what does that URL look like.
0
Steve TinsleyAuthor Commented:
I run some PHP which uses the twitter API on a cronjob to get new tweets every 1 minute.
The twitter API returns JSON.

I then loop over each tweet returned and insert this data into the db using this query...

    try
    {
        $sql = $db->prepare("INSERT INTO tweets (tweet_id, full_tweet, created_at, hashtag, text, profile_image_url, screen_name, name, favorite_count, retweet_count, media_url, lang)
        VALUES (:tweet_id, :full_tweet, :created_at, :hashtag, :text, :profile_image_url, :screen_name, :name, :favorite_count, :retweet_count, :media_url, :lang)");

        $sql->execute(array(':tweet_id'=>$tweet->id,
                            ':full_tweet'=>json_encode($tweet),
                            ':created_at'=>$tweet->created_at,
                            ':hashtag'=>$hashtag->text,
                            ':text'=>$tweet->full_text,
                            ':profile_image_url'=>$tweet->user->profile_image_url,
                            ':name'=>$tweet->user->name,
                            ':screen_name'=>$tweet->user->screen_name,
                            ':favorite_count'=>$tweet->favorite_count,
                            ':retweet_count'=>$tweet->retweet_count,
                            ':media_url'=>$media_url,
                            ':lang'=>$tweet->lang
                            ));
    }

    catch(PDOException $e)
    {
        echo "There was a problem(101): " . $e->getMessage();
    }

Open in new window


I connect to mySQL in the same way as the ajax:
<?php
	// Database connection config
	$dbData["dbHost"] = 'localhost';
	$dbData["dbUser"] = 'user';
	$dbData["dbPass"] = 'password';
	$dbData["dbName"] = 'twitterwall';

	try {
		$db = new PDO("mysql:host=".$dbData["dbHost"].";dbname=".$dbData["dbName"], $dbData["dbUser"], $dbData["dbPass"]);
		$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
	}
	catch(PDOException $e) {
	    echo $e->getMessage();
	    die;
	}
?>


<?php
		try
		{
			$sql = $db->prepare("SELECT * FROM tweets
				WHERE updated > '$lastUpdatedDT' AND moderate = 1 AND lang = 'en'
				$where
				ORDER BY tweet_id $orderByDirection
				LIMIT $noOfTweets");
			$sql->execute(array(0));
			$row_count = $sql->rowCount();
		}
		catch (PDOException $e) {
		    print "Error(101): " . $e->getMessage();
		    //die();
		}
?>

Open in new window

0
Julian HansenCommented:
Do you have the raw URL before it is inserted into the DB?

You can set the encoding on the table before you insert using the charset option as part of your PDO connection string

$link = new PDO("mysql:host=localhost;dbname=DB;charset=UTF8");

Open in new window

0
Steve TinsleyAuthor Commented:
Im not sure what you mean by raw url... the data comes over as json from twitter...
If I add UTF8 then is works.... BUT it doesnt store emojis. This is quite important as tweets are ofen written with these.
Thoughts?
0
Julian HansenCommented:
the data comes over as json from twitter...
That is what I am asking for. That RAW JSON data.

If you want to store Emoji's you will need to change your database and table collation
Change the database collation to utf8mb4.
Change the table as follows
       CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

You can read more here https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html
0
Steve TinsleyAuthor Commented:
This is the full tweet
{
   "created_at":"Mon Jan 23 21:49:21 +0000 2017",
   "id":823648843929620480,
   "id_str":"823648843929620480",
   "full_text":"Hilarious!! \u201c@kellyfurneaux: Which one is Jodie?! #AutovistaGroupPrague2017 https:\/\/t.co\/zBpZCv3bDe\u201d",
   "truncated":false,
   "display_text_range":[
      0,
      100
   ],
   "entities":{
      "hashtags":[
         {
            "text":"AutovistaGroupPrague2017",
            "indices":[
               50,
               75
            ]
         }
      ],
      "symbols":[

      ],
      "user_mentions":[
         {
            "screen_name":"kellyfurneaux",
            "name":"Kelly Furneaux",
            "id":192962843,
            "id_str":"192962843",
            "indices":[
               13,
               27
            ]
         }
      ],
      "urls":[

      ],
      "media":[
         {
            "id":823593019404390401,
            "id_str":"823593019404390401",
            "indices":[
               76,
               99
            ],
            "media_url":"http:\/\/pbs.twimg.com\/media\/C239fNEXEAEwWW-.jpg",
            "media_url_https":"https:\/\/pbs.twimg.com\/media\/C239fNEXEAEwWW-.jpg",
            "url":"https:\/\/t.co\/zBpZCv3bDe",
            "display_url":"pic.twitter.com\/zBpZCv3bDe",
            "expanded_url":"https:\/\/twitter.com\/kellyfurneaux\/status\/823593041458057216\/photo\/1",
            "type":"photo",
            "sizes":{
               "medium":{
                  "w":1200,
                  "h":900,
                  "resize":"fit"
               },
               "small":{
                  "w":680,
                  "h":510,
                  "resize":"fit"
               },
               "thumb":{
                  "w":150,
                  "h":150,
                  "resize":"crop"
               },
               "large":{
                  "w":2048,
                  "h":1536,
                  "resize":"fit"
               }
            },
            "source_status_id":823593041458057216,
            "source_status_id_str":"823593041458057216",
            "source_user_id":192962843,
            "source_user_id_str":"192962843"
         }
      ]
   },
   "extended_entities":{
      "media":[
         {
            "id":823593019404390401,
            "id_str":"823593019404390401",
            "indices":[
               76,
               99
            ],
            "media_url":"http:\/\/pbs.twimg.com\/media\/C239fNEXEAEwWW-.jpg",
            "media_url_https":"https:\/\/pbs.twimg.com\/media\/C239fNEXEAEwWW-.jpg",
            "url":"https:\/\/t.co\/zBpZCv3bDe",
            "display_url":"pic.twitter.com\/zBpZCv3bDe",
            "expanded_url":"https:\/\/twitter.com\/kellyfurneaux\/status\/823593041458057216\/photo\/1",
            "type":"photo",
            "sizes":{
               "medium":{
                  "w":1200,
                  "h":900,
                  "resize":"fit"
               },
               "small":{
                  "w":680,
                  "h":510,
                  "resize":"fit"
               },
               "thumb":{
                  "w":150,
                  "h":150,
                  "resize":"crop"
               },
               "large":{
                  "w":2048,
                  "h":1536,
                  "resize":"fit"
               }
            },
            "source_status_id":823593041458057216,
            "source_status_id_str":"823593041458057216",
            "source_user_id":192962843,
            "source_user_id_str":"192962843"
         }
      ]
   },
   "metadata":{
      "iso_language_code":"en",
      "result_type":"recent"
   },
   "source":"<a href=\"http:\/\/twitter.com\/download\/iphone\" rel=\"nofollow\">Twitter for iPhone<\/a>",
   "in_reply_to_status_id":823593041458057216,
   "in_reply_to_status_id_str":"823593041458057216",
   "in_reply_to_user_id":192962843,
   "in_reply_to_user_id_str":"192962843",
   "in_reply_to_screen_name":"kellyfurneaux",
   "user":{
      "id":74134728,
      "id_str":"74134728",
      "name":"Clare Renton",
      "screen_name":"ClareRenton",
      "location":"London",
      "description":"L&D Manager with a passion in psychometrics, coaching and team devt. A big foodie enthusiast and obsessed with pugs!",
      "url":null,
      "entities":{
         "description":{
            "urls":[

            ]
         }
      },
      "protected":false,
      "followers_count":253,
      "friends_count":232,
      "listed_count":15,
      "created_at":"Mon Sep 14 11:33:35 +0000 2009",
      "favourites_count":9,
      "utc_offset":0,
      "time_zone":"Dublin",
      "geo_enabled":false,
      "verified":false,
      "statuses_count":438,
      "lang":"en",
      "contributors_enabled":false,
      "is_translator":false,
      "is_translation_enabled":false,
      "profile_background_color":"EDECE9",
      "profile_background_image_url":"http:\/\/abs.twimg.com\/images\/themes\/theme3\/bg.gif",
      "profile_background_image_url_https":"https:\/\/abs.twimg.com\/images\/themes\/theme3\/bg.gif",
      "profile_background_tile":false,
      "profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/823560659128483840\/5MBtJ016_normal.jpg",
      "profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/823560659128483840\/5MBtJ016_normal.jpg",
      "profile_link_color":"088253",
      "profile_sidebar_border_color":"D3D2CF",
      "profile_sidebar_fill_color":"E3E2DE",
      "profile_text_color":"634047",
      "profile_use_background_image":true,
      "has_extended_profile":false,
      "default_profile":false,
      "default_profile_image":false,
      "following":false,
      "follow_request_sent":false,
      "notifications":false,
      "translator_type":"none"
   },
   "geo":null,
   "coordinates":null,
   "place":null,
   "contributors":null,
   "is_quote_status":false,
   "retweet_count":0,
   "favorite_count":0,
   "favorited":false,
   "retweeted":false,
   "possibly_sensitive":false,
   "lang":"en"
}

Open in new window



Currently my table is set to: latin1_swedish_ci
I think this is default. You suggest I should change this to: utf8mb4_bin ??
Can this be done using phpmyadmin?
0
Julian HansenCommented:
The problem is that the string for full_text is enclosed in slanting double quotes.

This worked for me.
Assume the above JSON is in variable $json
$data = json_decode($json);
$fulltext = htmlentities($data->full_text);
$query = "INSERT INTO `myTable` (fullText) VALUES ('{$fulltext}')";
// ...
// ... RETRIEVE THE DATA
$query = "SELECT * FROM `myTable` WHERE `id` = {$id}";
$result = $conn->query($query);
$row = $result->fetch_object();
echo $row->fullText;

Open in new window

In the above I am using the htmlentities() function to encode the string before it is sent to the DB.
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.