CODEWITHSUNDEEP
phpmysql
sher ullah baig
sher ullah baig

Reputation: 23

passing parameter from PHP page into MySQL view

i am struggling to Pass Parameter('User_ID') from PHP page to View (SELLER_INFORMTION ) at MySQL DB. can you please guide me.

Code Below:

<?php
session_start();
userid = $_SESSION['userid'];
include '../Database.class.php';
$dbe_obj = new Database_Executer();
$query =  "SELECT * FROM SELLER_INFORMATION('$userid')";
$data = $dbe_obj-> select_query_executer($query);
?>

CREATE VIEW SELLER_INFORMATION
AS SELECT `PRODUCT`.PRODUCT_NAME,`CATEGORY`.CATEGORY_NAME,`SELLER_PRODUCT`.QUANTITY,
`SELLER_PRODUCT`.UNIT_PRICE,`SELLER_PRODUCT`.CURRENCY 
FROM `PRODUCT`,`SELLER_PRODUCT`,`CATEGORY`,`USERS`
 WHERE SELLER_PRODUCT.Category_ID=CATEGORY.CATEGORY_ID 
    AND SELLER_PRODUCT.Product_ID = PRODUCT.PRODUCT_ID 
    AND SELLER_PRODUCT.User_ID=`USERS`.USER_ID 
    AND SELLER_PRODUCT.User_ID = '$userid'
    GROUP BY
    `PRODUCT`.PRODUCT_NAME,`CATEGORY`.CATEGORY_NAME,`SELLER_PRODUCT`.QUANTITY,
    `SELLER_PRODUCT`.UNIT_PRICE,`SELLER_PRODUCT`.CURRENCY 
    ORDER BY `CATEGORY`.CATEGORY_NAME DESC

Upvotes: 1

Views: 1717

Answers (2)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Another possible option, you can pass parameters to your views in a simple manner by creating a Function to GET your values from Session Variables. See https://www.stackoverflow.com/questions/14511760 for the technique. This is a copy of my create function you may wish to pattern after. DELIMITER //

CREATE FUNCTION fn_getcase_id()
RETURNS MEDIUMINT(11) DETERMINISTIC NO SQL BEGIN

see stackoverflow.com/questions/14511760 and read ALL the info TWICE or MORE. wh 04/13/2017

RETURN @sv_case_id;

END//

DELIMITER ; You will need to create a similar FN (one for each variable).

Upvotes: 0

Barmar
Barmar

Reputation: 780984

Change your view to:

CREATE VIEW SELLER_INFORMATION
AS SELECT DISTINCT
          `PRODUCT`.PRODUCT_NAME,
          `CATEGORY`.CATEGORY_NAME,
          `SELLER_PRODUCT`.QUANTITY,
          `SELLER_PRODUCT`.UNIT_PRICE,
          `SELLER_PRODUCT`.CURRENCY,
          SELLER_PRODUCT.User_ID
FROM `PRODUCT`
JOIN `SELLER_PRODUCT` ON SELLER_PRODUCT.Product_ID = PRODUCT.PRODUCT_ID 
JOIN `CATEGORY` ON SELLER_PRODUCT.Category_ID=CATEGORY.CATEGORY_ID 
JOIN `USERS` ON SELLER_PRODUCT.User_ID=`USERS`.USER_ID 
ORDER BY `CATEGORY`.CATEGORY_NAME DESC

Then use the query:

SELECT * FROM SELLER_INFORMATION WHERE User_ID = '$userid'

You can't have a PHP variable inside a view definition. You need to return the column in the SELECT list of the view, so you can compare with it in a WHERE clause.

Upvotes: 3

Related Questions