Jump to content

PDO Connection Function not working

kat35601

By kat35601
in PHP Coding Help

 Share

Recommended Posts

kat35601 Advanced Member

kat35601

Posted
Posted

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>
Link to comment
https://forums.phpfreaks.com/topic/301865-pdo-connection-function-not-working/
Share on other sites
Jacques1 Prolific Member

Jacques1

Posted
Posted

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.

Jacques1 Prolific Member

Jacques1

Posted
Posted

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 Advanced Member

kat35601

Posted
  • Author
Posted

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 Prolific Member

Jacques1

Posted
Posted

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 Advanced Member

kat35601

Posted
  • Author
Posted

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 Advanced Member

kat35601

Posted
  • Author
Posted

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 Prolific Member

Jacques1

Posted
Posted

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';

// ...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.