[RESOLVED] Trouble Accessing Database

[mousebear]

MySQL Server 5.0
phpMyAdmin 2.10.0.2
IE7

I am a total newbie. I found your Newbies FAQ thread very helpful...

I can create mySQL databases, tables, and records using phpMyAdmin. I seem to be able to connect to the databases, because I don't seem to be getting error messages at that point in my basic php scripts, but I can't seem to see or do anything with the data, or make tables, or anything else. I've been going a little crazy re. this for about a week.

Here's my script (genericized the login information):

<?php
  // Initialize Database Variables
  $DbHost = 'localhost';
  $DbUser = 'myusername;
  $DbPassword = 'mypassword';
  $DbName = 'mydatabasename';

  // Connect to MySQL Server and Verify Connection

  $Db = mysql_connect($DbHost, $DbUser, $DbPassword);
  if (!$Db)
  {
    die ('Error: Could not connect to database server.');
  }

  // Open MySQL Database and Verify Connection

  $DbSelect=mysql_select_db($DbName);
  if (!$DbSelect)
  {
    die ('Error: Could not connect to database.');
  }

  // Query the Database and Verify Query OK

  $query = "SELECT * FROM 'judges'";

  // Fetch and display the results
echo 'Did we get this far?' . '<br />';

   while($row = mysql_fetch_array($query)){ 
//Create the HTML table 

echo "<table><tr><td>JudgeName: ".$row[judgeName]." eMail:".$row[judgeEmail]."</td></tr></table><br />";  

} 

  // Close Database Connection
  mysql_close($Db);
?>  

Any ideas? I have written this lots of ways, including using a query to connect to the DB. I tried creating tables at first, but then I backed off to just trying to see existing data, and nothing there. I can see the "Did we get this far?" echo, which I move around to find out where the problems seem to be. There are 2 rows of records in this table 'judges'. There are only 2 fields: judgeName and judgeEmail. judgeName is the primary key. Both are CHAR(100). Collation is latin1_swedish_ci. I believe my PhP setup is for local connection only.

p.s. I'm not out to make a table, and realize the TABLE tag should probably be outside the while function, but I thought I should use your FAQ code as closely as possible until I get past this point.

[NogDog]

You have not actually executed your query -- you just assigned it to a variable. Try using this pattern:

$query = "SELECT * FROM 'judges'";
$result = mysql_query($query) or die("Query failed ($query) - " . mysql_error());
if(mysql_num_rows($result))
{
   echo "<table>\n";
   while($row = mysql_fetch_assoc($result))
   {
      echo "<tr><td>JudgeName: ".$row['judgeName']." eMail:".$row['judgeEmail']."</td></tr>\n";
   }
   echo "</table>\n";
}
else
{
   echo "<p>Sorry, no data found in table.</p>\n";
}

PS: Note that I added quotes around your array indexes (e.g.: $row['judgeName']). While your code will often work without them, it can potentially result in hard-to-debug issues. See Why Is $foo[bar] Wrong? for more info.

[mousebear]

Thank you, thank you. Omitting mysql_query() was dumb, but not what killed the other 19 different ways I tried this. When I added the query statement I got the following:

Query failed (SELECT * FROM 'judges') - 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 ''judges'' at line 1

I took the single quotes out from around 'judges', and I finally got results. Here's my revised code:

<?php
// Initialize Database Variables
   $DbHost   = 'localhost';
   $DbUser   = 'myusername';
   $DbPwd    = 'mypassword';
   $DbName   = 'mydatabasename';

   $Db       = mysql_connect($DbHost, $DbUser, $DbPwd) or die ("Error: Connection Failed - " .mysql_error());
   $DbSelect = mysql_select_db($DbName) or die ("Error: Could not select database " .$DbName);
   $query    = "SELECT * FROM judges";
   $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 

   if(mysql_num_rows($result)) 
   { 
   echo "<table>\n"; 
   while($row = mysql_fetch_assoc($result)) 
     { 
     echo "<tr><td>JudgeName: ".$row['judgeName']." eMail:".$row['judgeEmail']."</td></tr>\n"; 
     } 
   echo "</table>\n"; 
   } 
   else 
   { 
   echo "<p>Sorry, no data found in table.</p>\n"; 
   } 
// Close Database Connection
   mysql_close($Db);
?>

Do you know why this happened? My O'Reilly book, and a lot of other on-line references, put single quotes around the tablename... Is this something new to MySQL 5.0?

[leatherback]

Hi,

The single quotes are actually not single quote,afak, but back ticks:

single quote: '
back tick: `

In any case: They are not required as long as you have no 'special' characters in your column names

[mousebear]

Thanks about the backticks. Would you mind looking into 1 more thing: the place where I started a long time ago. I am trying to drop and create a table.

The connection part is the same as before, which works. So does this mean I have a priveleges problem?

<?php
// Initialize Database Variables & Connect
   $DbHost   = 'localhost';
   $DbUser   = 'pacifica';
   $DbPwd    = 'blue24';
   $DbName   = 'pacifica4hphotography';
   $Db       = mysql_connect($DbHost, $DbUser, $DbPwd) or die ("Error: Connection Failed - " .mysql_error());
   $DbSelect = mysql_select_db($DbName) or die ("Error: Could not select database " .$DbName);

// Drop Existing Table and Create a Clean Structure
   $query    = "DROP TABLE IF EXISTS judges; 
                CREATE TABLE judges ( 
                             judgeName CHAR(100) NOT NULL ,
                             judgeEmail CHAR(100) NOT NULL ,
                PRIMARY KEY (judgeName)) ENGINE = InnoDB ;";
   $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 

  } 

// Close Database Connection
   mysql_close($Db);
?>

Here are the results:

Query failed (DROP TABLE IF EXISTS judges; CREATE TABLE judges ( judgeName CHAR(100) NOT NULL , judgeEmail CHAR(100) NOT NULL , PRIMARY KEY (judgeName)) ENGINE = InnoDB 😉 - 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 '; CREATE TABLE judges ( judgeNa' at line 1

[bradgrafelman]

With the MySQL extension, you can't execute multiple queries at once. Separate the 'DROP TABLE IF EXISTS' and 'CREATE TABLE' queries into two separate queries (e.g. two separate calls to mysql_query()).

[mousebear]

Thanks everyone. That was it. Someone who does a lot of PHP gave me an old initialization script where they executed those multiple queries. I never would have figured this out...

I now can connect, drop a table, recreate it, insert data, and display it. You may hate the way I inserted the data. If so, I'm all ears. I think I'm mostly going to have my data posted from web forms, so it shouldn't be a big deal. I just wanted to have some data in there to check. I'm sure I'll want some good ideas for inserting records later, but there appears to be a lot of info for how to do that online. Thanks again...

<?php
// Initialize Database Variables & Connect
   $DbHost   = 'localhost';
   $DbUser   = 'myusername';
   $DbPwd    = 'mypassword';
   $DbName   = 'mydbname';
   $Db       = mysql_connect($DbHost, $DbUser, $DbPwd) or die ("Error: Connection Failed - " .mysql_error());
   $DbSelect = mysql_select_db($DbName) or die ("Error: Could not select database " .$DbName);

// Drop Existing Table and Create a Clean Structure
   $query    = "DROP TABLE IF EXISTS judges ;"; 
   $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 
   $query    = "CREATE TABLE judges ( 
                             judgeName CHAR(100) NOT NULL ,
                             judgeEmail CHAR(100) NOT NULL ,
                PRIMARY KEY (judgeName)) ENGINE = InnoDB ;";
   $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 

// Add some data to the Table
   $arrayRecordsAll = array ( 
      array ("George Washington", "washatgmail.com"), 
      array ("Thomas Jefferson", "jeffatgmail.com"), 
      array ("John Adams", "adamsatgmail.com") 
   ); 
   foreach ($arrayRecordsAll as $arrayRecord) {
      $query    = "INSERT INTO judges VALUES ('".array_shift($arrayRecord)."'";
      foreach ($arrayRecord as $recordVal) {
          $query = $query.",'".$recordVal."'";
      }
      $query = $query.")";
      $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 
   }

// Get some Data from the New Table and Display on Screen
   $query    = "SELECT * FROM judges";
   $result   = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 

   if(mysql_num_rows($result)) 
   { 
   echo "<table>\n"; 
   while($row = mysql_fetch_assoc($result)) 
     { 
     echo "<tr><td>JudgeName: ".$row['judgeName']." </td><td>eMail:".$row['judgeEmail']."</td></tr>\n"; 
     } 
   echo "</table>\n"; 
   } 
   else 
   { 
   echo "<p>Sorry, no data found in table.</p>\n"; 
   } 

// Close Database Connection
   mysql_close($Db);
?>