How can I change mysql_result to mysqli?

Report · Dec 19, 2016

Until one month ago I worked with CS6, and I created a site with login and authorisation by means of the server behaviours. Unfortunately these options are not available anymore on CC2017 and I am not able to install this extension either.

The problem is that I have to change my site from mysql to mysqli. Most code is easy to change: you only have to add the i and sometimes you have to add $connection. However the problem starts with mysql_result. How can I change this code to mysqli?: $loginStrGroup = mysql_result($LoginRS,0,'authorisation');

I hope that somebody can help me, because I am starting to get desperate.

Report · Dec 19, 2016

Its probably best you drop the old DW code as that wont mix with the new msqli code.

Below (scroll down) is mysqli login code example, replace ('localhost' , 'root' , 'root' , 'users_table') with the name of your server, username, password and database name.

The login form uses - name="username" and name="password" as the 'name' attribute values.

You get those values from the submitted form and asign them to variables:

$username = $conn->real_escape_string(trim($_POST['username']));

$password = $conn->real_escape_string(trim($_POST['password']));

The mysqli query selects ALL from the 'users' table in the 'users_table' database (you need to change the table name to that of your own table)

$sql = 'SELECT * FROM users';

If the query finds a match it takes you to a page named 'secure_page.php' (you can change the name of the secure_page.php in the script to what you like)

if ($row['username'] == $username && $row['password'] == $password) {

$_SESSION['username'] = $username;

header ('Location: secure_page.php');

}

If it doesnt find a match the script asigns an error message to a variable named '$response':

$response = "Sorry you do not have permission to access this website";

If the '$response' variable is set it gets echoed to the page (see full code below)

<?php

if(isset($response)) {

echo $response;

}

?>

You will need to make sure your database name columns and those in the script are correct ie it is no good using this:

if ($row['username'] == $username && $row['password'] == $password)

if the column names in your database are 'fred' and 'flintstone' - you would use:

if ($row['fred'] == $username && $row['flintstone'] == $password)

Copy and save as login.php

<?php session_start() ?>

<?php $conn = new mysqli('localhost' , 'root' , 'root' , 'users_table'); ?>

<?php

if (array_key_exists('submit', $_POST)) {

$username = $conn->real_escape_string(trim($_POST['username']));

$password = $conn->real_escape_string(trim($_POST['password']));

$sql = 'SELECT * FROM users';

$result = $conn->query($sql) or die($conn->error);

while ($row = $result->fetch_assoc()) {

if ($row['username'] == $username && $row['password'] == $password) {

$_SESSION['username'] = $username;

header ('Location: pass_secure.php');

}

else {

$response = "Sorry you do not have permission to access this website";

}

?>

<!DOCTYPE>

<html>

<head>

<title>Security Login</title>

</head>

<body>

<?php

if(isset($response)) {

echo $response;

}

?>

<label>Username</label><br />

<label>Password</label><br />

</form>

</body>

</html>

<?php session_start() ?>

<?php

if (!isset($_SESSION['username'])) {

header("Location: login.php");

}

?>

Report · Dec 19, 2016

Thank you very much for your answer. However I do not know ho to change my code with your information. The site is quite complicated with different authorisation levels and I am not sure whether the site will still work when I use your information. Maybe I did not give you enough information, so I will send you the complete code before the form. I hope you can help me, by only changing a part of the code. The problem line is bold-printed.

<?php require_once('../Connections/workspace.php'); ?>

<?php

//initialize the session

if (!isset($_SESSION)) {

session_start();

}

// ** Logout the current user. **

$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";

if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){

$logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){

//to fully log out a visitor we need to clear the session varialbles

$_SESSION['MM_Username'] = NULL;

$_SESSION['MM_UserGroup'] = NULL;

$_SESSION['PrevUrl'] = NULL;

unset($_SESSION['MM_Username']);

unset($_SESSION['MM_UserGroup']);

unset($_SESSION['PrevUrl']);

$logoutGoTo = "logout_it.php";

if ($logoutGoTo) {

header("Location: $logoutGoTo");

exit;

}

?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysqli_real_escape_string") ? mysqli_real_escape_string($workspace, $theValue) : mysqli_escape_string($workspace, $theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

?>

<?php

// *** Validate request to login to this site.

if (!isset($_SESSION)) {

session_start();

}

$loginFormAction = $_SERVER['PHP_SELF'];

if (isset($_GET['accesscheck'])) {

$_SESSION['PrevUrl'] = $_GET['accesscheck'];

}

if (isset($_POST['username'])) {

$loginUsername=$_POST['username'];

$password=$_POST['password'];

$MM_fldUserAuthorization = "authorisation";

$MM_redirectLoginSuccess = "pagina iniziale.php";

$MM_redirectLoginFailed = "../index.html";

$MM_redirecttoReferrer = false;

mysqli_select_db($workspace, $database_workspace);

$LoginRS__query=sprintf("SELECT username, password, authorisation FROM users WHERE username=%s AND password=%s",

GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));

$LoginRS = mysqli_query($workspace, $LoginRS__query) or die(mysqli_error());

$loginFoundUser = mysqli_num_rows($LoginRS);

if ($loginFoundUser) {

// $loginStrGroup = mysql_result($LoginRS,0,'authorisation');

$loginStrGroup = function mysqli_result($LoginRS, $row, 'authorisation') {

$LoginRS->data_seek($row);

$datarow = $LoginRS->fetch_array();

return $datarow['authorisation'];

}

if (PHP_VERSION >= 5.1) {session_regenerate_id(true);} else {session_regenerate_id();}

//declare two session variables and assign them

$_SESSION['MM_Username'] = $loginUsername;

$_SESSION['MM_UserGroup'] = $loginStrGroup;

if (isset($_SESSION['PrevUrl']) && false) {

$MM_redirectLoginSuccess = $_SESSION['PrevUrl'];

}

header("Location: " . $MM_redirectLoginSuccess );

}

else {

header("Location: ". $MM_redirectLoginFailed );

}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

etcetera......

Report · Dec 19, 2016

I dont use the DW server behaviours any longer as they are outdated and a complete wreck.

How can I change mysql_result to mysqli?

1 Correct answer

Photos