Solved

Problem locating databases created from mysqli commands on PHP

Posted on 2016-11-12
6
19 Views
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.
0
Comment
Question by:Soumen Roy
6 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 125 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.
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 125 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 ......
1
 

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?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 76

Expert Comment

by:arnold
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.
0
 
LVL 26

Accepted Solution

by:
skullnobrains earned 250 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 )
0
 

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. :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now