edit row if hashtag field already exists with the varible

[dropfaith]

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 February 22, 2017 by dropfaith

[ginerjm]

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?

[dropfaith]

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

[ginerjm]

Sorry.

[cyberRobot]

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.

[Barand]

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)

[dropfaith]

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);

[Barand]

try removing the (..)s from around the insert statement.

[dropfaith]

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);
}
  
  
  
  
  }

[Barand]

 

any way to reverse a foreach.

 

reverse the array first (array_reverse())

Edited February 22, 2017 by Barand