Jump to content

edit row if hashtag field already exists with the varible


Go to solution Solved by Barand,

Recommended Posts

Sorry this ends this script i got it doing everything i need except its making too many rows  for what i need so i figure an update to it if the hashtag field is already in 

 

no idea how to do that i am teaching myself this as i go

<?php
// Require J7mbo's TwitterAPIExchange library (used to retrive the tweets)
// You can get this library from here: https://github.com/J7mbo/twitter-api-php
require_once('TwitterAPIExchange.php');

/** database cred **/
        define('DBHOST','localhost');
        define('DBUSERNAME','');
        define('DBPASSWORD','');
        define('DBNAME','rogueweet');
        define('TWEETTABLE','hashtags');
/** end database cred **/





// Set here your twitter application tokens
$settings = array(
        'oauth_access_token' => "831981818408677377-FeZYWt3TYwmodmx3gMmFIqx",
        'oauth_access_token_secret' => "L1vwbaBjsUivKn56V1lSP5THvjBk3LiadHyOj",
        'consumer_key' => "t31OianjdeBAjWPqj3",
        'consumer_secret' => "zFZpwpaZl0K1CQPpsagBjVCMkTs2GtWHhRm"
        );

// Set here the Twitter account from where getting latest tweets


$screen_name = 'Dropfaith21';   //username to mock twitter feed

// Get timeline using TwitterAPIExchange
$url = 'https://api.twitter.com/1.1/statuses/user_timeline.json';
$getfield = "?screen_name={$screen_name}";
$requestMethod = 'GET';
$twitter = new TwitterAPIExchange($settings);
$user_timeline = $twitter
  ->setGetfield($getfield)
  ->buildOauth($url, $requestMethod)
  ->performRequest();

$user_timeline = json_decode($user_timeline);
foreach ($user_timeline as $user_tweet) {

 
  if (isset($user_tweet->entities->media)) {
    $media_url = $user_tweet->entities->media[0]->media_url;
    $hashtag = preg_replace('^(ht|f)tp(s?)\:\/\/[0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*(:(0-9)*)*(\/?)([a-zA-Z0-9\-‌​\.\?\,\'\/\\\+&%\$#_]*)?$^',' ', $user_tweet->text);
    // $hashtag = $user_tweet->text;
    echo "($hashtag)";
    echo "<img src='{$media_url}' width='60%' />";
    echo insertTweets($media_url,$hashtag);
  }
  }
  
  
  
    function insertTweets($link,$hashtag){
            $mysqli = new mysqli(DBHOST, DBUSERNAME, DBPASSWORD, DBNAME);
            if ($mysqli->connect_errno) {
                return 'Failed to connect to Database: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error; 
            }
            $prepareStmt='insert INTO '.DBNAME.'.'.TWEETTABLE.' (link,hashtag) VALUES (?,?)';
            
            if ($insert_stmt = $mysqli->prepare($prepareStmt)){
                $insert_stmt->bind_param('ss', $link,$hashtag);
                if (!$insert_stmt->execute()) {
                    $insert_stmt->close();
                    return 'Tweet Creation cannot be done at this moment.';
                }elseif($insert_stmt->affected_rows>0){
                    $insert_stmt->close();
                    return 'Tweet Added.<br><br>';
                }else{
                    $insert_stmt->close();
                    return 'No Tweet were Added.';
                }
            }else{
                return 'Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error;
            }
        }
        
?>
Edited by dropfaith
Link to post
Share on other sites

I'd love to help you out but....

 

Maybe you could work on your English usage too and put together some SENTENCES about your problem and give us a hand in understanding what you are trying to accomplish. I'm sure your problem is very clear in your head. How about giving us a better understanding of what you want so we can follow your code better?

Link to post
Share on other sites

Sorry i did that on no sleep at 5 am

basically right now it inserts a new row everytime the script runs which isnt ideal for the end game anymore i changed up my other side to fit it better

 

 

theres 3 columns  Id (auto inc) hashtags and link

 

what i need is if hashtag exists  edit link to the new link field

 

there will never be more then 1 link in for each hashtag.

 

i know it has something in here and an update instead of insert but im literally clueless on syntax for it

 function insertTweets($link,$hashtag){
            $mysqli = new mysqli(DBHOST, DBUSERNAME, DBPASSWORD, DBNAME);
            if ($mysqli->connect_errno) {
                return 'Failed to connect to Database: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error; 
            }
            $prepareStmt='insert INTO '.DBNAME.'.'.TWEETTABLE.' (link,hashtag) VALUES (?,?)';
            
            if ($insert_stmt = $mysqli->prepare($prepareStmt)){
                $insert_stmt->bind_param('ss', $link,$hashtag);
                if (!$insert_stmt->execute()) {
                    $insert_stmt->close();
                    return 'Tweet Creation cannot be done at this moment.';
                }elseif($insert_stmt->affected_rows>0){
                    $insert_stmt->close();
                    return 'Tweet Added.<br><br>';
                }else{
                    $insert_stmt->close();
                    return 'No Tweet were Added.';
                }
            }else{
                return 'Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error;
            }
        }
        
?>

Not sure how to check the database to see if hashtag exists then update a different field and if not just insert the full query

Link to post
Share on other sites

what i need is if hashtag exists  edit link to the new link field

 

there will never be more then 1 link in for each hashtag.

 

You could add a query that checks if the hashtag exists already. For the hashtags that exist, you would skip the insert query.

Link to post
Share on other sites
  • Solution

Put unique key on hashtag column.

CREATE TABLE `hashtag` (
  `hashtag_id` int(11) NOT NULL AUTO_INCREMENT,
  `hashtag` varchar(45) DEFAULT NULL,
  `link` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`hashtag_id`),
  UNIQUE KEY `idx_hashtag_hashtag` (`hashtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into hashtag (hashtag, link) VALUES
    -> ('abc', 'xyz'),
    -> ('bcd', 'stu');
Query OK, 2 rows affected (0.05 sec)

mysql> select * from hashtag;
+------------+---------+------+
| hashtag_id | hashtag | link |
+------------+---------+------+
|          1 | abc     | xyz  |
|          2 | bcd     | stu  |
+------------+---------+------+
2 rows in set (0.00 sec)

Instead of querying to see if it exists then doing a second query to update, do it in one query using "INSERT .. ON DUPLICATE KEY"

mysql> INSERT INTO hashtag (hashtag, link) VALUES ('abc', 'def')
    -> ON DUPLICATE KEY UPDATE link = values(link);
Query OK, 2 rows affected (0.06 sec)

mysql> select * from hashtag;
+------------+---------+------+
| hashtag_id | hashtag | link |
+------------+---------+------+
|          1 | abc     | def  |
|          2 | bcd     | stu  |
+------------+---------+------+
2 rows in set (0.00 sec)
Link to post
Share on other sites

hrmm producing an error in this and i thought i had it

 

ERROR: Could not able to execute (INSERT INTO hashtag (hashtag, link) VALUES ('arena ', 'http://pbs.twimg.com/media/C5QtXQEVUAAyk_-.jpg') ON DUPLICATE KEY UPDATE link='http://pbs.twimg.com/media/C5QtXQEVUAAyk_-.jpg'). You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO hashtag (hashtag, link) VALUES ('arena ', 'http://pbs.twimg.com/med' at line 1

/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "roguebro", "", "roguebro_tweet");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt insert query execution
$sql = "(INSERT INTO hashtag (hashtag, link) VALUES ('$hashtag', '$media_url') ON DUPLICATE KEY UPDATE link='" . $media_url . "')";
if(mysqli_query($link, $sql)){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);
Link to post
Share on other sites

that fixed that any way to reverse a foreach..

 

 

it displays from twitter so the loop is newest first and cycles thus replacing the newest  of the tags

 

otherwise i can just dump the foreach loop  and have my bot look thru the script more often thus eliminating its need but adding server strain

foreach ($user_timeline as $user_tweet) {

 
  if (isset($user_tweet->entities->media)) {
    $media_url = $user_tweet->entities->media[0]->media_url;
    $hashtag = preg_replace('^(ht|f)tp(s?)\:\/\/[0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*(:(0-9)*)*(\/?)([a-zA-Z0-9\-‌​\.\?\,\'\/\\\+&%\$#_]*)?$^',' ', $user_tweet->text);
  }
  /* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "roguebro", "eruditio2", "roguebro_tweet");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt insert query execution
$sql = "INSERT INTO hashtag (hashtag, link) VALUES ('$hashtag', '$media_url') ON DUPLICATE KEY UPDATE link='" . $media_url . "'";
if(mysqli_query($link, $sql)){
    echo "Records inserted successfully.";
    echo ($sql);
    echo "<br>";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
  
  
  
  
  }
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.