4

I ran this code to get the output shown in image below:

<?php
  phpinfo();
?>

enter image description here

Trouble is, I cannot figure out why this works:

$conn = @mysql_connect('host', 'user', 'pass') or die(mysql_error());
      @mysql_select_db('dbName') or die(mysql_error());

      $query="SELECT * FROM Clients";

     $result = @mysql_query($query) or die(mysql_error());
      if ($result)
      {
            $outp = "";
      while ($row = mysql_fetch_assoc($result))
      {

        if ($outp != "") {$outp .= ",";}
        $outp .= '{"ClientID":"'  . $row["cID"] . '",';
        $outp .= '"ClientsName":"'   . $row["clientsName"] . '"}'; 
      }
      mysql_free_result($result);
      }
    $outp ='{"records":['.$outp.']}';
    echo($outp);

But this does not:

$conn = new mysqli("host", "user", "pass", "dbName");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
$result = $conn->query("SELECT * FROM Clients");

$outp = "";
while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
    if ($outp != "") {$outp .= ",";}
    $outp .= '{"ClientID":"'  . $rs["cID"] . '",';
    $outp .= '"ClientsName":"'   . $rs["clientsName"] . '"}'; 
}
$outp ='{"records":['.$outp.']}';
$conn->close();

echo($outp);

The error that I get is:

Connection failed: Access denied for user 'user'@'host' to database 'dbName'

I read that as long as PHP5 is running, MySQLi is also already installed. What else might I need to check for? Maybe I am doing something wrong in my code? This is my first attempt at MySQLi, I haven't connected to a database in years, had to rummage through old code just to get the old SQL way to connect to database.

Machavity
  • 30,841
  • 27
  • 92
  • 100
Christine268
  • 722
  • 2
  • 13
  • 32
  • Are you running both scripts on the same machine? – Barmar May 21 '15 at 01:32
  • @Barmar Yes. One file, I just changed the script (comment one out, run other). So on same host, etc. – Christine268 May 21 '15 at 01:38
  • Then I can't think of any good reason why the new one would fail like this. Double check for typos. – Barmar May 21 '15 at 01:40
  • In your `phpinfo` do you have a `mysqli` block? Do you also have a `mysqlnd` block? Also, what kind of server? (CentOS, Ubuntu, Windows, etc) – Machavity May 21 '15 at 01:43
  • run `phpinfo()` on the server and make sure they have mysqli configured to be on (though that probably is not the issue). Also check your connection file; make sure the variable names are defined correctly – nomistic May 21 '15 at 01:45
  • did you get valid output in scenario 1? or just no errors? – fbas May 21 '15 at 01:46
  • @Machavity Linux. There is a table in the phpinfo() for mysqli running version 5. Didn't see anything about block. – Christine268 May 21 '15 at 01:51
  • have you tried the `mysqli` without specifying a dbName? – chiliNUT May 21 '15 at 01:51
  • @fbas valid output in scenario 1. I am able to move forward with my project, I would just rather connect to the database via mysqli. – Christine268 May 21 '15 at 01:51
  • @Christine268 Do you have access to the command line of this server? – Machavity May 21 '15 at 01:52
  • @chiliNUT I did not put actual names for the host, user, pass, or dbName in my question, naturally. But I have actual names for them in my php, naturally. The same ones are being used in mysqli connection that doesn't work as the mysql connection that doesn't work. – Christine268 May 21 '15 at 01:53
  • @Machivity I do not. It is on a host website. – Christine268 May 21 '15 at 01:53
  • @Christine268 In the `mysqli` block what is the client API version? – Machavity May 21 '15 at 01:55
  • 1
    @Christine268 I just mean, like, connect to mysqli without specifying the dbname, and run a query like "select * from dbName.tablename" and see if that yields a different result – chiliNUT May 21 '15 at 01:55
  • @Machivity 5.5.42-37.1 – Christine268 May 21 '15 at 01:56
  • 1
    @chiliNUT you are onto something. I got it to say "Connected successfully" from my echo! So we have connection now. :) Only strange thing now is the echo($outp); isn't echoing. – Christine268 May 21 '15 at 02:00
  • The `$outp` variable is coming from the database. In other words, you successfully connected to the server, but for some reason aren't connecting to the database. verify the database name, or add the database name to your query, such as `SELECT * FROM dbname.Clients` (note, case sensitivity matters) – nomistic May 21 '15 at 02:29
  • Nice! So my suggestion from before was based on the user somehow not having permission to connect to that db, while still having permission to connect to the server. why that would be different for mysqli vs mysql, however, I do not know. are you connecting to localhost? maybe this tangentially related SO could be helpful? http://stackoverflow.com/questions/13769504/mysqlimysqli-hy000-2002-cant-connect-to-local-mysql-server-through-sock – chiliNUT May 21 '15 at 02:38
  • @chilliNUT interesting. Yea I have no idea why it would differentiate, and yes, I am connecting to localhost, as I have always done. The user I am using has full permissions, so I haven't a clue why I wouldn't have permission to database..let alone permission with mysql but not with mysqli. I took a look at that link, unfortunately I am not so good with the technical side of things. But I suppose I could try those steps that are given in the answer and see if they do anything for me. – Christine268 May 21 '15 at 03:28
  • You may see this article for your issue. [http://stackoverflow.com/questions/6445917/connect-failed-access-denied-for-user-rootlocalhost-using-password-yes][1] [1]: http://stackoverflow.com/questions/6445917/connect-failed-access-denied-for-user-rootlocalhost-using-password-yes – GAMITG May 29 '15 at 11:29
  • Just throwing this in...are you specifying a port for mysqli? Probably not it if you are connecting to the server ok. However you can have users that are user@localhost or user@127.0.0.1 so check the address is the same and that the correct port is being passed. – Jon Holland Jun 01 '15 at 13:14
  • Try `SELECT user, host, password FROM mysql.user;` . Make sure your user information is what it's supposed to be. Otherwise, just make sure mysqli is enabled. Head scratcher. Let me know how it goes. Remember, on Linux the database names (actually directories on the filesystem) will be case sensitive. – Anthony Rutledge Jun 01 '15 at 21:27
  • For that matter, do `SHOW DATABASES` to check the names of your databases. Seems like that "user" does not have privileges to access the database? Odd, especially if it works with the mysql functions. – Anthony Rutledge Jun 01 '15 at 21:36
  • My last idea, try `SHOW GRANTS FOR 'user'@'hostname';`, where hostname is localhost or some other host's name. – Anthony Rutledge Jun 01 '15 at 21:39

2 Answers2

0

Your code looks perfectly fine.

Make sure there are no typos in the auth details.

Secondly, MySQL users are binded to hosts. For example root@localhost. If you're connecting from a remote host, make sure you have set up a user like root@123.123.123.123 or allow all hosts by using the % wildcard like root@%.

Hope this helps.

Peter B
  • 437
  • 4
  • 14
0

A while back I bumped into a sql client challenge where the version of php caused the client to refuse to connect to the server in mysqli for an arcane security reason. To resolve the issue, I had to turn on logging for mysql (debug logging) and check the debug log on the server after an attempt. Your problem sounds very familiar and may even be the same one. It required altering the password type if I recall correctly.

TheSatinKnight
  • 696
  • 7
  • 16