Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

prepared statement within a function

mysqli bind_param prepared statement

  • Please log in to reply
8 replies to this topic

#1 yonghan

yonghan

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 103 posts

Posted 03 March 2009 - 09:33 PM

Hi guys..i'm trying the following code to get results from query and display it in the tes.php page..

db.inc.php
<?php

function db_connect()
{
$handle=new mysqli('localhost','rekandoa','rekandoa','rekandoa');
if (!$handle)
{
return false;
}
return $handle;
}

function get_member()
{
$handle=db_connect();
$sql="Select email,nama,alamat,kota,propinsi from users where email=?";
$stmt=$handle->prepare($sql);
$mail='yonghan79@gmail.com';
$stmt->bind_param("s",$mail);
$stmt->execute();
$stmt->bind_result($email,$nama,$alamat,$kota,$propinsi);
$result=$stmt->fetch();
return $result;
}
?>


tes.php

<?php
error_reporting(E_ALL & ~E_NOTICE);
include('db.inc.php');
$w=get_member();
echo $w['member_id'];
echo '<br>';
echo $w['email'];
echo '<br>';
echo $w['status'];
echo '<br>';
?>


I got no error message but the results are not shown,it just a blank page..Suppose i did something wrong..Please help me..Thanks...
  • 0

#2 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 04 March 2009 - 04:03 AM

the $handle becomes an object, and it can't be copied like normal values.

try
return &$handle;
which would return a reference to the object instead.

EDIT


$stmt->bind_result($email,$nama,$alamat,$kota,$propinsi);
$result=$stmt->fetch();


makes a small mess for you. the $result will only contain true or false. the actual values will end up in $email,$nama,$alamat,$kota,$propinsi variables.

suggestion:

$stmt->bind_result($email,$nama,$alamat,$kota,$propinsi);
$stmt->fetch();
$result['email'] = $email;
$result['nama'] $nama;
etc etc...
return $result;


I'm unsure if you can bind results to arrays directly, try this...

$stmt->bind_result($result['email'],$result['nama'],$result['alamat'],
$result['kota'],$result['propinsi']);
but I'm not sure if it works...
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#3 yonghan

yonghan

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 103 posts

Posted 04 March 2009 - 04:36 AM

Do you mean that i should put the

return &$handle;

on the function get_member()?About your suggestion,how should i put it on the tes.php page?Thanks a lot..
  • 0

#4 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 04 March 2009 - 05:13 AM

on your first question, yes.

in tes.php if the othersuggestin works, do as you have it now, but with correct names in thearray...
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#5 yonghan

yonghan

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 103 posts

Posted 04 March 2009 - 05:16 AM

How about this code that you gave me?

$stmt->bind_result($email,$nama,$alamat,$kota,$propinsi);  
    $stmt->fetch();
    $result['email'] = $email;
    $result['nama'] $nama;
    etc etc...
    return $result; 

What should i use on the tes.php page?Thanks..
  • 0

#6 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 04 March 2009 - 06:13 AM

same there too actually...
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#7 yonghan

yonghan

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 103 posts

Posted 04 March 2009 - 08:33 AM

Hi Jan,i got it work finally.And sorry before,i didn't try your codes yet..I got this code from PHP and MySQL coding tips - Page 8 - SitePoint Forums
This is the code what i got

$mysqli = new mysqli($dbHost, $dbUsername, $dbPassword, $dbDatabase);

$stmt = $mysqli->prepare('select * from foobar');

$stmt->execute();

$stmt->store_result();



// this returns info about the query, script uses the 

// $meta->name to create an array with the key's as

// the column names

$meta = $stmt->result_metadata();



// this needs to be $bindVarArray[] = &$results[$column->name];

// rather than $results[$column->name] = NULL, when using 

// call_user_func_array it needs the &, I'm not sure why

while ($column = $meta->fetch_field()) {

    $bindVarArray[] = &$results[$column->name];

}        

call_user_func_array(array($stmt, 'bind_result'), $bindVarArray);



$stmt->fetch();



echo var_dump($results);

// outputs:

// 

// array(3) {

//  ["id"]=>

//  &int(1)

//  ["foo"]=>

//  &string(11) "This is Foo"

//  ["bar"]=>

//  &string(11) "This is Bar"

// } 


i modified it this way because i'm using a function

function get_member()
{
    $handle=db_connect();
    //$sql="Select email,nama,alamat,kota,propinsi from users where email='yonghan79@gmail.com'";
    $sql="Select id,email,nama,alamat,kota,propinsi from users where email=?";
    //$hasil=$handle->query($sql);
    //$result=$hasil->fetch_array();
	$stmt=$handle->prepare($sql);
	$mail='yonghan79@gmail.com';
	$stmt->bind_param("s",$mail); 
	$stmt->execute();
    $stmt->store_result();
// this returns info about the query, script uses the 
// $meta->name to create an array with the key's as
// the column names
$meta = $stmt->result_metadata();
// this needs to be $bindVarArray[] = &$results[$column->name];
// rather than $results[$column->name] = NULL, when using 
// call_user_func_array it needs the &, I'm not sure why
while ($column = $meta->fetch_field()) {
    $bindVarArray[] = &$results[$column->name];
}        
call_user_func_array(array($stmt, 'bind_result'), $bindVarArray);
$stmt->fetch();
return $results;
}

As for the tes.php,i did'nt modify..Thanks a lot jan. :)
  • 0

#8 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 04 March 2009 - 08:56 AM

by the way, I'm Orjan, not Jan...
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#9 yonghan

yonghan

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 103 posts

Posted 05 March 2009 - 07:46 AM

Sorry Orjan... :D
  • 0





Also tagged with one or more of these keywords: mysqli, bind_param, prepared statement

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download