SELECT * <fields> and then insert 1 into a 1 into BOOLEAN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
j.smith1981
Forum Newbie
Posts: 7
Joined: Mon Apr 06, 2009 3:48 pm

SELECT * <fields> and then insert 1 into a 1 into BOOLEAN

Post by j.smith1981 »

I am not sure if I was exactly correct with the above.

But I have a very pressing question, I think I have solved the theory that would go into this problem/task.

I work on an ecommerce system, and it is paramount that we get our orders right (asin not duplicating orders!).

I just wanted to get some idea of how to approach this in a practical form.

Enough waffle here we go:

A user clicks on the link that generates the report.

We have the following showing up:

productid

cost_price

amount

productcode

product

fmdate (date order was placed by the customer)

total (cost_price x by amount (amount refers to quantity!)

What we have is a field in the database called 'status' this is set to P (stands for processed) (if they have placed an order successfully).

What I was wanting to know was how would I set the 'status' field to C (stands for complete) when a user clicks on a link that says complete all orders listed above, for all results found in a list?

This is the code I have so far and this is working fine:

Code: Select all

 
<html>
<head>
<title>Shopping Cartridge World : X Cart : Purchasing Report</title>
</head>
 
<body>
 
<h3>Shopping Cartridge World : X Cart : Purchasing Report</h3>
 
<?php
 
$host = 'localhost'; // Sets the variable host as a literal no parsing for db connections
$user = 'xcart'; // username
$passwd = 'xcart0r'; // db password (abbreviated to unix password command)
$dbname = 'xcart';
 
$conn = mysql_connect($host, $user, $passwd) or die();
 
mysql_select_db($dbname);
 
// Working SQL just doesnt times by quantity and then final cost price as a whole!
$db_query = "SELECT A1.productid, SUM(A2.price) AS cost_price, SUM(A1.amount) AS amount, A1.productcode, A1.product, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS fmtdate, SUM(A2.price * A1.amount) AS total
FROM xcart_order_details A1 
LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid 
LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid 
WHERE A2.membershipid = '2' AND A3.status = 'P'
GROUP BY A1.productid
ORDER BY A1.orderid";
 
$result = mysql_query($db_query);
 
 
echo '<table border=0 width="80%" cellpadding="4">';
 
// Header columns
echo '<tr>';
echo '<td><strong>Date</strong></td>' . '<td><strong></strong></td>' . '<td><strong>Productcode</strong></td>' . '<td><strong>Product</strong></td>' . '<td><strong>Cost Price</strong></td>' . '<td><strong>Quantity</strong></td>' . '<td><strong>Total</strong></td>';
echo '</tr>';
 
// SQL results
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<tr>';
echo '<td>' . "{$row['fmtdate']}" . '</td>' . '<td>' . "{$row['orderid']}" . '</td>' . '<td>' . "{$row['productcode']}" . '</td>' . '<td>' . "{$row['product']}" . '</td>' . '<td>' . "{$row['cost_price']}" . '</td>' . '<td>' . "{$row['amount']}" . '</td>' . '<td>' . "{$row['total']}" . '</td>';
echo '</tr>';
}
echo '</table>';
?>
<table>
<tr>
<td colspan=2>&nbsp;</td>
</tr>
<tr>
 
<td><a href="#" onClick="history.go(-1)">Back</a></td>
<td><a href="http://shopping.cartridgeworld.co.uk/purchasing/purchase_Export.php">Export CSV</a></td>
</tr>
</table>
</body>
</html>
 
I am struggling to get my head around this and would appreciate any advice.

Thanks in advance,
Jeremy.

PS Just had a thought, wouldnt it be easier using a text button? Like a hidden field with the value of C and then getting that to fill in all the results at present? It surely would be just a matter of writing that code.

But for the life of me I cant remember how to do this for all fields, can someone help still?
izzy4505
Forum Newbie
Posts: 16
Joined: Tue Aug 11, 2009 1:38 pm

Re: SELECT * <fields> and then insert 1 into a 1 into BOOLEAN

Post by izzy4505 »

Sorry if my answer isn't correct, I was having a hard time understanding your question.

I understand that your user (not the customer) is looking at a table of orders, and while they can process them one by one right now, you want to add an option for them to process all that are visible?

Why not include check boxes along side each row in the table, similar to G-Mail and other web applications. If instead of checking a few boxes they wish to process all, you could use some javascript to set the value of those boxes to checked, and then submit the form immediately. Name your checkboxes something like order_13521 and order_1234987. When the form is submitted, do a for each loop, stepping through $_POST as $key -> $value and if the key begins with "order_", then take the last part, and you have your order id. Have your code then UPDATE the row in the database, and you're all set.

Of course you need to escape all input and make sure only authorized users can do this. Otherwise you'll have folks screwing with any order they want.
j.smith1981
Forum Newbie
Posts: 7
Joined: Mon Apr 06, 2009 3:48 pm

Re: SELECT * <fields> and then insert 1 into a 1 into BOOLEAN

Post by j.smith1981 »

I am so sorry.

I should be so much more concise with my question.

But I had a long hard think about it, and its now working (apologies for my lack of grammar lol).

But I put in a form, with a hidden text field with a value of 'C' (in the system I am trying to sort out, C means complete), asin a report I run off picks out orders that are with the status P (for processed).

Then I just input that into all orders in the database where the existing value is P and bingo.

Thanks for your reply though much appreciated!

Take care,
Jez.

PS I do have another question, any feedback would be wonderfully appreciated, just when ya think my works all done I get a whole load more, for the life of me I cant remember how to do this but know its doable, so I said to my line manager it is possible but would take some going over.

Basically, if I say that a user x has ordered 5 items all at random quantity's yea?

Then user b 10 minutes later orders 2 items, both at a single quantity of 1 still with me?

How would I list all orders, orderby customer 'username' and then by productname?

Basically for some additional information, it would list the username.

Then have a list of products at the quantites they ordered.

Below thats another users order with their items.

The process repeats for all orders placed by that user in that day.

If I could get some help, or refer me to another site to actually do this then that would be excellent.

Again many thanks,
Jez.
Post Reply