Date/Calendar functions and parameterized queries help needed.

[parkourmumbai]

Are mysqli functions disabled by default in the php configurations for new hosting account users?

I have been banging my head over the past 2 days, trying to get parameterized queries to work on my website for an insert statement, but it keeps giving me error messages. I have searched online for a solution high and low, but the only ones I can find for the specific error message that I recieve is that they ask to check the query itself or the connection string, both of which in my case have been checked thoroughly, and work absolutely fine in other areas of the site that don't work on parameterized queries.
And they also ask to check the syntax, which again, has been tripple checked by me, but it still throws the error.


Secondly, in the same insert statement, I need to post user comments on the same webpage, using a form at the bottom of the page. The form will collect data like the user's name, email, and the comment text, and will pass this data along with a timestamp to the backend database. Now I am located in the Indian Standard Time timezone, whereas the server I'm on (cossacks), is somewhere in the United States. And I'm a total noob where converting dates/time across timezones is concerned, even in the offline world.
Could someone please help me with a script/function that gets the exact datetime on the cossacks server at the moment when the user hits the submit button, then converts it to my local time (IST), and submits that converted datetime to the backend db, at the same time also accounting for daylight savings if any? Or are there any built-in scripts/functions in php that already do this that I am unaware of?

 

[sunils]

Please post the error message to check what could be the problem

 

[parkourmumbai]

These are the error messages I am getting -

Fatal error: Call to a member function bind_param() on a non-object in /home/[domain]/public_html/[webpage].php on line 194

Fatal error: Function name must be a string in /home/[domain]/public_html/[webpage].php on line 193

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage].php on line 15

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage].php on line 24

Warning: mysqli_stmt_affected_rows() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage].php on line 26

Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage].php on line 28



Edit: Oh, BTW, I forgot to mention, I still get these error messages, despite having just upgraded my php version to 2 on x10.



Edit2:
I don't seem to be getting any response here, am I in the wrong section?

 

[dickey]

Are mysqli functions disabled by default in the php configurations for new hosting account users?

I believe you have to upgrade your php to intermediate to do that. I got mine working on intermediate level.

http://x10hosting.com/account

check here and login with your forum account.

 

[parkourmumbai]

Dude, I've already mentioned above that I've done that, upgraded to intermediate php (ver 2). But parameterized queries still won't work on my website. :dunno:

 

[natsuki]

Why didn't you ever try to post the part of the code here? How can people help if they don't know where the problem comes from.. it says boolean given means that it probably is returning false, and <this is not an object>->bind_param() is what it's saying...

if you want to know if mysqli is disabled in your account type and save <?php phpinfo(); ?> and run it, if you see it there, then mysqli is not the problem

 

[scopey]

The following error clearly shows a coding error:

Fatal error: Function name must be a string in /home/[domain]/public_html/[webpage].php on line 193

Show us the source and we may be able to help a little more.

 

[parkourmumbai]

I've checked phpinfo(), and mysqli is enabled on my account.


Allright, the code I'm using is the same as is mentioned in the online php manual on the php website, on a test page on my website.
But it still gives me a variation of those above mentioned error messages, based on whether I'm using the procedural style or the object-oriented style for coding the parameterized query.

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'databasename');
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$stmt = $mysqli->prepare("Insert Into Table (Date, Name, Email, VisitorIp, Text, Field6) Values (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('sssssi', $Date, $Name, $Email, $VisitorIP, $Text, $Field6);
 
$Date = "1998-7-3";
$Name = "John Cusack";
$Email = "123@email.com";
$VisitorIP = "192.168.1.1";
$Text = "Test Data. Test Data.";
$Field6 = 1;
 
/* execute prepared statement */
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
/* close statement and connection */
$stmt->close();
/* close connection */
$mysqli->close();
?>

(Some variables have been changed for security purposes, but I can attest that in the actual code they are accurately supplied)

This code throws the following error message -
Fatal error: Call to a member function bind_param() on a non-object in /home/[domain]/public_html/[webpage] on line n




Okay, here is the same code as above, but this time using procedural style, as opposed to the object-oriented style above.

<?php
$link = mysqli_connect('localhost', 'username', 'password', 'databasename');
/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$stmt = mysqli_prepare($link, "Insert Into Table (Date, Name, Email, VisitorIP, Text, Field6) Values (?, ?, ?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssssi', $Date, $Name, $Email, $VisitorIP, $Text, $Field6);
$Date = "1998-7-3";
$Name = "John Cusack";
$Email = "123@email.com";
$VisitorIP = "192.168.1.1";
$Text = "Test Data. Test Data.";
$Field6 = 1;
 
/* execute prepared statement */
mysqli_stmt_execute($stmt);
printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
/* close statement and connection */
mysqli_stmt_close($stmt);
/* close connection */
mysqli_close($link);
?>

Again, some details have been obscured for security purposes.

And these are the error messages I get this time 'round -
Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage] on line n

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage] on line n

Warning: mysqli_stmt_affected_rows() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage] on line n
0 Row inserted.
Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in /home/[domain]/public_html/[webpage] on line n



And this is the page on the php website where I got the proforma code -
http://us2.php.net/manual/en/mysqli-stmt.bind-param.php

 

[natsuki]

this means the problem is originating from $stmt = $mysqli->prepare(blah); or $stmt = mysqli_prepare(blah); gotta try it first to check

EDIT: your bind_params work a treat, I tested it without touching your params

you just have either some syntax or table related errors in your prepare statement. If you really have a Date column in your table then that makes it ambiguous. Put backticks (`) on columns which resemble SQL reserved words: `Date` instead of Date or better yet, put backticks on them all to be safe.
or put this:

$stmt = $mysqli->prepare("Insert Into `blah` (`blah`,....) Values (?, ?, ?, ?, ?, ?)");
if ($mysqli->errno)
{
	die($mysqli->error);
}

 

[parkourmumbai]

'Hell no, I never use any sql or php reserved words, I know that would be programming suicide. The actual field and variable where I've used Date actually is in camelcase notation and reads something like tablenameDate or $tablenameDate, I've just obscured it over here.

Even my prepare statements are exactly the way they're supplied here, except for my changing the names altogether like tablenameDate to simply Date.

 

[natsuki]

use backticks on every identifier, the table name, columns, etc
else you have a problem in your prepare statement that I can't fix because you've obscured the actual words or your table or db or whatever (because it worked for me)

 

[parkourmumbai]

I don't know man, I even tried using the backticks on all identifiers, still the same result.

And there's nothing wrong with my sql, it's been tripple checked. I've even used the exact same insert query in other places on the site without the parameterized query route, and it has worked fine.

Do I also need to enclose the question marks in backticks?

 

[natsuki]

Nope, just the table name and column names should be enclosed in backticks.
If you put:

<?php.......
$stmt = $mysli->prepare('INSERT INTO `blah` (`blah`, ...) VALUES (?, ?, ...);');
if ($mysqli->errno)
{
    die('Prepare statement Failed: ' . $mysqli->error);
}
$stmt->bind_param('sssssi', $blah, ...);
...?>

to check what kinda SQL error you're getting there

 

[parkourmumbai]

Ah, okay, I see what is the problem.
This is what I get -

Prepare statement Failed: INSERT command denied to user [EMAIL="'username'@'localhost'"]'username'@'localhost'[/EMAIL] for table 'Table'

I just added the insert permission for the particular user and I got -

1 Row inserted.

Thanks man, problem solved. I'll just move this code from the hardcoded test page to my production page, and test to see if the form works. If it does, I'll notify here to close the thread.

Thanks to everyone for their help.




Edit:
Allright, my original issue seems to have been resolved, and I can enter form data using parameterized queries in my db fine, but now I seem to be getting the O'Malley issue.

The program is automatically adding slashes behind single quotes within the string, even when they are legitimate. For example, the word

I'm

is being rendered as

I\'m

How do I get around this?

Is the parameterized query route even supposed to do this? I thought only stipslashes and magic quotes did that?

 

[natsuki]

that's actually normal for database entries, they really need to be escaped (added backslashes) so you'll see I'm becomes I\'m in the database, or else you'll get an SQL syntax error, because ' can terminate a string so you really need to escape it

if you get data from GET/POST/COOKIE then it has slashes added to it, because AFAIK magic quotes is always on, so that's just normal, you just need to stripslashes it, you could check before stripping

<?php.....
if (get_magic_quotes_gpc())
{
    stripslashes(blah);
}.....?>

same as when adding slashes, you should check if magic quotes is on, if it is, you don't need to add slashes anymore

and when you get stuffs out of the db you need to stripslashes them too

 

[parkourmumbai]

that's actually normal for database entries, they really need to be escaped (added backslashes) so you'll see I'm becomes I\'m in the database, or else you'll get an SQL syntax error, because ' can terminate a string so you really need to escape it

But if I'm passing the unclean entry as a sting literal using a parameterized query, wouldn't that escaping seem an unnecessary step?



Allright, issue resolved. Mods, please close this thread as well.

 

[sunils]

/**************closing the thread as requested********/