PHP Function Calling MySQL

Question

I have made a function in PHP to grab info from a MySQL database but am not sure about something.

Currently the function looks like this:

function profile_info($option, $size = NULL){

    // MySQL Connection Info
    $mysql_hostname = "";
    $mysql_username = "";
    $mysql_password = "";
    $mysql_database = "";
    $mysql_table    = "";

    // MySQL Connect
    $con = mysqli_connect($mysql_hostname,$mysql_username,$mysql_password,$mysql_database);

    // Check the Database Connection
    if (mysqli_connect_errno()){
        echo (mysqli_connect_error());
    }

    // Define UID
    $uid = $_SESSION['login'];

    // Deploy Query
    $result = $con->query("SELECT * FROM $mysql_table WHERE uid='$uid'");

    // Define Row For All Data
    $row = $result->fetch_assoc();

    if($option == "firstname"){
        echo $row['first_name'];
    }
    if($option == "lastname"){
        echo $row['last_name'];
    }
    if($option == "nickname"){
        echo $row['nick_name'];
    }
    if($option == "email"){
        echo $row['email'];
    }
    if($option == "dob"){
        echo $row['date_of_birth'];
    }

    if($option == "status"){
        echo $row['status'];
    }
    if($option == "gravitar"){
        echo ("http://www.gravatar.com/avatar/" . md5( strtolower( trim( $row['email'] ) ) ) . "?d=mm&s=" . $size);
    }
    $result->close();
    $con->close();
}

I've tested it and it works perfectly.

Now my question is, does it make a new connection to the database everytime I call profile_info?

If so, how do I fix it so that it only calls the database once for all the information.

Regards, Tim

Answer 1

use PDO prepared statements. its secure and easy to use. here's something you could do to store it as a session. instead replace session variables with normal variables and you could call to this each time without using session variables.either way its possible, but session method would not call database each and every time after its initialized this way.

    //in case if you want to store all in session
    session_start();

    $con=new PDO('mysql:host='.$host.';dbname='.$dB.'', $dbUser, $dbPass);
        $q=$con->prepare("SELECT * FROM $mysql_table WHERE uid=:uid");
        $q->bindParam(":uid",$uid);
        if ($q->execute()) 
        {
            while ($row = $q->fetch()) 
            {
            $_SESSION['fname']=$row['first_name'];
            $_SESSION['lname']= $row['last_name'];
            $_SESSION['nick']=$row['nick_name'];
            $_SESSION['email']=$row['email'];
            $_SESSION['dob']=$row['date_of_birth'];
            $_SESSION['status']=$row['status'];
            }
        }
        else
        {
            echo '<pre>';
            print_r($q->errorInfo());
            echo '</pre>';
        }
     $con=null;

Answer 2

Q: does it make a new connection to the database everytime I call profile_info?

A: yes.

Q: how do I fix it so that it only calls the database once for all the information.

If you are calling this function more than once (or other functions also need a connectioN), then move the $con - mysqli_connect() and $con->close(); operations outside of the function, to a higher scope. Usually either at the start of the entire script, if you will almost always need a connection, or at the point in the script, at the point it's determined that a database connection will actually be needed.

Then pass the connection object $con as an argument to the function, e.g.

function profile_info($con, $option, ...

This will avoid the overhead of "churning" connections.

---

While you're at it, you might want to consider closing up some SQL Injection vulnerabilities. Including "unsafe" variables in SQL text can be an issue.

$sql = "SELECT * FROM $mysql_table WHERE uid='" 
     .  mysqli_real_escape_string($con, $uid)
     . "'");
$result = $con->query($sql);

Answer 3

Yes, it always connect for information.

You can use $_SESSION.

You can get user datas and save it to $_SESSION and get them from session. If session is not created before you can get them from database and save it to $_Session.

session_start();
function profile_info($option, $size = NULL){
    if(!$_SESSION['user']){
    // MySQL Connection Info
    $mysql_hostname = "";
    $mysql_username = "";
    $mysql_password = "";
    $mysql_database = "";
    $mysql_table    = "";

    // MySQL Connect
    $con = mysqli_connect($mysql_hostname,$mysql_username,$mysql_password,$mysql_database);

    // Check the Database Connection
    if (mysqli_connect_errno()){
        echo (mysqli_connect_error());
    }

    // Define UID
    $uid = $_SESSION['login'];

    // Deploy Query
    $result = $con->query("SELECT * FROM $mysql_table WHERE uid='$uid'");

    // Define Row For All Data
    $row = $result->fetch_assoc();
    $_SESSION['user'] = $row;

    echo $_SESSION['user'][$option]
    $result->close();
    $con->close();
    }else{
        echo $_SESSION['user'][$option]
    }
}

I am not coding PHP for years so there can be a syntax or logic mistake.

Answer 4

How about storing a global variable with the row, and only loading the data once. Then, if it's been loaded once already, just use the data stored in $data.

$called = false;
$data = null;
function profile_info($option, $size = NULL){
  $row;
  if ($called) {
    $row = $data;
  } else {
    // it's been called
    $called = true;
    // get the info from database (removed for space)
  }
  // Define Row For All Data
  $row = $result->fetch_assoc();
  // define global var for row data
  $data = $row;
  // use the info (removed for space) 
}