PDO Connection Function not working

kat35601 · August 12, 2016

all the code works if I put it together on the same page but if I put the connection information in a function then call the function it errors with

Connected successfully
Notice: Undefined variable: conn in /var/www/html/Theme/mysqltest.php on line 24

Fatal error: Call to a member function query() on a non-object in /var/www/html/Theme/mysqltest.php on line 24

What do I have wrong

the connection

function mysqlconnect() {

$servername = "localhost";
$username = "user";
$password = "removed";
$db="labor";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    die("Connection failed: " . $e->getMessage());
   }

}

The page calling the function

<html>
<head>
</head>
<body>

<?php
include('function.php'); 
mysqlconnect();


$gr_total = 0;
 $gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $conn->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

Jacques1 · August 12, 2016

A local variable of a function isn't visible outside of the function. So you cannot create a $conn variable inside your function and then access it in the main script.

The best solution in this case is to return the PDO instance:

function mysqlconnect($host, $username, $password, $database, $character_set)
{
    $dsn = 'mysql:host='.$host.';dbname='.$database.';charset='.$character_set;

    return new PDO($dsn, $username, $password, [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

Hard-coding the connection parameters into the function is not a good idea, because then you cannot easily change them. You should have a configuration script where those parameters are defined and then pass them to the above funciton.

kat35601 · August 12, 2016

Sorry

I don't no how to create a connection for that.

Jacques1 · August 12, 2016

What is the problem? What do you not understand?

All I've done is change your function. You pass your connection parameters as arguments to mysqlconnect(), and the function returns the connection:

<?php

// those parameters should be in an external configuration script
$database_host = 'localhost';
$database_user = '...';
$database_password = '...';
$database_name = '...';
$database_encoding = 'utf8';

$database_connection = mysqlconnect($database_host, $database_user, $database_password, $database_name, $database_encoding);

// now you can call $database_connection->query() etc.

kat35601 · August 12, 2016

What is the best way to create the config file I looked at a lot of examples and it does not seem very uniformed and I am not sure how to create the argument to pass from the config file to the function file.

<?php
define ('db_host', 'localhost');
define ('db_name', 'labor');
define ('db_user', 'user');
define ('db_pass', 'removed');
define ('db_char', 'utf8');

?>

This work's

<html>
<head>
</head>
<body>

<?php
/************************** This willl go in a file called connection.php **************************/
$host = "localhost";
$username = "user";
$password = "removed";
$database="labor";
$character_set="utf8";
/*****************************************************************************************************/
/************************** This will go into my Function.php file.  *********************************/
function mysqlconnect($host, $username, $password, $database, $character_set)
{
    $dsn = 'mysql:host='.$host.';dbname='.$database.';charset='.$character_set;

    return new PDO($dsn, $username, $password, [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

/*****************************************************************************************************/
/********************** this is my page that I am running ********************************************/

$database_connection = mysqlconnect($host, $username, $password, $database, $character_set);



$gr_total = 0;
$gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $database_connection->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

Jacques1 · August 12, 2016

Creating configuration constants with define() is OK for now. Constants are all-uppercase per convention, though.

Include this script wherever you need it, then pass the constants to the function:

<?php

define ('DB_HOST', 'localhost');
define ('DB_NAME', 'labor');
define ('DB_USER', 'user');
define ('DB_PASSWORD', 'removed');
define ('DB_CHARSET', 'utf8');

require_once '/path/to/config.php';

// ...

$database_connection = mysqlconnect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_CHARSET);

kat35601 · August 12, 2016

Notice: Use of undefined constant DB_HOST - assumed 'DB_HOST' in /var/www/html/Theme/mysqltest.php on line 32

Notice: Use of undefined constant DB_USER - assumed 'DB_USER' in /var/www/html/Theme/mysqltest.php on line 32

Notice: Use of undefined constant DB_PASSWORD - assumed 'DB_PASSWORD' in /var/www/html/Theme/mysqltest.php on line 32

Notice: Use of undefined constant DB_NAME - assumed 'DB_NAME' in /var/www/html/Theme/mysqltest.php on line 32

Notice: Use of undefined constant DB_CHARSET - assumed 'DB_CHARSET' in /var/www/html/Theme/mysqltest.php on line 32

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2005] Unknown MySQL server host 'DB_HOST' (110)' in /var/www/html/Theme/function.php:12 Stack trace: #0 /var/www/html/Theme/function.php(12): PDO->__construct('mysql:host=DB_H...', 'DB_USER', 'DB_PASSWORD', Array) #1 /var/www/html/Theme/mysqltest.php(32): mysqlconnect('DB_HOST', 'DB_USER', 'DB_PASSWORD', 'DB_NAME', 'DB_CHARSET') #2 {main} thrown in/var/www/html/Theme/function.php on line 12

kat35601 · August 12, 2016

connection.php

define ('DB_HOST', 'localhost');
define ('DB_NAME', 'labor');
define ('DB_USER', 'user');
define ('DB_PASSWORD', 'removed');
define ('DB_CHARSET', 'utf8');

function.php

<?php


function mysqlconnect($host, $username, $password, $database, $character_set)
{
    $dsn = 'mysql:host='.$host.';dbname='.$database.';charset='.$character_set;

    return new PDO($dsn, $username, $password, [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
   ]);
}
?>

mypage.php

include'function.php';
require_once 'connection.php';

$database_connection = mysqlconnect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_CHARSET);



$gr_total = 0;
$gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $database_connection->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

Jacques1 · August 12, 2016

Your connection.php has no PHP tags.

Also, you shouldn't use relative paths when including files, because it's not always clear which they're relative to. Use absolute paths. The __DIR__ constant yields the path of the parent directory and allows you to start from there:

<?php

require_once __DIR__.'/connection.php';
require_once __DIR__.'/function.php';

// ...

Sign In

PDO Connection Function not working

Recommended Posts

kat35601

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information