Jump to content

problems moving from mysql to PDO


Paul-D

Recommended Posts

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 by Paul-D
Link to comment
Share on other sites

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();

 

  • Great Answer 1
Link to comment
Share on other sites

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 by Paul-D
Link to comment
Share on other sites

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

MainPage.jpg

Edited by Paul-D
Link to comment
Share on other sites

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 by maxxd
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Paul-D
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Paul-D
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by kicken
  • Like 1
Link to comment
Share on other sites

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 by Paul-D
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

×
×
  • 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.