homepage Welcome to WebmasterWorld Guest from 54.237.54.83
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
help with mysqli prepared statements
mihomes




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

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.

 

mihomes




msg:4622781
 1:42 pm on Nov 12, 2013 (gmt 0)

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."'");
mihomes




msg:4622828
 6:11 pm on Nov 12, 2013 (gmt 0)

solved...

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved