php, problem echo'ing results from mysql table

[jamesmorris86]

I'm fairly new to php/mysql and I've been following the w3 tutorials.
i am using this code to connect to my database:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

Is there anyway to echo the results without having to use this whole chunk of code every time?. I tried the following with no luck.

<?php
echo .$row['FirstName']; 
?>

Does anyone know what I'm doing wrong?

 

[FabienO]

You could create a function that gets rows from a database.

GetFullName() {
    //the code
}

and include it on the pages necessary. Call the data by simply running the function.

Or go a step back and create a function that simply runs the query... so it would look like

GetResults($query) {
  // connect
  $r = mysql_query($query);
  return  $r;
  //close con
}

then include that file where needed and

$data = GetResults("SELECT first_name, last_name FROM persons");

then just

<?php foreach($data as $value): ?>
    <p><?php echo $value[first_name]; ?> <?php echo $value[last_name]; ?></p>
<?php endforeach;  ?>

 

[jamesmorris86]

Thanks for your help

 

[sniffy]

You should really be using a PDO to interact with databases in PHP.

 

[jamesmorris86]

Ah man, I've just started feeling comfortable using the mysql extensions.

I want to do things correctly though, are there many benefits using pdo ?

 

[sniffy]

That's hard for me to say really mate as I've always used the PDO. Well for a start it's object based and objects are nice I would imagine it provides better protection against SQL injection over the mysql functions. It's dead simple to use.

<?php
	$host = "localhost";
	$databaseName = "databaseName";
	$username = "username";
	$password = "password";
	
	try{
		$PDO = new PDO("mysql:host=".$host.";dbname=".$databaseName, 
						$username, $password);
						
		$sth = $PDO->prepare ("SELECT * FROM price WHERE price = :price");
		$sth->execute(array(":price"=>$price)); 
		$products = $sth->fetchAll(); //Array of rows
		foreach($products as $product){
			echo $product[0]; //Echo product's ID
			echo $product[1]; //Echo product's name
		}
		
		$PDO = null; //Close connection
	}
	catch(PDOException $e)
		//Deall with error
?>

 

[sniffy]

If you checked out the code before the edit please note the addition of the try-catch! Also I said that execute() will clean the string. Not sure why I remembered it operating like that (sorry, been a while!) so you need to ensure that any input from the user is clean. You probably already knew that but thought I'd mention it in-case.

 

[jamesmorris86]

Ah ok, cheers. It doesn't look too tricky, I'll give it a go

 

[sniffy]

No problem and sorry for the initial errors. I should be more careful when giving such advice

 

[jamesmorris86]

Ok, I think i have a basic understanding of it now, apart from I cant seem to close the connection without getting errors.

I have a connection.php file with the following code:

<?php

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=dbname", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database <br /><br />';
 }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }

?>

and i have a settings.php file that runs my query:

<?php
    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM website_test WHERE id='1'";
    foreach ($dbh->query($sql) as $row)
        {
        $id = $row['id'];
	$name = $row['name'];
        }

 ?>

now that all works fine when I echo my variables, but I cant seem to place the code to close the connection in any part without errors Any ideas where I can place it?

Cheers

 

[visibleman]

You should really be using a PDO to interact with databases in PHP.

My 2 pennies - Depends on the situation and application being designed as there are pros and cons for both PDO and mysql/mysqli ie: if performance is a big issue then mysqli offer marginally better performance than PDO, similary there are a few advanced functions PDO doesn't support that Mysqli does.

 

[sniffy]

snip

I'm not sure I entirely understand your problem. I assume you're using require() on connection.php within settings.php? If you want your database details stored within one location then I'd do the following:

<?php
	
Class DBConnection
{   
    /**
     * Database connection information
     */
    const HOST = "localhost";
    const DATABASENAME = "dbname";
    const USERNAME = "username";
    const PASSWORD = "password";
        
    /**
     * Represents a connection to a database
     * 
     * @var PDO
     */
    private $dbh;
    
    /*
     * Construct an instance of DBConnection. 
     * This will consequently invoke a connection to
     * the database
     */
    public function __construct() 
    {
        connect();
    }
		
    /**
     * Connect to the database
     */
    public function connect()
    {
        try{
            $this->dbh = new PDO(
                    "mysql:host=".self::HOST.";dbname=".self::DATABASENAME, 
                    self::USERNAME, self::PASSWORD);
        }
        catch(PDOException $e){
            echo "Connection failed: ".$e->getMessage();
        }
    }

    /**
     * Disconnect from the database
     */
    public function disconnect()
    {
        $this->dbh = null;
    }
    
    /**
     * Get the PDO to carry out queries
     * 
     * @return PDO
     */
    public function getPDO()
    {
        return $this->dbh;
    }
}
?>

Not tested! Within settings.php you can then just make an instance of DBConnection and invoke disconnect when you're done.

Let me know if I've misunderstood the problem and if not , let me know if that does what you need.

Oh yes, you'll need to include the .php file that DBConnection is stored within so I find it helpful to define the following function in any file that might make an instance of something:

function __autoload($className){include "classes/".$className . '.php';}

If you try to make an instance of a class that it can't "see", this will be the last port of call before error messages. Pretty handy if you're using multiple classes

snip

Fair points. Whatever fits the bill I guess