Run Multiple Sql Statements in a php script

Question

I have a series of SQL commands that result in the update of a table that I am currently displaying using a php page.

I would like to run a bunch of sql statements each time the page is loaded as there is a date field that is updated from time to time. Could somebody advise on the best way to do this?

Do I include all the statements in the php code?, if so how.
Do I create some kind of SQL function that I pass the date values to?, if so how.

Here is my current page code:

$sql = 'SELECT order_item_name, product_quantity, 
               order_number, category_name
        FROM pvz8y_shopping_list';

I would like to include the following:

DROP TABLE pvz8y_shopping_list;

CREATE TABLE pvz8y_shopping_list (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
isle_number int(11),
category_name char(180),
virtuemart_product_id int(11),
virtuemart_category_id int(11),
order_item_name char(255),
product_quantity int(11),
order_number char(64),
PRIMARY KEY (id)
);

INSERT INTO pvz8y_shopping_list (virtuemart_product_id, order_item_name, product_quantity, order_number)
SELECT virtuemart_product_id, order_item_name, product_quantity,order_number from pvz8y_virtuemart_order_items
INNER JOIN pvz8y_virtuemart_orders
ON pvz8y_virtuemart_order_items.virtuemart_order_id = pvz8y_virtuemart_orders.virtuemart_order_id
WHERE pvz8y_virtuemart_orders.created_on **>= '2014-07-19 00:00:00' AND
pvz8y_virtuemart_orders.created_on <= '2014-07-20 00:00:00';**

UPDATE pvz8y_shopping_list 
INNER JOIN pvz8y_virtuemart_product_categories ON pvz8y_shopping_list.virtuemart_product_id = pvz8y_virtuemart_product_categories.virtuemart_product_id
SET pvz8y_shopping_list.virtuemart_category_id = pvz8y_virtuemart_product_categories.virtuemart_category_id;

UPDATE pvz8y_shopping_list 
INNER JOIN pvz8y_virtuemart_categories_en_gb ON pvz8y_shopping_list.virtuemart_category_id = pvz8y_virtuemart_categories_en_gb.virtuemart_category_id
SET pvz8y_shopping_list.category_name = pvz8y_virtuemart_categories_en_gb.category_name;

as you can see use the sql statements to create the shopping_list table and currently only show the results but I would like to be able to not have to create the table in the backend each time the date (highlighted in bold changes).

You create a function or stored procedure to do all this, then just call that from your PHP code. http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html — ThreeLLMsInATrenchcoat
– ThreeLLMsInATrenchcoat, Commented Aug 12, 2014 at 19:23
Why do you need to recreate the table each time? If you want to clear out a table, use TRUNCATE. — Barmar
– Barmar, Commented Aug 12, 2014 at 19:24
ALso, if the table is only needed temporarily, consider using CREATE TEMPORARY TABLE. — Barmar
– Barmar, Commented Aug 12, 2014 at 19:25
Hi I am trying to use the stored procedure route but it seems the version of myphpadmin 2.8 does not allow me to define stored procedures. — Jaspal Singh Rathour
– Jaspal Singh Rathour, Commented Aug 12, 2014 at 23:36

Muthukumar Anbalagan · Accepted Answer · 2014-08-14 21:38:01Z

1

Well I don't know why do you want to drop and create the table? If all you want to clear the table then just use

TRUNCATE TABLE pvz8y_shopping_list

MySQL has stored procedure you may use that to do your job, or PHP also can to this on time basis but script has to be added in CRONTAB.

EDITED

Defining Connection

$hostname_appscon = "localhost";
$database_appscon = "DBNAME";
$username_appscon = "DBUSER";
$password_appscon = "DBPASS";
$appscon = mysql_pconnect($hostname_appscon, $username_appscon, $password_appscon) or  trigger_error(mysql_error(),E_USER_ERROR);

Now call using above defined connection you may call queries upon your requirement.

mysql_query("SQL QUERY1", $appscon);
mysql_query("SQL QUERY2", $appscon);
mysql_query("SQL QUERY3", $appscon);

This way you can call as many query you want

edited Aug 14, 2014 at 21:38

answered Aug 12, 2014 at 19:33

Muthukumar Anbalagan

1,17812 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaspal Singh Rathour Over a year ago

Hi Truncate will definately help -thank you. However, my main objective is to run multiple sql statements in a php script so that I can pass the date value to the sql statements.

Collectives™ on Stack Overflow

Run Multiple Sql Statements in a php script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related