Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post

    unable to get results

    can anyone tell me where i am going wrong with my code, i am wanting to display all the items that are stored in sessions but grab the related info from mysql.

    Code:
    <?
    $cartCount = 0; $cartCost = 0;
    foreach($_SESSION['cart'] as $id => $value) {
    	$cartCount += $value;
    	$getCost = mysql_query("SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1");
    	$itemCost = mysql_fetch_assoc($getCost);
    	$cartCost += ($itemCost['Price'] * $value);
    	echo("Product ID: " . $itemCost['Pid'] . " :		Product Name: " . $itemCost['Product Title'] . " :		Price: " . $itemCost['Price'] . " : ");
    }
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    DEBUG DEBUG DEBUG.

    Replace this:
    Code:
    	$getCost = mysql_query("SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1")
    with this:
    Code:
    	$sql = "SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1";
            echo "DEBUG SQL: " . $sql . "<br/>";
            $getCost = mysql_query( $sql );
    Do the SQL statements that generates look reasonable? Or are the PID values it looks for bogus?

  • #3
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    This code works and produces the price of each item to work out the cost based on the number of items that are stored in the session data

    but when i change the query to grab all the database info for the row it kicks up a fuss.

    Code:
    <?
    $cartCount = 0; $cartCost = 0;
    foreach($_SESSION['cart'] as $id => $value) {
    	$cartCount += $value;
    	$getCost = mysql_query("SELECT `Price` FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1");
    	$itemCost = mysql_fetch_assoc($getCost);
    	$cartCost += ($itemCost['Price'] * $value);
    	//echo("Product ID: " . $itemCost['Pid'] . " :		Product Name: " . $itemCost['Product Title'] . " :		Price: " . $itemCost['Price'] . " : ");
    }
    ?>
    <br class="clearfloat" /><br />
    Total items in cart: <?=$cartCount;?><br />Grand Total: £<?=sprintf("%02.2f", $cartCost);?>p

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Well, try specifically asking for only the fields you need. Maybe you have an unrelated field that is causing the problem?
    Code:
    	$sql = "SELECT Pid, Price, `Product Title` FROM products WHERE Pid = '" . $id . "' LIMIT 1";
    Incidentally, *IF* Pid is a NUMERIC field, then the apostrophes in there (surrounding the $id value) are technically incorrect. MySQL lets you use them, but it allows a lot of sloppiness that other DBs disallow.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Oh, and did you use a DB tool (not PHP) to test one or more of the $sql values you see from the debugging? To make sure that they indeed return all the data you expect?

  • #6
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by Old Pedant View Post
    Incidentally, *IF* Pid is a NUMERIC field, then the apostrophes in there (surrounding the $id value) are technically incorrect. MySQL lets you use them, but it allows a lot of sloppiness that other DBs disallow.
    I don't see it as sloppiness at all but as a last "back up" defence against possible attacks.

    Although all user inputs to an sql query should be validated and sanitised first there is no harm at all wrapping numbers in quotes afterwards as well notwithstanding MySQL might be one of few or the only DB that allows it.

    This is from the MySQL manual

    A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it
    I routinely wrap numbers in quotes when using MySQL since it does no harm.
    Last edited by bullant; 06-20-2011 at 05:17 AM.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    If you will never use any database except MySQL, go for it.

    I use MySQL, Oracle (well, seldom, but still...), SQL Server, Objectivity/DB, and a handful of others.

    And if I got in the habit of putting numbers in apostrophes, I'd keep getting syntax errors from those other DBs.

    I don't know what functions PHP has to make it easy/possible to ensure that a number *is* a number. In Java and C# and C++ and VBScript, there are functions to convert a string to a number and/or ensure that a string *is* just a number. And I routinely use those to validate strings-as-numbers.

    So, for example, in ASP code I would do:
    Code:
    SQL = "SELECT * FROM table WHERE id = " & CLNG(userinput)
    And if the user input is *NOT* a number (or if it has junk after the number) that either tosses an error (if not a number) or strips the junk after the number. And so I never have to worry about SQL injection.

    If PHP doesn't have some simple equivalent like this, then yeah, I can see why you'd use the apostrophes.

  • #8
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by Old Pedant View Post
    If PHP doesn't have some simple equivalent like this, then yeah, I can see why you'd use the apostrophes.
    It has plenty of functionality in that regard, intval() being one example. Personally, I'm off the same mindset as yourself. Design correctly and avoid the hacks, hence no apostrophes for integer fields.

  • #9
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by Old Pedant View Post
    If you will never use any database except MySQL, go for it.
    Over the years I have used other databases including Oracle as well.

    Nowadays if a client asks me to provide the hosting then I will be using MySQL if a database is required and I will consequently wrap numbers in quotes since they do no harm at all and can provide some "back up" defence against certain types of attack as described earlier.

    If the client says they have their own hosting then I ask them which database they will be using if a database is required. If they say MySQL then I will wrap numbers in quotes. If it is another database then I won't.

    It's not a time intensive task for me to remove quotes in the future in the unlikely event they will ever need to be.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •