MYSQL :: Some Advice For A Newbie On Big Scale Websites Like Ebay, Facebook, Youtube

1 Database For 2 Websites?
While I have been doing simple HTML and CSS websites for some time I just recently started out with PHP and MySQL. The question I have is I have an existing website which runs off a MySQL database and am in the process of creating a second site which is the EXACT same type of website (myspace resouce) and will be using the exact same scripts on both sites so rather than having to create a second database and updating two seperates sites all the time if I can run them both off the same database so that by updating one database both sites will update with the new content.
Also the only things that are run off of the database is non search engine friendly content such as myspace layouts, graphics, and other types of images but the pages text content for each site is unique.

2 Websites Using 1 Database
i have two logins systems(for 2 websites) and i use the same mysql database.
still now when the users login at the first site and link to the other,they have to login again to access it.how can i manage this so when someone login at the 1/2 sites,they apear online at the other without needing to login again?
note that:
*i use the same database for these two sites
*i use one url and users switch between these two sites using internal button link.
*both sites r in the same server.

Does Too Many Tables Slow Down Websites
Just wondering if having too many tables in one database slows down a website and would it be better to have seperate databases rather than one big one?

Access A Database From Different Websites
Is it possible to access a MySQL database from a domain other than the domain that the database is phtsically on, and if so, how?

For example, if my database is located on www.site1.com, and I want to access it from a script on www.site2.com, how do I do it? For arguments sake, assume both websites are physically located on the same server, and they share an IP address of 123.123.123.001.

Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems.

However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?

A Lil Advice
i'm looking to build a relational database that will hold product reviews and statistics that i will eventually publish on the web where they will be searchable, etc. i'm working on a mac os 10.3.9.

is mysql the way to go? what app should i be using to create the database?

Advice
I own a company whereby my 50 employees (soon to be 100) all work from home and access our server via terminal services client. These 50 people work on 40 separate Access databases. Some small, some large (over 100 mb).
My IT consultant tells me that I should move to a sql server (which I agree), upgrade to Access 2003, and minimize term server use by moving to a web based input % reporting engine for common employee tasks (due to the load of 100 employees on Access).
- Access 2003 is $200 a user (are there cheaper options)
- We regularly modify the Access databases. Note that I created the databases but I am not a developer. I an very comfortable in Access (its the only database software I have ever used).
So, is MySQL an option for this environment?
I know its a big, ugly question with few details, but I am looking at all options.

Advice Requested
I'm going to be creating a signup form with the standard fields (fname, lname, email, etc), plus user definable fields. Currently, I'm kind of stuck on how to go about doing this.

My users table has an id field (PK), email field, IP address field, etc. I want to give the site admins more control over what they can put into their signup forms. Some people may want to have a website field, others may want to have a field for the user's fav book. Whatever.

Now, if I put these into the users table, I'm now fixed at how many fields I can have, correct? I mean, I can set a determined number of fields and call them custom1, custom2, etc. and let the admin define their user-readable names, but it's still a fixed amount of fields.

I could create a new table with 2 fields - name and value - where name is the PK. That way the admin could enter as many as they want. But how would I then have the same fields in the users table to hold the data?

Best Architecture Advice
Let's say you have 800,000 employees (huge company huh?), 300 sectors, and 3 security levels for each sector. You want to hold the security level for each sector for each employee. I can't figure out the best way to do this. Right now, my company has a table for each sector - each with columns [employee_id, security_level]. This results in 300 different tables and the joining when searching on several sectors gets crazy. I'm trying to figure out an easier way to structure this.

If you have one table [employee_id, sector_id, level_id], because of the many-to-many relationship between employee_id and sector_id - that's going to give you a 240,000,000 row table. If you have one table made up of a columns for each sector holding the security level value [employee_id, sector1, sector2, sector3, etc] you have a table with 301 columns, which will put me way out of my index max limit.

Design Advice
I am looking at designing a mini database to handle subsets of data at my company. You probably know the drill--it's too much to handle with a spreadsheet, but not enough to warrant hassling the DBAs with loading it into our main servers.

So I am looking at managing the data via a local instance of MySQL. The largest file I need to manage at the moment is about 100 columns x 165k rows.

Obviously, I wouldn't build table of that size. That's the whole point of relational DBs.

But here's the deal, at my company, once someone finds a way to do something, it tends to stick. So I'm looking at solving a few temporary problems by managing subsets of data from our main DB.

I have this suspicion that once I am able to do this, the demands will grow, and all the sudden, I'll get stuck managing a whole bunch a data for a bunch of people.

So I want to build the DB right. I've never built a DB before. I just query the main DB.

Without getting into proprietary details, I can say that I am looking at unique IDs that correspond to clients--about 165k, plus all relevant associated data.

What I am tracking is which clients have performed what jobs for us, when, and what the result was.

I need to be able to run reports on this, and I need to add data at the end of each week.

Given all of the above, what general advice do you have? Are there any huge pitfalls to avoid? Any thing that I absolutely should do? Keep in mind that for now, this is just going to be run on a Core2Duo Desktop with 2 gigs of ram, so anything I can do design-wise to speed things along will be useful.

Luckily, I am not in a time crunch. But thanks in advance for whatever advice you are able to come up with.

Optimization Advice
I'm having Database connection problem on my VPS server runing SMF forum script.
It often displays "unable to connect to database" notification when there is more than 200 users online (in 15 minute period).
I have raised max mysql connection number from 100 to 150, but that didn't help.
Connections per second number has doubled in last month (but traffic is only slightly higher)

Here are some mysql informations, any advice how to get this numbers to normal values is welcome.

Advice Sought
I'm somewhat new to database design in MySQL from a Designing from the "Ground Up" Point of view. I mean I've used Databases in a limited capacity, but this is my first time developing one from scratch, and mainly its for educational purposes, so I can eventually do this myself, rather than hit hotscripts, or some other repository but will be for a functional website at the same time.
I have a library of links I would like to display. My client has presented me with a word doc with the links and a description.
I assume I would just create a table with Category,Title,URL,Description as fields, but if I want to present the end user with a dropdown list at the top of the page that allows them to select a category, would I have to create a separate table with just the categories, and link it to the 1st DB?

Advice On How To Structure Tables
I currently have a dynamic site that displays info about different musicians. My database is currently set up with a table that contains all the infor about each musican such as name, type of music, website address etc. i.e all single bits of information.

Now I would like to add a form to allow the musicians to upload one or more mp3 files.

It would be fine if it was just one mp3 file as I would simply have a field in the table to store the name of the mp3 file.

However I am not sure how to deal with multiple mp3 files. Do I just create say 10 fields in the table for a maximum of 10 mp3 files for each musician? This doesn't seem the mosst sensible idea but I am fairly new to php and mysql so I am not sure what alternatives there are.

I would appreciate some advice on this. I was thinking perhaps I could create a separate table to store all the names of all the mp3 files for all musicians but doing this I do not know how I would know which ones are related to each musician.

Also related, should I store the mp3 files in separate folders for each musician or just have one mp3 folder on the server and save them all in here?

Advice On Hierarchical Schema
I'm trying to come up with a scema for a hierarchy of shopping categories.

Each category can have many children categories and many parents. Ie. exist more than once, and at different levels of the hierarchy. There will be a limit of 5 to the depth of the tree, but some branches will terminate sooner. At the end of the branch will be a link to products within that category.

I've read up on hierarchical structures, even read joe celko's book on the subject (I've just recovered from the resulting brain embolism ) but haven't really found solutions that seem to handle the many-many relationships going on, and in the case of the celko book there was a lot that mysql 4 can't do.

I'm using version 4.1.20 (stuck on redhat and it doesn't support 5)

Advice On Plan Of Attack
OK, I have a large web based questionaire form I am constructing using PHP/MySql. There are around 50 questions with each question having anywhere between 4 and 30 possible answers, depending on the question, that the user can select using checkboxes, meaning they can have multiple checkbox answers per question.

My question is what would be the best method to store the checkbox data in MySql, keeping in mind there is also is going to be a search routine that will need to pull the data back out efficiently?

Should I | delimit each checkbox answer in a mysql field for each question so there are 50 fields with pipe delimited responses, or do I make a field in the table for each possible answer (around 750 fields)? Or is their a better method???

With so many checkbox answers, what is the best/ most efficient way to store their responses knowing the data will need to be searchable?

I can get through the programming side of it, although I am not an expert by any stretch of the imagination. Just looking for advice on how to set it up before I get started.

Don't want to do it one way then discover later that the search is too slow because I didn't lay it out right first

Beginners Advice / First Database
I am creating my first database / website and would appreciate advice on how to organize or set up the tables.

what I have is user uploaded documents.

CREATE TABLE `documents` (
`doc_id` int(10) unsigned NOT NULL auto_increment,
`member_name` varchar(255) NOT NULL,
`language` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
`pgrating` varchar(50) NOT NULL,
`document_name_file` varchar(255) NOT NULL,
`type` mediumint(2) default NULL,
`document_pathto` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
`keywords` varchar(255) default NULL,
`series` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`date` date default NULL,
`votes` mediumint(255) NOT NULL default Ɔ',
`price` mediumint(255) NOT NULL,
`rating` mediumint(255) NOT NULL default Ɔ',
`purchased` mediumint(255) NOT NULL default Ɔ',
`leamount` varchar(255) default NULL,
`leExpired` mediumint(2) NOT NULL default Ɔ',
`userDisabled` tinyint(1) NOT NULL default Ɔ',
`lowrateDisabled` mediumint(2) NOT NULL default Ɔ',
PRIMARY KEY (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

My question is, for searching for these should I make a lot of lookup tables such as?

category id | comic id

some of the documents can be (changed to) private and not displayed in search results.

so I created a table "a_docs"
user_name(id) | active doc (id)

also the visitor can sort by keywords, ratings, category, country, language, latest and type

My question is, should I create a "lookup" table for each of the search criteria and then do something like a join (I honestly don't have a clue what I am talking about yet) ?

I also need to generate reports for the members such as how many and what documents were purchased.

my guess is a lookup table
purchased_docs

user_name(id) | purchased doc (id)

then once I have the doc (id) for the user I can then lookup the "documents" table to find the price for that document (id). Is this a good approach?

I expect to eventually have hundreds of thousands of documents and after this is built I will have someone go over the structure to make sure it is fully optimized.

btw I have been reading documentation and in my table the interger values and settings are all wrong.

I am good with php and have built dynamic websites with user editable content in flat files but now its time to jump on the mysql wagon.

Table Design Advice
I'm looking for some advice on suitable ways to construct a table or tables to handle the following situation.

I have a dynamic and structured content requirement, where the user can define a new type of item with a new/different set of fields. So I have

item types:
- name, fields

items themselves
- one or more field+content pairs depending on the information in item-types.

That in itself is pretty straight forward, I can store the item details in a single table of item-id, field-id, value triplets. ie. my db could follow the structure shown at the bottom of the post. However, I need to be able to filter for items based on the values of several fields.

In order to do that I can join the item table to itself for each field after the first, e.g. SELECT item-id FROM item-details i1, item-details i2, item-details-i3 WHERE i1.item-id = i2.item-id AND i1.item-id=i3.item-id AND i1.field-id=<field1> and i1.value=<expr1> AND i2.field-id=<field2> and i2.value=<expr1> AND i3.field-id=<field3> AND i3.value=<expr3>;

If the item needs to be selected on five fields then there would need to be four joins. Seven ... six, etc, etc.

Also, I would like to be able to search the field values without reference to the fields themselves. e.g. Search for all items with a value containing "red".

Is this the best way to handle the problem?
Is there another table design that would work better?
e.g. for each item-type, use the field information to create a table specific to that item-type with columns for each of the fields.

Possible structure
=============================

item-type
- type-id
- type-name

fields
- field-id
- field-name
- other field data (description, validation, etc)

item-fields
- type-id
- field-id

items
- item-id
- type-id

item-details
- item-id
- field-id
- value

Query Optimization Advice?
I am familiar with the VERY basics of MySQL (in other words, I am not an expert...), and I am currently working to create a simple search engine on our website. Users should be able to search a database of over 150,000 sheet music titles by simply inputting a keyword(s) phrase. That phrase should be searched into different tables at the same time but if the keyword phrase includes more than one word, any word need to be searched.

For example, let's say an user is looking for "Brahms violin concerto". The search engine should display all the records that matches all those entered keywords.

Here are the tables that need to be searched:

|title|composer|instruments|description|

and here is the query I am trying to work on (after have split the keyword phrase into separate keywords):

Quote:

SELECT title, composer, instruments, description, price FROM mydatabase WHERE (title REGEXP "(^| )brahms( |$)" OR title REGEXP "(^| )violin( |$)" OR title REGEXP "(^| )concerto( |$)") AND (composer REGEXP "(^| )brahms( |$)" OR composer REGEXP "(^| )violin( |$)" OR composer REGEXP "(^| )concerto( |$)") AND ((instruments REGEXP "(^|;| )brahms(;| |$)" OR instruments REGEXP "(^|;| )violin(;| |$)" OR instruments REGEXP "(^|;| )concerto(;| |$)") OR (description REGEXP "(^| )brahms( |$)" OR description REGEXP "(^| )violin( |$)" OR description REGEXP "(^| )concerto( |$)")) order by title like "%brahms%", title like "%violin%", title like "%concerto%", instruments like "%brahms%", instruments like "%violin%", instruments like "%concerto%"

The query works, but it takes too long to be executed... over 10 seconds! I am aware that REGEXP (like "LIKE") don't uses indexes, but I cannot find a different solution to match any possible keyword or part of it.

Any suggestion to optimize it? Or should I work on a completely different approach?

Performance Advice Required
I'm after some performance advice if anyone has some. I've a table and query described below. The table holds about 13 million records. Query times for the query are about 135 seconds, which is way to long for live queries. Does anyone have any suggestions on how to reduce this. I realise live queries to a database this size might have to be tackled by spliting into other tables etc, but other sites manage this, and I just don't know how. I have already sumerised the data as much as possible to minimise the rows.

dns_stats CREATE TABLE `dns_stats` (
`hits` mediumint(11) unsigned default '0',
`domainname` varchar(100) NOT NULL default '',
`datetime` date NOT NULL default '0000-00-00',
`server` tinyint(4) unsigned NOT NULL default '1',
KEY `datetime` (`datetime`),
KEY `datetimedn` (`domainname`(20),`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

select domainname, sum(hits) as thits,server, datetime from dns_stats where domainname like '.moc.tsettsrif%' and datetime >= DATE_SUB(NOW(),INTERVAL 30 DAY) group by datetime,server order by datetime

and explain shows

1,SIMPLE,dns_stats,range,datetime,datetimedn,datetimedn,23,N,7925,Using where; Using temporary; Using filesort

This table gets updated each night, so is only read from during the day. The queries are almost exclusivly reads. I tried tests without the summary and grouping but this only takes about 10 seconds off, so I'm guessing almost all the time is spent retrieving the initial records.

Table Structure Advice
I have two tables - Users and Groups. Pretty straightforward layout where the Users table has ID, last_name, first_name, etc... The Groups table is simply ID, name, description, etc... I want to include in my Users table a group_ids field. What makes this complex, is one user could be a member of just one or many groups, so I can't simply say User1 is in Group1. User1 may be in Group2, Group17,etc...

How does one (or how would you) apply this? I've thought about making the group_ids field a 'text' datatype and separating the group ids by comma or space., but for several reasons this doesn't seem to be the right way to do this.

Let me know if I haven't explained this well or if I should give an example of the data for clarification.

Complicated Join Advice
My tables look something like this:

FIGHTER
fighter_id
first_name
last_name
age
style

FIGHTS
fight_id
fighter1_id (key to FIGHTERS table)
fighter2_id (key to FIGHTERS table)
event_id (key to EVENT table)
winner (this field will contain either 1 or 2 indicating which fighter won)
win_round
win_time

codes...

I've been trying to figure out an appropriate query but can't seem to get it. The only solution I've come up with so far is to break it into multiple SELECT statements which seems to defeat the purpose of a relational database.

Newbie
I've written my site using .ASP, Javascript and an MS Access database. I'm now looking to 'upgrade' to MySql to solve any database issues I may have in the future but I haven't a clue about MySql. My questions are;
Does it cost anything?
What do I need?
How do I transfer my tables?
Will my ASP code work on MySql?

Newbie
I am a beginner to Mysql however i have worked with Oracle n i had a few questions regarding the syntax,
in oracle we use the number(1) how is it when compared with mysql .I have seen that one can use int but could it be possible also to restrict the nr of digits like int(1).I have tried this out n it does not seem to work.I would also like to know the the diff in varchar n varchar2() if it is the same as in oracle

Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations.

First, here's the situation.

I want to store information about all the cards in a Tarot deck.

In the deck, there are two types of cards; namely the major and minor arcana.

Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type.

For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table.

Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign.

Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters.

Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification.

So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.

Advice On Creating Categorie ID's
Hi (Sorry the title may be a bit confusing - only realised after submitting - and now cant change it!)

Sorry if this is a bit basic but i am a noob at MySQL and therefore may be going about this in the wrong way.

The situation I have is that i am trying to get users to record information against categories of work set by different departments, but also allow the user to create custom categories.

The numbers of categories created by the Departments may be fairly small (20 - 30 per Dept) and the same can be said for each user - except that the number of users may grow quite large - and therefore the number of user defined categories.

I think i need to therefore split this into two tables DeptCategories and UserCategories, but what i need is the user to only have one combined category list to work with when recording data... so therefore need to be able to to create a list of the users DeptCategories and their own UserCategories...

The alternative I gues is to have one table with a column(s) that identifies the dept and or the user that entered them... but this then raises the question of how do you list all the available categories for a particular user in the order of DeptCategories and then UserCategories (both in alphabeticcal order)

Any suggestion greatly recieved, as I would like to set the DB up correctly at the start so I dont have to alter tables etc.

Advice On Setting Up Address Database Please
(Before i start i feel obliged to point out my Noobie status so not to shock! )

As part of a larger poject i need to set up an name and address database.

Before i start i would very much appreciate some comments on the format of the primarry tables.

For example if i set first_name as VARCHAR(15), last_name VARCHAR(30) - this seems ok, but from experience what would people suggest as being realistic for the Address part..

Do i need several fields to allow for each line of the address:

MyHome
MyStreet
MyStreet2 (optional)
MyTown
MyCounty / State
MyPostCode

I have found several different stratergies for this such as VARCHAR with no max length.... is this a good idea?

Aslo UK post codes - i guess a VARCHAR of max 10 would suffice, but does any one know of an imput mask / regular expression to ensure valid imput?

Idea, Advice If Feasible (somewhat Off-topic)
Here is what I am facing. We need to be able to run some pretty intense queries on our existing MySQL database at work. The programmers say it will put too much stress on the servers and can't be done without a major overhaul, and its just a handful of users who need to do it.

What I want to do is build some client software that can do the queries. I want to take the data from the MySQL server, build it into a client, and let the end user run as many queries as they want. If they slow down their machine, fine, but they won't slow down the server. Plus most users are sitting on some very powerful hardware, so it should be able to handle it.

The issue is my coding skills are rather rusty. So I'm wondering, if I were to take on this project, what would be the best way to tackle it? What languages are best, is there something open source that is already available, etc. The end result has to go into Excel, because thats what my users can operate. Is my idea even feasible or will this be a giant pain?

I'm looking at creating a custom application that can sort/calculate/compare a database that is 1,000 to 5,000 rows in length. Its comparing hours/shift lengths for a large company over several departments. The user will not be adding addition data, just analizing what is already there. I can think of a lot of ways I might be able to do this, but I don't know what way would be easiest. Any advice would be helpful, this project is a long time from happening, if at all.

[Newbie] Localhost???
is there any way to connect to the localhost database on another server from a different server?

Newbie In MYSQL
I m a newbie in MYSQL.Would somebody pls tell me the following questions-

1. WHich is my original database after creating a Database(e.g. Student) in MySql .I mean in MSAcces DB I can find a .mdb file which is to be used.But in MySql which file to use. (If I create a database named- Student).Would I have to use the whole SQL folder including all files for a single DB.

2. I want to use that DB in MYSQL with JAVA. Which should be the driver String for MySQL.
I am new pls help me.
Edit/Delete Message

Please Help Newbie Upgrading From 4.1.11 To 5.0.x
I'm almost in tears here, am having a lot of trouble with the MySQL upgrade from 4.1.11 to 5.0.x. I have read Chapter 2 of the manual. I've read it more times than I care to admit. I've done Google searches. I've searched this forum as well as the usenet group. I'm stuck and I need to get this upgrade done. I must be the most stupid human on the face of the planet but I don't think the manual is very clear when it comes to explaining how to do the upgrade. Please take pity on me and help if you can, I'll be EXTREMELY grateful!

My environment: Linux RHE 3, the MySQL 4.1.11 is a non-RPM installation. The software is not installed under the default directory, it's in /opt/mysql/tst/4.1.11. I have many databases and many user accounts all password-protected.

Here is what I tried:

1. dumped all databases, including mysql.
2. make a backup of the whole 4.1.11 (MySQL softare) directory. Shutdown mysql 4.1.11.
3. can't tell exactly from the manual where the new software is supposed to go (is it supposed to overwrite the existing installation?). So I extracted the mysql-...tar.gz file into the /opt/mysql/tst directory. It created its own directory structure called "mysql-5.0.51a-linux-i686-glibc23".
4. I renamed the "mysql-5.0.51a-linux-i686-glibc23" directory to "5.0".
5. tried to start w/ bin/mysql_safe & and the server started and then stopped immediately.
6. Couldn't figure out what else to do and am running on the assumption that the "upgrade" is really just an "install" so I followed the installation instructions that say to run the mysql_install_db script.
7. Now I can start mysql_safe, hooray. I logged in with root, no password required.
8. I ran the mysql.sql file (the script produced when I dumped mysql out of 4.1.11) assuming this would put all the user accounts & passwords back into the new 5.0 version.
9. Logged into mysql using root and still not required to enter a password. However, am pleased to find the user accounts are in the database.

What am I doing wrong? How do I install the new version of MySQL so that the user account information is preserved?

Is Query Possible? (Newbie)
I have 2 tables used for an online calendar...

first table fields: primary_key , start_date, event_name, event_description
second table fields: primary_key, end_date

Tables fields are shortened and can't be changed.

My second table only contains events that have a end date. I want to create
a query that will take all the fields in. If no end_date exists then set to
NULL. Been playing with it all day. Hoping some advance function exists. I
thought of using a temp table but there must be a better way.

Newbie Question.
I am trying to install an accounting package called web-erp on a Linux
system. Early on I need to establish a user with the name of
web-erp@localhost. Unfortunately my copy of MySQL seems to balk at
taking a user name with a hyphen in it,. I have tried enclosing the
user name in single quotes but that generates another kind of error.
Here is the exact statement:

GRANT ALL PRIVILEGES ON *.* TO web-erp@localhost IDENTIFIED BY
'passname' WITH GRANT OPTION;

This statement fails, but the same statement without the hyphen
succeeds.

Newbie Question--please Help
I'm setting up mysql for the first time, and running on xp pro. I have
no experience with mysql or databases in general, so I'm quite confused
about some things--I hope someone will be able to help me. I'm reading a
book on php and mysql and in the chapter on post-installation, they're
showing you how to create a password for the root user. They don't
really explain this well, so please bare with me:

1) the username and password for mysql refers not to the windows
username, but something specific to the mysql server correct?

2) the author mentions that when you try to connect to the mysql server
via the mysql monitor, you will have to put in your mysql server
username and password. He says that by default, a user called root is
created for you but not given a password. You should then access the
mysql database (which I assume stores all your user information) with
the root user as follows:

mysql -u root mysql

He then gives a series of commands to enter:

mysql>SET PASSWORD FOR root@localhost=PASSWORD("new password");
mysql>SET PASSWORD FOR root@"%"=PASSWORD("new password");
mysql>FLUSH PRIVILEGES;

the first one works fine and I assigned a password. However, the second
one doesn't work--I get an error message: "error 1133: can't find any
mathing row in the user table" -- I don't understand why this is
happening. It is the same user root right? Incidentally, why am I
entering two passwords for the same user (root)? Is this if I'm running
mysql monitor client app from a remote computer on the internet?

3) do the above commands assign specific usernames and passwords to the
server itself, or to a specific database within? Do all databases
require a separate set of usernames and passwords or is the mysql
database (which this info is being placed in) a special kind of databsee
used just for storing permissions and things like that--does the server
automatically know to check for a database called "mysql" in determining
who has permissions for what?

4) In the line:
mysql -u root mysql
he says that you are accessing the mysql database. How do I know I'm in
the mysql databse if the only thing the prompt just reads "mysql>" which
is just the standard mysql monitor prompt right? I'm able to get into
the mysql monitor without typing any username "mysql" dumps me in
"mysql>" -- but I assume then that I won't be able to access any
specific db info without putting in a username/password, right?

HUH--I probably have a million other questions, but this is all I can
think of right now. I've searched high and low on the Internet and
otherwise for answers but can't seem to get a good explanation of this
stuff. Will someone please explain all about usernames and passwords and
permissions and stuff in mysql--thanks and sorry for the long post?

Newbie Question
I want to make a form in mysql. To explain myself further its like when you go to a used car website and it has a text box with a list of different manufacturers and you keep on filling in different text boxes until you get a specific car. Is it possible to do that in mysql??

Newbie Question
I am trying to install mysql on XP to use with PHP for a web site that I =
am working on.
I have done everything that the books are telling me but I can't seem to =
get either PHP or Mysql to speak to each other. When I do the tests on =
frontpage, it doesn't seem to do anything.
On the Mysqladmin it tells me that my odbc driver 3,.51 not found is =
that a problem and how do I get the driver?
Also when I go to the database section and right click I don't get the =
create database how do I get this?

MySQL Newbie
I am a complete newbie to MySQL. I downloaded the MySQL 4.1.1-alpha
onto my windows 2000 machine and have been trying to figure it out
ever since. I followed the installation directions and managed to do
everything up to running mysql_install_db. cmd prompt wouldn't run the
..sh file, I don't have shell.exe on my machine and maybe that is the
problem, but I couldn't find any documentation about it.
Anyway, I can run the server and manipulate 'test' table, but I can't
log on as a user or administrator, or perform any administrative
tasks. When trying anything I get the "access denied for user
''@'localhost' " 1044 error
I am stuck, because I do not entirely understand the structure of
mysql (what does what exactly) and so I find it hard to try to figure
out the cause for my problem

MySQL Newbie
I have started to plan a conversion of my flat file system, to one run by MySQL.
If anyone would please answer these questions, it would clear up a lot of brain fogging around me and I would be very grateful.
1. I will have a table which in one of its fields, lists a series of items (alphabetical names). Where I want to retrieve only those beginning with A, can the MySQL query do this or should I bring in all the values and then process them with php/perl?
2. where I have a MySQL Db hosted on mydomain.com, can I enable other domains to read from it (to power their sites), without compromising security? (All the other sites would be on my own server).

Newbie Question
I am at the point in testing where I need to create new tables to keep things 'clean.' I have a users table that has userid in it, auto incrementing as you would expect. Now I want to add another table to deal with some other things. I have seen how to extract data using joins, but now I want to know how I can relate this new table with the users one(and put the userid into the new table)

Need Advice On Switching From Sql2000 & Access To Mysql
Im a coldfusion programmer, so it all started for me learning on access db's. Then sql 2000. Along the way, I started installing the cms systems and learning how mysql | phpmyadmin worked and really started to prefer this.

The .htaccess control is whats making my decision for me. I just cant control urls enough using IIS & coldfusion.

I have found a host that supports coldfusion running on the lamp platform.

To convert the sites, I figure 2 major tasks:
1. Converting the databases over to mysql
2. Making sure all my queries function properly.

I figure converting wont be too bad, but Im wondering how much structural differences exist between T-sql and the syntax mysql uses.

At most, my queries to convert are 3 tables using LEFT OUTER JOIN or INNER JOINS.

Any feedback on the process will be most appreciated
(I cant wait to get off IIS)

Advice On Table Setup To Maximize Efficiency
My table set is is garbage .. I knew this when I created the table a couple of years ago. Now that I have tonnes of data in it the queries are taking forever. So it's about time I constructed a more efficient table. I am turning to this forum for some advice.

My table stores RSS feed content in it -- every 15 minutes a cron job INSERTs data from 300+ feeds and dumps it into the table.

The table structure consists of:

id
dateUnix
headline - headline of the article
permalink - URL to the article
body - TEXT type - body of the article
author - person who wrote the article
authorURL - URL to the person's website
favico
sitetype - I categorize the types of sites (0 = news media, 1 = blogs, 2= videos, 3= podcasts)
vote - users vote on stories
verified - 1 = verified, 0 = censored/banned story
newsCat - 20 different categories
newsSubcat - 50 different subcategories

I think the best way to make this table more efficient would be to break it up into 3-4 different tables using the id as the key.

ps: my cron job checks for duplicate entries and does not insert duplicates -- so duplicate stories are not entered

DB Moving To New Server, Requesting Upgrade Advice
I currently have an older dedicated server with about 10 MySQL 3.23 databases on it. All but one of the DBs are for Wordpress. One is for Invision Power Board. The server is in need of an upgrade because it isn't handling the traffic very well so I've aquired a new, and much more powerful one.

The new server has MySQL 4.1.20 on it so right away I'm in trouble because I can't seem to figure out how to get my tables converted. I'm trying to follow the instructions at http://dev.mysql.com/doc/refman/4.1/en/upgrading-to-arch.html, specifically:

"You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.gz"

But when I try to do this with any of my databases while logged in as ROOT I get the following:
"Got error: 1045: Access denied for user 'root@localhost' (using password: NO) when trying to connect."

I tried using the help file to figure out how to specify the password but either I couldn't get the syntax correct or it just wasn't working. So, my first request would be for a little guidance here if possible.

Assuming that problem is solvable, I have question. If I'm already going to have to import the data into the new 4.x format, would it make more sense to have the MySQL on the new server upgraded to 5.x now - while it's still brand new and before it gets all loaded up?

What would be the benefits or drawbacks to doing so? It seems to me that since I have no experience with this that it would make sense to do the upgrade to 5 now so I don't have to go through the pain of another DB upgrade down the road so soon.

Newbie Can't Upload Dump Pls Help If You Can
This is the error I'm getting. when trying to upload in phpmyadmin.

PHP

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.8.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 22, 2006 at 12:48 PM
-- Server version: 4.1.18
-- PHP Version: 4.4.2
--
-- Database: `askdivas_ex`
--
CREATE DATABASE `askdivas_ex` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL said: Documentation
#1044 - Access denied for user 'askdivas'@'localhost' to database 'askdivas_ex'

Connect One Table To Another. Newbie Here....
iÂ´m using phpMyAdmin.

Hi have two tables: cars and constructor how can i make so that when inserting values in my cars table i will have lets say a dropdown list with my constructors?

Like having a products table and one table just for categories and when inserting a new product i would have the categories list to select...

Newbie: MySQL Locally?
This may be a dumb question but I am a newbie and well, don't know the answer.

Is there a way to set-up/test a MySQL database locally on my machine?

Newbie: Creating Users?
I'm tring to create new users. I've read the docs and still kee recieving the error:

mysql> CREATE USER joeuser IDENTIFIED BY PASSWORD somepass;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER joeuser IDENTIFIED BY PASSWORD somepass' at line 1

How can I check to see if I have access to create users?
I created tables, I don't understand why I can't create users.

13.5.1.1. CREATE USER Syntax
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists.

The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the keyword PASSWORD. See Section 13.5.1.3, â€œGRANT and REVOKE Syntaxâ€.

Extreme Newbie (using A Mac) Needs Basic Help
I bought the book "MySQL Tutorial" and immediately I am confused. I installed MySQL on my Mac, but immediately I am having questions regarding it's use. Am I to type these commands into the Terminal?

Creating A List Of Repeated Search Words, Advice?
I store the search terms people have been using for the last year in a mysql database. (migrated to Mysql 5 recently).

Now I want to try and find out which terms have been used the most, in order that I can try and understand what users might be looking for.

I have no clue what word describes this kind of self indexing, or how to start doing this...

Can anyone give me a pointer on where to start looking?

More:

I also stored a datestamp, so would like to know month by month what were the search terms used.

Heres a snap of the meta:
Table : search_terms
search_ID int(11) auto_increment
daydate int(11)
term varchar(40)

Only has 135k rows.