PDO Help wanted

[rocky48]

I have decided that using mysqli to connect to my database is now a depreciated service, so I am trying to change the code to use PDO.

 

I have downloaded all the manual which confuses my as it seems there are many ways of doing the same thing.

 

I have had a bash at changing a small section of my index page that look to see if there are any religious holidays today.

 

I have only written the part that looks for the data and desplays it in a table.

I will show the code I have written first and then the original mysqli code after:

try {
    $conn= new PDO('mysql:host=localhost;dbname=db464231930', root, 'xxxxx');
    
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}


function RelDay($conn){
//gather the Events
	$RelHol_sql  = "SELECT Events15.ID, Events15.Event_Date, Events15.Event_Description, Events15.Faith_ID, Religion.ID, Religion.Religion
	FROM Events15
	LEFT JOIN Religion
	ON Events15.Faith_ID = Religion.ID
	WHERE DAY(Events15.Event_Date)= Day(Now()) And MONTH(Events15.Event_Date)=Month(Now()) And YEAR(Events15.Event_Date)=YEAR(Now())
	ORDER BY Events15.Event_Date ASC";

	$display_block .= "
<table width=\"120%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" >
	<tr>
	<th>RELIGION</th>
	<th>EVENT</th>
	</tr>";	

foreach($conn -> query($Rel_Hol_sql) as $Row){
	$Rel_Date=$Row['Event_Date'];
	$Rel_Event=$Row['Religion'];
	$Event_text=$row['Event_Description'];
}
}	

//add to display
$display_block .= "
<tr>
<td width=\"18%\" valign=\"top\">".$Rel_Event."<br/></td>
<td width=\"45%\" valign=\"top\">" .$Event_text."<br/></td>";	
		

	
//close up the table
$display_block .="</table>";
echo $display_block;

mysqli code:

<?php
include("RelHol_connect_LOCAL.php");
\doDB3();

//verify the Event exists
$verify_Event_sql = "SELECT Events.ID, Events.Event_Date, Events.Event_Description, Religion.ID, Religion.Religion
	FROM Events
	LEFT JOIN Religion
	ON Events.Faith_ID = Religion.ID
	WHERE DAY(Events.Event_Date)= Day(Now()) And MONTH(Events.Event_Date)=Month(Now()) And YEAR(Events.Event_Date)=YEAR(Now())
	ORDER BY Events.Event_Date ASC";
$verify_Event_res =  mysqli_query($mysqli, $verify_Event_sql) or die(mysqli_error($mysqli));



if (mysqli_num_rows($verify_Event_res) > 0) {



//gather the Events
	$get_Event_sql  = "SELECT Events15.ID, Events15.Event_Date, Events15.Event_Description, Events15.Faith_ID, Religion.ID, Religion.Religion
	FROM Events15
	LEFT JOIN Religion
	ON Events15.Faith_ID = Religion.ID
	WHERE DAY(Events15.Event_Date)= Day(Now()) And MONTH(Events15.Event_Date)=Month(Now()) And YEAR(Events15.Event_Date)=YEAR(Now())
	ORDER BY Events15.Event_Date ASC";

	$get_Event_res = mysqli_query($mysqli, $get_Event_sql) or die(mysqli_error($mysqli));
//create the display string
	$display_block .= "
<table width=\"120%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" >
	<tr>
	<th>RELIGION</th>
	<th>EVENT</th>
	</tr>";	

	while ($Event_today = mysqli_fetch_array($get_Event_res)) {
		$Rel_Date = $Event_today['Event_Date'];
		$Rel_Event = $Event_today['Religion'];
		$Event_text = $Event_today['Event_Description'];

//add to display
$display_block .= "
<tr>
<td width=\"18%\" valign=\"top\">".$Rel_Event."<br/></td>
<td width=\"45%\" valign=\"top\">" .$Event_text."<br/></td>";	
		
	}
//free results
	mysqli_free_result($get_Event_res);	
}

if(mysqli_num_rows($verify_Event_res) < 1) {
	//this Event does not exist
	$display_block = "<p><em>No Religious festivals or holidays today.</em></p>";
	
}



//close connection to MySQL
	mysqli_close($mysqli);
	
//close up the table
$display_block .="</table>";
echo $display_block;

?>

As you can see I have not written the data test part yet, but I want to get the data gathering right first.

 

Your help is appreciated.

Edited August 14, 2015 by rocky48

[Ch0cu3r]

 

I have decided that using mysqli to connect to my database is now a depreciated service

Who said that? Last I checked it is the mysql_ api that is deprecated not the mysqli_ api

 

Also what is your problem/question? Your post does not make sense to me.

[rocky48]

I installed WAMP and ran my code it gave me a warning that the mysqli statements were depreciated!

I am having trouble using PDO!

I have written the code, but it does not output any data.

It is not giving any error messages, so I am not sure where it is failing.

The area on the page is blank.

As I am not conversant with this extension to PHP I am not sure where I am going wrong.

I just need some guidance to point me in the right direction?

[Ch0cu3r]

 

I installed WAMP and ran my code it gave me a warning that the mysqli statements were depreciated!

mysqli itself is not deprecated, but certain procedural functions/aliases are, which are listed here. 

 

When using mysqli you should be using the object syntax, rather than the procedural functions. For example instead of using mysqli_connect you should be doing

$mysqli = new mysqli('localhost', 'user', 'password', 'database');

 

 

I am having trouble using PDO!

I have written the code, but it does not output any data.

It is not giving any error messages, so I am not sure where it is failing.

The area on the page is blank.

You are not getting any output because your have your query and looping through the query result inside a function, called RelDay() (defined on line183). However you never appear to be calling this function. In order for the code inside the function to be execute the function must be called.

[ginerjm]

1 - What is "root"? You use it in your connect statement but what is it? A string? A constant? It's not a var.

2 - As already said - you never call the function that you wrote.

3 - The word is "deprecated", not depreciated. Two entirely different things.

4 - As also said - if you have a working mysqli model you do not have to change a thing.

5 - You are using a foreach to process your query and loop thru it. I find it very confusing to read and frankly am not even sure it works that way. A simpler way of doing this is to execute the query (and check the results for false before proceeding) and then to use a while loop as follows:

$qrslt = $conn->query($Rel_Hol_sql);
if (!$qrslt)
{
echo "Error running query";
exit();
}
while ($row = $qrslt->fetch(PDO::FETCH_ASSOC))
{
(handle your row here)
}

 

 

Don't know what you are referring to when you said you downloaded and read a manual and found it to be too technical, but this business is all about being technical. If reading the manual is already too technical, you are in for a long tough row to hoe if you choose to continue this effort.

[rocky48]

1) Root is the default user on MySQL on localhost, where I am testing the code.

2) I plagerised the manual and copied this from the section on connection. I know that I seem like a novice, but I usually get there in the end.

3) Isn't English a funny language! what's in an I!

4) True enough, but that's defeatist language.

5) Thanks for the code I will check this out and re-post.

 

I did not say it was too technical, I said it was confusing! There seem to be several ways of apparently arriving at the same answer.

[ginerjm]

1 - you still don't get what I asked about 'root'. Since you didn't say it was a PHP constant I have to say it is invalid code which should stop your script from working properly.

2 - you copied "this" from the manual. What are you referring to - the function? If you know anything about functions then you know that it doesn't execute by itself. It has to be called. You should have tried to understand what you were copying and made sure to copy a call to that function.

4 - Defeatist? How about "wasteful"? There's another term that could be used. You are switching because you think you have to. All I did was repeat what you have already been told and added my $.02. You don't have to recode this - and spend this time more fruitfully by trying to learn some basic php.

 

If you haven't done so already, plagiarize my signature and add that code to your script. You should get a few more warnings and notices from your code. As in the use of $row for one of your query processing lines. $row does not exist. $Row does.

[rocky48]

I have been a member for many years and I have never been treated so condescendingly by a member!

Most members have been very helpful.

I know aged 67 programming is more challenging than for younger programmers, but at that age I have plenty of time to spare!

[ginerjm]

At age 63 I have a lot to offer too. Unfortunately you don't seem to be reading what I'm saying. My questions/points are all valid and you don't get that I am trying to help you debug the problem. My first post was nothing but helpful, but you chose not to address my issues in a positive way.

 

Since you have plenty of time to spare (as do I), I'll leave you to ponder your code and solve your problem now. I tried. Ho hum...

[gizmola]

Hi Rocky48,

 

I have to agree with ch0cu3r & ginerjm.

 

I don't think anyone is being condescending.

 

To clarify things:

 

The mysql_ api is deprecated.

 

mysqli_ is not.

 

PDO and mysqli are two different ways to stay current with mysql use. You do not have to change your code to use PDO to avoid the deprecation.

 

 

The mysqli api has both an "object oriented" interface, and a procedural (function based) interface.

 

It also has a substantial number of "alias" functions. Unfortunately, the mysqli devs went a bit overboard with all the aliasing and eventually the PHP community came to the conclusion that they should clean things up by removing a bunch of the aliases, and that is what you have hit upon in the "warnings" you are seeing for your current mysqli based code.

 

 

You could remedy this with the least pain and suffering by simply looking at the page ch0cu3r linked, and simply changing the deprecated name of the function(s) you used to the one(s) it is aliasing.

 

With that said, if you really want to move to PDO that is fine, but please understand that people were simply trying to clarify things for you, and address what clearly were some unfounded assumptions about the warnings you received. Mysqli_ is not going anywhere, and you don't have to abandon it for PDO!

[rocky48]

Sorry about my outburst previously, I had other things that were making me tetchy!

Lets start again!

I have read the list that ch0cu3r mentioned, so I guess that this means that I could change mysqli_connect() to mysqli_construct() or conversly the mysqli_new()?

 

I still don't understand why ginerjm was so persistant about root?  according to the PHP Manual the correct syntax for the connection using PDO was:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

 

The root being substituted for the $user. Is it that I did not put quotes around it?

I am still determined to learn about PDO, even if I do not implement it for some time.  My site is running on an old version of PHP so there is no immediate rush to change anything.

 

To help me learn PDO  I have written some code in a test file to hep me debug it.  I have used an include file for connection, which appears to work as the echo message 'connected' is displayed.

<?Php
$host_name = "localhost";
$database = "db464231930"; // Change your database nae
$username = "root";          // Your database user id 
$password = "xxxxx";          // Your password

//////// Do not Edit below /////////
try {
$dbo = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
echo 'CONNECTED';
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>

Here is the code for the select query:

<?php
include(RelHol_connect_LOCAL.php);

//gather the Events
	$RelHol_sql  = "SELECT Events.ID, Events.Event_Date, Events.Event_Description, Religion.ID, Religion.Religion
	FROM Events
	LEFT JOIN Religion
	ON Religion.ID = Faith_ID
	WHERE MONTHNAME(Events.Event_Date) = 'August' And YEAR(Events.Event_Date)= '2015' And Religion.Religion = 'Christian'
	ORDER BY Events.Event_Date ASC";

$qrslt = $conn->query($Rel_Hol_sql);
if (!$qrslt)
{
echo "Error running query";
exit();
}
while ($row = $qrslt->fetch(PDO::FETCH_ASSOC))
{
    $Rel_Event=$Row['Religion'];
    $Event_text=$row['Event_Description'];
    
//add to display
$display_block .= "
<tr>
<td width=\"18%\" valign=\"top\">".$Rel_Event."<br/></td>
<td width=\"45%\" valign=\"top\">" .$Event_text."<br/></td>";    
$display_block .="</table>";

}

?>
<!DOCTYPE html>

<html>
<head>
    <title>Page Title</title>
</head>

<body>
    
    <?php echo $display_block; ?>


</body>
</html>

However when this is run I get a blank screen, and no error messages.

Is the method I am using to display the table incorrect?

By the way I have tested the query in phpmyadmin and it works OK.

Any help would be appreciated.

[ginerjm]

If you turned on error checking you might get some messages....

 

As for the first error I see: Your include references a string of letters that PHP cannot interpret. You must always put text in quotes unless it is a variable or a constant or a name of a function.

 

include("filename.php");

 

would be proper.

[maxxd]

In addition to ginerjm's observation about the quotes, you instantiate the PDO object as $dbo, then use $conn to run the query. What's $conn?

[rocky48]

The $conn was something I was trying in a previous attempt to solve the problem and was something I overlooked.

Am I right in assuming that I should use $dbo like so: $qrslt = $dbo->query($Rel_Hol_sql);

If so do I need the include?

Also in the function do I need to declare the argument in the function like so: function doDB4($dbo)

These atre the errors I get when running the code with the above changes:

Notice: Undefined variable: dbo in C:\wamp\www\1066mk4\EvTest2.php on line 12

&

Fatal error: Call to a member function query() on a non-object in C:\wamp\www\1066mk4\EvTest2.php on line 12

That says to me that $Rel_Hol_sql is not an object, so how do I write my code in such a way as to make the select query an object.

I've got to admit I am have trouble getting my head around Object Oriented style of coding.

[ginerjm]

Actually the message is saying that $dbo is not an object. Since we can't see this code I can't say anymore.

 

Also - remember that PHP is CASE-SENSITIVE so referencing a variable like $Row is not the same as referencing $row.

[maxxd]

You've also got another typo - you define the query string in $RelHol_sql, then pass $Rel_Hol_sql to the query() method.