Switching to PHP7

[SeanF]

Hi:

I have an application which I wrote in PHP/MySQL, beginning in about 2004. It's been growing over the past 15 years. I'm not a professional coder so it's definitely home-grown, backyard code but I've been learning over the years.

It's currently running on PHP5 and I have delayed upgrading to PHP7 because of all of the "mysql" -> "mysqli" conversions that I would need to do.

That said I am now in the process of converting hundreds of pages and thousands of SQL calls to mysqli. (The code is still mostly procedural) Using functions, it's a pretty straight forward process just time-consuming.

A question I have, however is mysqli seems much more strict (and not very intuitive) about what is required for data types.

As an example, I have a table with a date field. The "type" is "date" and the default is "null". However, when I set the "$date" variable explicitly to "NULL", I get the following error:
Incorrect date value: '' for column `managernew`.`biz_devel`.`date` at row 1

If I set the "$date" variable explicitly to "0000-00-00" it works fine... but I don't really want that.

Also, where I have an "integer" field, mysql used to let me pass a '' or a NULL but now I have to explicitly see if there is a value and if not, set it to zero.

What am I missing?

Can someone point be to a resource which explains how mysql and mysqli treat data types differently?

Thanks

[SeanF]

P.S. OK... I think I have partially figured it out

My code looks like this:

if(...some criteria...){
$next_action = NULL;
$next_action_date = NULL;
$assignee = NULL;
}

$insert_record = "INSERT INTO biz_devel(
mgt_co_id,
domain,
company_id,
next_action,
next_action_assignee,
next_action_date,
last_updated_by
)VALUES(
'$mgt_co_id',
'$domain',
'$company_id',
'$next_action',
'$assignee',
'$next_action_date',
'$mgt_user_id'
)";
echo "SQL: $insert_record<br/>"; // Comment out after checking
query($insert_record); // query() is a function that calls mysqli()

The problem appears to be that the values '$next_next_action_date' is interpreted as 'NULL' not as NULL. So mysqli is trying to insert a string into the date field (I think)

This was not a problem with old PHP5/mysql. How to I fix it for PHP7/mysqli?

Thanks again

[LifeinAsia]

May need to use prepared statements. Example:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

[SeanF]

Great, thanks! I was not aware of "prepared statements" but they look like a useful tool.

Thanks again

[HeadElf]

There's a nifty mysql to mysqli conversion tool online. I used it two days ago to convert a bookmarks page and it did a pretty nifty job.

[seabreezecomputers.com ]