Passing mysql resource around

[irken]

Hello again :).

Being a C# programmer I'm used to just being able to pass objects around as I please, but I can't seem to figure out how I'm supposed to do it in PHP. I'm very new to the language, but catching on.

I have a file called database.mysql.php which is located in my /includes/ folder. This file get's included in two scripts I currently use. player.login.php and player.create.php. Now, my question is.. when including this file in player.create.php and executing queries, then transfering the user to player.login.php if the creation of the user was a success - does the $connection object from the .mysql.php file result in another connection opening, whilst the old connection is still open (when issuing it from .create.php)?

Do I now have 2 connections open? I'm just going to post both the login, create and mysql files below.

Scenario:

player.create.php -> includes database.mysql.php ($connection variable gets triggerd and does it's thing)
player.login.php -> includes database.mysql.php ($connection variables again get's triggered and does it's thing)

No where am I closing the connection to the server, does this result in two open connections? That's bad!

[code]
/includes/database.mysql.php

<?php

    Header("Content-type: text/plain\n\n");
   
    if ($ping != "pong")
    {
        session_start();
       
        echo 'You been bad. ' . gethostbyname($REMOTE_ADDR);
        echo "\n";
       
        if ($_SESSION['username'])
        {
            // Attempt to find the user's session, and store the attempt by username.
            // TODO
            echo 'You are: ' . $_SESSION['username'];
            session_destroy();
            exit();
        }
       
        // User was not logged in, store the attempt by hostname.
        // TODO
        session_destroy();
        exit();
    }

    // Open a connection to the MySQL server.
    $connection = mysql_connect("localhost", "root", "master", "pixels")
        or die('Error in query: ' . $query . ' - ' . mysql_error());

    // Select the database.
    mysql_select_db("pixels", $connection)
        or die('Error in query: ' . $query . ' - ' . mysql_error());
?>
[/code]

[code]
player.create.php

<?php

    session_start();
    ob_start();
   
    // User is already logged in.
    if ($_SESSION['username'])
    {
        ob_flush();
        Header('Location: /pixels/index.php');
    }
   
    // Username is not being posted.
    if (!$_REQUEST['username'])
    {
        ob_flush();
        Header('Location: /pixels/index.php');
    }
   
    $ping = "pong";
    include('../includes/database.mysql.php');
   
    $username = $_REQUEST['username'];
    $password = $_REQUEST['password'];
    $password_encoded = md5($password);
   
    $query = "SELECT username FROM `users` WHERE username='$username'";
    $result = mysql_query($query)
        or die('Error in query: ' . $query . ' - ' . mysql_error());
   
    // We found a match.
    if (mysql_num_rows($result) > 0)
    {
        echo 'User already exists.';
        session_destroy();
    }
    else
    {
        $query = "INSERT INTO `users` (username, password) VALUES ('$username', '$password_encoded')";
        $result = mysql_query($query)
            or die('Error in query: ' . $query . ' - ' . mysql_error());

        Header('Location: /pixels/index.php'); // Transfer to main page, there the user can select weather to login.
    }
   
    ob_flush();
?>
[/code]

[code]
player.login.php

<?php

    session_start();
    ob_start();
   
    // User is already logged in.
    if ($_SESSION['username'])
    {
        ob_flush();
        Header('Location: /pixels/index.php');
    }
   
    // Username is not being posted.
    if (!$_REQUEST['username'])
    {
        ob_flush();
        Header('Location: /pixels/index.php');
    }
   
    $ping = "pong";
    include('../includes/database.mysql.php');
   
    $username = $_REQUEST['username'];
    $password = $_REQUEST['password'];
    $password_encoded = md5($password);
   
    $query = "SELECT username, password FROM `users` WHERE username='$username' AND password='$password_encoded'";
    $result = mysql_query($query)
        or die('Error in query: ' . $query . ' - ' . mysql_error());
   
    // We found a match.
    if (mysql_num_rows($result) > 0)
    {
        // Register the session.
        $_SESSION['username'] = $username;
        $_SESSION['password'] = $password_encoded;
       
        Header('Location: /pixels/index.php');
    }
    else
    {
        echo 'Wrong username and/or password.';
        session_destroy();
    }
   
    ob_flush();
?>
[/code]

[hitman6003]

Variables are not persistant, so each time the user is sent to a new page they are "reset", unless you save them in some manner

[irken]

[quote author=hitman6003 link=topic=118541.msg484458#msg484458 date=1166059097]
Variables are not persistant, so each time the user is sent to a new page they are "reset", unless you save them in some manner
[/quote]

Hi.

That's good news. Thanks for pointing that out. Maybe it's time to start thinking OOP, considering this connection object is going to be used for each user, every second or so. Great, more work  :D. (Wonder if PHP has static variables? Why not.. static's are your friend!)

[hitman6003]

Remember that PHP does not use pooled connections like Java.

If you are wanting to use a persistant connection, you may want to look into the mysql_pconnect function.

http://www.php.net/mysql_pconnect

[irken]

[quote author=hitman6003 link=topic=118541.msg484464#msg484464 date=1166059393]
Remember that PHP does not use pooled connections like Java.

If you are wanting to use a persistant connection, you may want to look into the mysql_pconnect function.

http://www.php.net/mysql_pconnect
[/quote]

Hi.

Will definitely read up on that. My only concern would be that if the script crashes somehow, the connection would stay open. Not sure how well PHP's try/catch workes, but I guess it's as good as any, and might solve that issue.

Thanks again.

[hitman6003]

when the script stops running, the variables, including database connections, go away

[irken]

Hi.

I had to bring this topic up again now that I'm using a different method of getting the file that's connecting to the MySQL server, which got me confused.

In my frameset.php file I have an ajax object which GET's to position.php once I click somewhere - that's all good. Position.php does the following:

[code]
<?php

    header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

    session_start();
   
    if ($_SESSION['username'])
    {
        $ping = "pong";
        include($_SERVER['DOCUMENT_ROOT'] . '/includes/database.php');
   
        $username = $_SESSION['username'];
        $password = $_SESSION['password'];
        $hostname = $_SESSION['hostname'];
   
        if ($_GET['x'] || $_GET['y'] || $_GET['field'])
        {
            $x = $_GET['x'];
            $y = $_GET['y'];
            $field = $_GET['field'];
       
            // Open a connection to the MySQL server.
            $connection = new mysql();
            $connection->connect();
       
            $query = "UPDATE `users` SET x='$x', y='$y', field='$field' WHERE username='$username'";
            $connection->query($query);
            //$connection->disconnect();
            exit;
        }
        else
        {
            // Get online users and stuff.
            exit;
        }
    }
    else
    {
        Header('Location: /index.php');
    }
?>
[/code]

You'll notice that $connection->disconnect(); is commented out. Question is.. after it's done executing these two lines:

[code]
            $query = "UPDATE `users` SET x='$x', y='$y', field='$field' WHERE username='$username'";
            $connection->query($query);
[/code]

does it automatically close the connection? Or would I need to exit(); as I'm done with the script for now.

Thanks.

[irken]

Small change in the above code.

Is it possible to get a display of number of connections to the MySQL server, from the script?