Problem locating databases created from mysqli commands on PHP

Posted on 2016-11-12
Medium Priority
Last Modified: 2016-11-13
Hello experts,

I am having a problem creating and selecting MySQL databases from my php script.

I have a fresh installation of Linux Debian 8, which came with an Apache 2 webserver installed. I connect to the server using the local IP of the server.

I installed the rest of the LAMP stack, PHP 7.0.12 and MySQL 5.5.2. Being a beginner in the Linux environment, I found the command line interface of MySQL quite intimidating. Hence I also installed MySQL workbench to create databases.

With all that done, I created two databases using Workbench that I needed for my sites. I am also a beginner in web programming and web development. Next I followed tutorials that show how to write simple PHP scripts and those worked. Then when I got into linking MySQL and PHP, things started going wrong.

Initially I had problem connecting to the database server using 'mysql_connect()', however, I found out after a bit of googling that mysqli_connect() is what works in PHP7. After the connection was successfully made, I created a Database using mysqli_query() from the PHP code, and also selected it using mysqli_select_db().

However, I could not select the databases I had initially created using workbench using mysqli_select_db(). I tried a lot of ways, like putting the php file and the db in the same folder, specifying the exact path of the db, but none worked. I am guessing it must be some problem regarding the location the php script looks at for finding the database.

Every tutorial I am looking into, uses some tool called PHPMyAdmin. Is it necessary to have PHPMyAdmin? From what I see, PHPMyAdmin is just a explorer and UI for working with databases, and so is workbench. So what is going missing here? The following are the things I would like to know:

1. Is there any way to specify to mysqli_select_db() to look for a database at a particular path/location?
2. What is the default location where the mysqli_##() commands look for or create databases at?
3. How to change this location?
4. What exactly does PHPMyAdmin do? What help would it be to use it?
5. Out of the context of MySQL and PHP, how to change the default webpage and the server directory of webpages of Apache 2 running on Linux?

Thanks in advance.
Question by:Soumen Roy
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 41884750
I have always used MySQLi in the object-oriented way.  More details in this article.  The connect statement (the class constructor) specifies the name of the database.  Check the host, port and socket  parameters.  Use var_dump() to print out the MySQLi object.  Recommend that you use the standard and default locations / values for your scripts.  The more you change, the less likely things will work well.  phpMyAdmin is a GUI tool kit.  Very popular - try it and you will see why!

If you're new to PHP and want to learn the language, this article may be able to help.
LVL 81

Assisted Solution

arnold earned 500 total points
ID: 41884807
The important part to consider is that often the users used in web/php application usually do not have rights to create databases as that would expose your DB server to mischief should there be an sql injection vulnerability or other compromise.
You could for the purpose of managing your mysql instance install phpmyadmin and that will provide you with a graphical user interface to the mysql instance. To make it available on your site, make sure to masquerade it someiunique_identifier that is not referenced anywhere and make sure not to have any links to this location. also in the root of the phpmyadmin make sure to have a robots.txt file that tells search engines should they come upon this site not to index/archive/catalog this directory......

It sounds as though your attempts also do not include testing whether the commands you've run succeeded or failed. and if failed why. The inclusion of testing of what you run is highly recommended by me otherwise, you might get into the habit that everything goes through smoothly and will continue running commands when some prior failed ......

Author Comment

by:Soumen Roy
ID: 41885152
Thank you for the response..

I have been using mysqli_error($conn) [$conn = mysqli_connect(#host:port, #user, #pass)], to look for errors, and that is how I found that the database I was looking for was not found, and that the full path does not work.. Now the codes work fine, and I keep using mysqli_error() as it turns out to be a very useful tool..

However, that does not solve my problem.. What I want to know is how to find out the path which phpmyadmin or the php scripts running on my webserver use to store and retrieve the databases?
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 81

Expert Comment

ID: 41885165
Ythe path is available in show variables. When you create a db, it defaults to the system configured datadir.

I am uncertain what it is you are after.
LVL 27

Accepted Solution

skullnobrains earned 1000 total points
ID: 41885447
1. Is there any way to specify to mysqli_select_db() to look for a database at a particular path/location?

not that i know of.
most likely, you created the dbs as root and are connecting like another user.
you can list the databases with the "show database" query either from the command line or a php script

2. What is the default location where the mysqli_##() commands look for or create databases at?

mysql barely asks the server and does not know about the location.

the sql server stores it's files in a directory that should be /var/lib/mysl on debian.
you can issue "show variables like 'base_dir' " in order to get the location
it should also be the home of the mysql user
note that there are quite a few options that can override this location

innodb databases and files should all be stored in a single file in that directory
if you have the file_per_table option enabled you'll get one file per table
if you manualy create tablespaces you can override this location

myisam databases should be subdirectories in that same location
each table is stored as 3 separate files : tblname.frm ( table description in a small text file ) tblname.dbd which contains the data, and tblname.myi which contain the pk and other indexes ( the last may not exist on tables with no index at all )

note that the files are probably not usable to you outside mysql

3. How to change this location?

change variables such as innodb_data_dir

there is no point in doing that in your case

4. What exactly does PHPMyAdmin do? What help would it be to use it?

it is a good tool. it is accessible from anywhere remotely with a web browser.
it does a small number of things that other tools do not and likewise lacks features compared to what you use.
i personally hardly ever install anything other than phpmyadmin

5. Out of the context of MySQL and PHP, how to change the default webpage and the server directory of webpages of Apache 2 running on Linux?

same as windows
httpd.conf refers to files in .../sites-enabled/ and .../config-enabled/ which are links to files in the corresponding .../sites-available/ and ...config-available/

the whole mess should be located under /etc/apache2 ( config files are almost always there. mysql is an exception )

Author Comment

by:Soumen Roy
ID: 41885892
@Arnold Sorry for not being able to frame up my question good.
@skullnobrain's answers  were good enough.. I googled out some of those questions yesterday though..

Thanks for the assistance experts. :)

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question