Web Hosting Forum | Lunarpages

Author Topic: creating a Mysql database within a script  (Read 1401 times)

Offline DaleR

  • Newbie
  • *
  • Posts: 3
creating a Mysql database within a script
« on: January 22, 2016, 01:14:38 PM »
Thanks in advance for your help. I know I have a lot to learn. I have dabbled in PHP in the past.  But now I plan to use it to design a couple of larger project I have on my table. I have a silly newbie error but I cant seem to get around it. Here is the info:

I have an existing site on the **** server. It is a CPanel site.  I am using a parked domain as the test location for my work.  I would like my script to eventually be a self installing CMS/blog. It will have to create it's own database and tables upon installation.  The code for this is widely available, but I can't seem to get it work.  Can anyone offer insight into what I am missing?

Code: [Select]

<?php
$servername 
"localhost";
$username "*******_test";
$password "psswrd99";
$dbname "testDB";

// Create connection
$conn = new mysqli($servername$username$password$dbname);
 
// Check connection
 
if ($conn->connect_error) {
     die(
"Connection failed: " $conn->connect_error);


// sql to create table
 
$sql "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)"
;

if (
$conn->query($sql) === TRUE) {
    echo 
"Table MyGuests created successfully";
} else {
    echo 
"Error creating table: " $conn->error;
}

$conn->close();
 
?>


 

The error returned by the server reads as follows:

Quote

Connection failed: Access denied for user '*******_test'@'localhost' (using password: YES)


The user and password shown above are not the ones I actually used.  I am not sure if I can arbitrarily assign these or if there is a protocol I am missing.

I FTP'ed the script into /public_html/parkeddomain.com/ and changed the file permissions to 0755 to make it executable.  Than I called the file in an IE browser.  The error it returns is quoted above.

Is this a problem with the password or the way I have made the username.....or some other newb mistake I am making?  Or has Lunarpages somehow blocked scripts like this from creating databases?

Your help is appreciated.

Dale R

<removed server name and account name for security reasons -- Mod>
« Last Edit: January 22, 2016, 03:03:31 PM by MrPhil »

Offline MrPhil

  • Senior Moderator
  • Berserker Poster
  • *****
  • Posts: 6187
Re: creating a Mysql database within a script
« Reply #1 on: January 22, 2016, 03:01:53 PM »
I seem to recall that some time ago, LP did not allow scripts (e.g., PHP) to create databases. Nevertheless, there must be some way for Softaculous and other installers to perform this task. I presume there is some sort of programmatic interface to allow such tasks -- have you looked at your "one-button" installer used on your account control panel to see if you can decipher their installation scripts?

Is this on a cPanel or an LPCP account? I ask because too many people refer generically to an account control panel as "cPanel", when in fact that is a specific brand (used on older LP servers... the newer ones use LPCP). If you intend to make this installable by others, including at non-LP hosts, I would assume that there is some sort of generic DB creation call, if in fact the MySQLi interface is not supported. I assume that you're prereq'ing MySQLi, as not all hosts may have it installed yet.

Note that at LP, cPanel installations used "localhost", while LPCP used a separate, named, DB server (e.g., mysql35.lunariffic.com). I think in both cases, both the user name and the DB name have your account name prepended. Maybe your $dbname is bad? Also, PHP does fine with 644 permissions, although if you want to make this usable across multiple hosts, 755 might be necessary. Just don't use 666 or 777, as many hosts (including LP) will block it as a security hazard. You might want to google for installer formats and maybe ask on a place like StackOverflow what other applications do about building their installers (if you're currently doing it by trial and error).

By the way, it's not a good idea to publicly display your account name. It's a security risk. I will edit your post to remove it.
« Last Edit: January 22, 2016, 03:04:51 PM by MrPhil »
Visit My Site

E-mail Me
  
-= From the ashes shall rise a sooty tern =-

Offline DaleR

  • Newbie
  • *
  • Posts: 3
Re: creating a Mysql database within a script
« Reply #2 on: January 22, 2016, 03:48:24 PM »
Thank you for the quick response, MrPhil.

I should have said that the account and usernames I used were made up....and not the ones actually for my hosting package.  Thank you for thinking of that and looking out for me.

I did double check just now.  It's CPanel.  CPanel logo and CPanel Documentation when you click the link. Definitely CPanel.

To the best of my knowledge (which is quite limited, but I read and comprehend very well) there used to be a direct PHP call to create a MySQL database, but that was deprecated and then discontinued a couple of PHP versions back.  According to W3schools.com, the two ways to call for db creation is either with Mysqli or PDO.  I gave the first one a shot.  And yes, trial and error seems to be the way I learn things.  I will check StackOverflow. It has been a good resource for me in the past.

I don't know if Lunarpages tech support will shorten this process by clueing me in on whether this even allowed.  But I guess I should get on the hold queue and try to find that out, also.

Since LP does have Softaculous integral with their servers, I wouldn't be surprised if they have given the Softaculous scripts a higher level of permissions than I have, and that enables them to create databases during software installations. But that is just a hypothesis.  I will take your suggestion and see if I can decipher anything of their's.

best regards,

Dale R

Offline DaleR

  • Newbie
  • *
  • Posts: 3
Re: creating a Mysql database within a script
« Reply #3 on: January 26, 2016, 04:46:43 AM »
Lunarpages Support, has advised me that on a shared hosting server, users do not have access to create db's from scripts.  They say it takes root permissions and suggested I pay more for a higher level hosting plan if this is what I want to do.

I am going to have to more exploration and testing if they will not (or can not) suggest a work-around.  I will try to locate the script above my public_html folder and call it like you would call a CGI or CRON job. Maybe  it will execute from there. 

Dale R

Offline MrPhil

  • Senior Moderator
  • Berserker Poster
  • *****
  • Posts: 6187
Re: creating a Mysql database within a script
« Reply #4 on: January 26, 2016, 06:26:02 AM »
"One Button" installers such as Softaculous apparently must be operating at some sort of higher privilege level than you can run scripts at. I note that many installation packages (osCommerce, SMF, etc.) require you to manually create the database first, before running an install.php script, if you're obtaining an installation .zip file directly from them (and not via Softaculous). So, you're in good company! Further note that if you find something that does work on your LP server, it may not be universal (i.e., won't work on all other hosts' servers). It's unfortunate that your script can't create databases, but my guess is that's a security measure of some sort. Perhaps hosts had bad experiences with rogue PHP scripts creating unwanted/unrequested databases that clogged up their systems.
Visit My Site

E-mail Me
  
-= From the ashes shall rise a sooty tern =-

 

Share |