how do run multi select statement in loop based on loop counter with php and mysql

Question

0.00/5 (No votes)

See more:

i work with PHP And MySQL

I have two table :

Products (product_id,product_name)

Prices (product_id,price_id,price,date)

Any product have unlimited price

Type of “date” field in table “Prices” is datetime (like 2014-02-23 18:41:22) – when I saving any record current datetime saved in this field (last record can be determine with this field) .

I want a loop for all products that can do below actions for any product :

1)get Last date from prices table that related with current product and set it to $last_Date

for example if current product_id is 1 get date of last record in price table that product_id=1 and date=$last_Date

2)get Last price from prices table that related with current product

for example if current product id is 1 get price of last record in price table that product_id=1 and date=$last_Date

3)get different between field price from last 2 record

for example if current product id is 1 get different between field price of last record 2 record in price table that product_id=1 and date=$last_Date

4)get min and max *price* field value that product_id=1 and date=$last_Date

Posted 15-Feb-14 18:51pm

Esmaeil Ebrahimi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2014-02-15T19:11:00

This is your homework and you should attempt it yourself.
what-have-you-tried[^]
If you meet with any problems with your code, always ask google first, then visit CP, show your code and ask questions related to specific issue.
You may refer to php_mysql[^] for help.
+++++++++++++++++++++++++++++++
Ok, since you insisted, I will try to shed some light on question 3 which appeared to be the hardest:
First you got to find the prices of the last datetime and second last datetime respectively. To find the last datetime is easy, just use MAX(date). How about the second last datetime, again use MAX(date) but this time excludes the last datetime from your SELECT pool.
The rest is just arithmetic.
Study my sql statement below and figure out the logic yourself. I have used local variables to make the code cleaner and reusable.

SQL

SET @product_id = 1;
SET @last_date = (SELECT MAX(date) FROM product WHERE product_id = @product_id);
SET @second_last_date = (SELECT MAX(date) FROM product WHERE product_id = @product_id
AND date < @last_date);
SELECT
(
  (SELECT price FROM product WHERE date = @last_date AND product_id = @product_id)
  -
  (SELECT price FROM product WHERE date = @second_last_date AND product_id = @product_id)
) AS PRICE_DIFFERENCE

If you can figure out my solution to question 3, you have no problem solving questions 1 and 2. My code will simply find the price difference between last date and second last date so it will result in negative value if last price is lower than the second last price. Good luck.