Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 946
  • Last Modified:

Bind _Get Array to MySQL Stored Procedure

I have a PHP page that gets 58 variables from a post and passes those to a MySQL (5.1) stored procedure that has the exact parameters in the same position as the post parameters.

I can get the _POST parameters one-by-one and make a parameter string to call the stored procedure, but now that is another place to have to change code when the vialriables change (and they often do.)    Isn't there some way just to bind the _POST[*] array to with something like

                            $stmt->bind_param($_POST[*]);

That particular code does not work, but it would make my life SO much easier if it did. I can't find a way to make it happen.  Is there a way to do this?  I have mysql, msqli and pdo loaded in PHP 5.1.6 with MySQL 5.1.53-MariaDB.

Is there something I can do without hard-coding all the post string variables into PHP?
0
jriggin
Asked:
jriggin
  • 7
  • 6
1 Solution
 
lwadwellCommented:
Use "call_user_func_array", something along these lines maybe ...
// Create the SQL statement and prepare it.
$sql  = "INSERT INTO tablea (product,version,version_date) VALUES (?,?,now())";
$stmt = $mysqli->prepare($sql);

// Create the array and bind them
$values = array('product'=>'abc','version'=>1);
$params = build_casts_4_mysqli($values);
call_user_func_array( array( $stmt, 'bind_param' ), $params ); // binds

// Run the SQL
$res = $stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);

function build_casts_4_mysqli ($arr) {
    $args = array();
    $args[0] = '';
    $i = 1;
    foreach ( $arr as $k=>$v ) {
        if (is_float($v)) { 
            $args[0].="d";
        } elseif (is_numeric($v)) {
            $args[0].="i";
        } else {
            $args[0].="s";
        }
        $args[$i] = $v;
	$i++;
    }
    return $args;
}

Open in new window

0
 
jrigginAuthor Commented:
Thanks, lwadwell.
But that still has me typing 58 parameters into
$sql  = "INSERT INTO tablea (product,version,version_date) VALUES (?,?,now())";

Open in new window

I am trying to not have to know the parameter names in the php code.  Maybe the equivalent of
    foreach (element in $_POST) {
        $nv = explode("=", $POST);
        $name = urldecode($nv[0]);
        $value = urldecode($nv[1]);
        $vars .= $value . ",";
    }
    $sql="call spPostData(".$vars.")"

Open in new window

but I'm green and can't find the right syntax.
0
 
lwadwellCommented:
Where do you need to type the parameters into?  I am confused ... I used an INSERT statement as my example with two bind parameters (as indicated by the question marks '?').  I though you were using a stored procedure.  

What is you SQL statement and perhaps I can show a more relevant example to your requirement.  Or are you also wanting to dynamically generate an SQL statement to be prepared() based on an arbitrary list of passed variables ... which is very different to what you originally asked.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jrigginAuthor Commented:
In your example I would have to enter "product,version,version_date"
Here's my stored procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `spLMB` $$
CREATE DEFINER=`sysop`@`%` PROCEDURE `spLMB`(
  list_id bigint,
	phone_code varchar(100),
	external_key varchar(100),
	address3 varchar(100),
	email varchar(100),
	gender varchar(100),
	comments varchar(100),
	title varchar(100),
	hla_has_fha varchar(100),
	SecondMortgageBalance varchar(100),
	postal_code varchar(100),
	LoanPurpose varchar(100),
	PropertyPurchasedYear varchar(100),
	PropertyDescription varchar(100),
	post_date datetime,
	PropertyFound varchar(100),
	SecondMortgageInterestRate varchar(100),
	NewPropertyValue varchar(100),
	hla_reverse_mortgage varchar(100),
	custom2 varchar(100),
	last_name varchar(100),
	address1 varchar(100),
	`user` varchar(100),
	IntendedPropertyUse varchar(100),
	alt_phone varchar(100),
	FirstMortgageInterestRate varchar(100),
	FirstMortgageBalance varchar(100),
	phone_number varchar(100),
	FirstMortgageRateType varchar(100),
	ExistingPropertyValue varchar(100),
	vendor_lead_code varchar(100),
	hla_cash_in varchar(100),
	DesiredRateType varchar(100),
	FirstTimeBuyer varchar(100),
	country_code varchar(100),
	Bankruptcy varchar(100),
	date_of_birth varchar(100),
	custom1 varchar(100),
	state varchar(100),
	DownPayment varchar(100),
	first_name varchar(100),
	address2 varchar(100),
	city varchar(100),
	middle_initial varchar(100),
	DesiredLoanAmount varchar(100),
	EmploymentStatus varchar(100),
	CreditProfile varchar(100),
	LoanType varchar(100),
	source_id varchar(100)
)
BEGIN
	insert into osdial_list (
    list_id,
    phone_code,
    external_key,
    address3,
    email,
    gender,
    comments,
    title,
    postal_code,
    custom2,
    last_name,
    address1,
    `user`,
    alt_phone,
    phone_number,
    vendor_lead_code,
    country_code,
    date_of_birth,
    custom1,
    state,
    first_name,
    address2,
    city,
    middle_initial,
    source_id
)
  values (
    list_id,
    phone_code,
    external_key,
    address3,
    email,
    gender,
    comments,
    title,
    postal_code,
    custom2,
    last_name,
    address1,
    `user`,
    alt_phone,
    phone_number,
    vendor_lead_code,
    country_code,
    date_of_birth,
    custom1,
    state,
    first_name,
    address2,
    city,
    middle_initial,
    source_id
);
set @Result = last_insert_id();

insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,70,NewPropertyValue);
insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,57,PropertyDescription);
insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,58,CreditProfile);
insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,56,LoanPurpose);
insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,68,PropertyFound);
insert into osdial_list_fields (lead_id, field_id, `value`) Values (@Result,62,hla_reverse_mortgage);

Open in new window

As you can see in my case, I have would have to enter all those parameters into the php code.  Clients send us different post strings, so I am trying to create PHP that doesn't care what query string is, it just calls the stored procedure with a string made of the post variables.  My previous post was an attempt to do this without bind.  I'm trying to iterate through the post string variables and append them to $var and call the stored procedure with the parameters in $var.  I can see why you got confused because I've been trying many different ways to do this.  It is simple in ASP.
0
 
jrigginAuthor Commented:
To be more clear, I want to use the same PHP with different post variables and stored procedures.  All I should have to change is the name of the stored procedure.  (And I could even make the stored procedure name a parameter in the client's http post)
0
 
lwadwellCommented:
OK.  I made this stored proc:
create procedure insert_rec_tablea (
    in_product      varchar(255),
    in_version      int
)
BEGIN
    INSERT INTO tablea (product,version,version_date) 
           VALUES (in_product,in_version,now());
END;

Open in new window

Then I changed, in my example code, this line:
    $sql  = "INSERT INTO tablea (product,version,version_date) VALUES (?,?,now())";
to be:
    $sql  = "call insert_rec_tablea(?,?)";

and it works.  The column names are not specified in the proc call in PHP.  I thought this is what you were doing.
0
 
lwadwellCommented:
Oh .. and my array "$values" I hard-coded in my example ... I was just running the PHP directly and not calling it from anywhere.  Just substitute $_POST for $values.
0
 
jrigginAuthor Commented:
I am sorry for not making myself clear.  I have many clients that post to our database.  Each has a php page that posts to the same MySQL database.  The post variables will ALWAYS match the stored procedure in name and position.  My intention is to have to have my clients include the name of the stored procedure in their post, and everyone calls the same php page.  That way, I only have to change/create stored procedures for clients and not also have tow write a new php page.  This code would do it if it worked.
foreach (element in $_POST) {
        $nv = explode("=", $POST);
        $name = urldecode($nv[0]);
        $value = urldecode($nv[1]);
        $vars .= $value . ",";
    }
    $sql="call spPostData(".$vars.")"

Open in new window

0
 
jrigginAuthor Commented:
I think I can do this with mysqli based on the examples I've seen.
<?php
$mysqli = new mysqli(’127.0.0.1’, ‘user’, ‘password’, ‘database’);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$parms="'";
foreach($_GET as $key => $value)
{
	$parms = $parms . $value . "','";
}
$parms=substr($parms,0,strlen($parms)-2);
$query = "call spMyProc(" . $parms . ")";
echo $query . "<br/><br/>";

$rs = $mysqli->query( $query );
while($row = $rs->fetch_object())
{
echo $row . "<br/>";
}
?>

Open in new window

The stored procedure returns either a recordid or error string.  The stored procedure works as expected when the SQL generated by this php is pasted into query browser.  When run in php, I get "PHP Parse error:  syntax error, unexpected T_DNUMBER in /var/www/html/LMB.php on line 2".  Line 2 is
$mysqli = new mysqli(’127.0.0.1’, ‘user’, ‘password>’, ‘database’);
What is a T_DNUMBER? Can anyone tell me what I'm doing wrong here?
0
 
lwadwellCommented:
I am so sorry, I thought that due to the title of the question being "Bind _Get Array to MySQL Stored Procedure" and the question states "Isn't there some way just to bind the _POST[*] array" and "$stmt->bind_param($_POST[*]);" that you were after a solution that uses the 'bind_param' approach.

Yes the approach in your last post will work.

Why doesn't the connect work?  Unless it is an issue with cut&paste into EE - the quotes on the values look wrong - back-ticks and other non standard (web based) quotation marks.  I have seen this when copying and pasting off web sites.
0
 
jrigginAuthor Commented:
duh!! I didn't see that, thank you - now it works sort of.  Here's what I have now:
<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'password', 'L360');
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$parms="'";
foreach($_GET as $key => $value)
{
	$parms = $parms . $value . "','";
}
// strip the last ,' from the string
$parms=substr($parms,0,strlen($parms)-2);
$query = "call spLMB(" . $parms . ")";
echo $query . "<br/><br/>";
$rs = $mysqli->query( $query );
while($row = $rs->fetch_field())
{
	printf("LeadID:     %s\n", $row->lead_id);
}
$rs->close();
$mysqli->close();
?>

Open in new window

When I run the query produced by line 14 in query browser, it returns a table with a single column "lead_id" and a single row that has a number.  All I see in the browser is "LeadID:           " - the value is not there.
0
 
lwadwellCommented:
Try $rs->fetch_assoc() instead of $rs->fetch_field() then use $row['lead_id'] instead of $row->lead_id
0
 
jrigginAuthor Commented:
That did it!  Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now