+- Sick Gaming (https://www.sickgaming.net)
+-- Forum: Programming (https://www.sickgaming.net/forum-76.html)
+--- Forum: PHP Development (https://www.sickgaming.net/forum-82.html)
+--- Thread: [Tut] phpMyAdmin – How to Import a Database? (/thread-100349.html)
[Tut] phpMyAdmin – How to Import a Database? - xSicKxBot - 12-06-2022
phpMyAdmin – How to Import a Database?
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/12/phpmyadmin-how-to-import-a-database.jpg" width="550" height="221" title="" alt="" /></div><div><div class="modified-on" readability="7.1304347826087"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on December 5th, 2022.</div>
<p>In this tutorial, we are going to learn how to import MySQL database using phpMyAdmin. There are two ways to do the import via this PHP application.</p>
<ol>
<li>Go to the “Import” tab in the phpMyAdmin and upload a file(SQL, CSV …) source that contains the database dumb.</li>
<li>Choose a database and drag and drop the import file to the phpMyAdmin interface.</li>
</ol>
<h2><span data-sheets-value="{"1":2,"2":"Sections:\nHow to import?\nImport CSV\nImport large SQL file"}" data-sheets-userformat="{"2":513,"3":{"1":0},"12":0}">How to import?</span></h2>
<p>Open the phpMyAdmin application and create a connection by logging in with the host, user and password. Then, follow the below steps to <a href="https://phppot.com/php/how-to-import-sql-into-a-database-using-php/">import a database</a>.</p>
<p>1) Choose the “Database” link and create a new or select an existing database. In a previous tutorial, we have seen the possible options of <a href="https://phppot.com/mysql/phpmyadmin-create-database/">creating a database using phpMyAdmin</a>.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-20104" src="https://phppot.com/wp-content/uploads/2022/12/create-database-550x221.jpg" alt="create database" width="550" height="221" srcset="https://phppot.com/wp-content/uploads/2022/12/create-database-550x221.jpg 550w, https://phppot.com/wp-content/uploads/2022/12/create-database-300x121.jpg 300w, https://phppot.com/wp-content/uploads/2022/12/create-database.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>2) Choose the file in .sql (or other phpMyAdmin-supported) format.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-20105" src="https://phppot.com/wp-content/uploads/2022/12/choose-import-file-550x325.jpg" alt="choose import file" width="550" height="325" srcset="https://phppot.com/wp-content/uploads/2022/12/choose-import-file-550x325.jpg 550w, https://phppot.com/wp-content/uploads/2022/12/choose-import-file-300x178.jpg 300w, https://phppot.com/wp-content/uploads/2022/12/choose-import-file.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>3) [optional] Choose char-set, SQL compatibility modes and other options like,</p>
<ul>
<li>Foreign key checks.</li>
<li>Partial import.</li>
</ul>
<p><img loading="lazy" class="alignnone size-large wp-image-20106" src="https://phppot.com/wp-content/uploads/2022/12/phpmyadmin-import-options-550x557.jpg" alt="phpmyadmin import options" width="550" height="557" srcset="https://phppot.com/wp-content/uploads/2022/12/phpmyadmin-import-options-550x557.jpg 550w, https://phppot.com/wp-content/uploads/2022/12/phpmyadmin-import-options-296x300.jpg 296w, https://phppot.com/wp-content/uploads/2022/12/phpmyadmin-import-options-768x778.jpg 768w, https://phppot.com/wp-content/uploads/2022/12/phpmyadmin-import-options.jpg 1000w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>Click “Go” to complete the import.</p>
<h2><span data-sheets-value="{"1":2,"2":"Sections:\nHow to import?\nImport CSV\nImport large SQL file"}" data-sheets-userformat="{"2":513,"3":{"1":0},"12":0}">Import CSV</span></h2>
<p>If you have the SQL dumb in a form of a CSV file, the phpMyAdmin allows that format to import.</p>
<p>Change the format to the CSV from the default format. The SQL is the default format that the phpMyAdmin populates under the “Format” section.</p>
<p>It is suitable to import a database containing a single table. If the import file contains multiple tables then the import will merge all into one.</p>
<p>It creates auto-generated columns like COL1, COL2… and stores the other comma-separated values as data.</p>
<p>Note the following <a href="https://phppot.com/php/import-csv-file-into-mysql-using-php/">CSV format to import a database table</a>.</p>
<pre class="prettyprint"><code class="language-html">"id","question","answer" "1"," What are the widely used array functions in PHP?","Answer1" "2","How to redirect using PHP?","Answer2" "3"," Differentiate PHP size() and count():","Answer3" "4","What is PHP?","Answer4" "5","What is php.ini?","Answer5"
</code></pre>
<h2><span data-sheets-value="{"1":2,"2":"Sections:\nHow to import?\nImport CSV\nImport large SQL file"}" data-sheets-userformat="{"2":513,"3":{"1":0},"12":0}">Import large SQL file</span></h2>
<p>Note the maximum file size allowed to upload via the phpMyAdmin application. It is near the “Choose File” option on the Import page.</p>
<p>If the import file is too large, then it interrupts to skip the number of queries during the import.</p>
<p>It is better to process import via Terminal if the import file exceeds the allowed limit. It will prevent the data inconsistency that may occur because of the partial import.</p>
<p>Note the below Terminal command to process importing larger SQL files.</p>
<pre class="prettyprint"><code>#path-to-mysql#mysql -u root -p #database_name# < #path-of-the-sql-file#
</code></pre>
<p>Replace the following variable in the above command</p>
<ul>
<li>#path-to-mysql# – Path where the MySQL is. Example: /Applications/XAMPP/bin/mysql</li>
<li>#database_name# – The target database where the import is going to happen.</li>
<li>#path-of-the-sql-file# – The path of the source SQL to import. Example: /Users/vincy/Desktop/db_phppot_example.sql</li>
</ul>
<p>The command line execution is also used to connect the remote server. It is in case of facing restrictions to access a remote MySQL server via phpMyAdmin.</p>
<h2>Features of the phpMyAdmin Import</h2>
<p>The phpMyAdmin “Import” functionality provides several features.</p>
<ul>
<li>It allows the import of files in the following formats. The default format is SQL.
<ul>
<li>CSV</li>
<li>ESRI shape file</li>
<li>MediaWiki table</li>
<li>OpenDocument spreadsheet</li>
<li>SQL</li>
<li>XML</li>
</ul>
</li>
<li>It allows choosing character sets and SQL compatibility modes.</li>
<li>It allows partial imports by allowing interruptions during the import of larger files.</li>
</ul>
<h2>Things to remember</h2>
<p>When you import a database or table certain things to remember.</p>
<h3>Database resource “Already exists” error</h3>
<p>This error will occur if the importing file contains statements of existing resources.</p>
<p>Example:<br />If the importing file has the query to create an existing table, then phpMyAdmin will show this error.</p>
<p>So, it is important to clean up the existing state before importing a database to avoid this problem.</p>
<h3>Access denied error</h3>
<p>If the users have no permission to import or create databases/tables, then it will return this error.</p>
<p>If the user can import and can’t create tables, the file must contain allowed queries only.</p>
<p><strong>Note:</strong><span data-offset-key="1t8uh-0-1"> If you are importing via remote access, give the right credentials to connect. Make sure about the user access privileges to import or related operations.</span></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/mysql/phpmyadmin-import-database/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2022/12/05/phpmyadmin-how-to-import-a-database/