How can I get a null field from the Twitter API to play nice with my database?

Question

Forgive me if I could have any sort of fundamental error here. I'd imagine there's something simple I'm missing. I'm looking to store Twitter updates in a database with only a few fields: an auto-increment index, the time posted, the actual status update & the user id the update is in reply to.

I'm simply storing this last field so I can provide a method of filtering out replies.

But it appears that my SQL code is throwing an error. As of this writing, the SQL properly inserts the two most recent updates, which are both replies to another user and, therefore, have data in the in_reply_to_user_id field. But on the third update, which is not in reply to anyone, I get the following error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

INSERT IGNORE INTO updates (time, status, postid, reply) VALUES (1260070319, 'I guess Johnny Cash knew what he was talking about in that \"A Boy Named Suh\" song. That guy is both fast and mean.', '6389320556', )

Twitter's API states no default value for this parameter. I tried the same query with the "favorited" parameter, and it correctly labeled each row with "false" in my database. So I'm assuming my problem is with inserting an empty string.

For what it's worth, here is my CodeIgniter method:

function insert_tweet($tweet){
 foreach($tweet as $t) {
  $when = strtotime($t->created_at);
  $status = $t->text;
  $postid = $t->id;
  $reply = $t->in_reply_to_user_id;
$sql = 'INSERT IGNORE INTO updates (time, status, postid, reply) VALUES (?, ?, ?, ?)';
  $this->db->query($sql, array($when, $status, $postid, $reply));
 }
}

Any help you could give would be great! I hope I've provided enough information!

More info: I should also note that CodeIgniter is throwing the following error:

A PHP Error was encountered

Severity: 4096

Message: Object of class stdClass could not be converted to string

Filename: database/DB_driver.php

Line Number: 598

In the off chance that this proves some sort of CI idiosyncrasy to be at fault.

Answer 1

I don't know php but in asp.net style pseudo code i would do this...

$this->db->query($sql, array($when, $status, $postid, string.IsNullOrEmpty($reply) ? null : $reply));

Hopefully you can adapt, just need to deal with the empty parameter.

Answer 2

I'm not sure about codeigniter (never used it) but I know that you will need to get the null value to appear as NULL

function insert_tweet($tweet){
    foreach($tweet as $t) {
            $when = strtotime($t->created_at);
            $status = $t->text;
            $postid = $t->id;
            $reply = (is_null($t->in_reply_to_user_id) ? 'NULL' : $t->in_reply_to_user_id);
            $sql = 'INSERT IGNORE INTO updates (time, status, postid, reply) VALUES (?, ?, ?, ?)';
            $this->db->query($sql, array($when, $status, $postid, $reply));
    }

}

I'm not sure if that will produce the desired SQL:

INSERT IGNORE INTO updates (time, status, postid, reply) VALUES (1260070319, 'I guess Johnny Cash knew what he was talking about in that \"A Boy Named Suh\" song. That guy is both fast and mean.', '6389320556', NULL)

edit Also make sure that the field in the database can be null.

Hope this helps..

Answer 3

I don't speak codeigniter, but I can tell you how to fix your problem.
You have two options

1. Check is the string empty, if so, instead of having a blank value, put in the null keyword.

2. Check is the value empty, if so, leave that column out of the insert statement's cloumns and values.

Hope this helps

Answer 4

Why not alter the last column and make it default to NULL?

When you want to insert some record without this field you just ignore it in the 'INSERT' statement.

And be sure not to put a pending comma between the last column value and the right parenthisis. :)

Answer 5

You're coming from the CodeIgniter forums right? I think the issue is coming from your Twitter library. Can you post that code? For somereason, it's returning a NULL status as an Object, which it shouldn't do.

Answer 6

Hey guys, the answers here seemed to be close, but no cigar. I got some help from the great folks on the CodeIgniter forums, and here's what I came up with:

$reply = (is_null($t->in_reply_to_user_id) || is_object($t->in_reply_to_user_id) ? ‘NULL’ : $t->in_reply_to_user_id);

Essentially, like Zack said, it is returning the empty field from the Twitter API as an empty object instead of null. So I needed to check if this is either null or an object, then return NULL. And if not, return the user_id string.

Thanks a ton for your help!