Insert FB birthday into Mysql gives NULL

Question

I Get NULL when i insert the Birthday and current time in MYSQL

$graph_url = "https://graph.facebook.com/me?access_token=".$params['access_token']."&format=json&fields=birthday";
$user = json_decode(file_get_contents($graph_url),true);
     echo("birthday is " . $user['birthday']);

SQL

$queryip = "insert into table (birthday,currenttime) values (".$user['birthday'].','.CURDATE().")";
                $resultip = mysql_query($queryip);

MYSQL Structure Both the birthday,currenttime fields are Type DATE

`CURDATE()` is a MySQL function, not PHP so it should be contained within the quotes. — Michael Mior, May 10 '12 at 19:29

score 2 · Accepted Answer · answered May 10 '12 at 19:26

2

You need to request the birthday_date column, not birthday, because it's always in the same format, DDMMYYY. After that, you need to add the following to your INSERT query:

STR_TO_DATE('" . mysql_real_escape_string ($user ['birthday_date']) . "', '%m/%d/%Y')

answered May 10 '12 at 19:26

Jeroen

13,056
4
42
63

I am using PHP / MYSQL > this statement is showing syntax error. – Yahoo May 10 '12 at 19:56
1

+1 for escaping at least! @AdiMathur: `STR_TO_DATE` is a MySQL function, not a PHP one. You need to understand the different between PHP and MySQL. What Jeroen has written more or less replaces `".$user['birthday'].'`. Follow Michael Mior's advice too. – Bruno May 10 '12 at 20:00

score 2 · Answer 2 · answered Aug 16 '12 at 19:43

2

In PHP:

function formatDateFbToSql($fbdate)
{
    // facebook user_birthday format (02/19/1988)
    $date = DateTime::createFromFormat('m/d/Y', $fbdate);
    // mysql format (1988-02-19)
    return $date->format('Y-m-d');
}

answered Aug 16 '12 at 19:43

psycho brm

7,494
1
43
42

score 1 · Answer 3 · edited May 23 '17 at 10:34

(Despite being a different language and RDBMS, this is the same problem as in this question.)

Firstly, SQL strings must be contained in '': you're clearly missing them in (".$user['birthday'].' .... Secondly, and more importantly, they must be escaped, to prevent SQL injection attacks (and to make sure the string is valid anyway).

In general, it's better to use prepared statements to pass the parameters to SQL query instead of inserting the variables in the query itself: this should take care of the escaping and conversion for you.

You'll also need to cast the birthday string into a date if necessary.

I'd recommend using mysqli and prepared statements instead. (There are a number of examples out there, perhaps this would help.)

EDIT:

Try something along these lines perhaps (assuming you've switched to mysqli):

$query = "insert into table (birthday,currenttime) values (STR_TO_DATE(?, '%m/%d/%Y'), CURDATE())";
if ($stmt = $mysqli->prepare()) {
    $stmt->bind_param('s', $user['birthday']);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

(This might need to be birthday_date as Jeroen suggested too.)

@Bruno- the birthday in the datebase is comming as 0000-00-00 — Yahoo, May 10 '12 at 19:57

score 0 · Answer 4 · answered May 10 '12 at 19:26

0

Did you try replacing ".$user['birthday'].','.CURDATE()." into '".$user['birthday']."',CURDATE() ?

answered May 10 '12 at 19:26

Vaidotas Strazdas

686
3
14

the birthday in the datebase is comming as 0000-00-00 but the time is comming fine now.. – Yahoo May 10 '12 at 19:57

score 0 · Answer 5 · answered Jan 03 '14 at 19:29

0

Try

$user['birthday'] = date("Y-m-d", strtotime($user['birthday']));

before the SQL insert statement.

answered Jan 03 '14 at 19:29

PJunior

2,649
1
33
29

Insert FB birthday into Mysql gives NULL

5 Answers5

Linked