PHP not displaying MYSQL result - so strange?? Please help

[lewisdow123]

Hi,

 

I am new to php but not to programming.

 

I created a script on a windows platform which connects to the mysql database and returns the results of a table.  A very basic script which I wrote to simply test my connection worked.  The script works fine on my windows machine but not on my new mac.  On the mac it simply does not display any records at all.

 

I know that the database connection has been established because there is no error but I can not see why the result set is not being displayed on screen, as I said it worked fine on my windows machine.

 

The Mac has mysql (with data) and apache running for php.

 

Please could someone help as I have no idea what to do now?

 

Script below:

 

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'test';

mysql_select_db($dbname);

mysql_select_db("test", $conn);

$result = mysql_query("SELECT * FROM new_table");

while($row = mysql_fetch_array($result))
  {
  echo $row['test1'] . " " . $row['test2'] . " " . $row['test3'];
  echo "<br />";
  }

mysql_close($con);

[Muddy_Funster]

change:

$dbname = 'test';

mysql_select_db($dbname);

mysql_select_db("test", $conn);

$result = mysql_query("SELECT * FROM new_table");

 

To:

$dbname = "test";
mysql_select_db($dbname, $conn);
$qry = "SELECT * FROM new_table";
$result = mysql_query($qry) or die("There was an error running the the following query:<br>$qry<br><br>The server responded with:<br>".mysql_error());

and let us know what you get back

[lewisdow123]

Thanks - I got the error message No Database Selected

 

but I did add in the mysql_select_db("test", $conn);

 

as you specified.

 

thanks for the quick response

[Muddy_Funster]

try this change for me:

$dbname = "test";
$db = mysql_select_db($dbname, $conn) or die("Unable to locate the $dbname databse on the current server, please ensure database exists on the server and try again");
$qry = "SELECT * FROM new_table";
$result = mysql_query($qry) or die("There was an error running the the following query:<br>$qry<br><br>The server responded with:<br>".mysql_error());

[lewisdow123]

Hi,

 

I got the following

 

Unable to locate the test databse on the current server, please ensure database exists on the server and try again

 

This was the code...

 

A connection was made and I can see in the mysql workbench that there is a database called test and table called test...

(please ignore the previous table name I provided).

 

Am not sure what to say, a connection can be made but the table does not exist, when it clearly does.... many thanks in advance for your help.

 

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');	

$dbname = "test";
$db = mysql_select_db($dbname, $conn) or die("Unable to locate the $dbname databse on the current server, please ensure database exists on the server and try again");
$qry = "SELECT * FROM new_table";
$result = mysql_query($qry) or die("There was an error running the the following query:<br>$qry<br><br>The server responded with:<br>".mysql_error());

[Muddy_Funster]

the connection is to the server, not the database.  The other thing that may cause an issue is permissions, normaly the root@localhost has full access, but depending on how the MySQL was installed, or if you are using another user, you may need to grant priviledges to that user.

[lewisdow123]

Hi,

 

Any idea on how I can do that?

 

Basically I am pro with MSSQL but a beginner in MySQL so dont really know how to add / check permissions etc.

 

Any advice?

 

Thank you,

 

Lewis

[lewisdow123]

But if the connection was to the server would it not fail at this stage rather than further down?

 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

[Muddy_Funster]

if the connection was the problem then it would fail there, however, you can connect to the server without accessing any databases.  that the error comes when you try to acceess a database is telling us that the connection is irelivent to the issue - unless said connection is being made with credentials that arn't allowed to access the database. Follow this link for more info on GRANT PRIVILEDGES http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

[PFMaBiSmAd]

You can use mysql_error in the or die() logic to get mysql/php to tell you why the mysql_select_db statement is failing.

 

What database management tool are you using to setup the databases/tables/users? You should also have php's error_reporting set to E_ALL and display_errors set to ON in your master php.ini on your development system so that ll the php detected errors will be reported and displayed.

[lewisdow123]

Hi,

 

I have updated the php.ini file as mentioned so that the display_errors = ON

 

I have checked the permissions of the Root User account which is set to All Privileges and is also sysadmin / DBA, so there is no permissions error.

 

I have displayed the mysql_error() for the db select statement and it comes back stating Unknown database 'test'

 

:S

 

Am not sure what to do next....

 

Any ideas?

 

Thank you,

 

Lewis

[PFMaBiSmAd]

Unknown database 'test'

 

Is the spelling and capitalization of your actual database name 'test'? Mac file systems are case-sensitive and unfortunately, mysql database and table names are not decoupled from the underlying operating system and are case-sensitive on such operating systems.

[lewisdow123]

Hi,

 

yes to both...

 

I created a new database and table just in case, named new_database and new_table.

 

Same error 

 

 

[lewisdow123]

Playing a little further I got the following output...

 

There was an error running the the following query:

SELECT * FROM NewTable;

 

The server responded with:

Table 'NewDatabase.NewTable' doesn't exist

 

 

The case and spelling is correct, but if I try again with the test database and test table I get the previous message.

 

Any ideas?

[mikosiko]

in your previous post you said:

I created a new database and table just in case, named new_database and new_table.

 

and in your last query you shown

 

There was an error running the the following query:

SELECT * FROM NewTable;

 

The server responded with:

Table 'NewDatabase.NewTable' doesn't exist

 

so which one is correct?...

[lewisdow123]

Hi,

 

Yes sorry for the confusion, both are correct.

 

I tried to create a new database and table after my first statement.

 

So basically I have tried three different database names (all of which exist).

test -> test

new_database -> new_table

NewDatabase -> NewTable

 

Only when there is capitals in the name of the database and / or table do I get the error:

 

There was an error running the the following query:

SELECT * FROM NewTable;

 

The server responded with:

Table 'NewDatabase.NewTable' doesn't exist

 

 

Otherwise if the name is all lower case, as in the case of test and new_database with their respective table names do I get...

 

Unable to locate the test databse on the current server, please ensure database exists on the server and try again

 

 

What I am really confused about is how it works perfectly with windows but not the mac, even though my settings and server name, user account are the same :s

 

Thanks,

 

Lewis

 

 

[Muddy_Funster]

could you create a new php file and run the following in it, copy and paste the output.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());	
$qry = "SHOW DATABASES";
$result = mysql_query($qry) or die (mysql_error());
while ($row = mysql_fetch_assoc($result))
{
  $x[] = $row['Database'];
}
$dispX = print_r($x, true);
echo "<pre>$dispX</pre>";
?>

[lewisdow123]

Thank you for your help

 

Please see below:

 

Array

(

    [0] => information_schema

    [1] => NewDatabase

    [2] => mysql

    [3] => performance_schema

)

 

Seems that NewDatabase is there but the other two database names are not, strange no?

I can see them in my mysql workbench :s

 

 

 

[Muddy_Funster]

yeah, bin mysql workbench, use phpMyAdmin or somthing else thats not as quirky.  That's also the first install I have seen without an information_schema table.  How did you install your DB?

[lewisdow123]

Wow... OK that was the deal maker.

 

So I opened PHPMyAdmin provided by MAMP.

There is where I had created one database and hence it existed, so I created the table and added one row... the php code now works perfectly!!

 

BUT and a big BUT....

 

What instance is the workbench connecting to then?  Because I have obviously been creating database on a different instance just that the web user is pointing to a completely different instance.

 

The problem with binning WorkBench is that it has a good database development interface.  The PHPMyAdmin web interface is horrible.  So what would be ideal now is if I could get my WorkBench to connect to the same instance as the one that PHPMyAdmin is connecting to...

 

Any ideas on how I can find that info???

 

Thank you so much for your help, at least I am one step further.

[Muddy_Funster]

nope, I tried Workbench on 2 seporate ocasions, never again!  it may look more intuative, but in my opinion the one that works is the better one.  try to get into phpMyAdmin, once you know what your doing it won't seem all that bad.  what you could try is Navicat for MySQL, there should be a trial download that you can get. They used to do a light version for free, but I think your will need to pay to keep using it these days.

[lewisdow123]

Thanks for all your help!