|
Posted by Dodger on 07/05/06 12:07
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
[Back to original message]
|