SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to display SELECTED columns from a JOIN statement in PHP

    Hey, I have this INNER JOIN query:

    PHP Code:
    SELECT oi.quantityst.stock_idst.quantity FROM order_items AS oi INNER JOIN stock AS st USING(product_idWHERE oi.order_id $order_id 
    That works perfectly and when I do the query in phpMyAdmin it returns rows and no errors. As I run through the rows with a while() function I have this:

    PHP Code:
    $order_item_quantity intval($row['oi.quantity']);
    $stock_quantity intval($row['st.quantity']);
    $stock_id intval($row['st.stock_id']); 
    The problem is if I echo out any of those values or write them to a file they are always zero. Does anyone know why that is? Thanks!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The name of the columns in the result set is not oi.quantity, st.stock_id, st.quantity.
    It's quantity, stock_id and the last one I don't know, since quantity is already the name of the first column.

    That's why you should use an alias in your query to rename the columns with duplicate names:
    Code:
    SELECT 
        oi.quantity
      , st.stock_id
      , st.quantity AS stockquantity
    FROM order_items AS oi 
    INNER JOIN stock AS st 
    USING(product_id) 
    WHERE oi.order_id = $order_id
    Now the values can be accessed with:
    $row['quantity']
    $row['stock_id']
    $row['stockquantity']

    By the way, why do you pass them through intval?

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yeah Inever thought of using the alias for column names only table names. I pass them through intval() because im very paranoid security wise and I will be using those values later in an SQL query. Just to make sure there is no SQL injection basically. Thanks for the reply.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Since the values are coming from your database, they should already have been sanitized before you saved them in the database.

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Since the values are coming from your database, they should already have been sanitized before you saved them in the database.
    But let's say when you add data into the database and that data has for example a single quote in it. When you so mysql_real_escape_string() it will put a backslash in front of the single quote to escape it but that backslash won't get added into the database with the data so wouldn't that mean if you retrieved that piece of data later and put it into a query without escaping the single quote it's going to cause an error?

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    You have a point, data from your database is in effect Input to your script and therefore you should Filter Input (FIEO), unless you know for a fact that you have correctly filtered it before it got stuffed into the database.

    (this also suggests you are using a varchar to store an integer which sounds a bit wrong)

    The problem is if there is a rogue ' before your integer, eg "'9" intval() turns it into 0 in any case - I suppose a 0 cannot do any harm, but you have lost the value.

  7. #7
    SitePoint Member
    Join Date
    May 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    You have a point, data from your database is in effect Input to your script and therefore you should Filter Input (FIEO), unless you know for a fact that you have correctly filtered it before it got stuffed into the database.

    (this also suggests you are using a varchar to store an integer which sounds a bit wrong)

    The problem is if there is a rogue ' before your integer, eg "'9" intval() turns it into 0 in any case - I suppose a 0 cannot do any harm, but you have lost the value.
    Yeah but I just secure every single thing that goes into a query even if I'm 100% sure it will be an integer coming from an Integer column I will still use intval(). Better safe then sorry it only takes an extra 2 seconds to put intval into your script and it doesn't really hurt I suppose.

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I do applaud your paranoia (being of much the same vein myself) but still I fail to see how you can have a ' in an integer column in a table. I'd be really interested to find out I am missing something...

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I think the OP is saying he does it by default, without checking if maybe in this case it isn't needed. This of course may lead to some unnecessary filtering, but you won't make any mistakes (not filtering when needed).

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Yes, but the OP is also invoking new variables, which means more typing which means more possibility of errors.

    PHP Code:
    $order_item_quantity intval($row['oi.quantity']); 
    $stock_quantity intval($row['st.quantity']); 
    $stock_id intval($row['st.stock_id']); 
    Which in turn can lead to code comprehension issues further down the script, "$stock_id? Hang on, where did that come from?" rather than the rather more explicit $row['stock_id']; (or maybe that could be the more expressive $lastOrder['stock_id']).

    Now if you are planning on using $stock_id in multiple places, then that would be a good argument for invoking that new variable. If the argument is that you don't like typing or reading:
    PHP Code:
    $row['stock_id'
    all over the place then use the object notation:
    PHP Code:
    $row->stock_id 
    All I am saying is that to adopt a policy of typecasting to an integer what should only be an integer because of the fear a column which ought to contain integers might be accidentally defined as being a varchar will invariably lead to errors somewhere else because of the increased illegibility of the code - if indeed it this is the OPs case.

    I know because that is how I used to think, pretty much like I used to use $row for every 'row'. **

    I can see the OPs point, but I thought I'd take the trouble to explode.

    **Anyway, this is all Anthony's fault he sent me a spare copy of Bob Martins Clean Code.


Bookmarks

Posting Permissions

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