Page 1 of 1
Calcualting a value from a lookup- Please help
Posted: Fri Mar 25, 2005 11:50 am
by shab620
Hye I'm trying to calcualte a total price by taking a lookup from a table. I have already perfomed a previous lookup which worked fined. this previous lookup took the item description and placed that into a variable. i then inserted that fine but when i try to add an additional query i have parse errors. I have now removed my code because i think i have totally gone off track.
I was wondering if somebody can help me on how to do multiple queries in one script????
The original code is as follows
Code: Select all
<?php
$item_id=$_POST['orderitem1'];
$comments=$_POST['comments'];
$quantity=$_POST['quantity'];
$user_name=$_POST['username'];
$hostname="localhost";
$mysql_login="root";
$mysql_password="*********";
$database="coachhouse";
$connect = mysql_connect("$hostname", "$mysql_login" , "$mysql_password");
$table_name = emp_order;
mysql_select_db($database);
$result = mysql_query ("SELECT item_Description from item where item_id = '$item_id'",$connect);
//$row = mysql_fetch_*($result);
//or die("Invalid query: " . mysql_error());
while ($row = mysql_fetch_row($result))
{
foreach ($row as $attribute);
}
$query = ("INSERT INTO emp_order VALUES ('','$attribute','$quantity','$comments','$user_name','')");
//mysql_query($query);
PRINT '<br><font size ="4" colour="blue">';
IF (mysql_query($query))
PRINT "<center>Order Has Been Placed</Center></font><BR><BR>";
PRINT $attribute;
mysql_close($connect);
?>
Where and how could i modify the code to insert a second lookup query so that i may insert the price of the item for each record into the existing insert statement. the last field on the insert represnts where the price will go but that is currently blank.
To get the total price i am wanting to calcualte the quantity by the lookup price returned.
please help.
PHENOM| PLEASE REVIEW HOW TO POST PHP (notice on posting page)
Posted: Fri Mar 25, 2005 12:49 pm
by pthomas
Well, basically you just need to:
1) connect to the database
2) create a query
3) query the DB
and then repeat steps 2 and 3 for as many queries or different queries that you wish to do. Don't try to do multiple queries in a single query statement, and only connect up to the DB once. Try something like this:
Code: Select all
<?php
####################
# Set POST vars
####################
$item_id=$_POST['orderitem1'];
$comments=$_POST['comments'];
$quantity=$_POST['quantity'];
$user_name=$_POST['username'];
####################
# Connect to Database
####################
$hostname="localhost";
$mysql_login="root";
$mysql_password="*********";
$database="coachhouse";
mysql_connect($hostname, $mysql_login , $mysql_password) or die ("Unable to select database");
mysql_select_db("emp_order") or die ("Unable to select database");
## We are now connected to the DB, make your queries!
$result = mysql_query ("SELECT item_Description from item where item_id = '$item_id'");
//$row = mysql_fetch_*($result);
//or die("Invalid query: " . mysql_error());
# we are pulling out "item_Description" only from DB... put them into an
# array
while ($row = mysql_fetch_row($result)) {
$item_desc_array[] = $row['item_Description'];
# at this point you will have an array containing each
# "item_Description" in the $item_desc_array
# I'm not sure what you are wanting to do here, but you could make
# multiple DB queries for each value in the $item_desc_array with
foreach ($item_desc_array as $one_item) {
$query = ("INSERT INTO emp_order VALUES '','$one_item','$quantity','$comments','$user_name','')");
$result = mysql_query($query);
#maybe check result to see if it went ok
if ($result){
echo "The query was successful";
}
else {
echo "Error: ".mysql_error();
}
}
####....
#PRINT '<br><font size ="4" colour="blue">';
#IF (mysql_query($query)) {
# PRINT "<center>Order Has Been Placed</Center></font><BR><BR>";
#}
#PRINT $attribute;
### connection to DB will close automatically here
###mysql_close($connect); <- not really needed
?>
helps? hurts? Need clarification>
Paul
Posted: Fri Mar 25, 2005 1:25 pm
by shab620
hye Pthomas, thanks for that.
I understand that part
I now want to perfrom an additional query on the same table but this time want to extract the price which is also a column just like item_description.
I know i can use the same query e.g
Code: Select all
SELECT price from item where item_id = '$item_id'
but my problem is how can i incorporate this into the code, when ever i try to start a new query i keep getting parse errors.
I then want to multiply the price returned by the quantity from the $_post command.
I then want to insert this value into the insert statement as the last column, currently the insert statement has a empty string there.
I dont supoose you know how i would incorporate this into the query???
Thanks for the help so far
Posted: Fri Mar 25, 2005 1:50 pm
by pthomas
What are the parse errors that you are getting? PHP errors are actually very descriptive to what is the cause of the problem. If it says ....line 17 look on that line and also a few lines above it. Anytime you have a parsse error, you have a mistake in your code.
So let me get this straight. You want to
1) set quantity from $_POST
2) query the DB for the price
3) multiply price x quantity
4) put the result from #2 into the DB in appropriate spot (total)
I'm going to assume for this example we have a table that looks something like this:
ROW -> item_id......item_description.....quantity.....price.....total
Just take everything one step at a time and you'll be fine.
I would do it something like this:
Code: Select all
//First, the quantity and price
$quantity = $_POST['quantity'];
$query = "SELECT quantity, price FROM item WHERE item_id = '$item_id'";
$result = mysql_query($query) or die ("Query failed";
while ($row = mysql_fetch_row($result)) {
//$quantity = $row['quantity']; <- use to get quantity from DB query
$price = $row['price'];
}
//Now calculate the total for this item
$total = $quantity * $price;
//Now insert it into the DB
$query = "UPDATE item SET total = '$total' WHERE item_id = '$item_id'";
$result = mysql_query($query) or die ("Update failed");
if ($result)
echo "Success\n";
else
echo "Error".mysql_error();
Paul
Posted: Fri Mar 25, 2005 2:02 pm
by shab620
Hello again Pthomas
That makes a little more sense
Just clarify what i want to do
1) I want to query a table called items where orignally i was taking the item description and then inserting that into the emp_order table.
2) i then wanted to do a similar query for the price and query the item table which also contained the price. From here i want to do what you previously said and do a quntity*price. this result is then inserted as well into the emp_order table
the current insert statement is :
Code: Select all
$query = ("e;INSERT INTO emp_order VALUES ('','$attribute','$quantity','$comments','$user_name','','')"e;);
the last filed in this is currently an empty field, what i want to do is take the result from the multiply sum and place it here
e.g
$totalprice = $qunatity* price
I want to then insert this into the Insert statement at the end
The emp_order has a structure as follows:
Item_id: set to auto increment
Item_Description
Quantity
Comments
User_name
Price
Complete: this is for an additional function-ignore at this stage
Hope you can help
Shab
Posted: Fri Mar 25, 2005 2:11 pm
by pthomas
I think your train-of-thought is correct. It sounds like you just need to convert your thought into code...
Your query statement should look like this, without the two '(' ')'s on either side
Code: Select all
$query = "INSERT INTO emp_order VALUES ('', '$attribute, '$quantity', '$comments', '$user_name', '$total', '')";
Paul
Posted: Fri Mar 25, 2005 2:29 pm
by shab620
Hye i'm getting a better picture now
I tried what you said and i have got a parse error
the parse error is:
Code: Select all
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\web\processorder3.php on line 19
Order Has Been Placed
the code is as follows
Code: Select all
<?php
$item_id=$_POST['orderitem3'];
$comments=$_POST['comments'];
$quantity=$_POST['quantity'];
$user_name=$_POST['username'];
$hostname="localhost";
$mysql_login="root";
$mysql_password="******";
$database="coachhouse";
$connect = mysql_connect("$hostname", "$mysql_login" , "$mysql_password");
$table_name = emp_order;
mysql_select_db($database);
$result = mysql_query ("SELECT item_Description, price from item where item_id = '$item_id'",$connect);
//$row = mysql_fetch_*($result);
//or die("Invalid query: " . mysql_error());
while ($row = mysql_fetch_row($result))
{
$item_description = $row ['item_description'];
$price = $row['price'];
//foreach ($row as $attribute);
}
$query = ("INSERT INTO emp_order VALUES ('','$item_description','$quantity','$comments','$user_name','','')");
//mysql_query($query);
PRINT '<br><font size ="4" colour="blue">';
IF (mysql_query($query))
PRINT "<center>Order Has Been Placed</Center></font><BR><BR>";
PRINT $attribute;
mysql_close($connect);
?>
I'm getting a parse error on the 19th line which relates to the 'while ($row = mysql_fetch_row($result))'
Do you know where i'm going wrong?
Sorry to bother you so much
feyd | Please review how to post code using Code: Select all
tags. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Fri Mar 25, 2005 2:49 pm
by pthomas
You have to be fairly cautious when reading into the errors. This
Code: Select all
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\web\processorder3.php on line 19
Says that the item you gave mysql_fetch_row() wasn't the right type. In other words, your $result variable doesn't hold what you think it should. So you either have an error in your Query statement or are using the mysql_query function wrong. Whenever you get an error and it says look at this line, all it means is that that line doesn't work. Often that line is fine, the problem is in a few previous lines. In this case, your problem making line is #14
Change:
Code: Select all
$result = mysql_query ("SELECT item_Description, price from item where item_id = '$item_id'",$connect);
TO:
Code: Select all
$result = mysql_query ("SELECT item_Description, price from item where item_id = '$item_id'",$connect) or die ("There was an error in the query");
Now if the query doesn't go right, you'll see the message "There was an erro...";
Also, you should connect up to the DB in the beginning of your code and not everytime you make a query like I showed in my first post to you, lines 13-23. And then your query should look just as simple as line 24 or look like this too:
Code: Select all
$query = "SELECT item_Description from item where item_id = '$item_id'";
$result = mysql_query ($query) or die ("There was an error in the query. Query: $query");
//shows your query string if failed
-or-
$result = mysql_query ($query) or die ( ("There was an error in the query. Error:".mysql_error() ) );
//shows the mysql error if failed
Also notice how I always put a "or die ("error message")" after a DB query? Helps find mistakes like what you are currently experiencing.
Paul
Posted: Fri Mar 25, 2005 2:52 pm
by feyd
shab, please start posting code correctly.

Posted: Fri Mar 25, 2005 2:56 pm
by pthomas