How to display SELECTED columns from a JOIN statement in PHP

PHP
1

Hey, I have this INNER JOIN query:

SELECT oi.quantity, st.stock_id, st.quantity FROM order_items AS oi INNER JOIN stock AS st USING(product_id) WHERE 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:


$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

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:


SELECT 
    oi.quantity
  , st.stock_id
  , st.quantity [B][COLOR="Red"]AS stockquantity[/COLOR][/B]
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

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

Since the values are coming from your database, they should already have been sanitized before you saved them in the database.

5

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

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

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

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

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

Yes, but the OP is also invoking new variables, which means more typing which means more possibility of errors.


$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:


$row['stock_id']

all over the place then use the object notation:


$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 [google]Bob Martins Clean Code[/google].