mysql Database

Gareth37

Hi,

Ive downloaded and installed MYSQL 5.0 and using the MYSQL command line everything is going ok.

Now, I want to access the database from another PC using a PHP or PERL script.

1. How do I know what my MYSQL username is? It never gave me the option of entering one during installation.

2. Is it possible to access the other PC's database using PHP or PERL?

$connection_name =mysql_connect(“Host_IP_Address”, “username”, “password”)
or die("Could not Connect");

mysql_select_db=(“database_name”, $connection_name);
or die("Could not Select Database");

$query="Select * from table_name”;

$result = mysql_query($query);

$mysql_close($connection_name);

misterq

1. If it didn't ask you when you installed, then you are probably using the username "root" with no password. If you download the MySQL administrator GUI you can do exciting things like create new users

2. Yes you can access the database from another pc assuming you know the IP address of the pc that is running MySQL, have a valid username and password and ensure that there are no firewalls blocking access.

MySQL runs on port 3306 if you need to open this to allow access.

robydrupo

If you'll look at this page: http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html
you'll find a guide to manage mysql users from a shell. (if you've got another version of mysql, you can choose that in the high-left corner).

hi.
Roberto

philologos

Gareth37 wrote:

$connection_name =mysql_connect(“Host_IP_Address”, “username”, “password”)
or die("Could not Connect");

mysql_select_db=(“database_name”, $connection_name);
or die("Could not Select Database");

$query="Select * from table_name”;

$result = mysql_query($query);
[COLOR=Red][B]
$i = 0;
$numrows = mysql_num_rows($result);
while ($i < $numrows) {
$tablename = mysql_result($result, $i, "tablename");[/B][/COLOR]
[COLOR=Silver][I](continue for any subsequent tables format is mysql_result(result source, line, table name);[/I][/COLOR]
[B][COLOR=Red]$i++;
}[/COLOR][/B]
[COLOR=Red][B] mysql_close($connection_name);[/B][/COLOR]

just a small error in your code and you need to do more after $result, maybe a while $i < $numrows or something similar to grab all the data out.
Red stuff is what i've added basically.

As for setting the password for the root user (on Linux if you are using a Windows server it's different)
mysqladmin -u root password 'passwordyouwant'

mysql -u root -p
use this to login after you have set the root password and it will prompt you for your password.. (on Linux if you are using a Windows server it's different)

democrates

What robydrupo and jakkass said and also
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html
and follow instructions depending on your os.

I remain baffled as to why setting the root password isn't part of the install process, it's one of the few free software packages that defaults on install to no security. That aside it's the dogs knackers.

Gareth37

thanks.

i also have a question about tables in databases:

id | col2 | col3 | col4
1 | john | 23 | dd
2 | john | 23 | ff
3 | john | 24 | gg


In the above table the 1st column is the unique primary key. Each entry/row is unique.

My question relates to the fact that there is a relationship between many entries. For example john is in every entry in coumn 2 while 23 is in 2 rows in col 3. This is how Im doing it:

insert into tablename ('1','john','23','dd');

I do yhis for each row. Is there a more efficient way of doing it since many entries overlap for col 2 and col 3?

Thanks

G

robydrupo

It depends of what is the reality you want to represent. For example if you know that there is a functional dependence(http://www.sitepoint.com/article/database-design-management) in col2 and col3 (if there is john then there is also 22, always), you should think to create a new table to store john and 22, and put into your table only the key of that table.
But I don't know if I understood you question correctly!

hi,
Roberto

Gareth37

robydrupo wrote:

It depends of what is the reality you want to represent. For example if you know that there is a functional dependence(http://www.sitepoint.com/article/database-design-management) in col2 and col3 (if there is john then there is also 22, always), you should think to create a new table to store john and 22, and put into your table only the key of that table.
But I don't know if I understood you question correctly!

hi,
Roberto

Im not sure if i underdtand the question fully my self.:D

What Im asking I suppose; is there some way of entering john only once while adding the changing data each time keeping in mind that there may be other entries in column 2 too such as james etc. Is it possible to enter john only once without creating a new table for john.?

democrates

i also have a question about tables in databases:

id | col2 | col3 | col4
1 | john | 23 | dd
2 | john | 23 | ff
3 | john | 24 | gg


In the above table the 1st column is the unique primary key. Each entry/row is unique.

My question relates to the fact that there is a relationship between many entries. For example john is in every entry in coumn 2 while 23 is in 2 rows in col 3. This is how Im doing it:

insert into tablename ('1','john','23','dd');

I do yhis for each row. Is there a more efficient way of doing it since many entries overlap for col 2 and col 3?

This is a key(sorry) difficulty in learning the art of relational databases.

While it's convenient to use an auto-incrementing primary key column in a table it by no means protects against the rest of the data in the record being duplicated. Just as in coding, know your data.

In your example let's say we have a table of drag queens with the columns id, firstname, cocktails required before performances, and bra size.

As it is you are lucky enough to have enough information to distinguish these people, but if another john requiring 23 cocktails to perform and wearing a dd bra is added you're in trouble. So then you might decide to add date of birth. But what if the new john has the same dob? Ok add place of birth. See where this is going?

The national PPS would be dead handy, but even that isn't infallible as we've seen from electoral and social welfare fraud. So what then, fingerprints? Vocal scans? Retina scans? DNA? IF you store any of these you create a serious privacy risk and also face huge user resistance against having such data, so they're all non-starters. Email address or mobile number could be useful because each one can reasonably reliably identify an individual.

My bank once posted a lodgement to my Dad's account instead of mine because the teller did a search by name but didn't check the account number on my lodgement slip versus my dad's on the screen, that's something else to watch out for on your user interface design, make sure potential duplicates are highlighted for fuller scrutiny before they are committed.

Your personal identity policy must match what's at risk if you get a duplicate entry with strong enough identity information, and with appropriate security levels.

I'm guessing you may have been getting at another aspect though. Let's say you've solved the identity issue by adding a new column for email address, then John emails you and says he's gone into rehab and now only requires 10 cocktails before performing. His record is already there so you only need to update it;

UPDATE `queens` SET `cocktails` = 10 WHERE `email` = 'john@wagon.com';

Is that any use to you?

The Corinthian

Gareth37 wrote:

I do yhis for each row. Is there a more efficient way of doing it since many entries overlap for col 2 and col 3?

It’s actually pretty much impossible unless we know more about what your data represents.

For example, is John one person and the number is his ongoing score in a game or are the entries for John referring to different people called John and the number denote their ages? If the former we create the individual John as a separate single entry in another table and replace col2 in our original with an ID look-up to that table. That is (I’m doing this from memory so forgive me if I make a mistake):

> SELECT * FROM tbl_people;
id | name
 1 | john

> SELECT * FROM tbl_score;
id | col2 | col3 | col4
 1 |  1   | 23   | dd
 2 |  1   | 23   | ff
 3 |  1   | 24   | gg

> SELECT name, col3, col4 FROM tbl_people, tbl_score WHERE tbl_people.id = col2;
name | col3 | col4
john | 23   | dd
john | 23   | ff
john | 24   | gg

However, if it's the latter case you probably can't really create the same relationship. Ultimately, without knowing the entity relationships, it is impossible to normalize your database.