Re: [PHP-DB] When does using multiple tables make sense?
Liam Friel wrote: 2008/10/20 Lester Caine [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Jason Pruim wrote: So my question is... When is it best to use more tables? All the info will be related to each other, so I think I would be looking at either a many-to-many relationship, or a many-to-one relationship (still figuring that out). One thing that I've realised make sense is to have a 'sub-table' for things like phone number, email, fax and the like. All too often we have two phone numbers or different email addresses, so a four field table with ID number, type of info, info, note This way one can add as many info fields of any different type to a client/contact record. The type of info field flags things like primary phone. Address details often need the same treatment as well, but I use UK post code as a key for the bulk of that information so it just goes into a another info field. I usually like to think of multiple tables in terms of - how many of this type of data will the users need? If it is a set number i.e. users should only have name then I would put it in a customer table. If this type of data may have many entries i.e. user uploaded images (they can have any number), then I would use a different table to store the images or information along with a reference to which user they belong. This practice stops redundant data and using uneccessary space in your database being used. For example: if you wanted to have 10 fields for user images and you put them in the contact table, users that do not use the 10 image fields will be wasting space. whereas if they are in a related seperate table, only space is used for images that have been uploaded. Using PHP you would do the necessary validation to check the number of images etc a user was allowed. so in short - if a type of data you are inputting has an unknown number of results - it is best to put it in another table: it is also known as normalisation. I think that is more or less what I said ;) One of my areas of interest is genealogical data, and there can be several areas where some 'individual' records have no data and others can have a large number. Even 'date of birth' may be something that is not a simple date ;) Almost as soon as you put a field in the main index table, there will be an exception to the rule :) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/lsces/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] using database without mysql installed
Patrick Price wrote:
I am trying to access a mysql database server from a php 5.2 server that
doesn't have mysql installed on it. I don't have access or the ability to
reinstall php on this server. Is it possible to install mysql after php is
installed and up and running?
Of course.
php just needs the php-mysql connector. When that's provided doesn't
matter (just remember to restart the webserver).
The best solution I could think of would be to require a php file that is
running on a server that I have control of and that has mysql installed and
using that file to call the database to get the information I need.
Connect to mysql remotely. The db does not have to be on the same
machine at all.
mysql_connect lets you put in a server name - it can be an ip address
(recommended) or a hostname (not so recommended - if dns plays up, you
can't connect).
mysql_connect('192.168.0.50', 'user', 'pass');
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Help needed - SELECT query optimization
Hi, I have a mysql database with one table in it, currently it contains 36,807 records and it costs 6.8MB of space, every night another thousand~ of rows are being added to the table. The scheme (as taken from phpmyadmin) is like follows: CREATE TABLE IF NOT EXISTS `search` ( `id` int(11) NOT NULL auto_increment, `time` int(10) default NULL, `unique` varchar(255) collate utf8_unicode_ci default NULL, `site` varchar(50) collate utf8_unicode_ci default NULL, `url` varchar(255) collate utf8_unicode_ci default NULL, `filename` varchar(255) collate utf8_unicode_ci default NULL, `snippet` varchar(255) collate utf8_unicode_ci default NULL, `tags` varchar(255) collate utf8_unicode_ci default NULL, `password` varchar(255) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=44306 ; insert example: INSERT INTO `search` VALUES (null, 1225041602, '110755357', 'rapidshare', ' http://rapidshare.com/files/110755357/Taxi4.By.HuNTeR.part1.rar', 'Taxi4.By.HuNTeR.part1.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110756297', 'rapidshare', ' http://rapidshare.com/files/110756297/Taxi4.By.HuNTeR.part2.rar', 'Taxi4.By.HuNTeR.part2.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110767009', 'rapidshare', ' http://rapidshare.com/files/110767009/Taxi4.By.HuNTeR.part3.rar', 'Taxi4.By.HuNTeR.part3.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110768015', 'rapidshare', ' http://rapidshare.com/files/110768015/Taxi4.By.HuNTeR.part4.rar', 'Taxi4.By.HuNTeR.part4.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110779013', 'rapidshare', ' http://rapidshare.com/files/110779013/Taxi4.By.HuNTeR.part5.rar', 'Taxi4.By.HuNTeR.part5.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110792243', 'rapidshare', ' http://rapidshare.com/files/110792243/Taxi4.By.HuNTeR.part6.rar', 'Taxi4.By.HuNTeR.part6.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'),(null, 1225041602, '110793721', 'rapidshare', ' http://rapidshare.com/files/110793721/Taxi4.By.HuNTeR.part7.rar', 'Taxi4.By.HuNTeR.part7.rar', 'Taxi 4 (2007) DVDRip XviD - KL', '', '--'); It should be a rapidshare links database (which updates with a PHP crawler I wroted last Saturday). I would like to change the snippet to title and add another column for the snippet, My main queries are INSERT's - a big insert (usually 10-100 links per insert) in each hour. My crawler is checking if the link it is about to add is already in the database with a select query like this: SELECT `id` FROM `tbl_name` WHERE `unique` = '%_UNIQUE_VARIABLE_%' I'm definiatly not an database scheme expert, I'm looking to optimaize the table for fast select queries (to check if file is already exists) And for fast filename-based search (select * from `tbl_name` where `filename` like '%_WHATEVER_%') The unique colmn is used for other websites unique ID's as well so I don't think I have any chance to crop it's length. I probably can change the site colmn to numeric id or something (I'm just about doing that) - but this is not the real problems, I have heard about something called Indexes but I have no idea what this is about. I hope I will find an answer, or a path where to look for in order to get this table optimaized, Thanks in Advance, Nitsan
Re: [PHP-DB] Help needed - SELECT query optimization
It should be a rapidshare links database (which updates with a PHP crawler I wroted last Saturday). I would like to change the snippet to title and add another column for the snippet, My main queries are INSERT's - a big insert (usually 10-100 links per insert) in each hour. My crawler is checking if the link it is about to add is already in the database with a select query like this: SELECT `id` FROM `tbl_name` WHERE `unique` = '%_UNIQUE_VARIABLE_%' create index unique_idx on tbl_name(unique); mysql might work better with this index: create index unique_idx on tbl_name(unique, id); because in some cases it doesn't have to go back to the data file to get the actual data, it can just read everything from the index. I'm definiatly not an database scheme expert, I'm looking to optimaize the table for fast select queries (to check if file is already exists) And for fast filename-based search (select * from `tbl_name` where `filename` like '%_WHATEVER_%') like queries are harder to optimize. If you put a wildcard at the front: like '%...%'; the db can't use an index to find it, because you're saying the text can be anywhere and for that type of search you're best off setting up fulltext (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html). If you don't put a % at the front: like '...%'; the db use an index to find it (up until the wildcard).. create index filename_idx on tbl_name(filename); To understand indexing, check out my article: http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database (Yes it's on a postgres site but the ideas/understanding work for all db's - and the commands should even work for mysql). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
