7 replies to this topic

[lol33d]

hi guys

please give me better query for this tables:

CREATE TABLE IF NOT EXISTS `member` (

	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

	`firstname` varchar(32) NOT NULL DEFAULT '',

	`lastname` varchar(32) NOT NULL DEFAULT '',

	`username` varchar(15) NOT NULL DEFAULT '',

	`password` varchar(32) NOT NULL DEFAULT '',

	PRIMARY KEY (`id`)

) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `contact` (

	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

	`memberid` int(11) unsigned NOT NULL DEFAULT '0',

	`firstname` varchar(32) NOT NULL DEFAULT '',

	`lastname` varchar(32) NOT NULL DEFAULT '',

	PRIMARY KEY (`id`)

) ENGINE=MyISAM;

i need query for contact table and i need username in this query

please give me a better query

thank you so much

[Vaielab]

First of, is there a reason why you are using MyISAM insted of InnoDB?

Tell me if I understand your db, each member may have multiple contact (someone else) and each contact is linked to only one member?
If so, this would be the query to display all the contacts with the member they are linked to

select c.id, c.firstname, c.lastname, m.id as memberId, m.firstname as memberFirstName, m.lastname as memberLastName  from contact c inner join member m on m.id = c.memberid

I'm using alias since you have the same columns name, and if you fetch in php with the columns name you would have a problem without any alias

[lol33d]

thank you vaielab

InnoDB is better than MyISAM?

[Vaielab]

InnoDb is transaction safe, it has data integrety
But take more ram & disk space, and don't have full-text search

A quick google search of innodb vs myisam will give you millions of results of argument why using one or the other.
Take a look, and decide for yourself

Both are good, but in your example, I would go with innoDb for the data integrety

[lol33d]

no problem some tables InnoDB and some tables MyISAM in a database?

[Vaielab]

I never really tried it
I read some time ago that you could do it, but I don't know if there is any disavantage of it

[lol33d]

i have 25 tables

all tables is MyISAM, and only 5 tables are parentid of members

help for use of MyISAM or InnoDB ?!

[Vaielab]

For me transaction and data integrety is the most important thing.
So I always use InnoDB
If you don't need top speed, or full text search, you should go with innoDB