Error talking to SQL database

[Rocketman46]

Hi Team,

I have my website live and up and running, but I get connection errors when logging into the SQL database. I have read that this maybe an error on the server provider, the "mysqli php extension" is not enabled. My sever is 1and1 using MySQL5.5.

My development steps to get this PHP example working, were to create a new login page on my website, that logs into a hidden PHP page. When this page is logged into the below PHP code is executed.

If somebody would be kind enough indicate what I am doing wrong.

Below is my code and the error, can you see any obvious problems:

<?php

$servername = "servername";
$username = "username";
$password = "password";
$dbname = "dbname";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

?>

Error:

Fatal error: Call to undefined function mysqli_connect()

[Derokorian]

Sounds like PHP wasn't built with mysqli enabled. I would talk to 1 and 1 support and find out why you don't have it.

If they refuse to change your install to include a recommended library, then I would recommend you use a VPS solution where you have control over your server. I use Digital Ocean myself and have been very happy. On the flipside this requires more server knowledge to get up and running. The link above will provide a $10 credit, good for one month of their low end droplet.

[NogDog]

You might also check to see if they support both php 4.x and 5.x. Some hosts do that by requiring you to use a ".php5" file name suffix for the good stuff. (Or sometimes you can configure the version via a local .htaccess setting.)

[Rocketman46]

Hi,

Thanks for your replies. I have tried many variations including the example below, but still the same error. Also included below is the wording off the server, and the exact example. A very stupid question, do I have to include "mysqli.php" above "<?php"

Copy and paste the following code to your PHP script to set up the database connection.
For your security, we are not showing the password.
Please always connect to the database through your website. The direct access to the database via your local PC (external ODBC connection) is not possible.
Please note that PHP 5.5 and later versions do not support MySQL. Please use the MySQLi code instead.

Many Thanks,

Rocketman46

mysqli.php
<?php
$host_name = "host_name";
$database = "database";
$user_name = "user_name";
$password = "password";

$connect = mysqli_connect($host_name, $user_name, $password, $database);
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

?>

[dalecosp]

Once again, in the Queen's most respectful English:

It's not going to work unless your host installs the MySQLi extension on the server for you.

Call them up, cuss them out, and find a new host. Or, if you're particularly well-heeled, sue them.

[Rocketman46]

OK thanks everybody.

I am new to PHP and just needed to 100% confirm, my thinking and code is correct.

I have emailed them twice and they say the MySQLi extension is working correctly on the server.

So annoying when this kinda thing happens :-/

On a slightly different subject, I currently edit my code in phpDesigner8, copy and paste this code into my web development package, upload this code to server via FTP, then test my code works on the live website. I think/ guess I can edit my code in phpDesigner8 and talk direct to my SQL database on the server. Can I do this as this would make my development process/ debugging a lot easier.

When I eventually log into my sever SQL database I want to read one number back initially, then edit code a number of times and read more numbers back etc. But if possible I would like a direct like to the database via a PHP IDE if possible?

Sorry if I am talking rubbish.

Many Thanks,

Rocketman46

[sneakyimp]

This script should tell you specifically if the mysqli extension is loaded.

<?php
if (!extension_loaded("mysqli")) {
	echo "mysqli is NOT loaded\n";
} else {
	echo "mysqli loaded!\n";
}

VERY VERY IMPORTANT: MySQLi is not the same thing as MySQL. That tiny little "i" means everything. You should probably clarify this with the support folks at your hosting company. They may be talking about MySQL (without the i).

Perhaps you could show us the output of this script:

<?php
print_r(get_loaded_extensions());

Then we can see for ourselves what extensions your hosting company has installed.

[Bonesnap]

Rocketman46;11051035 wrote:

But if possible I would like a direct like to the database via a PHP IDE if possible?

PhpStorm can do this. I do it with my projects a lot now. Sometimes I don't even need to open phpMyAdmin (but it's in a pinned tab so it's always open :p).

[sneakyimp]

Rocketman46;11051035 wrote:

When I eventually log into my sever SQL database I want to read one number back initially, then edit code a number of times and read more numbers back etc. But if possible I would like a direct like to the database via a PHP IDE if possible?

In order to connect to a db server from your IDE, your hosting provider would need to open the MySQL server not just to connections from localhost or its internal network, but the the world at large -- this presents a security problem and I would probably never do it unless I was required to by some needed functionality. I'd be willing to bet your hosting company limits connections to your db to only its own servers.

[Bonesnap]

Yeah sneaky is right. Some hosts allow this and some don't. I think GoDaddy allows it but you have to explicitly enable it on a per-database basis. I have used 1and1 before but cannot recall if they allow it.

[Derokorian]

sneakyimp;11051075 wrote:

In order to connect to a db server from your IDE, your hosting provider would need to open the MySQL server not just to connections from localhost or its internal network, but the the world at large -- this presents a security problem and I would probably never do it unless I was required to by some needed functionality. I'd be willing to bet your hosting company limits connections to your db to only its own servers.

Not entirely, mysql users are defined on a host basis. Which means user_a from 192.168.0.1 may work but not from 192.168.0.2 because the user is defined to only work from this source IP. I know for my production DB, I have my user set up to work from my house, but I can't login from work since its from an unrecognized IP. The reason its partially true, is you would have to open the port to external connections.

[Rocketman46]

Hi all, thanks for your input and help.

Sneakyimp below are the outputs to your code:

Let me know if you want me test any other code. Thanks very much for your help.

Cheers,

Rocketman46

Code test 1 - Exact code with no passwords or login:

<?php

if (!extension_loaded("mysqli")) {
    echo "mysqli is NOT loaded\n";
} else {
    echo "mysqli loaded!\n";
}

?>

Output: mysqli is NOT loaded

Code test 2 - Exact code with no passwords or login:

<?php

print_r(get_loaded_extensions());

?>

Output: Array ( [0] => Core [1] => bcmath [2] => calendar [3] => com_dotnet [4] => ctype [5] => date [6] => ereg [7] => filter [8] => ftp [9] => hash [10] => iconv [11] => json [12] => mcrypt [13] => SPL [14] => odbc [15] => pcre [16] => Reflection [17] => session [18] => standard [19] => mysqlnd [20] => tokenizer [21] => zip [22] => zlib [23] => libxml [24] => dom [25] => PDO [26] => Phar [27] => SimpleXML [28] => wddx [29] => xml [30] => xmlreader [31] => xmlwriter [32] => cgi-fcgi [33] => gd [34] => mysql [35] => sqlsrv [36] => mhash )

[sneakyimp]

Rocketman46;11051093 wrote:

Code test 1 - Exact code with no passwords or login:

<?php

if (!extension_loaded("mysqli")) {
    echo "mysqli is NOT loaded\n";
} else {
    echo "mysqli loaded!\n";
}

?>

Output: mysqli is NOT loaded

This clearly indicates that mysqli is NOT installed. Looks like you had a misunderstanding with your hosting provider.

Rocketman46;11051093 wrote:

<?php

print_r(get_loaded_extensions());

?>

Output: Array ( [0] => Core [1] => bcmath [2] => calendar [3] => com_dotnet [4] => ctype [5] => date [6] => ereg [7] => filter [8] => ftp [9] => hash [10] => iconv [11] => json [12] => mcrypt [13] => SPL [14] => odbc [15] => pcre [16] => Reflection [17] => session [18] => standard [19] => mysqlnd [20] => tokenizer [21] => zip [22] => zlib [23] => libxml [24] => dom [25] => PDO [26] => Phar [27] => SimpleXML [28] => wddx [29] => xml [30] => xmlreader [31] => xmlwriter [32] => cgi-fcgi [33] => gd [34] => mysql [35] => sqlsrv [36] => mhash )

They do have mysql installed, which is OLD OLD OLD. You should ask them to upgrade and if they don't, you should find a different hosting company.

[Derokorian]

Test 1 says it all, you need to talk to 1and1 and find out why this extension isn't loaded. If they say it is, then point them to the script and output and firmly say "No, it is not."

[Rocketman46]

OK thanks for your help.

I was going to get a basic SQL database going and link to my website, then when this is stable move to another server provider.

But if it is going to be this much hassle from the start, I think I will jump ship now.

They argue black and blue its working, but as you said I will send them this script now.

I want to start a small business, then grow, but I need something stable from the start. In the UK these look very good, I think they are also popular in the USA. Have a quick look on your next tea break and tell me what you reckon.

https://aws.amazon.com/

Many thanks,

Rocketman46

[sneakyimp]

I use AWS for a few sites I host. They have a lot of good technology that I can certainly vouch for, but it can be quite complicated just to get a server up and running.

I would strongly suggest you check out Digital Ocean (see Derokorian's link up above for $10 discount). Their "droplets" are basically little stand-alone servers. I don't know if they have apache and all that stuff installed yet, but you have complete control over them. Getting apache and php installed is something we can talk you through in this other forum here on phpbuilder. Basically, it's like having your very own server where you can install whatever modules you like. They are cheap too as virtual servers go.

If you plan to start connecting to a database using PHP, then you'll definitely NOT want to use what they have installed on 1and1. It's been deprecated since php 5.5. You'll want mysqli or PDO or something else instead -- read the link in my signature.

[Rocketman46]

OK thanks sneakyimp.

I will have a good read and come back to you.

I have to go now, but will catch up after I have done some more reading.

Cheers,

Rocketman46

[Derokorian]

I used AWS for a while, it was a great start but once the free tier ended it was WAY too expensive for me to justify and this is why I switched to Digital Ocean myself. While I can easily build a server (aka droplet) myself on digital ocean, I can vouch from friends' experiences that their support and tutorials will definitely get you up and running rather simply. They are also known to help me figure out server configuration issues, even though other companies have told me "If your private server to manage".

[laserlight]

sneakyimp wrote:

I don't know if they have apache and all that stuff installed yet

They do have "One-Click Applications" including one for LAMP, but I have never tried any of them as installing individual components worked for me so I kept doing it that way. From what I understand, choosing this route means that the VPS would be created with the requested software installed.

Derokorian wrote:

While I can easily build a server (aka droplet) myself on digital ocean, I can vouch from friends' experiences that their support and tutorials will definitely get you up and running rather simply.

Yeah, I got into server management thanks to Slicehost articles; Digital Ocean is like the new Slicehost in this respect (though it is not so new anymore).

[dalecosp]

I like RootBSD, finding a small VM there affordable and completely customizable, and their support team (tickets and whatnot) are topnotch.

OTOH, you're not gonna find HOWTOs, or videos explaining what to do. I don't find this an issue, but many might; I'm kind of known as 'ye olde phart who continually talks about that old BSD OSen* at PHPBuilder' anyway.

(it ain't old, really... old school* perhaps. Y'know, it works correctly, DTRT and no eye-candy on the server.)