Solved

php and postgres quessie.....

Posted on 2002-06-02
6
238 Views
Last Modified: 2006-11-17
i have an sql command like...

select account.* billing.* orders.* from account, billing, orders WHERE.....

and I am putting the results of this query into a serialized php array... like so.....

 for($i=0; $row = @pg_fetch_array($res,$i); $i++)
    {
        $tabledata = $row;
    }

however, i am getting undesired results because the tables have common column names, for example, accounts.id order.id and billing.id and yet my $tabledata variable just has the on $tabledata["id"] element...

what i need (and i am not sure if its at a php level or sql level) is something which will allow me to have an array indexed by tablename.columnname rather than just column name...

so for the above example i would have

$tabledata['accounts.id'] == //the id var from the account table
$tabledata['billing.id'] == // the id var from the billing table
$tabledata['orders.id'] == // the id var from the orders table

instead of just the one $tabledata['id'] value from who knows what table...

can anyone help!!??


0
Comment
Question by:n1875621
6 Comments
 
LVL 6

Expert Comment

by:christopher sagayam
Comment Utility
how about having something like

$tabledata[$tablename][$id];
0
 

Author Comment

by:n1875621
Comment Utility
no... because the information need to be serialized into an array and I cant tell what "$tablename" is from the sql query...
0
 
LVL 5

Expert Comment

by:dkjariwala
Comment Utility
In that case you should go for pg_fetch_row.

$result = pg_Exec($conn,"select account.*,billing.*,order.* from ....");

$num = pg_numrows($result);

for ($i=0; $i < $num; $i++)
{
  $r = pg_fetch_row($result, $i);

  for ($j=0; $j < count($r); $j++) {
    echo "$r[$j]&nbsp;";
}

// the fields would be in the same order as in table and between tables it would be in same order as you have wrote table names.

JD


0
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:n1875621
Comment Utility
thats fine, except i am not sure what tables are being selected and in what order... its a dynamic sql query....

any ideas?
0
 
LVL 5

Accepted Solution

by:
Hamlet081299 earned 100 total points
Comment Utility
What type of database are you using, MySQL, MSSQL, Oracle, ...?

I think the ideal solution would be to alias each of the fields, but it depends how the dynamic SQL is being built.  Can you give more details of this part of the code?

Generally speaking, unless you actually alias the fields in some way then the first "id" field will be "id" the next one "id_1" and so on.

If you can get you sql to look more like...

select
  account.id 'account.id',
  account.name 'account.name',
  billing.id 'billing.id',
  billing.address 'billing.address',
  orders.id 'orders.id'
  ...
from
  account, billing, orders
where.....

... then your problems will be solved.

Depending on your database being used, you can probably extract the necessary field names to build the SQL like this.
0
 

Author Comment

by:n1875621
Comment Utility
that worked fine.... not exactly what i needed but the pricipal was there...

cheers
john
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 26
compressing images after upload 4 29
How do I edit this Drupal page? 9 27
php image upload 3 24
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now