addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobegmailgooglegroupsimageimagesinstagramlinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1outlookpersonStartprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

Re: [php-49] mysqli prepared query question

From: David M.
Sent on: Thursday, November 20, 2008 8:42 PM
Wow!  Not sure why I was moving away from PDO, but I think I'm moving back now!

Without getting into too much detail, I determined it would be easier for what I am doing to use the (yikes) SELECT * ... for parts of what I'm doing.  Hence the run-in with binding.

If PDO will let me bind going in but not going out, that's EXACTLY what I need.

MUCH! thanks Ian (and Greg) on this matter!!
David




--- On Thu, 11/20/08, Ian Maddox <[address removed]> wrote:
From: Ian Maddox <[address removed]>
Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 8:07 PM

Yeah, I can confirm that too.
I've never gotten that far into the mysqli bind syntax.  It seems a bit heavy handed, but I guess it's not the worst thing.

I've modified your code to run on against a wordpress database:

<?php
require_once('../inc/global_prepend.php');
$user = '';
$pass = '';
$db = '';
$mysqli = new mysqli("localhost", $user, $pass, $db);

// Prepare query
if(!$stmt = $mysqli->prepare("SELECT user_nicename FROM wp_users WHERE ID=?"))
{
    die("query prep fail!");
}
/* @var $stmt mysqli_stmt */
// Bind the result.  The output for the column(s) will appear in the list of variables passed into this method.
$stmt->bind_result($result);

for($i = 1; $i < 100; $i++)
{
    $stmt->bind_param('i',$i);

    // Execute
    $stmt->execute();

    // Show results
    if($stmt->fetch())
    {
        echo "$i: $result\n";
    }
}


The problem I have with the mysqli bound parameters query is that you must change your interface code in order to retrieve the results.  If you want an extra column in your results, you not only have to modify your query and the code that will eventually digest that new column, you have the added work of adding a new bound result to your db interface code.  Not that you should be doing it, but heaven forbid you have to run a SELECT * query with bound parameters.

After comparing the two, I much prefer the flexibility you get from the PDO approach:


$pdo = new PDO("mysql:dbname={$db};host=localhost",$user,$pass);
if(!$stmt = $pdo->prepare("SELECT user_nicename FROM wp_users WHERE ID=:id"))
{
    die("statement prepare fail!");
}

for($i = 1; $i < 100; $i++)
{
    $stmt->bindParam(':id',$i);

    // Execute
    $result = $stmt->execute();

    // Show results (or not!) -- why doesn't this work?
    while($result = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo "$i: {$result['user_nicename']}\n";
    }
}

It does the same thing, but you aren't forced to mess with bound results.  As a side bonus, your variable anchors are named (which IMO makes debugging a lot easier) and you get all of the extra flexibility that comes with PDO.

--Ian


On Thu, Nov 20, 2008 at 7:22 PM, greg russell <[address removed]> wrote:
That is what it looks like, I haven't read it explicitly but all of the examples I've seen do it that way. Don't forget to use free_result  to clear the memory after it is done. If it is a large result set you may have to modify php.ini to allow the script to use more memory than the default which is 16mb if I recall.


On Thu, Nov 20, 2008 at 7:09 PM, David Malouf <[address removed]> wrote:
[Continuing to scour search engines for hints of insight...]

I came across a 2006 article that stated, basically, binding going in means binding coming out.  If I use bind_param, I HAVE to use bind_result.

Can anyone verify this for me?
David

--- On Thu, 11/20/08, David Malouf <[address removed]> wrote:
From: David Malouf <[address removed]>

Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 6:56 PM


I was trying to get away from binding the results because my results are going to include a TON of columns.  Further, I am attempting to make this aspect of the site/results be column-agnostic and let a viewer-type page loop-and-present the results.

Plus it's frustrating me that I can't figure out what's going on!!

David


--- On Thu, 11/20/08, greg russell <[address removed]> wrote:
From: greg russell <[address removed]>
Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 6:33 PM

To tell you the truth, I have never used mysqli. Lately I have been using pdo with zend framework. I am just bored at work and can't write any code on this computer.

All bind_result does is set the result to the variable passed to it.
Why not use it?

On Thu, Nov 20, 2008 at 6:25 PM, David Malouf <[address removed]> wrote:
The thing that's KILLING me is that if I bind the results, I get the Name of the organization whose ID is 15.  Why do I have to bind_result ?!?!


David

--- On Thu, 11/20/08, greg russell <[address removed]> wrote:
From: greg russell <[address removed]>
Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 6:15 PM


Maybe put the variable declaration before the bind_param

How it is in email:

$stmt->bind_param("i", $TheID);
$TheID = 15;

TRY:
$TheID = 15;
$stmt->bind_param("i", $TheID);


On Thu, Nov 20, 2008 at 6:03 PM, David Malouf <[address removed]> wrote:
Still nothing, except the column name "Name" as a key in an array with no data.

I'm thinking I've got a problem, now, with my preparing of the query.  I'm getting the key/column from the database, but no data.

(btw, when I run the query with 'ID = 15' and not 'ID = ?', and no binding, I DO get the Name of an organization).

But thank you, Greg, for the idea of fetching an object.  I'm still so new to Objects - forcing myself to use the result as an object (vs. an array) will be good for me . . . should I get any data!


David



--- On Thu, 11/20/08, greg russell <[address removed]> wrote:
From: greg russell <[address removed]>
Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 5:53 PM


try:

$row = $result->fetch_object();
    echo $row->name;


On Thu, Nov 20, 2008 at 5:50 PM, David Malouf <[address removed]> wrote:
Greg,

Thank you!  I had forgotten that requirement.

So now I'm left with that feeling like I'm loosing it.  Even with Greg's 'correction,' I am still not able to get what I need.
 - I changed to fetch_assoc which DOES give me the correct key (ex. Name below) from the query/database.  However, I am still not getting the value (e.g. the name of the organization whose ID is 15).

After the execute(), here's what I have:

// Get data
$result = $stmt->store_result();
$row = $result->fetch_assoc();
print_r($row);        // still nothing

Thank you Greg for getting me one step closer... 

any other ideas?
David




--- On Thu, 11/20/08, greg russell <[address removed]> wrote:
From: greg russell <[address removed]>
Subject: Re: [php-49] mysqli prepared query question
To: [address removed]
Date: Thursday, November 20, 2008, 5:12 PM


From what I am reading on here : //1st comment at bottom.

As a consequence, if you want to use to use fetch_row() with an executed prepared statement, first you'll have to get the result out of this statement with mysqli_store_result() or mysqli_use_result().

hope that helps. link to store_result() 

On Thu, Nov 20, 2008 at 4:35 PM, David Malouf <[address removed]> wrote:
I'm wrestling my way into the world of mysqli, especially prepared queries.  This is also my first foray into an Object Oriented approach/style.

Here's my current roadblock that I seem to be stuck on: I am trying to create a simple, 'prepared statements'-type query (SELECT Name FROM organizations WHERE ID = ?).  The only way I can get this to work is to not only use 'bind_param' (to bind a variable to the 'WHERE ID = ?' part of the query) but also use the 'bind_result' method.  If I don't do this, I get no results.

I've been messing around with this so much that I no longer have a true sample to offer.  But here's something close to what I've been doing (you'll notice a lot of book-type names of variables):

// Connect
$mysqli = new mysqli("localhost", "me", "supersecret", "thedatabase");

// Prepare query
$stmt = $mysqli->prepare("SELECT Name FROM organizations WHERE ID=?");

$stmt->bind_param("i", $TheID);

$TheID = 15;

// Execute
$stmt->execute();

// Show results (or not!) -- why doesn't this work?
$row = $stmt->fetch_row();
print_r($row);

// If I use bind_result, it works
$stmt->bind_result($NewResult);
$stmt->fetch();

print_r($NewRsult);     // Yeah!

Why might this be?
David









--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by David Malouf ([address removed]) from The Seattle PHP Meetup Group.
To learn more about David Malouf, visit his/her member profile
To unsubscribe or to update your mailing list settings, click here

Meetup Support: [address removed]
632 Broadway, New York, NY 10012 USA





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by greg russell ([address removed]) from The Seattle PHP Meetup Group.
To learn more about greg russell, visit his/her member profile

To unsubscribe or to update your mailing list settings, click here

Meetup Support: [address removed]
632 Broadway, New York, NY 10012 USA





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Ian Maddox ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Ian Maddox, visit his/her member profile
To unsubscribe or to update your mailing list settings, click here

Meetup Support: [address removed]
632 Broadway, New York, NY 10012 USA

Our Sponsors

  • TUNE

    Meeting space and food

  • PluralSight

    PluralSight subscriptions for developer training

  • O'Reilly

    Disc Code: PCBW is good for 40% off print and 50% off ebooks and videos

  • JetBrains PhpStorm

    Occasional free licenses to raffle off at meetups

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy