Call sql function php

Содержание

Call sql function with php
Call sql function with php
How do I call this function from php?
PHP Tutorial (& MySQL) #13
How to call SQL statements from a php function
How to call a stored procedure in SQL Server with parameters from PHP
Call sql function php

Call sql function with php

What you may try is to use query bindings or prepared queries. In both cases, the Query Builder and Database connections handle escaping the data: Statement using query bindings: Statement using prepared query:

Call sql function with php

I have a SQL function that returns a string value, and I want to call this via php. Should be easy, but after two days I have made little progress.

So for testing I created a function called testString within Namcheap’s phpMyAdmin that just returns a string, and it works when I run it on the namecheap server:

DELIMITER $$ CREATE DEFINER=`user`@`localhost` FUNCTION `testString`(`yo` INT) RETURNS varchar(255) CHARSET latin1 NO SQL SQL SECURITY INVOKER return "this is a string"$$ DELIMITER ;

In php I am able to open the database select, get values, etc. No problem, however I cannot successfully call a function. When I try I get a boolean type rather than a string:

$stringQuery = "SELECT testString();"; $stringResult = $con->query($stringQuery); echo "Type:".gettype($stringResult)."
";

How do I call this function from php?

thanks to @Mehdi I did figure out that the user was not granted permission to execute a function, so I granted permission and now it returns an object rather than a boolean. OK, how do I extract the text string from this object?

You’re probably better off just dumping the entire thing.

$stringQuery = "SELECT testString();"; $stringResult = $con->query($stringQuery); var_dump($stringResult->fetch_all());

Getting to this this simple answer was torture. Thank you @Mehdi for some guidance. I never imagined something this simple could be so complicated.

So, if you have a mySQL function that outputs a string, and you want to retrieve that string value via php, try this php code (troubleshooting lines commented out):

 $stringQuery = "SELECT testString();"; //testString is the function name. $stringResult = $con->query($stringQuery); //echo "
error: ".$con->error."
"; //var_dump($stringResult); $resultArray = $stringResult->fetch_assoc(); //var_dump($resultArray); echo $resultArray["testString()"]; //the function output.

If there is a more direct way of achieving this result, please share.

**Edit: Here is a very direct way (although harder to parse out):

$stringVal = $con->query("SELECT testString();")->fetch_assoc()["testString()"];

obviously if you are passing a value to the function, it would go in the parenthesis, as testString(1).

PHP Tutorial (& MySQL) #13

Hey gang, in this PHP tutorial I want to introduce you to the concept of functions. Functions are Duration: 12:30

How to call SQL statements from a php function

I’m trying to write a code where all SQL statements are stored inside a separate PHP file so I can call them later as functions.

I’ve tried different things but I can’t seem to make it work properly.

Here is my code (I’m trying to make a login page here)

    Username  Password  
 
 
 admin(); ?> admin($username,$password); // Mysql_num_row is counting table row $count=mysql_num_rows($sql); // If result matched $username and $password, table row must be 1 row if($count==1) < //unset($_SESSION); $row = mysql_fetch_assoc($sql); $_SESSION[logged] = $row[logged]; // Register $username, $password and redirect to file "index.php" $_SESSION['username'] = $username; $_SESSION['password'] = $password; $_SESSION['submitted'] = $row[submitted]; $_SESSION['date_submitted'] = $row[date_submitted]; session_register($_SESSION['username']); session_register($_SESSION['password']); ?> else < ?>Wrong Username or Password > ?>

 function admin($username,$password) < echo $sql=mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' "); >> ?>

The error that appears with this code is

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in login.php on line 40

I’ve finally got it. As you said, I’ve added a line in my sql.php which is

another reason why my code didn’t work was because I didn’t place my

inside a variable. Now it looks like this

$a = $result->admin($username,$password);

Like GBD told, you have to return the result set from the mysql query. Furthermore, you have to use the result in your script to handle the submission from the login page.

$result = admin($username,$password) // Mysql_num_row is counting table row $count = mysql_num_rows($result)

You should return result set in your function

function admin($username,$password) < $result=mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' "); return $result; // return result set back >

Your admin function should return a resultset of data:

function admin($username,$password)

$resultset = admin($username, $password);

You can then pass this resultset (not the sql) to mysql_num_rows:

$count = mysql_num_rows($resultset);

//THIS IS THE PART WHERE I CALL THE SQL STATEMENT FROM A SEPARATE PHP FILE echo $sql->admin($username,$password);

The parameters are send correctly, but the result of SQL should be returned from the function.

You used echo in sql.php as well as in login.php

function admin($username,$password)

$result=$sql->admin($username,$password);

and then use the variable $result as $sql is already used for CLASS

Run a PHP function if returned SQL data matches a specific value, I want to run a function if the returned data from database matches a specific value. For example, if SELECT city FROM users ORDER BY id DESC

How to call a stored procedure in SQL Server with parameters from PHP

I use Codeigniter and this is how I call a stored procedure in MySQL:

public function InsertDataSistra($NumPart, $typeChild)< $sql = $this->db->query("CALL PYCC_InsertDataSistra('$NumPart', '$typeChild')"); >

But I need to call a stored procedure with parameters in SQL Server. My sp in sql server is:

CREATE PROCEDURE CantidadReal_PRMS @NumPart VARCHAR(25) = NULL, @HOUSE VARCHAR(15) = NULL, @LBAIS FLOAT(45) = NULL, @LBROW FLOAT(45) = NULL, @LBTIR FLOAT(45) = NULL .

So what would be the call of this SP?

public function PzRealesPRMS($NumPart, $House, $LBAIS, $LBROW, $LBTIR)< $sql = $this->db->query("EXEC CantidadReal_PRMS . "); >

Injecting values in an SQL STATEMENT makes your approach wide open to possible SQL injection. What you may try is to use query bindings or prepared queries. In both cases, the Query Builder and Database connections handle escaping the data:

Statement using query bindings:

public function PzRealesPRMS($NumPart, $House, $LBAIS, $LBROW, $LBTIR) < $sql = $this->db->query( "EXEC CantidadReal_PRMS @NumPart = ?, @HOUSE = ?, @LBAIS = ?, @LBROW = ?, @LBTIR = ?", [$NumPart, $House, $LBAIS, $LBROW, $LBTIR] ); >

Statement using prepared query:

public function PzRealesPRMS($NumPart, $House, $LBAIS, $LBROW, $LBTIR) < $query = $this->db->prepare(function($db) < $sql = "EXEC CantidadReal_PRMS @NumPart = ?, @HOUSE = ?, @LBAIS = ?, @LBROW = ?, @LBTIR = ?"; return (new Query($db))->setQuery($sql); >); $results = $query->execute($NumPart, $House, $LBAIS, $LBROW, $LBTIR); >

Call sql function with php, $stringQuery = «SELECT testString();»; //testString is the function name. $stringResult = $con->query($stringQuery); //echo «
error: «.

Источник

Call sql function php

// Store procedure call without params

$MyConnection = new mysqli ( «DB_SERVER» , «DB_USER» , «DB_PASS» , «DB_NAME» );

mysqli_multi_query ( $MyConnection , «CALL MyStoreProcedure» ) OR DIE ( mysqli_error ( $MyConnection ));

while ( mysqli_more_results ( $MyConnection ))

if ( $result = mysqli_store_result ( $MyConnection ))

while ( $row = mysqli_fetch_assoc ( $result ))

// i.e.: DBTableFieldName=»userID»
echo «row keyword»>. $row [ «DBTableFieldName» ]. «
» ;
.

>
mysqli_free_result ( $result );
>
mysqli_next_result ( $conn );

// Store procedure call using params

$MyConnection = new mysqli ( «DB_SERVER» , «DB_USER» , «DB_PASS» , «DB_NAME» );

mysqli_query ( $MyConnection , «SET @p0='» . $MyParam1 . «‘» );
mysqli_query ( $MyConnection , «SET @p1='» . $MyParam2 . «‘» );
mysqli_multi_query ( $MyConnection , «CALL MyStoreProcedure (@p0,@p1)» ) OR DIE ( mysqli_error ( $MyConnection ));

while ( mysqli_more_results ( $MyConnection ))

if ( $result = mysqli_store_result ( $MyConnection ))

while ( $row = mysqli_fetch_assoc ( $result ))

// i.e.: DBTableFieldName=»userID»
echo «row keyword»>. $row [ «DBTableFieldName» ]. «
» ;
.

>
mysqli_free_result ( $result );
>
mysqli_next_result ( $conn );

/**
* Small function to facilitate call procedure with multiple arguments (supports in/inout/out)
*/
$db = new mysqli ( ‘localhost’ , ‘root’ , ‘password’ , ‘database’ );

$lt_query = callProcedure (
$db ,
«stored_procedure» ,
array(
«in_param1» => «Value1» ,
«in_param2» => «Value2» ,
«inout_param3» => «Value3» ,
«out_param4» => «» ,
«out_param5» => «»
));

function callProcedure ( $po_db , $pv_proc , $pt_args )
if (empty( $pv_proc ) || empty( $pt_args ))
return false ;
>
$lv_call = «CALL ` $pv_proc `(» ;
$lv_select = «SELECT» ;
$lv_log = «» ;
foreach( $pt_args as $lv_key => $lv_value )
$lv_query = «SET @_ $lv_key = ‘ $lv_value ‘» ;
$lv_log .= $lv_query . «;\n» ;
if (! $lv_result = $po_db -> query ( $lv_query ))
/* Write log */
return false ;
>
$lv_call .= » @_ $lv_key ,» ;
$lv_select .= » @_ $lv_key AS $lv_key ,» ;
>
$lv_call = substr ( $lv_call , 0 , — 1 ). «)» ;
$lv_select = substr ( $lv_select , 0 , — 1 );
$lv_log .= $lv_call ;
if ( $lv_result = $po_db -> query ( $lv_call ))
if( $lo_result = $po_db -> query ( $lv_select ))
$lt_result = $lo_result -> fetch_assoc ();
$lo_result -> free ();
return $lt_result ;
>
/* Write log */
return false ;
>
/* Write log */
return false ;
>

/**
* This will return an array like this:
*
* $lt_query = array(
* ‘in_param1’ = ‘Value1’, // Same value as in call
* ‘in_param2’ = ‘Value2’, // Same value as in call
* ‘inout_param3’ = ?, // Value is changed accordingly
* ‘out_param4’ = ?, // Value is changed accordingly
* ‘out_param5’ = ? // Value is changed accordingly
* )
*/
?>

Источник