vbconz Posted June 20, 2011 Share Posted June 20, 2011 I am sure this has been done to death but I cant find the answer.... feel free to rtfm me is there is a thread somewhere that I haven't found. My code - has three tables, customer, invoice header and invoice lines. Customer.id joins to invoiceheader.custID which joins to invoiceline.InvoiceID = invoiceHeader.id In my return query I want to display cust.id, invoiceHeader.id ...... How do I differentiate between cust.ID and InvoiceHeader.Id If I use echo "<u>".$row['id']." - ".$row['firstname'].", ".$row['lastname']."</u><br />"; echo " Invoice: ".$row['invoiceheader.id']." ".$row['totalincl']."<br />"; the invoiceHeader.id does not show If I use just " Invoice: ".$row['.id']."... in the second line I do get the invoiceHeader.ID - presumeably the cursor moves across one field and gets the second field with an name of ID ... but that leaves me with no way of getting header.Id printed before cust.id. Is there an exact / explicit way of referenceing these fields? Ideally I would like to reference c.id and ih.id explicitly using .$row['ih.id'] and $row['c.id'] I do realise I could change the query to give an alternative name e.g. invoiceheader.id as myinvoiceheaderid but this is not going to help when accessing pre_built queries, views and functions where I cannot change the underlaying sql code. My code is below ThanKs in advance. Shane <html> <body> <?php //make connection mysql_connect("localhost", "foo", "bar") or die ('I cannot access the datbase because: ' .mysql_error()); mysql_select_db("db_fooBar"); //set query $query="SELECT c.id, c.lastname, c.firstname, ih.id, ih.invoicedate, ih.totalincl, il.invoiceline, il.quantity, il.unit, il.code, il.description, il.linetotal FROM (customer c INNER JOIN invoiceheader ih ON c.id = ih.custid) INNER JOIN invoiceline il ON il.invoiceid = ih.id ORDER BY c.lastname, c.firstname, ih.id, il.invoiceline"; $sql = mysql_query($query); //display results while ($row = mysql_fetch_array($sql)){ echo "<u>".$row['id']." - ".$row['firstname'].", ".$row['lastname']."</u><br />"; echo " Invoice: ".$row['invoiceheader.id']." ".$row['totalincl']."<br />"; } ?> </body> </html> KP: Added code tags Quote Link to comment Share on other sites More sharing options...
Philip Posted June 20, 2011 Share Posted June 20, 2011 Pretty simple - in your query you can do like the following: SELECT c.id as customerID, ih.id as invoiceHeaderID, .... PS - don't forget code tags Quote Link to comment Share on other sites More sharing options...
vbconz Posted June 21, 2011 Author Share Posted June 21, 2011 Pretty simple - in your query you can do like the following: SELECT c.id as customerID, ih.id as invoiceHeaderID, .... PS - don't forget code tags Hi KingPhilip, thanks for the reply. In my post I did ention that was a possibility I do realise I could change the query to give an alternative name e.g. invoiceheader.id as myinvoiceheaderid but this is not going to help when accessing pre_built queries, views and functions where I cannot change the underlaying sql code. In the situation above I have control of the query but if I am using a predefined query / function / view / stored procedure, then I have to alias the results which is an extra load on the CPU. e.g. query$ = "SELECT customer.id as custId, invoiceHeader.ID as invoiceHeaderID, headerLine.Id as InvoiceHeaderID from myStoredProcedure"; when ideally I should be able to go query = "SELECT * from mySTOREDPROCEEDURE"; echo $row['customer.id]." ".$row[invoice.id]....... With the aliases code the dbms accepts my query, processes it, optimises it etc and then calls it where as the second code is a straight call as the storedProc is pre optimised, stored, cached etc. Second method is far less intense on the system. Is there really no way that PHP lets you pick up a field by its full name (ie table/alias.columnID ). I would be really surprised if that was the case as multiple uses of the same column id is quite common in joins where you need to query the same table twice, joins between tables where the naming convention for a key column is ID etc. In my query above the ideal is to move it into a view / stored procedure as it is a commonly called query and having it as a view or storedproc is ideal for db optimisation. Other Dbs i work on I dont have the luxury of writing my own stored procedure / views as I am working on DBMS data I dont control. So .. is there a way for me to get around this issue and reference the results as alias.columnid or table.column ID. Cheers Shane Quote Link to comment Share on other sites More sharing options...
ebmigue Posted June 21, 2011 Share Posted June 21, 2011 Your problem is caused by SQL's permission to allow for duplicate attribute names and incomplete support of data types. Had this feature not been allowed in the first place, database designers would have been more cautious in naming their attributes. For instance, giving an attribute a name "ID" in the "invoices" table, where an attribute named "ID" is also found in a "customers" table could have been avoided. How so? If those two tables are JOINed, then the underlying RDBMS ought to have raised an exception (due to the fact that both tables/relvars has the same name but of different purpose/types). Then the designers would obviously avoided such situation by appropriately naming their such attributes to avoid ambiguity. Now for your problem: 1.) If you are only interfacing with/using views and SQL expressions (i.e., SELECT statements), you can always use a SUBQUERY or the simple RENAME operator (a.k.a "AS"). That has already been posted above. 2.) If you are interfacing with stored procedures, the situation is hopeless AFAIK. This has something to do with SQL lack of support for relational closure. In a conventional programming language, a programmer can write a function that, say, takes an integer then returns an integer as a result; which in effect allows you to nest your functions with user-defined or built-in functions. You can't do that in SQL stored procedures. You can't use a stored procedure then use the result as an argument in another relatively complex relational expression (i.e., SELECT statement). Disclaimer: check your version, as this might be supported. At least for MySQL that is the case. For other DBMSs you could simulate that using "table-valued functions," but which I think is not relevant in your case. In any case: Fix your queries (i.e., with the proper non-ambiguous attribute names) then "finalize" them as VIEWS. If you want to have fast and parameterized access to those views, use them with STORED PROCS. The important thing is that VIEWS must be the basis of your fix/improvements. They are more re-usable with other arbitrary SQL expressions (i.e., SELECT statements.) Hope it helps. Quote Link to comment Share on other sites More sharing options...
vbconz Posted June 21, 2011 Author Share Posted June 21, 2011 Your problem is caused by SQL's permission to allow for duplicate attribute names and incomplete support of data types. Had this feature not been allowed in the first place, database designers would have been more cautious in naming their attributes. ..... In any case: Fix your queries (i.e., with the proper non-ambiguous attribute names) then "finalize" them as VIEWS. If you want to have fast and parameterized access to those views, use them with STORED PROCS. The important thing is that VIEWS must be the basis of your fix/improvements. They are more re-usable with other arbitrary SQL expressions (i.e., SELECT statements.) Hope it helps. Thanks. but it is a bit of a begger.. I agree with most of what you are saying but it is a semi regular thing to have a table joined to itself (self join) so the un-ambiguous column ids cannot be avoided in that case. Quote Link to comment Share on other sites More sharing options...
ebmigue Posted June 21, 2011 Share Posted June 21, 2011 Thanks. but it is a bit of a begger.. ....but it is a semi regular thing to have a table joined to itself (self join) so the un-ambiguous column ids cannot be avoided in that case. Could you explain this more thoroughly? I can't see why tables/relvars joined with itself is a problem, considering today's SQL standards. To be sure, a table/relvar can be joined with itself without much problems, using today's leading RDBMs. In as much as I would like to offer an SQL code to help, I just can't because the "true problem" in your case is bad design (ambiguous attribute naming). However, to improve your predicament, the most viable solution is using either the "AS" operator, or the SUBQUERY construct. AFAIK, those could only be the solutions. The situation is somewhat hopeless if we bring SPs. Hope it helps. Quote Link to comment Share on other sites More sharing options...
vbconz Posted June 27, 2011 Author Share Posted June 27, 2011 Thanks. but it is a bit of a begger.. ....but it is a semi regular thing to have a table joined to itself (self join) so the un-ambiguous column ids cannot be avoided in that case. Could you explain this more thoroughly? I can't see why tables/relvars joined with itself is a problem, considering today's SQL standards. To be sure, a table/relvar can be joined with itself without much problems, using today's leading RDBMs. It is not the join but the way that PHP mysql_fetch_array presents data. In VB as an example you can access as ADO / ADODB record set (array object) with rs[tablename.fieldname] in querys that may have ambiguous names caused by joining two tables with a similar column / field ID or by performing a self join on a table. e.g ' Below returns t1.id, t2.id, t1.name, t2.lastname query$ = 'Select t1.id, t2.id, t1.name, t2.lastname from myTable as t1 left join myTable as t2 on t1.id=t2.id' ... set rs = new adodb.recordset(myDbObj) .... while id1$ = rs[t1.id] id2$ = rs[t2.id] rs.eof ..... In PHP it is not possible to do that. The best you can hope for is to remember what order the ambiguus col / field names are returned as and call the fieldName twice e.g. // Below returns t1.id, t2.id, t1.name, t2.lastname $query = 'Select t1.id, t2.id, t1.name, t2.lastname from myTable as t1 left join myTable as t2 on t1.id=t2.id'; //connect db etc here - and run query - get reasults,. $sql = mysql_query($query); //display results while ($row = mysql_fetch_array($sql)){ .... id1$ = $row['id']; //this will get t1.id as it is rthe first col with label = id id2$ = $roiw['id']; // this wil get t2.id as it is the second call to get row['id'] and the cursor has moved across a column - dont ask me why it has but it has .... } If the query changes (e.g. it is in a stored proc, is called dybnamically from a text version of the query stored in a db, etc. where users can change things ..) and the t2.id is now returned prior to t1.id then your code is screwed. In the VB version it is explictly referenced - making the code more robust. Now I agree with unambigous naming schemes ... mostly, but they are a stylistic choice not a hard and fast rule. Also as a coder I often dont have access or control of the underlaying tables / data layer - that is up to the customer or RDBMS Admin person. I once spent 18 months working with Sybase stored procs with out once getting to see the code underneath them. Large corporate - division of labour. I was tweir two and three , not tier one coder. In a situation like that explicit coding is a defensive technique to make code robust and durable. Assuming t1, alsways comes before t2, is not a robust assumption. Anyway - the other arguments againt the unambigous namings: 1 - Some DB systems have a very limited col_length / field name length. 8 chars for old dbase type Dbs. Abnd yes - they still exist. Trying to make a uniue field name while keeping symantec context and readability becomes very difficult. 2 - Knowing that a table always cas a unique key of type number, in an ascending sequence / auto number / and it is always called ID makes life a lot easier when you are trying to access 100s of tables and may need to run adhoc querys with no data dictionary at your shoulder. E.g. Bob - record 123456 on table foo has strange data. Ok ROB - I'll select * from foo where ID=123456; job done - otherwise you end up going - ok rob, whats the table primary index / column / field name? Hang on bob I'll find a data dictionary and look it up. - Its QWGTZRYU Say what Rob? why the weird name? Well Bob - its our unique naming convention - makes them unambiguous.... Rob, it certasinluy makes them something that for sure. and before anyone leaps on me - I've worked with a Db2 DB, on an AS400 that had 400 tables, each with over 20 fields, all named using an unambiguous system with 8 char names like QFGTRWEB It was a coding nightmare and a maintenance sod of the first order. Getting unambigous names can be really really hard in a large RDBMS. E.g. staff, client, supplier, contact, customer, dept head, all likely to have firstname, lastname, DOB in it. Purchase orders, invoices, quotes, receipts etc all have reference, PO numbers, quote type numbers, lines, headers, dates, clientID, payer ID etc in it. In practical terms the best way to show unambiguous field names without creating a data duictionary nightmare is by pre-pending table names before col names in a query or result set. The mysql_fetch_array() doesn't allow you to pull col names using tablename.fieldname. It there fore makes more work / more risk. My $0.02 for what it is worth. Quote Link to comment Share on other sites More sharing options...
ebmigue Posted June 27, 2011 Share Posted June 27, 2011 The whole point of avoiding ambiguous column names, and to strictly adhere to unambiguous names, is to avoid confusion. If in your household, you name everyone "Bob", where in fact they are different persons - the problems are obvious. If you say "bob", a lot of them would respond to your inquiry, thus confusion arises. The set of all attribute/column names for a given database is no different. Your example illustrates the case: Ok ROB - I'll select * from foo where ID=123456; job done - otherwise you end up going - ok rob, whats the table primary index / column / field name? Hang on bob I'll find a data dictionary and look it up. - Its QWGTZRYU Precisely. Because the purpose of attribute named ID in every table in your database is to be a "tuple/row ID". No confusion exists. But if you name an attribute "ID" which is really a "customer id" in a "customers" table, and then you use again "ID" which is really an "invoice id" in an "invoices" table, obviously they are used as names for two very different things. Obviously, a customer's id and an invoice's are not the same in purpose and essence. We know what problems arise when we use the same name for two or more different concepts: confusion. 1 - Some DB systems have a very limited col_length / field name length. 8 chars for old dbase type Dbs. Abnd yes - they still exist. Trying to make a uniue field name while keeping symantec context and readability becomes very difficult. This is of course a practical argument, and may not be anymore true a few years from now. Getting unambigous names can be really really hard in a large RDBMS. E.g. staff, client, supplier, contact, customer, dept head, all likely to have firstname, lastname, DOB in it. Purchase orders, invoices, quotes, receipts etc all have reference, PO numbers, quote type numbers, lines, headers, dates, clientID, payer ID etc in it. It need not be that hard. What I am suggesting is to pin down the true nature/purpose of an attribute. If we do so, the idea of "aliasing" a table to be prepended in an attribute name becomes needless. Also, SQL's lack of support for proper usage and definition of user-defined types contributes to this problem. Now I agree with unambigous naming schemes ... mostly, but they are a stylistic choice not a hard and fast rule. I disagree. It is a scientific choice, not a whimsical style. Also as a coder I often dont have access or control of the underlaying tables / data layer - that is up to the customer or RDBMS Admin person. I once spent 18 months working with Sybase stored procs with out once getting to see the code underneath them. Large corporate - division of labour. I was tweir two and three , not tier one coder. In a situation like that explicit coding is a defensive technique to make code robust and durable. Assuming t1, alsways comes before t2, is not a robust assumption These observations are real of course. Bureaucracy can be painful at times. However, considering such social setup, I doubt if it can counter-argue the self-evident fact that: if two or more different things are similarly named, then confusion will arise. In practical terms the best way to show unambiguous field names without creating a data duictionary nightmare is by pre-pending table names before col names in a query or result set. If it is taken as correct to go on ambiguously name attributes, then perhaps that is the best way. But what really is taking place when we do prepend an attribute with its table name? We are renaming an attribute! Although not directly, but basically we are telling the DBMS's compiler to "disambiguate" certain "ambiguous" columns. How? Through its "source table". But what is "disambiguating"? Renaming. What is renaming? Making such attributes names unambiguous! But why go through all this, when, by good design, we can avoid all this. Thus, there is the culprit. Database design. I would argue, however, that there is no such thing as "the perfect design." But again, an acceptable design ought to have been performed to avoid these problems. (Note: there are cases where attribute renaming is unavoidable. That is the purpose of the relational RENAME operator. However, the instances which require RENAMEing will be *significantly* lessened if we unambiguously name table attributes). The mysql_fetch_array() doesn't allow you to pull col names using tablename.fieldname. It there fore makes more work / more risk. I see this as mysql_'s merit. At least it forces the user to unambiguously name the columns of his/her result set. Hope it helps. Quote Link to comment Share on other sites More sharing options...
vbconz Posted June 29, 2011 Author Share Posted June 29, 2011 The whole point of avoiding ambiguous column names, and to strictly adhere to unambiguous names, is to avoid confusion. The mysql_fetch_array() doesn't allow you to pull col names using tablename.fieldname. It there fore makes more work / more risk. I see this as mysql_'s merit. At least it forces the user to unambiguously name the columns of his/her result set. Hope it helps. While I do agree - mostly - in the practical sense, working in the real world, having an option to access an attribute via a tablename.columnId at least gives some options. when I get better at PHP I will try to re-write or superClass?!? the mysql_fetch_array to accept table.ColID. At worst it will be a good learning experience. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.