homepage Welcome to WebmasterWorld Guest from 54.237.213.31
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Update cells in database
mitzter



 
Msg#: 4456874 posted 9:38 am on May 23, 2012 (gmt 0)

Hello,

Background information

I'm preparing a stock website where i want to give members the option to track their own stockpicks. A so called "watchist" or "track record".

I made a script where the member can enter the stockpick, whereafter the symbol and the price of the stock at that moment will be entered and stored into the database.

My question

When the member wants to sell the stock, he needs to fill in a form with the ticker symbol he wants to sell and then the ticker should be stored in the database with the sell price at that single moment. The sell price should be stored in the "sell" column in the database, in the same row as the bought stock (column "buy").
How shall this script look like?

Code

The table in the database is "portfolio" with rows "id", "symbol", "buy" and "sell".

This is what i have now:

<?php

if(isset($_POST['symbol'])){
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, 'http://download.finance.yahoo.com/d/quotes.csv?s='.$_POST['symbol'].'&f=sl1d1t1c1ohgv&e=.csv' );
curl_setopt( $ch, CURLOPT_HEADER, false );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
$output = curl_exec( $ch );
curl_close( $ch );

$contents = explode( ',', str_replace( '"', '', $output ) );
echo "<p>Stock: <b>\$$contents[1]</b> </p>";

$conn = mysql_connect("#*$!","#*$!","#*$!");
$db = mysql_select_db("#*$!",$conn);


mysql_select_db("my_db", $con);

mysql_query("UPDATE portfolio SET sell=$contents[1]
WHERE symbol=’$_POST[symbol]’");

echo “Your stock has been sold”;


mysql_close($conn);

?>

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4456874 posted 5:16 pm on May 23, 2012 (gmt 0)

Welcome aboard, you have syntax errors, I think. Always add "or die" in executing select statements.

The first, assuming "sell" is at least a decimal field (which it should be) or might be a varchar field, the value needs to be quoted.

The second is the funny quotes in your where statement. Do not use MS Word or other "rich text" editors to modify PHP code. It will hose you up every time. At the very least use Notepad.

You are also using raw input from POST and GET in your programming, which is very bad, especially when working with databases. It leaves you open to mySQL injection.

When you double-quote strings, you CAN interpolate scalar variables

....symbol='$some_variable'"

But when you attempt to do this with arrays,

...symbol='$_POST[symbol]'"

You get the value of "Array". You need to either concatenate

...symbol='" . $_POST[symbol] . "'"

or use curlies to disambiguate

....symbol='{$_POST[symbol]}'"

Last, the token "symbol" may very well be interpreted as a constant here, not an array key. Always quote them.

....symbol='{$_POST['symbol']}'"

All together, storing the select in a variable for clarity,

$query = "UPDATE portfolio SET sell='" . mysql_real_escape_string($contents[1]) . "'
WHERE symbol='" . mysql_real_escape_string($_POST['symbol']) . "'";

mysql_query($query) or die(Cannot update record: " . mysql_error());

Note that mysql_real_escape_string() does not cleanse your variable input, it just makes it database safe. I'd offer some suggestions but don't know the nature or data type of "$_POST['symbol']".

mitzter



 
Msg#: 4456874 posted 6:47 am on May 24, 2012 (gmt 0)

My comments are in bold:

The first, assuming "sell" is at least a decimal field (which it should be) or might be a varchar field, the value needs to be quoted.
Sell is a decimal field.

The second is the funny quotes in your where statement. Do not use MS Word or other "rich text" editors to modify PHP code. It will hose you up every time. At the very least use Notepad.
I use dreamweaver, but because i was at work i needed to use word for some additions.

You are also using raw input from POST and GET in your programming, which is very bad, especially when working with databases. It leaves you open to mySQL injection.
i will do this at the end, but thank you for noticing because it is a important comment!

Note that mysql_real_escape_string() does not cleanse your variable input, it just makes it database safe. I'd offer some suggestions but don't know the nature or data type of "$_POST['symbol']".
post symbol will be the symbol that is posted in the form to get the sell price of that ticker symbol get updated in the database

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4456874 posted 4:16 pm on May 24, 2012 (gmt 0)

What I meant was the data type of "symbol." is it characters only? You could do

$symbol = preg_replace('/[^a-z]/i','',$_POST['symbol']);

This would strip anything not a-z (case-insensitive) and store it in $symbol, which is one way to cleanse that variable.

Is it numeric?

$symbol = preg_replace('/[^\d]/','',$_POST['symbol']);

Strip anything not a digit.

Is it both?

$symbol = preg_replace('/[^a-z\d]/i','',$_POST['symbol']);

It gets more complicated if you need to cleanse input with other characters.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved