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.belongsTo
order by entry.dateCreated
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).

Jul 17 '05 #1

Herbie Cumberland

In message-id <da*************************@posting.google.com> ,
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.belongsTo
order by entry.dateCreated
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.

Jul 17 '05 #2

Michael Austin

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.belongsTo and properties.name = 'name' order by entry.dateCreated
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).

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.

Jul 17 '05 #3

lawrence

Herbie Cumberland <sp******@skipraider.com> wrote in message news:<cr********************************@4ax.com>. ..

In message-id <da*************************@posting.google.com> ,
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.belongsTo
order by entry.dateCreated
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.

Jul 17 '05 #4

Malcolm Dew-Jones

lawrence (lk******@geocities.com) wrote:
: Herbie Cumberland <sp******@skipraider.com> wrote in message news:<cr********************************@4ax.com>. ..
: > In message-id <da*************************@posting.google.com> ,
: > 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.belongsTo
: > >order by entry.dateCreated
: > >
: > >
: > >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.

Jul 17 '05 #5