php and mysql

[Namibnat]

I have been working on a site for a while now, and finally wanted to load it up to the server. I have been working on my own computer with xampplite. Everything was working just fine. When I uploaded it, after a few little things to resolve, I got most things working just fine. But I have one major problem, each time I connect to the database I get errors. It connects fine, and when I ping the database, it is there. But as soon as I try something like:

mysql_fetch_array() or mysql_fetch_assoc() or whatever, it gives me errors. After trying all sorts of things and searching for solutions on forums, I believe that my code is good, but somehow the server's settings or the php settings are giving me problems. I tried to ask for help from my server's help thingi, but they couldn't help me with technical problems like that.

I have had a look at the php.ini file and everything looks okay there, but maybe I am missing something. I am just a novice with mysql and php.

[Josh Connerty]

Can you give me the exact result resource error please. This will help me diagnose the issue.

Aslo you will need to post your script in [ code ] tags.

[Namibnat]

Hi Josh,

I am rather sure that it is some setting or permission that I need to change. The error I get is below. I have found many posts of a similar nature on many forums and I have tried all sorts of things with the code. When I take the code back to my own system, I get it to work. When I upload it to the server (also apache and mysql and copy the database exactly) I get the errors, such as the one below. I have tried to stip my database table and the database query down to the bare basics and still I can't get a single database transaction to work.

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/...mydomainname../public_html/test_DB_connection.php on line 5

I do have permissions set right (I believe) on both the database and the website. Is there not some basic thing that beginners often overlook that needs to be set. Such as default_port?

Just as an example of what I have been trying:

<?php
mysql_connect("localhost", "user", "password");
mysql_select_db("MyDataBase");
$mydbstuff = ("select * from MyTable");
$ALine = mysql_fetch_assoc($mydbstuff);
$theTitle = $ALine['title'];

echo $theTitle;
?>

[Josh Connerty]

<?php
mysql_connect("localhost", "user", "password");
mysql_select_db("MyDataBase");
$mydbstuff = mysql_query("SELECT  * FROM `MyTable`");
$ALine = mysql_fetch_array( $mydbstuff );
$theTitle = $ALine['title'];

echo $theTitle;
?>

Try that it might work better.

Did you notice you were not running a query at all?

The best way to do what you are trying to acheive is:

1) Run a query to pull the data [$query = mysql_query("SELECT * FROM `MyTable`");]
2) Put it into an array [$row = mysql_fetch_array( $query )]
3) Take the specific array [$variable = $row;]

[Namibnat]

Thanks,

Sorry, I was getting sloppy with the code, but I have tried all sorts of variations on it. Does the $query variable have special significance, or can it be any variable name. If so, I have tried it already. I am fairly sure that it is not the code. I spent the day googling it and trying everything I came up with in tutorials and forums (including this one.) Perhaps it is the code, but I really feel like I must be missing something in the set up. I get everything to work on my system and nothing to work on the server.

[Josh Connerty]

Hmmm who is your web server host? Youm are right this seems very much like you either have the wrong username or password or maybe a database name etc.

There are a few ways you can debug this error on the connection variables try adding this:

$con = mysql_connect( "host" , "username" , "password" ) or die( "SERVER SELECT ERROR: " . mysql_error() );

$db = mysql_select_db( "database" , $con ) or die( "DB ERROR: " . mysql_error() );

If these return any problems then it is indeed one of the listed problems. However if it isn't then it has something to do with your query. Make sure that the table exists and that you have not givein it an upercase name (must all be lower case, some linux servers do this).

If you get any errors please post them back here.

Hope this helps you out a little.

[Namibnat]

Aha, I am getting somewhere now. I used the 'die' as described and get the following message:

DB ERROR: Access denied for user 'myusername'@'localhost' to database 'databasename'

The server uses cPanelX and I have the setting ticked where it says "Privileges:ALL "

Using your die method I determined that I have got the server, username and password right because if I change them the error comes up from the earlier die message. So it is a permission problem. How do I change that or must that be set by my server. I should have all privileges, since I can create the database and work with it in phpmyadmin, including creating, adding values, deleting and so on. If I can do it there, why not on my website.

[Josh Connerty]

0I think cpanel has the databse wizard correct? Well when you create a user you must add them to the database you are using. This is because the user can do everything they need to do but have no database assigned thus can't access your database.

If you have any issue's doing this then you should fisrt speak with your web host and ask them if they can do it :P

Or post back here for help I'm sure I can post some images with instructions etc.

[Namibnat]

Thanks, that fixed it. I simple hadn't added the user. I know it was a stupid mistake, but since that user created the tables and could work with in phpmyadmin I had assumed that the 'add user' part would only be to add another user.

Anyway, thanks. Beginner error.

[Josh Connerty]

lol easy mistake to make.

Can you mark this as solved please?

Thanks,
Josh Connerty