begginer's simple PHP select statment

[Alex Glaros]

I need help on my basic table connection syntaxt using PHP. I call
the php from a web browser url_name_goes_here/test.php. The database
name is tennis and the user name is tennis.

If I type the commands directly from the MySQL> mode, my access works
fine (see example 1 below). But when I try to access the table from
PHP, I have a syntaxt problem (see example 2 below). Can anyone
please type up the correct PHP syntaxt for me? The 3rd example works
but I don't know how to translate that into a select statement for the
table.

Finally, after I get it working, how do I keep my password out of
sight? if I forgot to put an index file in the directory, wouldln't all
of the files be visible from a surfer's browser?

Thanks!

Alex Glaros

EXAMPLE 1 (from within MySQL - this works correctly)
--------------------------------------------------------------------------------------------------------------------------------------------------------------

/usr/local/mysql3/bin/mysql -h db1.pacific.net -u tennis -p
Enter password: ( get prompted for my password here, then sucessfully
get to mysql> prompt)
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 123456 to server version: 3.23.57
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use tennis
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tourneys;
| tourney_id | tourney_name | minutes_per_game | td_id |
multi_venued_y_or_n | date_tourney_begins | date_tourney_ends

Then I get all the rows for table tourneys correctly displayed
--------------------------------------------------------------------------------------------------------------------------------------------------------------

EXAMPLE 2 (Web browser calls PHP - this doesn't work)

<?
$db_username = 'tennis';
$db_password = 'mypassword';
$db_hostname = 'db1.pacific.net';
$db_connect = mysql_connect($db_hostname, $db_username, $db_password)or
die("Unable to connect to MySQL");
$db_select = mysql_select_db('tourneys', $db_connect)or die("Unable to
select tourneys");
while ($row = mysql_fetch_array($result)) {
printf "$row[tourney_id] $row[tourney_name] $row[td_id]";
}
mysql_close($db_connect);
?>

HERE'S THE ERROR MESSAGE I GET:
Parse error: parse error, unexpected '\"' in
/server_path_name/public_html/test.php on line 8
--------------------------------------------------------------------------------------------------------------------------------------------------------------

EXAMPLE 3 (Web browser calls PHP - this does work but I don't know how
to get the select statement above to work)

<?php
mysql_connect("db1.pacific.net", "tennis", "mypassword");
$result = mysql_list_tables("tennis");
$num_rows = mysql_num_rows($result);
for ($i = 0; $i < $num_rows; $i++) {
echo "Table: ", mysql_tablename($result, $i), "\n";
}
mysql_free_result($result);
?>

[News Me]

Alex Glaros wrote:

I need help on my basic table connection syntaxt using PHP. I call
the php from a web browser url_name_goes_here/test.php. The database
name is tennis and the user name is tennis.

If I type the commands directly from the MySQL> mode, my access works
fine (see example 1 below). But when I try to access the table from
PHP, I have a syntaxt problem (see example 2 below). Can anyone
please type up the correct PHP syntaxt for me? The 3rd example works
but I don't know how to translate that into a select statement for the
table.

Finally, after I get it working, how do I keep my password out of
sight? if I forgot to put an index file in the directory, wouldln't all
of the files be visible from a surfer's browser?

Thanks!

Alex Glaros

EXAMPLE 1 (from within MySQL - this works correctly)
--------------------------------------------------------------------------------------------------------------------------------------------------------------

/usr/local/mysql3/bin/mysql -h db1.pacific.net -u tennis -p
Enter password: ( get prompted for my password here, then sucessfully
get to mysql> prompt)
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 123456 to server version: 3.23.57
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use tennis
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tourneys;
| tourney_id | tourney_name | minutes_per_game | td_id |
multi_venued_y_or_n | date_tourney_begins | date_tourney_ends

Then I get all the rows for table tourneys correctly displayed
--------------------------------------------------------------------------------------------------------------------------------------------------------------

EXAMPLE 2 (Web browser calls PHP - this doesn't work)

<?
$db_username = 'tennis';
$db_password = 'mypassword';
$db_hostname = 'db1.pacific.net';
$db_connect = mysql_connect($db_hostname, $db_username, $db_password)or
die("Unable to connect to MySQL");
$db_select = mysql_select_db('tourneys', $db_connect)or die("Unable to
select tourneys");
while ($row = mysql_fetch_array($result)) {
printf "$row[tourney_id] $row[tourney_name] $row[td_id]";
}
mysql_close($db_connect);
?>

HERE'S THE ERROR MESSAGE I GET:
Parse error: parse error, unexpected '\"' in
/server_path_name/public_html/test.php on line 8

<snip>

mysql_select_db() is used to select the database, not the table. It is
equivalent to the "USE tennis" you did from the mysql client, so you
need to change "tourneys" to "tennis" in the mysql_select_db call. Also,
you aren't executing a query. You need this between the mysql_select_db
and the while():

$result = mysql_query("SELECT * FROM tourneys", $db_connect);

NM

[News Me]

Alex Glaros wrote:

Finally, after I get it working, how do I keep my password out of
sight? if I forgot to put an index file in the directory, wouldln't all
of the files be visible from a surfer's browser?

As long as your web server is properly configured to do PHP, users won't
be able to see your PHP code. If PHP is broken, that's another story.

Check the PHP manual re MySQL. I seem to recall there being environment
variables where you can put the MySQL user/pass into. That way if you
break PHP, you aren't exposing your code. You should also check the
MySQL manual, as you can put a file called ".mysql_rc" (or something
like) that in the homedir of the user you are accessing MySQL as. You
can put the password in that file as well.

NM

[News Me]

> Alex Glaros wrote:

EXAMPLE 2 (Web browser calls PHP - this doesn't work)

<?
$db_username = 'tennis';
$db_password = 'mypassword';
$db_hostname = 'db1.pacific.net';
$db_connect = mysql_connect($db_hostname, $db_username, $db_password)or
die("Unable to connect to MySQL");
$db_select = mysql_select_db('tourneys', $db_connect)or die("Unable to
select tourneys");
while ($row = mysql_fetch_array($result)) {
printf "$row[tourney_id] $row[tourney_name] $row[td_id]";
}
mysql_close($db_connect);
?>

HERE'S THE ERROR MESSAGE I GET:
Parse error: parse error, unexpected '\"' in
/server_path_name/public_html/test.php on line 8

News Me wrote:
mysql_select_db() is used to select the database, not the table. It is
equivalent to the "USE tennis" you did from the mysql client, so you
need to change "tourneys" to "tennis" in the mysql_select_db call. Also,
you aren't executing a query. You need this between the mysql_select_db
and the while():

$result = mysql_query("SELECT * FROM tourneys", $db_connect);

NM

Too much Holiday cheer....

Your printf line won't work either. Assuming "tourney_id",
"tourney_name", and "td_id" are columns in TENNIS.TOURNEYS, then you
should change your while() and printf() to this:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "$row{'tourney_id'} $row{'tourney_name'} $row{'td_id'}\n";

The MYSQL_ASSOC causes the row to be returned as an associative array
rather than an ordinal array, which is required if you want to refer to
columns by names. Otherwise you must use $row[0] for the first column,
$row[1] for the second, etc.

You should read up on print and printf, as you are using printf in your
example as if you were using print. The print statement I show above is
considered bad form (at least in perl it is), but it works.

NM

[Alex Glaros]

It works!

I changed the print row so that the single quotes were removed and I'm
all set.

print "$row[tourney_id] $row[tourney_name] $row[td_id]\n";
Thanks for showing me the basics.

Alex Glaros

News Me wrote:

Alex Glaros wrote:

EXAMPLE 2 (Web browser calls PHP - this doesn't work)

<?
$db_username = 'tennis';
$db_password = 'mypassword';
$db_hostname = 'db1.pacific.net';
$db_connect = mysql_connect($db_hostname, $db_username, $db_password)or die("Unable to connect to MySQL");
$db_select = mysql_select_db('tourneys', $db_connect)or die("Unable to select tourneys");
while ($row = mysql_fetch_array($result)) {
printf "$row[tourney_id] $row[tourney_name] $row[td_id]";
}
mysql_close($db_connect);
?>

HERE'S THE ERROR MESSAGE I GET:
Parse error: parse error, unexpected '\"' in
/server_path_name/public_html/test.php on line 8
News Me wrote:

mysql_select_db() is used to select the database, not the table.

It is equivalent to the "USE tennis" you did from the mysql client, so you need to change "tourneys" to "tennis" in the mysql_select_db call. Also, you aren't executing a query. You need this between the mysql_select_db and the while():

$result = mysql_query("SELECT * FROM tourneys", $db_connect);

NM

Too much Holiday cheer....

Your printf line won't work either. Assuming "tourney_id",
"tourney_name", and "td_id" are columns in TENNIS.TOURNEYS, then you
should change your while() and printf() to this:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "$row{'tourney_id'} $row{'tourney_name'} $row{'td_id'}\n";

The MYSQL_ASSOC causes the row to be returned as an associative array

rather than an ordinal array, which is required if you want to refer to columns by names. Otherwise you must use $row[0] for the first column, $row[1] for the second, etc.

You should read up on print and printf, as you are using printf in your example as if you were using print. The print statement I show above is considered bad form (at least in perl it is), but it works.

NM

[Glenn]

News Me wrote:

Alex Glaros wrote:

Finally, after I get it working, how do I keep my password out of
sight? if I forgot to put an index file in the directory, wouldln't all
of the files be visible from a surfer's browser?

As long as your web server is properly configured to do PHP, users won't
be able to see your PHP code. If PHP is broken, that's another story.

Check the PHP manual re MySQL. I seem to recall there being environment
variables where you can put the MySQL user/pass into. That way if you
break PHP, you aren't exposing your code. You should also check the
MySQL manual, as you can put a file called ".mysql_rc" (or something
like) that in the homedir of the user you are accessing MySQL as. You
can put the password in that file as well.

NM

(Correct me if I'm wrong, but from what I understand...)

Additionally, you can define a user specifically for your host
(user@localhost) with the access permissions that you need with no
password. Once again, from what I understand, that user _must_ be
accessing from localhost to work, which seems pretty secure to me.

If there are any hacks on this that should be addressed, please let me
know.. just a beginner here myself ;)

Thanks,
Glenn