0

I'm trying to parse an email and find the last occurrence of the pattern XXXX-XX-XX where X is an integer. I then want to insert that into a database, but it's entering 0000-00-00 each time. The other parts (body and from) are successfully parsed.

    // PULL IN EMAIL
    $Parser = new PhpMimeMailParser\Parser();
    $Parser->setStream(fopen("php://stdin", "r"));

    // SET VAIABLES OF 'FROM' AND 'BODY'
    $from_raw = $Parser->getHeader('from');
    $entry_raw = $Parser->getMessageBody('text');

    // GRAB NAME AND EMAIL FROM 'FROM'
    $from = preg_split('/\s*<([^>]*)>/', $from_raw, -1, PREG_SPLIT_NO_EMPTY|PREG_SPLIT_DELIM_CAPTURE);

    $regex = '/.*\b\K\d{4}-\d{2}-\d{2}/s';
    preg_match_all($regex, $entry_raw, $date, PREG_SET_ORDER, 0);

    $date = $date[0][0];

    // TRIM 'BODY' DOWN TO FIRST PARAGRAPH
    $entry = explode("\n", $entry_raw);

    // CONNECT TO DATABASE
    $hostname = "xxx";
    $database = "xxx";
    $username = "xxx";
    $password = "xxx";

    $con = mysqli_connect($hostname, $username, $password, $database) or die (mysql_error());

    if ($con->connect_error) {
        die("ERROR: Unable to connect: " . $con->connect_error);
    }

    $stmt = $con->prepare("SELECT id FROM users WHERE email=?");
    $stmt->bind_param("s", $from[1]);
    $stmt->execute();
    $stmt->bind_result($id);
    $stmt->fetch();
    $stmt->close();

    if ($id) {
        $stmt = $con->prepare("INSERT INTO entries (entry, user_id, date) VALUES (?, ?, ?)");
        $stmt->bind_param("sis", $entry[0], $id, $date);
        $stmt->execute();
        $stmt->close();
    }

I've tried this code in a separate file and it works fine:

    $entry_raw = "this is some text 1111-11-11 2222-22-22 this is some more text";
    $regex = '/.*\b\K\d{4}-\d{2}-\d{2}/s';
    preg_match_all($regex, $entry_raw, $date, PREG_SET_ORDER, 0);
    $date = $date[0][0];
    echo $date;
Sebastian
  • 3,548
  • 18
  • 60
  • 95
  • what's the `date` column's type? – Funk Forty Niner Jan 25 '18 at 19:46
  • @FunkFortyNiner type is date – Sebastian Jan 25 '18 at 19:46
  • well, the result I got back from your last bit of code was `2222-22-22` so that worked ok. Yet, where is this coming from `$from[1]`? Maybe that binded result failed. Try checking for errors on both queries and php error reporting. – Funk Forty Niner Jan 25 '18 at 19:48
  • I've added some code. I'm using an extension to parse most of the fields from the raw email – Sebastian Jan 25 '18 at 19:51
  • I can't spot it. Try `var_dump()`'ing stuff around and make sure there isn't any characters that are getting interpreted as a possible directive of sorts. If that's the case, try putting the `$entry_raw` inside single quotes. – Funk Forty Niner Jan 25 '18 at 19:55
  • The difficulty is that this is a piped script so I can't `var_dump()`, unless there's a way of writing it to a file? – Sebastian Jan 25 '18 at 19:56
  • You can try doing that. And/or echo / print_r the variables if possible. – Funk Forty Niner Jan 25 '18 at 19:58
  • There is one thing though, you didn't store the results using `store_result()` anywhere and checking if that row exists. See one of my answers here that might help https://stackoverflow.com/a/22253579/ and this might be the cause, or "a" cause. There is a prepared statement method in there. – Funk Forty Niner Jan 25 '18 at 20:02
  • @FunkFortyNiner yes, I've just run `file_put_contents("logs/error.txt", $date);` after `$stmt->bind_param("sis", $entry[0], $id, $date);` and it logs the date correctly, so something must be happening there. – Sebastian Jan 25 '18 at 20:08
  • the only thing left that I can think of is this. The parsed `$date` has a date in this format: `YYYY-DD-MM` as opposed to `YYYY-MM-DD` which is what MySQL uses as the date format. https://dev.mysql.com/doc/refman/5.7/en/datetime.html - so what is that date that was saved in your file and the one in the parsed one? – Funk Forty Niner Jan 25 '18 at 20:12
  • Fixed it! I was testing with dates like 1111-11-11 or 2222-22-22 which MySQL doesn't see as real years. If I put something like 2018-01-01 it works fine. – Sebastian Jan 25 '18 at 20:14
  • well.. that I had a feeling about earlier but didn't think that that would have been the real date... and it may be due to the 2038 date bug. Or the 1111 not being a date in the real years range. – Funk Forty Niner Jan 25 '18 at 20:14
  • there's something that doesn't wash for me. The manual on DATE https://dev.mysql.com/doc/refman/5.7/en/datetime.html states *"The supported range is '1000-01-01' to '9999-12-31'."* - You sure there isn't a hidden character somewhere? a linefeed? – Funk Forty Niner Jan 25 '18 at 20:21
  • All I changed was the date. How could I test for hidden characters? – Sebastian Jan 25 '18 at 20:22
  • you would need to `var_dump()` and a hex check on it but since you can't, well... you'll never really know. – Funk Forty Niner Jan 25 '18 at 20:22

0 Answers0