Posted by Jerry Stuckle on 07/05/06 11:11

Dodger wrote:
> Okay, background... yes, I am another of those evil, spurned, damnable
> Perl mongers, but I'm not trying to start a flamewar, I'm juust tryung
> to understand something...
>
> I can write a script in Perl like so, and it's pretty to me (and the
> using of the heredocs I think does defend perl against many arguments
> withthe HTML being all escaped and explicit returns and stuff -- which
> I can see... 'print "<p class=\"text\">stuff</p>\n";' is terrible to
> me to.. so I use heredocs), and simple, and fast under mod_perl...
>
> ...but I don't want my horizons limited so I wan to work out how to do
> it in PHP:
>
> #!/usr/bin/perl
> use strict;
>
> use CGI;
> use DBI;
>
> sub notLoggedIn();
>
> my $cgi = new CGI;
> print $cgi->header;
> my $sessionid = $cgi->cookie('session');
>
> notLoggedIn unless $sessionid;
> my $dbh = DBI->connect('DBI:mysql:session','user','password');
>
> my $get_session_st = <<"EOF";
> SELECT *
> FROM session
> WHERE id = ?
> EOF
> my $get_session = $dbh->prepare($get_session_st);
> $get_session->execute($sessionid);
>
> my $session;
> $session = $get_session->fetchrow_hashref or notLoggedIn;
> $get_session->finish;
>
> for my $k (keys %{$session}) {
> $ENV{"SESSION_".uc($k)} = $session->{$v};
> }
>
> print <<"EOF";
> <!--#include virtual="/includes/page_header.shtml"-->
> <p>You are logged in.</p>
> <p>Here's your session data...</p>
> <table>
> EOF
>
> while (my ($k, $v) = each %ENV) {
> print <<"EOF" if $k =~ /^SESSION_/;
> <tr>
> <td>$k</td>
> <td>$v</td>
> </tr>
> EOF
> }
>
> print <<"EOF";
> </table>
> <!--#include virtual="/includes/page_footer.shtml"-->
> EOF
>
> sub notLoggedIn () {
> print <<"EOF" and exit;
> <!--#include virtual="/includes/page_header.shtml"-->
> <p>Sorry, you are not logged in.</p>
> <!--#include virtual="/includes/page_footer.shtml"-->
> EOF
> }
>
>
> this whole thing is pretty simple. But anyway...
>
>>From what I can see, the normal construct in the Perl DBI as seen above
> is a bit hard to figure out in PHP...
>
> To restate the perl bit and modify to a more-than-one-off- result
> set... using that wunnerful cities example people love:
>
> use DBI;
> my $dbh = DBI->connect('DBI:mysql:database','user','password')
> or die "Can't connect to database: $DBI::errstr\n";
>
> my $query = <<"EOF";
> SELECT *
> FROM cities
> WHERE Country = ?
> EOF
>
> my $statement = $dbh->prepare($query);
> $statement->execute('USA');
>
> my @cities;
> while (my $city = $statement->fetchrow_hashref) {
> push @cities, $city;
> }
>
> for my $city (@cities) {
> # do stuff over each city... keys of hashref are same as
> # column names in table
> }
>
> I don't want to, and don't think I *can* use the query() method in PHP
> off a mysqli object because I need placeholders. The documentation
> makes no indication that I can find that query() (which seems to
> combine prepare and execute, but doesn't seem to do so exactly) can use
> placeholders. All the placeholder examples say to use prepare and
> execute, as well as bind_params However I also do not know the names of
> the things I'm getting back -- fetchrow_hashref() in Perl DBI basically
> returns an associative array (though couched under a reference).
>

Yes, it combines prepare and execute. you could, for instance, build
your query string dynamically, i.e.

mysql_query("SELECT * FROM cities WHERE Country = '$country'");

Or, you can use the stmt member of mysqli, i.e.

$stmt = $mysqli->stmt_init();
if ($stmt->prepare(""SELECT * FROM cities WHERE Country = ?")) {
$stmt->bind_param("s", "USA");
$stmt->execute();
}


> I can get things back using fetch() after prepare, execute, and
> store_result -- though the last of these seems kind of silly to me --
> should be automatic.
>

Actually, this is normal operation in almost any language with almost
any dataset. The database returns a result set; you need to retrieve
each row from the result set. The fact you *don't* have to do this seems
to be almost unique to the Perl::DBI module (I don't do Perl, so I'm not
familiar with it). But I suspect this module just fetches the data for
you, and the basic interface between Perl and MySQL doesn't do it.

> But if I try to use fetch_row() or fetch_assoc(), I get PHP Fatal
> Error: Call to undefined method mysqli_stmt::fetch_row() -- though the
> documentation seems to indicate I should be able to do this...
>

It is valid. But it can be confusing.

>>From what the docs say, the direct translation of the perl that I can
> write while asleep (fact, not embellishment, as I have in fact done so
> and it's damned annoying to get Perl in a dream when you were hoping
> for a Suicide Girl...) , seems like it should be:
>
> $dbh = new mysqli('localhost','user','password','database');
> if ($err = mysqli_connect_errno()) {
> print "Connect failed: $err";
> exit();
> }
>
> $query = <<<EOF
> SELECT *
> FROM cities
> WHERE Country = ?
> EOF;
>
> $statement = $dbh->prepare($query);
> $statement->bind_param('USA');
> $statement->execute();
> $statement->store_result();
>
> $cities = array();
> $i = 0;
> while ($city = $statement->fetch_assoc()) {
> $cities[$i] = $city;
> $i++;
> }
>
> foreach ($cities as $k => $v) {
> # do stuff over each city... keys of hashref are same as
> # column names in table
> }
>
> But it doesn't work. I get that messed up error string that says the
> fetch_assoc() method doesn't exist...
>

Ah, fetch_assoc() is a member of mysqli_result, and you're trying to
call it from a mysqli_stmt object. The function doesn't exist in
mysqli_stmt.

Rather, you should bind variables to the results and fetch the
variables, i.e.

$statement->execute();
$statement->bind_result($city);

while ($statement->fetch()) {
$cities[] = $city; // Automatically adds it to the end
}

$stmt->close();



> So I'm driven to the point of posting to usenet and writing all that
> out, despite those three Perl programmer virtues that otherwise fill my
> lazy, hubristic and impatient being (they're supposed to do so--ask
> Larry Wall).
>
> So can someone tell me what I'm doing totally wrong?
> I almost thing this would be easier if it were *nothing* like Perl,
> instead of feeling to me like I'm writing something in some strange
> perl that was written in a universe a few over in Sliders...
>

You're close, just a little more understanding of the different classes.
But it is confusing, especially to the new PHP programmer.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]