|
|
Posted by Jerry Stuckle on 07/05/06 13:26
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.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|