[PHP] MySQL and PHP

[peacesam]

It's gonna take me sometime to learn all this. I have never dealt with PHP and MySQl but i know some programming languages and I've seen some sql work on Unix system at work...
Right now i'm trying to add a comment box and a shoutout box to my website but apparently it requires a database so to store the data, which database requires mysql to create one.

 

[cursedpsp]

You made the mySQL connections a bit complicated for beginners
Where you put this

$username = ""; // MySQL Username
$password = ""; //MySQL Password
$server = ""; // MySQL server you wish to connect to. Usually "localhost"
$mysqlconnection = mysql_connect($server, $username, $password);
if (!$mysqlconnection) {
   die('There was a problem connecting to the mysql server. Error returned: '. mysql_error());
}

It was alot more simpler to put this

mysql_connect("host", "username", "password") or die(mysql_error());

It saves 6 lines and 4 variables

 

[jeyam]

thanks for it

 

[ecstars]

Good Job !

 

[flexnets]

Fantastic idea. Can save execution time too !

 

[hezuo]

Thanks for the tutorial! Please, try to keep them coming.

 

[Steeevoe]

Make a file with something like the following...

<?php 

$host = 'host';
$user = 'user';
$pass = 'pass';

function dbConnect($database='') {
    global $host, $user, $pass;
    
    mysql_connect($host, $user, $pass)
        or die('The site database appears to be down.');

//    if ($db!='' and !mysql_select_db($database))
        mysql_select_db($database) or die('The site database is unavailable.');
    
}
?>

then all you have to do on any page on your website is:
include file
use dbConnect("yourdatabasename")

and your in!

 

[ppeetteerr]

:eek4:tnx 4 all info:eek4:

 

[johndass]

Excellent work Steeevoe! :biggthump

Putting as a separate file and function to access database will enable a clean code so that any changes to the server or login information can be made at one place and yet can select the relevant database to work with.

 

[Steeevoe]

You can do that for a few other things too. Includes help to make the making of websites more bearable! When I were not with a provider with PHP editing the main navigation menu was such a painful task

 

[kevinscallions]

Check out my website www.ksprog.net and there is information on MySQL and PHP.:drool:

 

[fguy64]

I wonder if he even remembers this thread

Anyway, that's a nice tut. I could suggest changing the DB Host comment to include "but on x10 you must use mysql.x10hosting.com for it to work", but somehow I don't think people will take note of it :dunno:

Is this remark still valid? I'm not sure what you are referring to in the context of this tutorial. Do you mean this line here...

$server = ""; // MySQL server you wish to connect to. Usually "localhost"

and are you saying that the server must be mysql.x10hosting.com


edit: to expand on this I get mixed between the server and the location of the databases. When I see something called MySQL server, I think of an application server. When I see DB_Host I think of where the databases are stored, which may or may not be the same. as the MySQL server. So you have to first create a connection to the server, then you use that to create a connection to a database. Do yo have to actually specify a location for the database, or is it enough that you have specified your server connection in the database connect statement?

edit: ok it's working, i did not have to specify a path with my database name. just specified the database name prefixed by userid_ , and "localhost" as my server.

 

[mrdaro]

thank You very much,

it all seems a bit easier now :wavey:

 

[ogtonganon]

Why can't I create a database. It has a note that says I don't have the privilege.

 

[Gouri]

Why can't I create a database. It has a note that says I don't have the privilege.

Can you tell us where you are seeing this note.

Actually you can create the databases in Cpanel by selecting the "mysql databases"

 

[ogtonganon]

I'm using phpMyAdmin. I'm a little bit familiar with this, but the link to create a database is disabled.

 

[Gouri]

I'm using phpMyAdmin. I'm a little bit familiar with this, but the link to create a database is disabled.

I saw in my phpmyadmin. It is there but i didn't tried to create any.

But using the cpanel option is very much easier than the phpmyadmin.

Just try once.

 

[ogtonganon]

Ok, i'll try that. Thanks.

 

[misson]

Make a file with something like the following...

function dbConnect($database='') {

Personally, I like something similar to:

<?php
class DB {
    static $db = array();
    public var $connection;
    protected var $dbName;

    // may throw a PDOException
    function __construct($dbName='') {
        $this->dbName = $dbName;
        $this->connection = self::connect($dbName);
    }
    function __destruct() {
        self::close($this->dbName);
    }

    // may throw a PDOException
    static function connect($dbName='') {
        if (! isset(self::$db[$dbName]) ) {
            if ($dbName) {
                $dbOpt = ';dbname=' . $dbName;
            } else {
                $dbOpt = '';
            }
            self::$db[$dbName] = array('connection' => new PHP("mysql:host=localhost$dbOpt", 'username', 'password'), 
                                       'count' => 1);
            self::$db[$dbName]['connection']->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } else {
            ++self::$db[$dbName]['count'];
        }
        return self::$db[$dbName]['connection'];
    }
    /* DB::close(): decrement retain count for a DB connection, close if count reaches 0.
     * it's not necessary to call DB::close(...), but it can reduce resource usage.
     * returns False if the connection is still open, True otherwise.
     */
    static function close($dbName='') {
        if (isset(self::$db[$dbName])) {
            if (--self::$db[$dbName]['count'] < 1) {
                unset(self::$db[$dbName]);
                return True;
            }
            return False;
        }
        return True;
}   }
?>

With DB, you can call DB::connect(...) multiple times or create multiple instances and get a shared connection. Note that it also uses PDO (Bryon, the tutorial could stand to be updated in this regard), rather than the outdated mysql driver. DB could be extended to store prepared queries, allowing them to be shared. It also supports RAII:

function topAlbum($id) {
    try {
        $db = new DB('music');
        $stmt = $db->connection->prepare('SELECT alb.id, alb.name, COUNT(*) AS track_cnt, SUM(t.length) AS total_length 
                           FROM albums AS alb JOIN tracks AS t ON alb.id=t.album_id 
                           GROUP BY alb.id, alb.name,votes ORDER BY votes LIMIT 1');
        $stmt->execute();
        return $stmt->fetchObject();
    } catch (PDOException $pe) {
        throw new RuntimeException("Couldn't get top album.", 0, $pe);
    }
    // $db goes out of scope, decrementing the connection's retain count
}

 

[indiaprofessionals]

really gud