sql join question

[lawrence]

Suppose I have this table:


CREATE TABLE properties (
id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
modifierText text NOT NULL,
modifierChar char(1) NOT NULL default '',
modifierVarchar varchar(255) NOT NULL default '',
modifierInt int(11) NOT NULL default '0',
belongsTo int(11) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

I also have another table called entry, and this has an id column,
plus a name, body, and date created column.

An unlimited number of properties can be attached to each entry in the
table "entry". The id in the table "entry" is generated by auto
increment in MySql. The field "belongsTo" in the properties table
tells the code which entry in the "entry" table this particular
property belongs to.

Now, I want a function called getEntry() that gets an entry from the
"entry" table, plus all the properties from the the property table,
and brings them together as if they were all there in one table. I get
tripped up on how to do this.

select entry.*, properties.*
from entry, properties
where entry.id = properties.belo ngsTo
order by entry.dateCreat ed


What I'd really like is to get back the mass as an associative array
where every entry from properties has the field name of "name" and
then the associated value (of the "modifier" fields, only should ever
be used for each property, though I can't know which one).

[Herbie Cumberland]

Re: sql join question

In message-id <da7e68e8.04061 81152.2b8b4cd@p osting.google.c om>,
lawrence wrote:
[color=blue]
>Suppose I have this table:
>
>
>CREATE TABLE properties (
> id int(11) NOT NULL auto_increment,
> name varchar(255) NOT NULL default '',
> modifierText text NOT NULL,
> modifierChar char(1) NOT NULL default '',
> modifierVarchar varchar(255) NOT NULL default '',
> modifierInt int(11) NOT NULL default '0',
> belongsTo int(11) NOT NULL default '0',
> PRIMARY KEY (id)
>) TYPE=MyISAM;
>
>I also have another table called entry, and this has an id column,
>plus a name, body, and date created column.
>
>An unlimited number of properties can be attached to each entry in the
>table "entry". The id in the table "entry" is generated by auto
>increment in MySql. The field "belongsTo" in the properties table
>tells the code which entry in the "entry" table this particular
>property belongs to.
>
>Now, I want a function called getEntry() that gets an entry from the
>"entry" table, plus all the properties from the the property table,
>and brings them together as if they were all there in one table. I get
>tripped up on how to do this.
>
>select entry.*, properties.*
>from entry, properties
>where entry.id = properties.belo ngsTo
>order by entry.dateCreat ed
>
>
>What I'd really like is to get back the mass as an associative array
>where every entry from properties has the field name of "name" and
>then the associated value (of the "modifier" fields, only should ever
>be used for each property, though I can't know which one).[/color]

i think your problem is that when fetching results from mysql as an
associative array, the 'name' field from the second table overwrites
the 'name' field from the first table?

get your results from mysql as a non-associative array, then process
into your own requirements.

[Michael Austin]

Re: sql join question

lawrence wrote:
[color=blue]
> Suppose I have this table:
>
>
> CREATE TABLE properties (
> id int(11) NOT NULL auto_increment,
> name varchar(255) NOT NULL default '',
> modifierText text NOT NULL,
> modifierChar char(1) NOT NULL default '',
> modifierVarchar varchar(255) NOT NULL default '',
> modifierInt int(11) NOT NULL default '0',
> belongsTo int(11) NOT NULL default '0',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> I also have another table called entry, and this has an id column,
> plus a name, body, and date created column.
>
> An unlimited number of properties can be attached to each entry in the
> table "entry". The id in the table "entry" is generated by auto
> increment in MySql. The field "belongsTo" in the properties table
> tells the code which entry in the "entry" table this particular
> property belongs to.
>
> Now, I want a function called getEntry() that gets an entry from the
> "entry" table, plus all the properties from the the property table,
> and brings them together as if they were all there in one table. I get
> tripped up on how to do this.
>
> select entry.*, properties.*
> from entry, properties
> where entry.id = properties.belo ngsTo[/color]
and properties.name = 'name'[color=blue]
> order by entry.dateCreat ed
>
>
> What I'd really like is to get back the mass as an associative array
> where every entry from properties has the field name of "name" and
> then the associated value (of the "modifier" fields, only should ever
> be used for each property, though I can't know which one).[/color]

Give me the details for the entry table and a short/brief example of
what you currently get and what you want to get.

Michael Austin.
DBA for hire.

[lawrence]

Re: sql join question

Herbie Cumberland <spamtrap@skipr aider.com> wrote in message news:<crc7d01d5 kvjvh72v1r0t4u9 ihvgm4l1lu@4ax. com>...[color=blue]
> In message-id <da7e68e8.04061 81152.2b8b4cd@p osting.google.c om>,
> lawrence wrote:
>[color=green]
> >Suppose I have this table:
> >
> >
> >CREATE TABLE properties (
> > id int(11) NOT NULL auto_increment,
> > name varchar(255) NOT NULL default '',
> > modifierText text NOT NULL,
> > modifierChar char(1) NOT NULL default '',
> > modifierVarchar varchar(255) NOT NULL default '',
> > modifierInt int(11) NOT NULL default '0',
> > belongsTo int(11) NOT NULL default '0',
> > PRIMARY KEY (id)
> >) TYPE=MyISAM;
> >
> >I also have another table called entry, and this has an id column,
> >plus a name, body, and date created column.
> >
> >An unlimited number of properties can be attached to each entry in the
> >table "entry". The id in the table "entry" is generated by auto
> >increment in MySql. The field "belongsTo" in the properties table
> >tells the code which entry in the "entry" table this particular
> >property belongs to.
> >
> >Now, I want a function called getEntry() that gets an entry from the
> >"entry" table, plus all the properties from the the property table,
> >and brings them together as if they were all there in one table. I get
> >tripped up on how to do this.
> >
> >select entry.*, properties.*
> >from entry, properties
> >where entry.id = properties.belo ngsTo
> >order by entry.dateCreat ed
> >
> >
> >What I'd really like is to get back the mass as an associative array
> >where every entry from properties has the field name of "name" and
> >then the associated value (of the "modifier" fields, only should ever
> >be used for each property, though I can't know which one).[/color]
>
> i think your problem is that when fetching results from mysql as an
> associative array, the 'name' field from the second table overwrites
> the 'name' field from the first table?
>
> get your results from mysql as a non-associative array, then process
> into your own requirements.[/color]


Yes, you understood my question right. Thanks for the suggestion of
the non-associative array. I don't like that idea, but I suppose it is
the only way to get back the data I need. I'll have to transform the
array to an associative array in the PHP code. Thanks.

[Malcolm Dew-Jones]

Re: sql join question

lawrence (lkrubner@geoci ties.com) wrote:
: Herbie Cumberland <spamtrap@skipr aider.com> wrote in message news:<crc7d01d5 kvjvh72v1r0t4u9 ihvgm4l1lu@4ax. com>...
: > In message-id <da7e68e8.04061 81152.2b8b4cd@p osting.google.c om>,
: > lawrence wrote:
: >
: > >Suppose I have this table:
: > >
: > >
: > >CREATE TABLE properties (
: > > id int(11) NOT NULL auto_increment,
: > > name varchar(255) NOT NULL default '',
: > > modifierText text NOT NULL,
: > > modifierChar char(1) NOT NULL default '',
: > > modifierVarchar varchar(255) NOT NULL default '',
: > > modifierInt int(11) NOT NULL default '0',
: > > belongsTo int(11) NOT NULL default '0',
: > > PRIMARY KEY (id)
: > >) TYPE=MyISAM;
: > >
: > >I also have another table called entry, and this has an id column,
: > >plus a name, body, and date created column.
: > >
: > >An unlimited number of properties can be attached to each entry in the
: > >table "entry". The id in the table "entry" is generated by auto
: > >increment in MySql. The field "belongsTo" in the properties table
: > >tells the code which entry in the "entry" table this particular
: > >property belongs to.
: > >
: > >Now, I want a function called getEntry() that gets an entry from the
: > >"entry" table, plus all the properties from the the property table,
: > >and brings them together as if they were all there in one table. I get
: > >tripped up on how to do this.
: > >
: > >select entry.*, properties.*
: > >from entry, properties
: > >where entry.id = properties.belo ngsTo
: > >order by entry.dateCreat ed
: > >
: > >
: > >What I'd really like is to get back the mass as an associative array
: > >where every entry from properties has the field name of "name" and
: > >then the associated value (of the "modifier" fields, only should ever
: > >be used for each property, though I can't know which one).
: >
: > i think your problem is that when fetching results from mysql as an
: > associative array, the 'name' field from the second table overwrites
: > the 'name' field from the first table?
: >
: > get your results from mysql as a non-associative array, then process
: > into your own requirements.


: Yes, you understood my question right. Thanks for the suggestion of
: the non-associative array. I don't like that idea, but I suppose it is
: the only way to get back the data I need. I'll have to transform the
: array to an associative array in the PHP code. Thanks.

lookup column alias


select column as name ...



--

(Paying) telecommute programming projects wanted. Simply reply to this.