Welcome to WebmasterWorld Guest from 23.20.223.88

Forum Moderators: open

Message Too Old, No Replies

help with mysqli prepared statements

     
8:45 am on Nov 12, 2013 (gmt 0)

10+ Year Member



I am trying to move from working MySQL to mysqli prepared statements... I did a lot of reading today and have seen a ton of different ways the same operation is handled.

Looking for some advice/assistance on what I came up with.

Original working :


function user_login($user)
{
// Update user's last ip and last login date in db
mysql_query("UPDATE test_users SET lastIP=currIP, dtLastLogin=dtCurrLogin WHERE user='".$user."'");

// Update user's current ip and current login date in db
mysql_query("UPDATE test_users SET currIP='".$_SERVER['REMOTE_ADDR']."', dtCurrLogin=UTC_TIMESTAMP() WHERE user='".$user."'");

// Store user db info in session for use
$row = mysql_fetch_assoc(mysql_query("SELECT id,user,pass,email,timezone,lastIP,currIP,dtLastLogin,dtCurrLogin FROM test_users WHERE user='".$user."'"));

$_SESSION['id']= $row['id'];
$_SESSION['user']= $row['user'];
$_SESSION['pass'] = $row['pass'];
$_SESSION['timezone'] = $row['timezone'];
$_SESSION['email'] = $row['email'];
$_SESSION['lastIP'] = $row['lastIP'];
$_SESSION['currIP'] = $row['currIP'];
$_SESSION['dtLastLogin'] = $row['dtLastLogin'];
$_SESSION['dtCurrLogin'] = $row['dtCurrLogin'];
}


and what I came up with :


function user_login($user)
{
// Update user's last ip and last login date in db
$stmt = $mysqli->prepare("UPDATE test_users SET lastIP = currIP, dtLastLogin = dtCurrLogin WHERE user= ?");
// bind params
$stmt->bind_param('s', $user);
// execute prepared statement
$stmt->execute();

// Update user's current ip and current login date in db
$stmt = $mysqli->prepare("UPDATE test_users SET currIP = ?, dtCurrLogin = UTC_TIMESTAMP() WHERE user = ?");
// bind params
$stmt->bind_param('ss', $_SERVER['REMOTE_ADDR'], $user);
// execute prepared statement
$stmt->execute();

// Store user db info in session for use
$stmt = $mysqli->prepare("SELECT id,user,pass,email,timezone,lastIP,currIP,dtLastLogin,dtCurrLogin FROM test_users WHERE user = ?");
// bind params
$stmt->bind_param('s', $user);
// execute prepared statement
$stmt->execute();
// Bind result variables
$stmt->bind_result($_SESSION['id'], $_SESSION['user'], $_SESSION['pass'], $_SESSION['timezone'], $_SESSION['email'], $_SESSION['lastIP'], $_SESSION['currIP'], $_SESSION['dtLastLogin'], $_SESSION['dtCurrLogin']);
// fetch values
$stmt->fetch();
// close statement
$stmt->close();
}


The last section I part I couldnot find any reference to use the same method of storing the values in a 'row' array which seemed strange to me as it was soo easy that way. I have a feeling this is completely wrong, but first shot at this and have yet to come across any good reading about it.
1:42 pm on Nov 12, 2013 (gmt 0)

10+ Year Member



Couple hours has passed and things have changed... I am now running into a problem with the following :


// Update user's last ip and last login date in db
$stmt = $mysqli->prepare("UPDATE test_users SET lastIP=currIP, dtLastLogin=dtCurrLogin WHERE user= ?");
// bind params
$stmt->bind_param('s', $user);
// execute prepared statement
$stmt->execute();
// close statement
$stmt->close();



Basically all I am doing is copying one column to another two different times. This worked fine when using regular MySQL, however, it appears to be a problem using mysqli.

I am receiving 'Fatal error: Call to a member function prepare() on a non-object in' for the prepare.

Ideas? Can you not 'copy' when using mysqli?

This was my previous MySQL version which worked fine :

mysql_query("UPDATE test_users SET lastIP=currIP, dtLastLogin=dtCurrLogin WHERE user='".$user."'");
6:11 pm on Nov 12, 2013 (gmt 0)

10+ Year Member



solved...
 

Featured Threads

Hot Threads This Week

Hot Threads This Month