[Resolved] Simple Output Question

move3rd

Could someone possibly give me an example of how I could output '$balance' from the following statement:

		$sql_order_inv ="SELECT SUM(((price - $fee) * quantity)) AS balance".
					    " FROM store_order_inv WHERE".
						" product='$product' AND".
						" (artist_payroll_status='Pending' OR artist_payroll_status='Queued')".
	                    " AND status='Complete'";


    $result_order_inv = mysql_query($sql_order_inv);

    // Report Db errors
    if (!$result_order_inv) {
    $content = "Could not successfully run query ($sql) from DB: " . mysql_error();
                            }

drawmack

<?php
$row = mysql_fetch_object($result_order_inv);
echo $row->balance;
?>

move3rd

Thanks drawmack

Weedpacket suggested the above sql statment to replace some over complicated code I had written to do the exact same thing (though it wasnt working). I wasnt aware that you can do calculations within the sql statment.

Do you know of any good php/mysql tutorials that go into more dertail than the standard SELECT, UPDATE, DELETE?

I've looked everywhere but I can only find really basic examples. The php manual tell's you about php the mysql manual tells you about mysql. How do I learn how they work together?

drawmack

basically here is the deal:

sql is the langauge you use to ask questions to the database server.

The database server then uses the data that is stored in it to answer those questions for you.

The questions can be complex or simple.

For example:
If I have a pay roll database I may want to ask questions like:

Who makes the least? (select * from employees order by salary limit 0,1)

Who makes at least 10% more then Joe? (select a. from employees as a, employees as b where a.emp_id != b.emp_id AND a.salary >= (b.salary 1.1) AND b.emp_name = 'Joe')

So what you do in php to interact with a database is this.

Step 1) Put your question into clear concise english.
Step 2) Translate that question into sql with the help of the mysql manual
Step 3) Implement the translated question in php by storing it in a variable
Step 4) Use the mysql_* commands in php to execute the query and retrieve the data
Step 5) Write the php to manipulate the data any way you like.

That's really all there is too it.

move3rd

I see the logic in writing the question in plain english first! You lost me @ 'select a.*' in your secound example though. What would the clear concise english be for your secound example?

Originally posted by drawmack

Who makes at least 10% more then Joe? (select a. from employees as a, employees as b where a.emp_id != b.emp_id AND a.salary >= (b.salary 1.1) AND b.emp_name = 'Joe')

drawmack

Table a is the employees table (so it table b but it could be a different table if I had asked a different question) so I'm retrieving all the information stored in the employees table for all the people who make 10% more then Joe. Therefor the question is, as stated, who makes at least 10% more then Joe.

move3rd

Yes, I understand now.

Why do you need to put:

select a.* from employees as a,
employees as b

and not just:

select * from employees as a, employees as b

drawmack

you don't need the a but it makes it easier to read because then you can look at the a.* and in the where clause when you see all those a.... it makes it more apparent that I'm showing the people who make more then Joe and not showing Joe.

move3rd

Thanks again drawmack 😃