PHP-MySQL Connect

[gdejong]

Help!
I don't consider myself a newbie with MySQL, but the PHP-MySQL connection is baffling me! So here is my PHP newbie question: When I try to access already built and populated database tables from my subdomain and run the following code fragment from my browser, I get no exceptions but the only thing echoed *verbatim* is the items following the "0)" in the conditional if statement. When I try to remotely access the database it Pings but no connection. I have created a user with "?" as host, and have tried every combination of username, password, hostname from the administrator access on down. What dunderheaded thing am I doing wrong?

$host = "localhost";
//...or "?" or URL, both ours and APIS
$user = "gdejong";
// ...or any defined users
$pass = "";
// ...or any pre-established p/w
$db = "oldmilwaukee_buildings";
// this is the only db, it doesn't work with table names either

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// create query
$query = "SELECT * FROM buildings";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {

echo "<table cellpadding=10 border=1>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>".$row[0]."</td>";

// etc.....




Since I am a newbie to PHPI am probably missing something--magic quotes? session variables? --I am at a loss. Thanks beforehand...

[Matt]

You won't be able to access the database remotely until you add a grant for user gdejong on host % (% verbatim). By default database grants are applied to users connecting locally on the server. PM me or post here the URL to your example script on the server.

I took a look at your buildings table and here are a few helpful recommendations:

(a) you might want to NOT NULL the building_name column as there are a few empty records in there with blank building names
(b) if there is only one instance of the building_name in the table for each building, make that a unique column
(c) year_built/year_demolished should be of type int(4), not varchar. For small scale databases it really doesn't matter how you store the data, but 1999 will be smaller in terms of storage space to store if it's a number (int type) rather than a string (varchar type). Plus it will be quicker to search through (e.g. select building_name from buildings where year_demolished > 2000) as no type casting is involved.

If you're making a small site then the performance hits are negligible, but it's always wise to start off with good practices early on... otherwise it will return to bite you in the rear when you least expect it. You can make those changes through phpMyAdmin in the control panel.