[RESOLVED] Can only connect using root

Kurtismonger

I'm having a heck of time connecting to mySQL. By putting libmysql.dll in the system32 folder I can connect but only as root. If I try to connect as another user I get the following:
Notice: Unable to connect to DBMS: Access denied for user 'user'@'localhost' (using password: YES) in D:\Program Files\Apache Group\Apache2\htdocs\results.php on line 32

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'ODBC'@'localhost' (using password: NO) in D:\Program Files\Apache Group\Apache2\htdocs\results.php on line 35

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in D:\Program Files\Apache Group\Apache2\htdocs\results.php on line 35

Warning: mysql_query() [function.mysql-query]: Access denied for user 'ODBC'@'localhost' (using password: NO) in D:\Program Files\Apache Group\Apache2\htdocs\results.php on line 37

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in D:\Program Files\Apache Group\Apache2\htdocs\results.php on line 37

User and pass are correct and I can log in to mysql monitor without a problem. When I connect using root I get nothing returned from my query even though I know there should be a value there. If I run the same query from the MySQL monitor I get a return. Here is my code:

<html>
<head>
  <title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
echo 'this is a test1<br />';
  // create short variable names
  $searchtype=$_POST['searchtype'];
  $searchterm=$_POST['searchterm'];

  $searchterm= trim($searchterm);

  if (!$searchtype || !$searchterm)
  {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }

  $searchtype = addslashes($searchtype);
  $searchterm = addslashes($searchterm);

ini_set('display_errors', 1);
error_reporting(E_ALL);

$db = mysql_pconnect('localhost','user','password');

if(!$db)
{
   user_error("Unable to connect to DBMS: " . mysql_error());
}

  mysql_select_db('books');
  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysql_query($query);
echo 'Query '.$query;
echo '<br />';
echo 'Result '.$result;
echo '<br />';
echo phpinfo();
  $num_results = mysql_num_rows($result);

  echo '<p>Number of books found: '.$num_results.'</p>';

  for ($i=0; $i <$num_results; $i++)
  {
     $row = mysql_fetch_array($result);
     echo '<p><strong>'.($i+1).'. Title: ';
     echo htmlspecialchars(stripslashes($row['title']));
     echo '</strong><br />Author: ';
     echo stripslashes($row['author']);
     echo '<br />ISBN: ';
     echo stripslashes($row['isbn']);
     echo '<br />Price: ';
     echo stripslashes($row['price']);
     echo '</p>';
  }
?>
this is a test
</body>
</html>

I've enabled mysql and mysqli extensions in php.ini and made sure my PHP install directory is in PATH. Also added extension_dir = "C:\PHP\ext" to php.ini

sheephat

is there more users? other than root?

Kurtismonger

Yes. I've setup another user and can log in and use it without problem via MySQL monitor.

bradgrafelman

When posting PHP code, please use the board's [PHP][/PHP] bbcode tags - they make the code much, much easier to read (as well as aiding the diagnosis of syntax errors). You should edit your post now and add these bbcode tags to help others in reading/analyzing your code.
What version of PHP and MySQL are you using?
You should never move PHP-related files outside of the main PHP folder (e.g. to system32). You should instead add the PHP directory to your system's PATH environment variable. A link to more information on how to do this can be found in the FAQ in the Install forum.

Kurtismonger

Sorry about the missing tags thanks for the tip- I tried to figure out how others did that but couldn't. Not sure how I can edit the post now?

I'm using PHP 5.2.3 and MySQL 5.0.37

There are other posts that recommended putting libmysql.dll in system32 as a possible solution. I have since reinstalled PHP in C:\PHP and added that to my path. I can now connect as root without libmysql in system32, but I still don't get a return on my query. Still can't connect as a non-root user.

bradgrafelman

Kurtismonger wrote:
Not sure how I can edit the post now?

FAQ: Can I edit my own posts?

Kurtismonger wrote:
I still don't get a return on my query. Still can't connect as a non-root user.

Try using the [man]MySQLi[/man] or [man]PDO[/man] extensions. Since MySQL Server 4.1, a new client-server protocol has been established. Not only do these two extensions utilize this new protocol, but many other improvements have been made as well.

Kurtismonger

For some reason the Edit button was not showing up before. All I could see was the Quote button. I've added the

 tags.

Doesn't seem like MySQLi would be the reason why I can only connect using root but I tried it. Changed to MySQL_Connect but got the same result. As root it appears to connect to MySQL, but I get no result to my query. Cannot connect at all with other user.

bradgrafelman

Weird... try this script:

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);

mysql_connect('localhost', 'root', 'your_pw_here') or die('Error connecting, MySQL said: ' . mysql_error());

$query = 'SHOW DATABASES';
$exec = mysql_query($query) or die('Couldn\'t list databases, MySQL said: ' . mysql_error());

echo "Available Databases:<hr>\n";

while($result = mysql_fetch_row($exec))
    echo $result[0] . "<br>\n";
?>

What does that output?

Kurtismonger

Wow. Bradgrafelman thanks a ton for that script, it helped me locate the problem. I'll explain it here just in case someone else runs into it.

When I ran the script provided I noticed a database listed for SugarCRM in addition to the 'books' I was trying to connect to. When I ran a show databases from MySQL console it wasn't there but 'books' still was. I knew SugarCRM had installed a second instance of Apache, but it was stopped so I assumed it wasn't an issue. On a hunch I looked under the SugarCRM directory and saw both a PHP directory and a MySQL directory. I then remembered that several months ago when I was first playing with SugarCRM I had installed the whole stack (SugarCRM, Apache, PHP and MySQL) rather than just SugarCRM. I don't know exactly what the problem was, but obviously two instances of MySQL and/or PHP was causing the difference between what I saw from MySQL console and via Apache.

So to make a long story short, I uninstalled Apache, MySQL and SugarCRM, deleted all the relevant directories, downloaded fresh copies of Apache and MySQL and reinstalled fresh.

My original script now runs exactly as it should.
Three days of beating my head against the keyboard down the crapper, but it feels good to have finally figured it out.
Some days you drink wine and some days you stomp grapes.