The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
PHP5 - Obtain data from SQL DB until condition has been met
Discuss Obtain data from SQL DB until condition has been met in the PHP Development forum on Dev Shed.
Obtain data from SQL DB until condition has been met PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
March 2nd, 2013, 05:01 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
|
PHP5 - Obtain data from SQL DB until condition has been met
Hi,
I have a SQL Database and I wish to obtain records until the customer's quantity requested has been met. Here is what I mean.
Request Table
Code:
Product Quantity Price
-----------------------
Apple 10.0 5.00
here is the SQL query to obtain matches:
Code:
select c.FName,
p.ProductName,
s.Description,
s.Quantity,
s.Price
FROM requests r
inner join sellers s on r.ProductID = s.ProductID
inner join products p on p.ProductID=s.ProductID
inner join customers c on c.ID=s.C_ID
where r.C_ID = 3 AND r.matchType='Price'
ORDER BY s.Price ASC
And here is the result:
Code:
FName | ProductName | Description | Quantity | Price
--------------------------------------------------------------------------=
compny1 Apple royal apples fm appleco. 5.0 5.00
daz Apple sweet apples 6.0 5.50
company2 Apple Apples yum 8.0 9.00
I want to display the full requested 10KG quantity by selecting the rows and updating the database i.e. the output should be:
apples @5kg from compny1 = 5.00
apples @5kg from daz = 5.50
total = 10.50
the Database should then show 'Daz' quantity being 1.0 KG left. However, I keep getting suck in doing this. I have tried to do the following:
Code:
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($rows1['Quantity']==$quantityRequested){ //If the first row = 10KG output only this row.
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
}else{
//stuck here check the next rows and see there is 6KG's .. We need 10KG Requested - 5KG from daz(row1)
// -Remaining amount left(i.e.5KG)
//hence, print the output specified above and UPDATE Database where quantity has been reduced by X amount.
}
I do not know how to do the next step i.e. check the next rows and keep added until the quantity is met. Can anyone please help me of what I need to do?
|
March 2nd, 2013, 05:10 PM
|
|
|
|
I don't think I understand your question. In the else block, you want to update the database? How do you decide what record to update? How much to you add to the quantity field each time?
__________________
There are 10 kinds of people in the world. Those that understand binary and those that don't.
|
March 2nd, 2013, 05:24 PM
|
|
Registered User
|
|
Join Date: Jul 2010
Location: Manchester
Posts: 10
Time spent in forums: 4 h 13 m
Reputation Power: 0
|
|
If I've understood you right...you want to update the quantity remaining with the original quantity minus the requested amount...
PHP Code:
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantityRequested > 0){
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
mysql_query("UPDATE products SET quantity = quantity - ".$quantityRequested) or die (mysql_error());
$quantityRequested -= $rows1['Quantity'];
}
}
Assuming the quantityRequested is set somewhere maybe from request table... just tell it to only output rows and update database while quantityRequested is greater than 0 and decrement the value of quantityRequested by the Quantity required value of the specific row before it moves on to the next row. If it reaches 0 it won't go any further meaning the one at 6 would end up at 1Kg left.
Hope it helps
|
March 2nd, 2013, 05:43 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by gw1500se I don't think I understand your question. In the else block, you want to update the database? How do you decide what record to update? How much to you add to the quantity field each time? |
Hey, Okay let me try and explain... This is what the customer has requested (i.e. my request table)
Code:
requestid[PK] | cid[FK] | productid[FK] | Quantity | Price
---------------------------------------------------------------------------
7 3 1 10.0 7.00
here is the sellers table
Code:
FName | ProductName | Description | Quantity | Price
--------------------------------------------------------------------------
compny1 Apple royal apples fm appleco. 5.0 5.00
daz Apple sweet apples 6.0 5.50
company2 Apple Apples yum 8.0 9.00
Ok, So what the problem is. I need to 'Make up' the 10KG requested demand.
Now the 'Cheapest' way to do this is by sorting the available matches by Asc Price which I have.
No we can see the First row shows 'compny1' selling 5KG for £5.00... So, we need 10KG hence we display row 1..
Next, as we have not met the 10KG demand yet (still need another 5KG) we look at the second row. We see 'daz' is selling 6KG. but we only need 5more KG's to make the 10KG demand.
Hence, what I wish to do is... UPDATE the table so that the first record is copied to another table and the second row is updated to 1.0kg remaining(6KG-5KG). And hence, display to the customer:
Your 10KG can be purchased by
compny1 5KG@5.00
daz 5KG@5.50
------
Total £10.50
So, that is my problem. I dont know how to keep checking the rows until the quantity requested is met. I guess I need some kind of 'counter' that keeps track of how much quantity has been added added 'So far' and then check how much more is needed from the next row.
Does this make sense? If you wish I could clarify more for you ??
Thanks
|
March 2nd, 2013, 05:50 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by SWH Admin If I've understood you right...you want to update the quantity remaining with the original quantity minus the requested amount...
PHP Code:
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantityRequested > 0){
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
mysql_query("UPDATE products SET quantity = quantity - ".$quantityRequested) or die (mysql_error());
$quantityRequested -= $rows1['Quantity'];
}
}
Assuming the quantityRequested is set somewhere maybe from request table... just tell it to only output rows and update database while quantityRequested is greater than 0 and decrement the value of quantityRequested by the Quantity required value of the specific row before it moves on to the next row. If it reaches 0 it won't go any further meaning the one at 6 would end up at 1Kg left.
Hope it helps |
Hi, Thanks for your reply. I think you are along the right lines. I would appreciate if you could read my response I gave on my previous post as this may help calrify and understand what I wish to achieve. If you could please read my reply to 'gw1500se' it might be helpful. Once again thanks 
|
March 2nd, 2013, 06:03 PM
|
|
Registered User
|
|
Join Date: Jul 2010
Location: Manchester
Posts: 10
Time spent in forums: 4 h 13 m
Reputation Power: 0
|
|
Ok, I've added a bit more based on your last post...
PHP Code:
$quantityRequested = 10; // The 10Kgs requested which comes from your requests table
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantityRequested > 0){ // This says as long as the $quantityRequested value is still above 0 then check the rows
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
mysql_query("INSERT INTO orders (company,product,quantity,price) VALUES (
'".$rows1['FName']."',
'".$rows1['NameProduct']."',
'".$rows1['Quantity']."',
'".$rows1['Price']."'
)") or die (mysql_error()); // inserts the data into a new table
mysql_query("UPDATE products SET quantity = quantity - ".$rows1['Quantity']) or die (mysql_error()); // Updates the products table by reducing the available quantity
$quantityRequested -= $rows1['Quantity']; // decrements the $quantityRequested value
}
}
I've added comments too which explain what each part does.
Hope it helps
**By the way, I'm assuming the table is called orders where it inserts the data to 
|
March 2nd, 2013, 06:10 PM
|
|
|
OK, so you don't really mean you want to update the database. You want to keep reading sorted rows until the quantity is met. You need to keep a counter initialized before the loop. Use that counter for your loop not the row data. Untested:
PHP Code:
$quantity=0;
$price=0;
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantity>=$quantityRequested){
echo "$price<br />";
break;
}else{
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
$quantity+=$rows1['Quantity'];
$price+=$rows1['Price'];
}
}
You may need to play with it a bit to get exactly what you need but this should get you close.
Note the use of [ PHP ] tags for code. See the sticky at the top of this forum.
Last edited by gw1500se : March 2nd, 2013 at 06:13 PM.
|
March 2nd, 2013, 06:59 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by SWH Admin Ok, I've added a bit more based on your last post...
**By the way, I'm assuming the table is called orders where it inserts the data to |
Hey thanks so much I am also from the UK and as it is late now I shall indeed give this a go tomorrow and it looks really positive from what I can read.
I have 4 tables at the moment.
Customers (CID, name, address, email, password)
Products (PID, Name)
Sellers (SID,CID,PID,Descp,Price,Quantity)
Requests (RequestID,CID,PID,QuanitityReqested,PriceRequested)
CID,PID,SID,RequestID are all primary keys in there own table and forign keys in the other tables.
So, I have a order table but without any order yet. How would you suggest I suggest I structure this table based on my previous tables?
Thanks
|
March 2nd, 2013, 07:28 PM
|
|
|
|
Not being a DBA my suggestions would be as good as yours. For that I suggest you formulate a separate question and put it on the MySQL forum.
Also, I forgot to mention that you should change your code and stop using the deprecated MySQL extensions. You should be using PDO.
|
March 2nd, 2013, 09:31 PM
|
 |
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
Hi,
there are several big issues with the above suggestions.
Like gw1500se already said, the old MySQL extension is hopelessly outdated. To be exact, it's obsolete since almost 10 years and will officially be deprecated in the next PHP version 5.5. This means every call to mysql_connect, mysql_query etc. will generate an E_DEPRECATED error. If you got any chance to switch to the "new" extensions, do it.
A big issue of the old extension is that people constantly forget to escape variables before inserting them into their query strings, which makes the code vulnerable to SQL injections. This also happened above. Check the link in my signature to avoid typical mistakes like that.
If this is an actual website with real money and real products involved, you also cannot send queries in that naive way. What if two concurrent queries each buy all apples from a certain seller? What if the ordering process crashes between inserting the order and reducing the available amount? etc.
As you can see, it's not as easy as doing an INSERT and then an UPDATE. This might work on some private homepage, but not with critical data. You'll need a transaction.
Given all those problems, I strongly suggest using standard ERP software or hiring an actual programmer/DBA to implement the website. Yeah, that will cost you some dollars. But at least you can be pretty sure that your website won't get hacked by some script kiddies stealing your customers' credit card data ("Oops, I didn't know about SQL injection."). And your database won't break down if you happen to get two orders at the same time.
|
March 3rd, 2013, 11:11 AM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by Jacques1
If this is an actual website with real money and real products involved, you also cannot send queries in that naive way. What if two concurrent queries each buy all apples from a certain seller? What if the ordering process crashes between inserting the order and reducing the available amount? etc.
As you can see, it's not as easy as doing an INSERT and then an UPDATE. This might work on some private homepage, but not with critical data. You'll need a transaction.
Given all those problems, I strongly suggest using standard ERP software or hiring an actual programmer/DBA to implement the website. |
Hey, Thanks very much for your response. I throughly appreciate and understand what you are saying. It is not as secure at all with the sql injections and all. At the moment it will not be a 'Real' application. I am just using it as a localhost and dont plan on making it live at all. It is however my project I am working on and since I have started a fair amount of it I dont have that much time to change everything i.e. all the code again and learn the new syntax and way its coded. However, I do fully understand what you mean and thanks for that feedback.
|
March 3rd, 2013, 11:30 AM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by gw1500se OK, so you don't really mean you want to update the database. You want to keep reading sorted rows until the quantity is met. You need to keep a counter initialized before the loop. Use that counter for your loop not the row data. Untested:
PHP Code:
$quantity=0;
$price=0;
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantity>=$quantityRequested){
echo "$price<br />";
break;
}else{
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
$quantity+=$rows1['Quantity'];
$price+=$rows1['Price'];
}
}
You may need to play with it a bit to get exactly what you need but this should get you close.
Note the use of [ PHP ] tags for code. See the sticky at the top of this forum. |
Hi,
Yes what I wish to do is look at the first row see if the quantity can be satisfied. If it can then we dont need to look at any other rows just display to the user you can meet ur quantity by selecting just the first row seller.
However, if the first row seller is not meeting the quantity requested. Then, add the first row's (sellers) quantity work out how much you still need to meet the requested quantity look at second row quantity + first row. Is this = to the quantity required? if it is then we display the total and use a orders table to show the new reflected quantities i.e. in this case seller 'compny1' quantity will be 0 and 'daz' will 1.0 ...
I am really stuck on how I can do this. Spend a long amount of time but just dont know what to do. I know the logic of what needs to be done having problems of how I can code it.
|
March 3rd, 2013, 12:01 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 21
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by SWH Admin Ok, I've added a bit more based on your last post...
PHP Code:
$quantityRequested = 10; // The 10Kgs requested which comes from your requests table
while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
if($quantityRequested > 0){ // This says as long as the $quantityRequested value is still above 0 then check the rows
echo $rows1['FName'];
echo $rows1['NameProduct'];
echo $rows1['Quantity'];
echo $rows1['Price'];
mysql_query("INSERT INTO orders (company,product,quantity,price) VALUES (
'".$rows1['FName']."',
'".$rows1['NameProduct']."',
'".$rows1['Quantity']."',
'".$rows1['Price']."'
)") or die (mysql_error()); // inserts the data into a new table
mysql_query("UPDATE products SET quantity = quantity - ".$rows1['Quantity']) or die (mysql_error()); // Updates the products table by reducing the available quantity
$quantityRequested -= $rows1['Quantity']; // decrements the $quantityRequested value
}
}
I've added comments too which explain what each part does.
Hope it helps
**By the way, I'm assuming the table is called orders where it inserts the data to |
Hey, thanks for helping me.
Ok, so this is the problem.
I have my 4 tables. Customers, Products, Requests and sellers. Each on has the fields I mentioned in my previous post.
No the customer comes on the site and fills in a form to show what they wish to purchase . Hence, fill in the product, quantity, price.
Now, a seller comes along and says okay I have X product to sell, for X quantity at X price.
Another seller may also be selling the same product for a different price and quantity.
Now, In order to meet the customer's quantity I am using the query mentioned to get the suitable 'Available' matches sorted by price.
so say..
Request table shows
Code:
customer3 apples 10KG for £7.00
Now we have 3 sellers potentially who can match the customer's request of 10KG. (obtained using the SQL stated)
Code:
seller1 5kg £5.00
seller2 6Kg £5.50
seller3 15kg £25.00
so, I am running the while loop to access each record:
PHP Code:
while ($rows1 = mysql_fetch_assoc($queryPrice2)){
//so this checks say if the sorted query the first row matches the requested
//then display this result. Hence, the demand has been met the cheapest way.
if($rows1['Quantity']==$quantityRequested){
echo $rows1['ProductName'];
echo $rows1['FName'];
echo $rows1['Quantity'];
echo $rows1['Price'];
}else{
// What needs to be done here is.
// ADD the first row's quantity. Check how much more is needed. i.e.
// 5.0 quantity from first row received... need another 5.0
// The next row shows the quantity as 6.0kg
// we need 5.0kg fm that row. + 5.0 from first row. hence quantity has been met
//echo row's 1 and 2 in a table
//table headings here
echo "<tr>";
echo "<td>".$rows1['FName']."</td>";
echo "<td>".$rows1['ProductName']."</td>";
echo "<td>".$rows1['Quantity']."</td>";
echo "<td>".$rows1['Price']."</td>";
echo "<td>"."<input type= \"button\" value= \"COMMIT\">";
echo "</tr>";
}
}
hence, the table would show
Code:
FName | Product | Quantity | Price
---------------------------------------------------------------
compny1 apple 5.0 5.00
daz apple 5.0 6.50
total = £11.50
And then once the customer 'COMMITS' to the deal, the seller's table is updated and the new rows are added to the order's table.
Any help at all would be so so helpful as I really am stuck of how I can do this.
I hope this helps answers any questions
|
March 3rd, 2013, 01:03 PM
|
|
|
|
First, as Jacques1 and I advised rewrite your code to use PDO and prepared statements. I won't advise you on code using obsolete extensions leaving yourself open to injections.
Second, I pretty much gave you the solution. The only change is to check the quantity being added to it does not exceed the requested and adjust the price accordingly. You will need to add hidden fields to your form to tell PHP the quantity taken from each vendor record so the database can be updated accordingly. Keep in mind that PHP is stateless so all the needed information must be provided on each invocation of the script.
|
March 3rd, 2013, 04:01 PM
|
|
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
Note that he's crossposted his thread around the whole f*cking internet, so the question probably has been answered 10 times already.
Don't waste your time repeating other peoples' replies.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
