problems moving from mysql to PDO

[Paul-D]

My website has worked for 15 years under mysql noew under php 8 I have to convert queries to PDO. My question is do these functions return any form of error code that can be examined. I have suaght help on this in this forum before but as I am new to PDO I need advice on trapping any PDO errors.

 $stmt = $pdo->prepare($sqlAllData);
 $stmt->execute(['StartDate' => $StartDate]);

 

'' and in the main page

$stmt = GetAllData($Date);
while($row = $stmt->fetch())
{
}

 

TIA

 

Edited December 7, 2023 by Paul-D

[Barand]

The easiest way to use it is set error reporting option when you connect, plus a couple of other options. This will save you from have to test for errors after every PDO method call.

const HOST     = 'localhost';                                                           
const USERNAME = '????';                                                                
const PASSWORD = '????';                                                                
const DATABASE = '????';

function pdoConnect($dbname=DATABASE)                                                   
{                                                                                       
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); 
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                       
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                  
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                               
    return $db;                                                                         
}

then, to connect...

$pdo = pdoConnect();

 

[Paul-D]

I have done this. All the files have

error-Reporting(ALL) and ini-set('display_errors', '1')

 

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
date_default_timezone_set('Europe/London');
define ('HOSTNAME1', 'mysql09.iomart.com'); 
define ('USERNAME1', 'User');
define ('PASSWORD1', 'password');
define ('DATABASE1', 'databes');

function connectDB()
{
    static $pdo = null;
    if($pdo === null)
    {
    $host = HOSTNAME1;
    $user = USERNAME1;
    $pass = PASSWORD1;
    $MyDB = DATABASE1;

    $pdo = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    }
return $pdo;    
}
 

Edited December 7, 2023 by Paul-D

[Paul-D]

 <div style="width:150px;height:4px;padding:10px;text-align:right;float:left;clear:both;"><a style="color:#ae22e2;" href="DataInput.php">* <?=$StartDate?></a>
        </div>
        <div style="width:70px;height:4px;padding:10px;text-align:right;float:left;">
        </div>
        <div style="width:70px;height:4px;padding:10px;text-align:right;float:left;">
        </div>
        <div style="width:70px;height:4px;padding:10px;text-align:right;float:left;">&pound;<?=number_format( $Value,2,'.',',')?>
        </div>
        <div style="width:100px;height:4px;padding:10px;float:left;">
        </div>
        <div style="width:250px;height:4px;padding:10px;float:left;">Brought forward
        </div>

        <?
        $stmt = GetAllData($Date);
        echo $stmt;
        // Is there an error?
        exit;

        while($row = $stmt->fetch())
        {
            $stamp =  strtotime($row['EntryDate']);
            $ViewDate = date('D d-M-Y' ,$stamp);
            $CurDate = date('D d-M-Y' ,time());
]] etc ...

 

 

// The function //

function GetAllData($StartDate) {
    $pdo = connectDB();
    $sqlAllData = "SELECT * FROM Bank_Data WHERE EntryDate > :StartDate AND EntryDate <= DATE_ADD(:StartDate, INTERVAL  6 WEEK) ORDER BY EntryDate ASC, Output";

    if ($_SESSION['CounterValue'] == 'Total') {
        $sqlAllData = "SELECT * FROM Bank_Data ORDER BY EntryDate ASC, Output";
    }

    if ($_SESSION['CounterValue'] == 'Database') {
        $sqlAllData = "SELECT * FROM Bank_Data ORDER BY EntryDate ASC, Output";
    }

    $stmt = $pdo->prepare($sqlAllData);
    $stmt->execute(['StartDate' => $StartDate]);

    return $stmt;
}

Edited December 7, 2023 by Paul-D

[Paul-D]

AND FINALY. To make things worse. I have to work this on a webhousting company because I am using a Windows computer and can not set the whole thing up with loacal host, PDO driven database and PHP 8. Life would be great if I could.

[Barand]

I am using Windows with localhost, pdo and mysql (although mine is v5.7 as I keep failing trying to install v8 - gave up and also installed MariaDB 11 on PC)

[maxxd]

I'm on Windows 11 and use WSL2 with Ubuntu 20 LTS - works like a charm. Create your site files in the Ubuntu filesystem and it's hella fast, as well. You could also use Docker if WSL2 doesn't work for you.

Edited December 7, 2023 by maxxd

[mac_gyver]

3 hours ago, Paul-D said:

I need advice on trapping any PDO errors.

from a reply in your previous thread - 

Quote

the default setting in php8+ for PDO errors is to use exceptions for all the database statements that can fail. you should only catch and handle database exceptions for user recoverable errors, such as when inserting/updating duplicate or out of range data. in all other cases, simply let php catch and handle any database exceptions, where php will use its error related settings to control what happens with the actual error information, via an uncaught exception error (database statement errors will 'automatically' get displayed/logged the same as php errors.)

 

1 hour ago, Paul-D said:

<?

also in your previous thread, two different forum members (ignoring the chatbot replies by the 3rd respondent in that thread) pointed out that you need to change all the short opening php tags to full <?php tags.

 

1 hour ago, Paul-D said:

can not set the whole thing up with loacal host

i also use windows (10) for development, with PDO and php8.

[Paul-D]

As you can see from the screen print of my web page. The trapping errors is not working. I need to find out why the error is. I have shown my ConnectDB() code and the function FetAllData().

I have directly tried the SQL on the database and it is fine.  It is the same SQL I used before in mMySQL which worked for years. The SQL is fine. My question is, is there a problem with the PDO database conection code? Can someone check the PDO code please.

Checked out Ubinto download and got... What you will need: A Windows 10 or Windows 11 physical or virtual machine with all the updates installed. Out of my league vertual Windows.

Edited December 7, 2023 by Paul-D

[Paul-D]

I know I am getting old and I haven’t touched this stuff for ten years or more but this section of my website, a virtual bank statement from today and into the 6 week future, to give me an idea of my spending limits, is very important to me.

[Barand]

2 hours ago, mac_gyver said:

also in your previous thread, two different forum members (ignoring the chatbot replies by the 3rd respondent in that thread) pointed out that you need to change all the short opening php tags to full <?php tags.

Your screenshot shows symptoms of php not executing because of the above reason

[mac_gyver]

there are xAMP pre-built systems you can download and install on windows, e.g. https://www.apachefriends.org/

[Paul-D]

My screenshot only shows a web page with problems. it dosn't show code. all the pages start withy <?php. I HAVE CHECKED.

I know I am getting old and I haven’t touched this stuff for ten years or more but this section of my website, a virtual bank statement from today and into the 6 week future, to give me an idea of my spending limits, is very important to me.

Statement.php.txt

Edited December 7, 2023 by Paul-D

[dodgeitorelse3]

Although your pages have long <?PHP tags I think you also need to changes short tags I see in your code such as 

5 hours ago, Paul-D said:

<a style="color:#ae22e2;" href="DataInput.php">* <?=$StartDate?></a>

See the short tags just before $StartDate?

[Barand]

@dodgeitorelse3 "<?=" is valid shorthand for "<?php echo"

[Barand]

1 hour ago, Paul-D said:

all the pages start withy <?php. I HAVE CHECKED.

It isn't just the start of the page that counts. All sections of php code (other than <?=" echo only) must begin with <?php. If they don't, and you use <?, then the following php code will be treated as text and output to the page - as in your screenshot.

[kicken]

1 hour ago, Paul-D said:

all the pages start withy <?php. I HAVE CHECKED

Not just start the file with.  Every PHP code block must use <?php (or <?= for the short echos).  The Statement.php file you just uploaded contains blocks where you did not fix this issue.

Line 101

		<?
		$stmt = GetAllData($Date);
echo $stmt;

Line 156

        <? 
	} else { ?>        

Line 161

	<? } 
	?>

Line 174

	<?
	}
	?>

You need to fix ALL of these tags.  Do so in ALL of your files.

 

Edited December 7, 2023 by kicken

[Paul-D]

Well that is new to me. I have only used <?php at the start of a page and under php 5.4 it worked fine for over 10 years. Maybe this is more strickt in PHP 8. It did work fine for over 10 years. FACT. I will go through all my files tomorrow and let you know the outcome.

Edited December 7, 2023 by Paul-D

[Paul-D]

Thanks for all your help. I have managed to isolate the function with the problem. The problem is I can't figure a way out of it.

I have a database table of futuristic entries into an look ahead statment. What I want to do is display an online bank statment where the start date is given to a function and I want the querie to create a data set from tthat date to six weeks in advance. Can someone help me out with this query. What is strange here is this all worked fine for tebn years untill my provider updated the server from PHP 5.4 to PHP8. So the SQL will probably need to change.

 

function GetAllData($StartDate) {
    $pdo = connectDB();
    $sqlAllData = "SELECT * FROM Bank_Data WHERE EntryDate > :StartDate AND EntryDate <= DATE_ADD(:StartDate, INTERVAL  6 WEEK) ORDER BY EntryDate ASC, Output";
    $stmt = $pdo->prepare($sqlAllData);
    $stmt->execute(['StartDate' => $StartDate]);

    return $stmt;
}

Error
Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in /vhost/d/e/s/desmond-otoole.co.uk/www/bank2/secure/SecureFunctionsBankPDO.php:39 
Stack trace: #0 /vhost/d/e/s/desmond-otoole.co.uk/www/bank2/secure/SecureFunctionsBankPDO.php(39): PDOStatement->execute() #1 /vhost/d/e/s/desmond-otoole.co.uk/www/bank2/Statement.php(104): GetAllData() #2 {main} thrown in /vhost/d/e/s/desmond-otoole.co.uk/www/bank2/secure/SecureFunctionsBankPDO.php on line 39

 

[Barand]

It won't let you reuse placeholder names** so you need :StartDate1 and :StartDate2 then provide the same value for both when you execute.

$stmt->execute(['StartDate1' => $StartDate, 'Startdate2' => $StartDate]);

Alternatively you can use ? placeholders

    $sqlAllData = "SELECT * FROM Bank_Data WHERE EntryDate > ? AND EntryDate <= DATE_ADD(?, INTERVAL  6 WEEK) ORDER BY EntryDate ASC, Output";
      
      ...
      
    $stmt->execute( [ $StartDate, $StartDate ] );  

** it will if if you use emulated prepares, but that is a bad idea.

[Paul-D]

Well thanks for all that. I thought ther was a problem with that function but it worked fine under PHP 5.4 for some strange reason. THings are looking up now. Thanks also to kicken. I have only used <?php at the beginning of a file but stand correctedd now.