CODEWITHSUNDEEP
phpsql
Ham
Ham

Reputation: 814

SQL writing format

I would like to inner join to tables with sql like this:

$check_unscored =   "select * from [user]

            INNER JOIN [tenderrc]
            on [user].[id] = [tenderrc].[userid]";

$result_unscored = mssql_query($check_unscored, $s);

while ($record_unscored = mssql_fetch_array($result_unscored))
{
$id = $record_unscored['id'];
$name = $record_unscored['name'];   
$userid = $record_unscored['userid'];   
$touser = $record_unscored['touser'];

$id = $record_unscored['id'];
$username = $record_unscored['username'];   
$password = $record_unscored['password'];
}

I observed that a key name column called "id" exists in both table, and therefore $id = $record_unscored['id']; would be overwritten;

I tried $id = $record_unscored['user.id']; but it's not working! Actually how shall I specify the table name as well?

Upvotes: 0

Views: 69

Answers (3)

biziclop
biziclop

Reputation: 14596

You could do this using this function

http://hu2.php.net/manual/en/function.mssql-fetch-field.php

which can provide "column_source - the table from which the column was taken".

Example code:

$result = mssql_query("....");
$check_unscored = "
  SELECT *
  FROM       [user]
  INNER JOIN [tenderrc] ON [user].[id] = [tenderrc].[userid]";
$rows = fetch_multitable( mssql_query( $check_unscored, $s ));

foreach( $rows as $row ){
  echo $row->user['id'];
  echo $row->tenderrc['id'];
}

function fetch_multitable( $result ){
  $fields = mssql_num_fields( $result );
  $infos = array();
  for( $i=0; $i < $fields; $i++ ){
    $fieldinfo = mssql_fetch_field( $r, $i );
    $infos[ $i ] = (object)array(
      'table'=> $fieldinfo -> column_source
     ,'name' => $fieldinfo -> name
    );
  }

  $rows = array();
  while( $raw = mssql_fetch_row( $r )){
    $row = array();
    foreach( $raw as $i => $v ){
      $info = $infos[ $i ];
      $row[ $inf->table ][ $inf->name ] = $v;
    }
    $rows[] = (object)$row;
  }
  return $rows;
}

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Use aliases:

$check_unscored =   "
       SELECT 
           u.id
         , u.name 
         , u.userid
         , u.touser
         , t.id          AS tender_id              --- column alias
                                                   --- to remove duplicate names
         , t.username 
         , t.password
        FROM [user]   AS u                         --- table alias her
            INNER JOIN [tenderrc]   AS t           --- and here
                ON  u.[id] = t.[userid]
                    ";

Upvotes: 1

Nikola Velimirovic
Nikola Velimirovic

Reputation: 33

you could create a alias field etc if you wont id from user you could use something like this select [user].[id] as 'userid', [tenderrc].[*] from ... I come from mySql world but i think its managable this way and later access it this way $id = $record_unscored['userid'];

Upvotes: 1

Related Questions