Confused by mysqli

Dodger

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 "stuff\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";

You are logged in.
Here's your session data...
<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>

EOF

sub notLoggedIn () {
print <<"EOF" and exit;

Sorry, you are not logged in.

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).

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.

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

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

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

--
Dodger

Jul 5 '06 #1

Subscribe Post Reply

4637

Rik

Dodger wrote:

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

mysqli_stmt_fetch_assoc doesn't exist, mysqli_stmt_fetch does.

$statement->bind_results($city);
while ($statement->fetch()) {
$cities[] = $city;
}

Look at the example at
http://www.php.net/manual/en/functio...ind-result.php

Grtz,
--
Rik Wasmus

Jul 5 '06 #2

Sandman

In article <11**********************@a14g2000cwb.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

$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...

I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>
Why the extra foreach? Just "do stuff" in the mysql while loop.

--
Sandman[.net]

Jul 5 '06 #3

David Haynes

Dodger wrote:
[snip]

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

The store->result() method returns a mysqli_result type.
You need to use the mysqli_type for the fetch_assoc() method.

So your code would look like:

$statement->execute();
$result = $statement->store_result();

$cities = array();
while( $city = $result->fetch_assoc() ) {
$cities[] = $city; // you don't need to supply the $i++ index this way
}
$result->free(); // don't forget to free the result memory

-david-

Jul 5 '06 #4

Dodger

Sandman wrote:

In article <11**********************@a14g2000cwb.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

Why the extra foreach? Just "do stuff" in the mysql while loop.

Could be plenty of reasons. In the example up top, I am populating the
environment (%ENV in Perl, $_ENV in PHP) with my special session
variables (and yes, I know PHP has session handling systems, but I'm
not gonna use them because I want stuff to work in *both* Perl and PHP
CGIs* -- I might be populating some other SESSION_* environment
variables elsewhere, for instance.

But it was an illustration of having a populated data structure,
mostly.
--
Dodger
*Yes, both are CGIs. Both use Common Gateway Interface. <-- Pet Peeve
when people think CGI is, like, a language or something. It's just a
protocol for handing off variables, and *all* web stuff uses it by
default (yeah some weird stuff takes it by rewriting other things,
but...)

Jul 5 '06 #5

Dodger

Rik wrote:

Dodger wrote:

mysqli_stmt_fetch_assoc doesn't exist, mysqli_stmt_fetch does.

http://us2.php.net/manual/en/functio...etch-assoc.php
Not stmt_fetch_assoc, just fetch_assoc...

$statement->bind_results($city);
while ($statement->fetch()) {
$cities[] = $city;
}

Look at the example at
http://www.php.net/manual/en/functio...ind-result.php

Thanks, but that's the problem...

I can't use vars named $col1, $col2, etc. I need names -- preferably as
keys in an associative array... I'm selecintg * -- there could be a
number of reasons that table might have differing numbers of columns --
for instance if I am selecting which table touse dynamically, and my
session tables consider different things important to hold in a SQL
fast lookup table... consider if I have one site that's a subscription
site and another that's a web stire. The subscription site might want
to have 'SESSION_SUBSCRIBED' as a session variable, while the store
might want to have 'SESSION_STORED_CCARD_REF' as a session variable,
where neither would be useful to the other. This way as long as they
both have an 'id' column, it's fine.

I actually do this. I have one site where I plugged in (shuddering as I
type this because the code is atrocious...) PostNuke with PnPHPBB forum
system, but I hacked it so that I can share my own session system with
the PostNuke one... there are five extra session variables, including
SESSION_PN_UID, SESSION_PHPBB_UID, and so on that Perl drops into the
environment, so that, for instance, a Perl script can check and popup
if they have new IMs...

Jul 5 '06 #6

Jerry Stuckle

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 "stuff\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";

You are logged in.
Here's your session data...
<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>

EOF

sub notLoggedIn () {
print <<"EOF" and exit;

Sorry, you are not logged in.

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.
js*******@attglobal.net
==================

Jul 5 '06 #7

Erwin Moller

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

Hi Dodger,

Other helped you with the msql part, but I want to make 1 remark on your
next comment:

>
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 "stuff\n";' is terrible to
me to.. so I use heredocs), and simple, and fast under mod_perl...

Only people who like to write unreadable code use syntax like that.
consider:
print "stuff\n";
and
stuff

The latter is the usual normal way if you first jump out of the PHP-tags,
which every experienced PHP-coder with a sane mind does, for sake of
readability.
If you need to jump back to get some variable, do this:
[just an example that uses ADODB-lib.]

<table>
<tr>
<td>
userid
</td>
<td>
firstname
</td>
<td>
lastname
</td>
</tr>

<?php
$SQL = "SELECT userid, firstname, lastname FROM tbluser;";
$RS = $con->Execute($SQL)->getArray;
for($i=0;$i<count($RS);$i++){
$row = $RS[$i];
?>
<tr>
<td>
<?php= $row["userid"] ?>
</td>
<td>
<?php= $row["firstname"] ?>
</td>
<td>
<?php= $row["lastname"] ?>
</td>
</tr>

<?php
}
?>
</table>
I think that makes great readable code, both from a HTML perspective as from
a PHP perspective.
Don't use print and echo for HTML, unless you have some compelling reason.

just my 2 cent.

Regards,
Erwin Moller

Jul 5 '06 #8

Dodger

David Haynes wrote:

Dodger wrote:
[snip]

The store->result() method returns a mysqli_result type.
You need to use the mysqli_type for the fetch_assoc() method.

Thank you!

Though pardon my saying that that's weird as snake shoes...
Result objects... *blinks*

$cities = array();
while( $city = $result->fetch_assoc() ) {
$cities[] = $city; // you don't need to supply the $i++ index this way
}

I think ... I think I'm gonna end up writing function push {} -- to
preserve my sanity by doing that bit elsewhere hidden away...

$result->free(); // don't forget to free the result memory

Is there a way to lexically scope things, something like my() in
Perl... so the garbage handling bit can be taken care of automagically
when things fall out of scope?

Jul 5 '06 #9

Dodger

Erwin Moller wrote:

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

Hi Dodger,

Other helped you with the msql part, but I want to make 1 remark on your
next comment:

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 "stuff\n";' is terrible to
me to.. so I use heredocs), and simple, and fast under mod_perl...

Only people who like to write unreadable code use syntax like that.
consider:
print "stuff\n";
and
stuff

The latter is the usual normal way if you first jump out of the PHP-tags,
which every experienced PHP-coder with a sane mind does, for sake of
readability.
If you need to jump back to get some variable, do this:

-snip-

I think that makes great readable code, both from a HTML perspective as from
a PHP perspective.
Don't use print and echo for HTML, unless you have some compelling reason.

just my 2 cent.

Regards,
Erwin Moller

Oh, yeah, I know... I was more coming from the angle of 'Perl can keep
it pretty too, but 98% of perl programmers don't and I don't know why
-- just cause it's one of the things I hear all the time as a complaint
about Perl CGIs... and if you grab scripts off the web, it's true --
but those are usually worth every penny, minus the bandwidth cost, IMO.

Actually I think it's because all the die-hard PHP fans I know have
used the argument once or twice, and so coming into a PHP group I
wanted to point out that I want to be able to do a lot of the stuff I
do in Perl in PHP also, not because I feel a *need* to, but because I
*want* to -- which I hope might garner more respect. But it's
ecumenicism, not conversion.

But yeah, I know the whole

<?php
$var = 'foo';
?>
<?= $var ?bar.. 
for you.

thing... got that out of the box... plus I used to do ColdFusion, which
is a lot the same, and I have dorked around with embedded Perl script
in mod_perl, as SHTML tags, or embperl or even, once, PerlAsp (yuk).

Its just I see people do comparisons a lot and they try and compare
that to this Perl code:
my $var = 'foo';
print "<class=\"blue\">$var bar.. \nfor you\n";

and it's just totally the wrong thing people compare with.
The proper thing to make such a comparison would in my case be:

my $var = 'foo';
print <<"EOF";
$var bar.. 
for you.
EOF

See, Pretty Perl. Only thing missing is the vim syntax highlighting on
the HTML bits. But you can't have everything, neh? B^)

(
And I prefer the heredocs vastly to the CGI faux-oo approach thing some
people try and push, which is horrible stuff, like:
my $var = 'foo';
print $cgi->p("$var bar", $cgi->br(), "for you.", {class ='blue'});

That stuff is only useful, IMO, for, like, the popup_menu method and
stuff where just writing out the HTML is a pain.
)

Buy yeah, I want to know how to do this every way, and I figure
branching out to PHP was one of the best starts.

Jul 5 '06 #10

Jerry Stuckle

Dodger wrote:

Sandman wrote:

>>In article <11**********************@a14g2000cwb.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

>>I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

Proper escaping of the string will prevent sql injection attacks. Bind
variables are not required. See mysql_real_escape().

>
>>Why the extra foreach? Just "do stuff" in the mysql while loop.

Could be plenty of reasons. In the example up top, I am populating the
environment (%ENV in Perl, $_ENV in PHP) with my special session
variables (and yes, I know PHP has session handling systems, but I'm
not gonna use them because I want stuff to work in *both* Perl and PHP
CGIs* -- I might be populating some other SESSION_* environment
variables elsewhere, for instance.

You're really trying to make things complicated, aren't you?

But it was an illustration of having a populated data structure,
mostly.
--
Dodger
*Yes, both are CGIs. Both use Common Gateway Interface. <-- Pet Peeve
when people think CGI is, like, a language or something. It's just a
protocol for handing off variables, and *all* web stuff uses it by
default (yeah some weird stuff takes it by rewriting other things,
but...)

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

Jul 5 '06 #11

David Haynes

Dodger wrote:

David Haynes wrote:
>Dodger wrote:
[snip]

>The store->result() method returns a mysqli_result type.
You need to use the mysqli_type for the fetch_assoc() method.

Thank you!

Though pardon my saying that that's weird as snake shoes...
Result objects... *blinks*

Well... its not all that uncommon. Java, for example, hands you a result
which you then have to play with.

>$cities = array();
while( $city = $result->fetch_assoc() ) {
$cities[] = $city; // you don't need to supply the $i++ index this way
}

I think ... I think I'm gonna end up writing function push {} -- to
preserve my sanity by doing that bit elsewhere hidden away...

The [] operator is a push operator.

>$result->free(); // don't forget to free the result memory

Is there a way to lexically scope things, something like my() in
Perl... so the garbage handling bit can be taken care of automagically
when things fall out of scope?

No. Not really. PHP doesn't really have a good model for memory
management - its based more upon the C(++) of malloc/free. i.e. You are
a big boy and should clean up your own messes.

-david-

Jul 5 '06 #12

Jerry Stuckle

Dodger wrote:

>

Oh, yeah, I know... I was more coming from the angle of 'Perl can keep
it pretty too, but 98% of perl programmers don't and I don't know why
-- just cause it's one of the things I hear all the time as a complaint
about Perl CGIs... and if you grab scripts off the web, it's true --
but those are usually worth every penny, minus the bandwidth cost, IMO.

Actually I think it's because all the die-hard PHP fans I know have
used the argument once or twice, and so coming into a PHP group I
wanted to point out that I want to be able to do a lot of the stuff I
do in Perl in PHP also, not because I feel a *need* to, but because I
*want* to -- which I hope might garner more respect. But it's
ecumenicism, not conversion.

But Perl and PHP are two different languages, which at times approach a
solution in two different ways. And other languages will approach the
problem in still other ways.

If PHP and Perl always approached a solution in the same way, why have
two languages?

But yeah, I know the whole

<?php
$var = 'foo';
?>
<?= $var ?bar.. 
for you.

thing... got that out of the box... plus I used to do ColdFusion, which
is a lot the same, and I have dorked around with embedded Perl script
in mod_perl, as SHTML tags, or embperl or even, once, PerlAsp (yuk).

Its just I see people do comparisons a lot and they try and compare
that to this Perl code:
my $var = 'foo';
print "<class=\"blue\">$var bar.. \nfor you\n";

and it's just totally the wrong thing people compare with.
The proper thing to make such a comparison would in my case be:

my $var = 'foo';
print <<"EOF";
$var bar.. 
for you.
EOF

See, Pretty Perl. Only thing missing is the vim syntax highlighting on
the HTML bits. But you can't have everything, neh? B^)

(
And I prefer the heredocs vastly to the CGI faux-oo approach thing some
people try and push, which is horrible stuff, like:
my $var = 'foo';
print $cgi->p("$var bar", $cgi->br(), "for you.", {class ='blue'});

That stuff is only useful, IMO, for, like, the popup_menu method and
stuff where just writing out the HTML is a pain.
)

Buy yeah, I want to know how to do this every way, and I figure
branching out to PHP was one of the best starts.

If you want to learn another language, I would also suggest you learn
how the other language does things. Don't try to force the language
you're learning into the method you're used to.

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

Jul 5 '06 #13

Dodger

Jerry Stuckle wrote:

Dodger wrote:

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

Yeah -- I don't wan tto do that, because then I have to do unnecesary
regex's to play games to make wure they don't pass in a result like ...
uhm... say it was instead of City it was Musketeer and they passed in
D'Artagnan.... Oops. Welcome to the message that asks me to check my
SQL syntax because it failed to recognise the keyword Artagnan.

Or worse, some script kiddy finds ways to sneak it past. Bind_vars are
my friends.

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

Cool. It was the whole response object thing that was leaving me
confused.

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.

As above, it turns out that this little bit was at the heart of my
confusion... In the DBI, if there are records coming back, your
statement handle doubles as your response object. If you issue a SELECT
or other thing that returns rows, your statement handle's fetch*
methods will do things. If not, they return undef, and so a while loop
around them will never execute -- but the methods are *there* they are
just empty because there's no return recordset.

I figure that's probably all form the Perlish DWIM philosophy -- Do
What I Mean -- in that, well, honestly, I can't think of a single
instance where someone would issue a pure 'SELECT' statement to an
RDBMS without wanting to know what they got back, so just making that
wrapped up as part of the database abstraction layer makes sense to me.

Compared to English, it's the difference between:

Person A: "Hey Person B, I'm going to ask you a question" (prepare)
Person A: "What's your name?" (execute)
Person B: "I'm Person B." (fetch)

and

Person A: "Hey, Person B, I'm going to ask you a question" (prepare)
Person A: "What's your name?" (execute)
Person A: "Now get your answer ready and tell Person C" (store result)
Person C: "He says, 'I'm.. uh... person B and you're a damned control
freak...'" (fetch (with warnings))

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

But I need to do it with the response object instead of the statement
so I can use fetch_assoc, because I don't know how many columns or what
their names will be ahead of time.

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.

indeed -- and those package names seemingly being seperated by _ throws
me a lot too. Thank you though!

--
Dodger

Jul 5 '06 #14

Dodger

Jerry Stuckle wrote:

Dodger wrote:

If you want to learn another language, I would also suggest you learn
how the other language does things. Don't try to force the language
you're learning into the method you're used to.

I won't. Thanks. Haven't started doing that yet.

Only reason I'm holding to using bind variables rather than dynamically
building a statement is because those bind variables have a reason, and
the only reason I need an associative array back from query results
rather than named variables to be bound to is that I'm using the same
database backend and the table structure varies between session tables
on the different virtual domains.

In other words, it's not force it to be like Perl, it's just do the
things I take for granted in Perl, that I often find people not
bothering with in PHP, but my sites internals are largely builtaround
those idioms existing.

Jul 5 '06 #15

Dodger

Jerry Stuckle wrote:

Dodger wrote:
Sandman wrote:

>In article <11**********************@a14g2000cwb.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

>I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

Proper escaping of the string will prevent sql injection attacks. Bind
variables are not required. See mysql_real_escape().

>Why the extra foreach? Just "do stuff" in the mysql while loop.

Could be plenty of reasons. In the example up top, I am populating the
environment (%ENV in Perl, $_ENV in PHP) with my special session
variables (and yes, I know PHP has session handling systems, but I'm
not gonna use them because I want stuff to work in *both* Perl and PHP
CGIs* -- I might be populating some other SESSION_* environment
variables elsewhere, for instance.

You're really trying to make things complicated, aren't you?

Nope. Got a perfectly good reason to drop my session variables into the
script local environment. Those environment variables trickle down to
SSI includes under Apache 2, and I put SSI include tags in my output,
which means that normal SHTML markup can access things -- and act
accordingly without needing to talk to a database at all on its own.

And I do want everything to be able to use the same sessioning system
rather than two seperate ones.

Of course, if I can work out where the 'leak' is happening, I may be
able to simply set it up to work in my previously attempted mod_perl
PerlSetupHandler -- which would then set ENV variables for whatever
extension I tell it. But I'm not putting things on hold until that,
either. B^)

Jul 5 '06 #16

Jerry Stuckle

Dodger wrote:

Jerry Stuckle wrote:

>>Dodger wrote:

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

Yeah -- I don't wan tto do that, because then I have to do unnecesary
regex's to play games to make wure they don't pass in a result like ...
uhm... say it was instead of City it was Musketeer and they passed in
D'Artagnan.... Oops. Welcome to the message that asks me to check my
SQL syntax because it failed to recognise the keyword Artagnan.

No, you use mysql_real_escape, i.e.

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

That's part of what this function is for.

Or worse, some script kiddy finds ways to sneak it past. Bind_vars are
my friends.

So is mysql_real_escape().

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

Cool. It was the whole response object thing that was leaving me
confused.

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

As above, it turns out that this little bit was at the heart of my
confusion... In the DBI, if there are records coming back, your
statement handle doubles as your response object. If you issue a SELECT
or other thing that returns rows, your statement handle's fetch*
methods will do things. If not, they return undef, and so a while loop
around them will never execute -- but the methods are *there* they are
just empty because there's no return recordset.

When using mysql_query or mysqli_query, the result is your response
object (or false if the statement failed). But not with the mysqli_stmt
object.

I figure that's probably all form the Perlish DWIM philosophy -- Do
What I Mean -- in that, well, honestly, I can't think of a single
instance where someone would issue a pure 'SELECT' statement to an
RDBMS without wanting to know what they got back, so just making that
wrapped up as part of the database abstraction layer makes sense to me.

It is, but you have multiple ways of doing the SELECT and getting the
response.

Compared to English, it's the difference between:

Person A: "Hey Person B, I'm going to ask you a question" (prepare)
Person A: "What's your name?" (execute)
Person B: "I'm Person B." (fetch)

and

Person A: "Hey, Person B, I'm going to ask you a question" (prepare)
Person A: "What's your name?" (execute)
Person A: "Now get your answer ready and tell Person C" (store result)
Person C: "He says, 'I'm.. uh... person B and you're a damned control
freak...'" (fetch (with warnings))

But what if you ask a group of people "what's your name"? i.e.

Person A: "Hey, I'm going to ask you a question" (prepare)
Person A: "What's your name?" (execute)
Person B: "Bill"
Person C: "Carl"
Person D: "Diane"
Person A: "Now get an answer" (process Bill)
Person A: "Now get an answer" (process Carl)
Person A: "Now get an answer" (process Diane)

>

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

But I need to do it with the response object instead of the statement
so I can use fetch_assoc, because I don't know how many columns or what
their names will be ahead of time.

You don't know the underlying database structure? Then how do you know
the data you want is even there? Or, if you only want specific data,
you shouldn't be doing SELECT * - just select the columns you want.

Or, if you insist on using the bind parameters, you can use
stmt->result_metadata to get the metadata for the statement, including
the number of columns returned and their names.

>

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

indeed -- and those package names seemingly being seperated by _ throws
me a lot too. Thank you though!

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

Jul 5 '06 #17

Jerry Stuckle

Dodger wrote:

Jerry Stuckle wrote:

>>Dodger wrote:

>>If you want to learn another language, I would also suggest you learn
how the other language does things. Don't try to force the language
you're learning into the method you're used to.

I won't. Thanks. Haven't started doing that yet.

Only reason I'm holding to using bind variables rather than dynamically
building a statement is because those bind variables have a reason, and
the only reason I need an associative array back from query results
rather than named variables to be bound to is that I'm using the same
database backend and the table structure varies between session tables
on the different virtual domains.

In other words, it's not force it to be like Perl, it's just do the
things I take for granted in Perl, that I often find people not
bothering with in PHP, but my sites internals are largely builtaround
those idioms existing.

Well, as I indicated in my most recent post - you can do it by fetching
the metadata, but it's a hard way of doing things.

Bind variables go both ways - if you're going to bind the parameters,
you need to bind the results, also. Or, you can use non-bound
statements and mysql(i)_fetch_array to get the results.

But you can't mix and match.

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

Jul 5 '06 #18

Rik

Dodger wrote:

Rik wrote:
>Dodger wrote:

>mysqli_stmt_fetch_assoc doesn't exist, mysqli_stmt_fetch does.

http://us2.php.net/manual/en/functio...etch-assoc.php
Not stmt_fetch_assoc, just fetch_assoc...

if you :
$statement->method
Then you use the mysqli_stmt_ * family.
Check the manual for where functions can be used.

Grtz,
--
Rik Wasmus

Jul 5 '06 #19

Sandman

In article <11**********************@l70g2000cwa.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

If you're going to use a user-submitted value, it's going to be a
user-submitted value.

# ?country=USA's

$c = addslashes($_GET["country"]);

$q = mysql_query("select * from cities where country = '$c'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}
--
Sandman[.net]

Jul 6 '06 #20

Jerry Stuckle

Sandman wrote:

In article <11**********************@l70g2000cwa.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

>>>I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

If you're going to use a user-submitted value, it's going to be a
user-submitted value.

# ?country=USA's

$c = addslashes($_GET["country"]);

$q = mysql_query("select * from cities where country = '$c'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

You shouldn't use addslashes. That's what mysql_real_escape_string() is
for.

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

Jul 6 '06 #21

Richard Levasseur

Jerry Stuckle wrote:

Sandman wrote:
In article <11**********************@l70g2000cwa.googlegroups .com>,
"Dodger" <el********@gmail.comwrote:

>>I've not used "mysqli", but doing the above in mysql:

<?
$db = mysql_connect("host", "user", "password");

$q = mysql_query("select * from cities where country = 'USA'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

foreach ($cities as $city){
# Do stuff
}
?>

Well, I do need bind variables. In the non-mysqli example up there, the
country is hardcoded into the statement. Even if I filled that in with
a variable, what if it was a variable that was passed in by a user? It
would be too easy to hack and would end up either potentially crippling
things or with an arms race between me and some l33+ haxcsore wanker
whi thinks he's clever.

If you're going to use a user-submitted value, it's going to be a
user-submitted value.

# ?country=USA's

$c = addslashes($_GET["country"]);

$q = mysql_query("select * from cities where country = '$c'");
while ($r = mysql_fetch_assoc()){
$cities[] = $r["city"];
}

You shouldn't use addslashes. That's what mysql_real_escape_string() is
for.

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

Also make sure to turn magic_quotes OFF

Jul 6 '06 #22

Dodger

Jerry Stuckle wrote:

You don't know the underlying database structure? Then how do you know
the data you want is even there? Or, if you only want specific data,
you shouldn't be doing SELECT * - just select the columns you want.

Or, if you insist on using the bind parameters, you can use
stmt->result_metadata to get the metadata for the statement, including
the number of columns returned and their names.

No, you misunderstand me--
I know certain things about the data structure, but other things will
vary depending on which virtual domain is being served up -- and I
shouldn't need to code the same basic sessioning system more than once.

Okay, there's a session table for each virtual domain. The virtual
domain (hostname) the request goes to determines which table to use.
The table structure of the session have additional fields on top of the
basic ones. I should be able to change this without needing to redo
code at all, just by adding columns to the session system.

For instance, say we have foo.com and bar.com. foo.com is a
subscription content site, and bar.com is a online store selling sporks
in assorted varieties.

foo.com therefore needs to know whether a user is subscribed. bar.com
on the other hand doesn't even know the concept of a subscription, but
it does need to know the shopping cart ID of a user. Therefore, we take
the basic table structure:

[generic_session]
id varchar(32) // MD5 string
member varchar(32) // or whatever
handle varchar(16)

and for foo.com's foo_session table it's different. It has one more
column:
[foo_session]
id varchar(32)
member varchar(32)
handle varchar(16)
subscribed enum('Yes','No')

While bar.com doesn't even need a handle (perhaps email is used instead
of a username to log in) but it does need a shopping cart id, thus
[bar_session]
id varchar(32)
member varchar(32)
cart_id varchar(32)

Now, there's no reason on Earth that I should be forced to make up
seperate session modules for foo and bar, when the statement:

SELECT *
FROM $table
WHERE id = ?

is plenty to get me id, member, handle, subscribed on foo, and to get
me id, member, cart_id on bar.

All that said, things are still not working...

if ($sessionid) {
$get_session_st = <<<EOF
SELECT *
FROM apache.xfx_session
WHERE id = ?
EOF;
?>
<pre><?= $get_session_st ?></pre>
<?php
$get_session_q = $sql->prepare($get_session_st);
if ($get_session_q->bind_param('s', $sessionid)) {
?>Bound params<?php
$get_session_q->execute();
$get_session_res = $get_session_q->result_metadata();
if ($get_session_res) {
?>Session results returned fine.<?php
$session_data =
$get_session_res->fetch_array(MYSQLI_ASSOC);

if ($session_data) {
?>Session data is true

<table border="1">
<?php

foreach ($session_data as $col =$val) {
?>

<tr>
<td><?= $col ?></td>
<td><?= $val ?></td>
</tr>

<?php
}

?>
</table> 

<?php
}
else {
?>$session_data is false<?php
if ($get_session_q->error) {
?>Errstr: <?= $get_session_q->error ?><?php
}
else {
?>No error string...?<?php
}
}

$get_session_res->free_result();
$get_session_q->free_result();
}
else {
?>
No session results...?

<?php
}
}
else {
?>Could not bind params<?php
}

}
else {
$sessionid = 'No session ID';
}
Now I'm explicitly getting the get_session_res result object
$get_session_res. And checking. And it's working fine. That far.

Narrowing things down, the fetch_assoc() method is just supposed to be
internally aliased to fetch_array(MYSQLI_ASSOC) from what the docs are
indicating. A lot.

But
$session_data = $get_session_res->fetch_array(MYSQLI_ASSOC);

Raises no errors. However it also doesn't give me back anything.

"
SELECT *
FROM apache.xfx_session
WHERE id = ?

Bound params

Session results returned fine.

$session_data is false

No error string...?
"

Yes I can explicitly do:
SELECT *
FROM apache.xfx_session
WHERE id = '29f0b7ee81407e58aa.snip.247f5897'

and get back

id member handle last_action login_dt access subscribed
remember newsletter aeoncust nudity violence mature
29f0b7ee81407e58aa.snip247f5897 4d15bea711d36.snip.4768914c27129
Dodger 2006-07-07 06:37:00 2006-07-07 06:36:58 ADMIN:9;SUPERUSER:9
Yes Yes No No Yes Yes Yes

when I query it in the database.

Logged into the database as *root* right now in both cases to avoid any
possible other things messing up the process. Perl and the MySQL
monitor are giving me back things. The result object seems to be
turning up dead -- and from everything I've been seeing it shouldn't
be.

So I am once again lost.

Jul 7 '06 #23

Jerry Stuckle

Dodger wrote:

Jerry Stuckle wrote:

>>You don't know the underlying database structure? Then how do you know
the data you want is even there? Or, if you only want specific data,
you shouldn't be doing SELECT * - just select the columns you want.

>>Or, if you insist on using the bind parameters, you can use
stmt->result_metadata to get the metadata for the statement, including
the number of columns returned and their names.

No, you misunderstand me--
I know certain things about the data structure, but other things will
vary depending on which virtual domain is being served up -- and I
shouldn't need to code the same basic sessioning system more than once.

They are two different tables - so typically they have two different
routines. In this case one size does NOT fit all.

Okay, there's a session table for each virtual domain. The virtual
domain (hostname) the request goes to determines which table to use.
The table structure of the session have additional fields on top of the
basic ones. I should be able to change this without needing to redo
code at all, just by adding columns to the session system.

And why is that? You're accessing different data - so you need
different code.

For instance, say we have foo.com and bar.com. foo.com is a
subscription content site, and bar.com is a online store selling sporks
in assorted varieties.

foo.com therefore needs to know whether a user is subscribed. bar.com
on the other hand doesn't even know the concept of a subscription, but
it does need to know the shopping cart ID of a user. Therefore, we take
the basic table structure:

[generic_session]
id varchar(32) // MD5 string
member varchar(32) // or whatever
handle varchar(16)

and for foo.com's foo_session table it's different. It has one more
column:
[foo_session]
id varchar(32)
member varchar(32)
handle varchar(16)
subscribed enum('Yes','No')

While bar.com doesn't even need a handle (perhaps email is used instead
of a username to log in) but it does need a shopping cart id, thus
[bar_session]
id varchar(32)
member varchar(32)
cart_id varchar(32)

Now, there's no reason on Earth that I should be forced to make up
seperate session modules for foo and bar, when the statement:

SELECT *
FROM $table
WHERE id = ?

is plenty to get me id, member, handle, subscribed on foo, and to get
me id, member, cart_id on bar.

Sure. But they're two different tables.

All that said, things are still not working...

I'm not surprised. Short cuts rarely are.

if ($sessionid) {
$get_session_st = <<<EOF
SELECT *
FROM apache.xfx_session
WHERE id = ?
EOF;
?>
<pre><?= $get_session_st ?></pre>
<?php
$get_session_q = $sql->prepare($get_session_st);
if ($get_session_q->bind_param('s', $sessionid)) {
?>Bound params<?php
$get_session_q->execute();
$get_session_res = $get_session_q->result_metadata();
if ($get_session_res) {
?>Session results returned fine.<?php
$session_data =
$get_session_res->fetch_array(MYSQLI_ASSOC);

if ($session_data) {
?>Session data is true

<table border="1">
<?php

foreach ($session_data as $col =$val) {
?>

<tr>
<td><?= $col ?></td>
<td><?= $val ?></td>
</tr>

<?php
}

?>
</table> 

<?php
}
else {
?>$session_data is false<?php
if ($get_session_q->error) {
?>Errstr: <?= $get_session_q->error ?><?php
}
else {
?>No error string...?<?php
}
}

$get_session_res->free_result();
$get_session_q->free_result();
}
else {
?>
No session results...?

<?php
}
}
else {
?>Could not bind params<?php
}

}
else {
$sessionid = 'No session ID';
}
Now I'm explicitly getting the get_session_res result object
$get_session_res. And checking. And it's working fine. That far.

Narrowing things down, the fetch_assoc() method is just supposed to be
internally aliased to fetch_array(MYSQLI_ASSOC) from what the docs are
indicating. A lot.

But
$session_data = $get_session_res->fetch_array(MYSQLI_ASSOC);

Raises no errors. However it also doesn't give me back anything.

That's right, because you're trying to mix bind and non-bind functions.
It doesn't work.

"
SELECT *
FROM apache.xfx_session
WHERE id = ?

Bound params

Session results returned fine.

$session_data is false

No error string...?
"

No, there's nothing wrong with the syntax. But there are no results to
return the way you're doing it. You used mysql_bind_param to bind the
parameters so you need to use mysql_bind_result to get the results back,
not mysql_fetch_assoc().

Yes I can explicitly do:
SELECT *
FROM apache.xfx_session
WHERE id = '29f0b7ee81407e58aa.snip.247f5897'

and get back

id member handle last_action login_dt access subscribed
remember newsletter aeoncust nudity violence mature
29f0b7ee81407e58aa.snip247f5897 4d15bea711d36.snip.4768914c27129
Dodger 2006-07-07 06:37:00 2006-07-07 06:36:58 ADMIN:9;SUPERUSER:9
Yes Yes No No Yes Yes Yes

when I query it in the database.

Yes, because you're not binding the parameters.

Logged into the database as *root* right now in both cases to avoid any
possible other things messing up the process. Perl and the MySQL
monitor are giving me back things. The result object seems to be
turning up dead -- and from everything I've been seeing it shouldn't
be.

So I am once again lost.

You're trying to mix apples and oranges. It doesn't work.

People have tried to create generic libraries before. Some of them,
like Pear::DB, are pretty decent. Others are not so good.

If you insist on using bound parameters, you *must* use bound results.
It doesn't work otherwise. And if you need to know the column names,
you need to fetch the metadata.

Now tell me - how long have you been fooling around trying to get this
to work - vs. how long it would have taken you to copy/past/modify the
code to work with different tables in the first place? It could have
easily been done by simply creating separate classes for each table,
reuse common code (or better yet, derive all from a common base) and add
code to handle the unique stuff.

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

Jul 7 '06 #24

Dodger

Jerry Stuckle wrote:

They are two different tables - so typically they have two different
routines. In this case one size does NOT fit all.

And why is that? You're accessing different data - so you need
different code.

But you *shouldn't* need different code...
*drops face into hands*

I'm not surprised. Short cuts rarely are.

I'm not talkin g about a shortcut, I'm talking about polymorphism.

That's right, because you're trying to mix bind and non-bind functions.
It doesn't work.

No, there's nothing wrong with the syntax. But there are no results to
return the way you're doing it. You used mysql_bind_param to bind the
parameters so you need to use mysql_bind_result to get the results back,
not mysql_fetch_assoc().

Yes, because you're not binding the parameters.

You're trying to mix apples and oranges. It doesn't work.

People have tried to create generic libraries before. Some of them,
like Pear::DB, are pretty decent. Others are not so good.

If you insist on using bound parameters, you *must* use bound results.
It doesn't work otherwise. And if you need to know the column names,
you need to fetch the metadata.

See, that's where I'm not seeing any sense to this. Returning a row
from a database as an associative array seems *basic* to me, and should
always be able to be done. Further, using bind variables in order to
execute an equation also seems simple and basic to me.

What doesn't make any sense to me is *why* they wouldn't be allowed...

Now tell me - how long have you been fooling around trying to get this
to work - vs. how long it would have taken you to copy/past/modify the
code to work with different tables in the first place? It could have
easily been done by simply creating separate classes for each table,
reuse common code (or better yet, derive all from a common base) and add
code to handle the unique stuff.

Quite honestly, that doesn't matter in the grand scheme of things.
Copying, pasting, and modifying code is not a good coding practice.
It's not clean code.

When I started using MySQL with Perl back in 1998, I did stuff like
that. I'd write queries like:

SELECT *
FROM session
WHERE id = '$sessionid'

After carefully checking sessionid for the existence of a ' and stuff
with regexes. I later learned *not to*... not because of Perl, but
because of good coding practices.

And I don't see any rationale for *why* I shouldn't be able to call
fetch_assoc() on a query I prepared with bind variables, nor do I see
what tying results to variables has to do with submitting a query with
bind variables have to do with each other, except that whoever
originally wrote the mysqli interface stopped halfway on the API.

Binding variables into the query has *nothing* do to with binding (we
perlmongers call the latter case tying) the results to variables.
Except that someone inscrutably wrote it that way.

Anyway I tried to work around it... I tried an eval. I went through the
metadata which I *could* gat back from a bound query and generated a
string of code to do all the result binding.

So things escalated to a new level of inscrutable. It didn't work. The
eval raised an exception about an unexpected '$' in the eval string --
???

So I had it print out its eval string that it was trying to run. Then I
copied *that* and pasted it in to see if I could get a more detailed
answer outside of an eval...

It worked fine. Got everything back.

Anyway I have put up a comparison of three scripts that would do the
same thing, one in Perl, one in PHP, and one in ColdFusion.

http://www.xfx3d.net/scriptcompare/

There's one main difference between all three's functionality:

If I do the following in MySQL:

ALTER TABLE session
ADD spork VARCHAR(12)

The three scripts will react differently.

Perl will continue to function and do everything expected. It will
press on and add in a SESSION_SPORK environment variable.

ColdFusion will continue to function, but it will ignore the spork
column and thus will work *exactly* as it did -- without setting the
SESSION_SPORK environment variable. ColdFusion does have proper
bind_params too, but the database itself must support them. However in
a cfquery it's my understanding that it automatically escapes the input
anyway (don't use my statement as a citation on Wikipedia or anything
-- I'm not a big CF person, just some small experience and could be
totally wrong about that) and thus works the same pretty much.

PHP will fail to run at all, because bind_result will not have enough
arguments anymore.

I will look into this Pear::DB thing. But if it won't do the job right
either, I think I am going to have to give up on using PHP for the task
I had in mind. Sorry. I tried. I really did give it a shot.

Just if there's not a solid and functional database abstraction layer
that works right and doesn't make arbitrary rules about 'if you bound a
parametre on the query you have to tie the results' and so on... then
PHP doesn't appear to be ready to me.

Jul 8 '06 #25

Dodger

Though honestly I will admit I want PHP on my resume because all sorts
of employers seem to be demanding it *sigh*

Maybe if I just set up a decent PerlFixupHandler to do this under
mod_perl and *prepopulate* the environment variables, PHP will simply
have the environment already populated by the time it gets to run.

Jul 8 '06 #26

Jerry Stuckle

Dodger wrote:

Jerry Stuckle wrote:

>>They are two different tables - so typically they have two different
routines. In this case one size does NOT fit all.

>>And why is that? You're accessing different data - so you need
different code.

But you *shouldn't* need different code...
*drops face into hands*

In 25 years of RDB programming I've never been able to do what you're
trying to do efficiently unless I have an OO database - and MySQL isn't
an OO database. Sure, I've been able to cobble things together. But
the results have never been satisfactory performance wise.

>
>>I'm not surprised. Short cuts rarely are.

I'm not talkin g about a shortcut, I'm talking about polymorphism.

This is not polymorphism. First of all, MySQL is not an OO database.
Secondly, PHP doesn't use polymorphism in this way (in fact, neither do
Java, C++ or ADA).

If you want polymorphism, you:

1. Create a base class with code common to all tables (i.e. connect,
disconnect)
2. Create a derived class for each table with code specific to that
table, i.e. SELECT statements where column names are different
3. Instantiate the appropriate derived class where you need it.

>
>>That's right, because you're trying to mix bind and non-bind functions.
It doesn't work.

>>No, there's nothing wrong with the syntax. But there are no results to
return the way you're doing it. You used mysql_bind_param to bind the
parameters so you need to use mysql_bind_result to get the results back,
not mysql_fetch_assoc().

>>Yes, because you're not binding the parameters.

>>You're trying to mix apples and oranges. It doesn't work.

>>People have tried to create generic libraries before. Some of them,
like Pear::DB, are pretty decent. Others are not so good.

>>If you insist on using bound parameters, you *must* use bound results.
It doesn't work otherwise. And if you need to know the column names,
you need to fetch the metadata.

See, that's where I'm not seeing any sense to this. Returning a row
from a database as an associative array seems *basic* to me, and should
always be able to be done. Further, using bind variables in order to
execute an equation also seems simple and basic to me.

What doesn't make any sense to me is *why* they wouldn't be allowed...

Because that's the way it works. If you want it changed, put in a
suggestion to the PHP developers. Or create your own fork of PHP to
include it.

>
>>Now tell me - how long have you been fooling around trying to get this
to work - vs. how long it would have taken you to copy/past/modify the
code to work with different tables in the first place? It could have
easily been done by simply creating separate classes for each table,
reuse common code (or better yet, derive all from a common base) and add
code to handle the unique stuff.

Quite honestly, that doesn't matter in the grand scheme of things.
Copying, pasting, and modifying code is not a good coding practice.
It's not clean code.

I've found it's much cleaner code than what you're doing, if it's done
right. I cut/paste all the time, then modify for the specific instance.
Really shortens my development time (and my client's costs).

When I started using MySQL with Perl back in 1998, I did stuff like
that. I'd write queries like:

SELECT *
FROM session
WHERE id = '$sessionid'

After carefully checking sessionid for the existence of a ' and stuff
with regexes. I later learned *not to*... not because of Perl, but
because of good coding practices.

It's still not good practice in PHP or any other language to SELECT * if
you only need one or two columns. Get only what you're going to use.

And I don't see any rationale for *why* I shouldn't be able to call
fetch_assoc() on a query I prepared with bind variables, nor do I see
what tying results to variables has to do with submitting a query with
bind variables have to do with each other, except that whoever
originally wrote the mysqli interface stopped halfway on the API.

Because that's the way it works. See above.

Binding variables into the query has *nothing* do to with binding (we
perlmongers call the latter case tying) the results to variables.
Except that someone inscrutably wrote it that way.

In your opinion. Perhaps they have access to information you don't.

Anyway I tried to work around it... I tried an eval. I went through the
metadata which I *could* gat back from a bound query and generated a
string of code to do all the result binding.

So things escalated to a new level of inscrutable. It didn't work. The
eval raised an exception about an unexpected '$' in the eval string --
???

I can believe that. As I said before - it can be done, but I wouldn't
recommend it.

So I had it print out its eval string that it was trying to run. Then I
copied *that* and pasted it in to see if I could get a more detailed
answer outside of an eval...

It worked fine. Got everything back.

Anyway I have put up a comparison of three scripts that would do the
same thing, one in Perl, one in PHP, and one in ColdFusion.

http://www.xfx3d.net/scriptcompare/

There's one main difference between all three's functionality:

If I do the following in MySQL:

ALTER TABLE session
ADD spork VARCHAR(12)

The three scripts will react differently.

Perl will continue to function and do everything expected. It will
press on and add in a SESSION_SPORK environment variable.

ColdFusion will continue to function, but it will ignore the spork
column and thus will work *exactly* as it did -- without setting the
SESSION_SPORK environment variable. ColdFusion does have proper
bind_params too, but the database itself must support them. However in
a cfquery it's my understanding that it automatically escapes the input
anyway (don't use my statement as a citation on Wikipedia or anything
-- I'm not a big CF person, just some small experience and could be
totally wrong about that) and thus works the same pretty much.

PHP will fail to run at all, because bind_result will not have enough
arguments anymore.

I don't know about Perl or Cold Fusion. But I agree PHP won't work.
But it will work fine if you only select the columns you need.

I will look into this Pear::DB thing. But if it won't do the job right
either, I think I am going to have to give up on using PHP for the task
I had in mind. Sorry. I tried. I really did give it a shot.

No, you're just trying to force PHP to conform to *your* idea of how
programming should be, instead of adapting to *PHP's* method of programming.

There are millions of programmers who work quite well with PHP. But you
have problems with it. Gee. I wonder where the real problem is?

Just if there's not a solid and functional database abstraction layer
that works right and doesn't make arbitrary rules about 'if you bound a
parametre on the query you have to tie the results' and so on... then
PHP doesn't appear to be ready to me.

As I said - to *you* they are arbitrary. But you don't have all the
facts, do you?

Try learning some good programming practices when using RDB's. Things
like only selecting the columns you want. Handling tables which are
different by using different code.

The problem here is not with PHP.

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

Jul 8 '06 #27

Confused by mysqli

Similar topics