PHP and mySQL | Mo Ghaoui, Personal Blog

Backup/restore mySQL DB using phpMyAdmin/SSH

admin

Apr 2 |07:48

Step 1 – Backing up using phpMyAdmin

Note: Most good hosts provide phpMyAdmin (mySQL administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.

Login to phpMyAdmin.
Select the database you wish to use.
On the navigation bar on the top, select “Export” (besides SQL, Structure, Search, Query… etc.)
You should now be presented with a nifty little page which shows all the tables in the database, SQL Options and Save Types. *
To backup the whole database, click “Select All” under the list of tables in the page.
To backup a selected few, just hold down CTRL on your keyboard and select the tables you wish to backup (release the key when all selecting is done).
SQL Options can be left as default, but I suggest ticking the “Add DROP TABLE” option as if you are restoring a backup on a database that already exists and has the table in it – you will get a lot of errors! This way, by ticking the “Add DROP TABLE” option you will avoid the errors. For more information on the options just click the little “?” icon after SQL Options.
If you want to save the backup as a file (recommended) then tick “Save as file” – Leave file name as is or edit for your needs. Select “gzipped” as compression. Now click “Go”. If asked, choose “Save to disk” and save it wherever on your computer (it may take sometime depending on the size).
If you want to show the whole backup SQL on your browser window, don’t tick “Save as file” – once you have selected the tables you wish to backup (explained in step 5/6) click “Go”. The page will now change and should show the SQL information (it may take sometime depending on the size). Copy and paste it to a text file or do whatever you want with it!

Note: Yes, the tables of the database will be shown to the far left in a frame, but they will also be displayed in a different form on this new page to the right in phpMyAdmin.

Congratulations! You have successfully backed up your database / selected tables!

Step 2 – Restoring your database using phpMyAdmin

Note: Most good hosts provide phpMyAdmin (mySQL Administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.

Login to phpMyAdmin.
Select the database you wish to use.
On the navigation bar on the top select “SQL” (besides Export, Structure, Search, Query… etc.)
You should now be presented with a little page which allows you to run SQL query/queries on the database, either by inputting the query/queries to the input box or by locating a text file on your computer.
You now have 2 options: 1) Paste the SQL which you backed up earlier in the “Input Box” and click “Go” or 2) If you saved the file on your PC then use the option below the first one; Click Browse > Locate the File > Click “Go”. (Note: Most servers set a “Max File Size” for uploading the SQL, the size appears beside the Browse button.)
It may take sometime for the file to be uploaded and fully run, so give it a chance. Once it worked, it will bring you back to the same page and should confirm if all went well “Your SQL-query has been executed successfully: The content of your file has been inserted.”

Congratulations! If all went well, you have successfully restored your backed up database!

Step 3 – Backing up using SSH

Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don’t know how to use it? Search on Google for a tutorial.

Connect to your host via SSH, login and run: mysqldump –opt -u user -p dbname > {path}/backup.sql
You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line).

Congratulations! You successfully backed up your database using SSH.

Step 4 – Restoring using SSH

Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don’t know how to use it? Search on Google for a tutorial.

Connect to your host via SSH, login & run: mysql -u user -p dbname < {path}/backup.sql
You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line).

Congratulations! You have successfully restored your database using SSH.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

admin

Feb 9 |06:51

The error by itself is not from 1 single reason, but rather whenever there is no result to be arrayed,
There is two cases you should be aware of

1- The page is not loading and throwing off this error, in which you surely have a wrong statement and should revise it, in that case, there is not one fixed solution but rather a code revision.

2- This is the case where the page loads normally but yet throwing this error, which is the most confusing to many people, so when you get this error but the page loads, you do not have a coding syntax issue but rather a coding workaround that mostly resolves it.

Wherever the line that throws this error use the @ sign before the statement ..

In my case it was a creloaded template throwing off this error, most templates can be poorly coded as they are not fully tested with the original product , here is how it looked like :

At the error line number there was ”

  function tep_db_num_rows($db_query) {
    return mysql_num_rows($db_query);
  }
By itself, its not enough to figure what it is, so we go back
to the template (tpl) where it uses this function and we find

 if (tep_db_num_rows($sub_products_sql) ==0) {
                      echo TEXT_ENTER_QUANTITY . ":  " . tep_draw_input_field('cart_quantity', '1', 'size="6"');
               }
We change the first line adding the @ sign
So it becomes :
 if (@tep_db_num_rows($sub_products_sql) ==0) {
                      echo TEXT_ENTER_QUANTITY . ":  " . tep_draw_input_field('cart_quantity', '1', 'size="6"');     

Using this method, will parse the error .
NB : Using error_reporting(0); in your php is not sufficient in such cases to hide the errors.

Hope this tip helps.

               }

Getting all _post or _get variables, array or per 1

admin

Jan 29 |06:32

By default, all forms submitted values, whether by method=get or method=post

It will result in 1 global variable

$_POST or $_GET

With the $_post array
if(isset($_post['variable_in_question'])) echo “yea”;
This is supposing you know the variable
Or simply printing it out or adding it to variable as in

$myvar = $_POST[variable_in_question];

Elsewhere, if you do not know all variables coming in, or you want to print out all posted or got items from a form, here is how to put them in an array :

Simplest print method to know what are the printed variables is
echo ‘<pre>’;
print_r($_post);
echo ‘</pre>’;
To array those variables :
foreach ($_POST as $var => $value) {
echo “$var = $value<br>n”;
}

In my example, i wanted to re-create a link, with the posted information, so what i did was:

foreach ($_GET as $var => $value) {
echo “$var=$value&”;
}
$myurl=”http://wwwDOTexampleDOTcom/main.php?$var”

That was since i was going to repull another link with same variables from unknown coming post items.
ps: switch get or post according to your predefined form method.

Synchronise database field columns in mysql

admin

Jan 28 |06:42

This post is useful if you are having a similar issue , which in my case was like this

I was importing a drupal database, using node_import , and importing tables that way will double md5 passwords fields.

On the old database, the passwords are already md5′d .
Drupal login will md5 it.

So two solutions can occur to mind,

1: disable md5′ing from login system (user’s module)

2: keep original login system and sync database 1 time, and problem is solved.

Here is the short method to do so….

First, i took out the old table with users and md5′d passwords from the database 1.

Opened a new table, inserted the values in .

(this step is needed just to simplify the update command without connecting to other database especially if they are not on same localhost as it was in my case)

Step two, consider fields to sync.

T1: has username, password, etc
T2: has user, pass, etc

So we want to update the pwd on table T2 to have the password from T1 based on the username (there must be an identified or common field to use ofcourse)

The table 2 i had was called enusers and the old one was called users.

From phpmyadmin or any mysql command line :

UPDATE enusers set enusers.pass = (select users.password from users where users.username = enusers.user)

Problem solved,

Password are updated with the old md5′d passwords from past table and tables synced.

nb: Do this step after using the node_import as you would do usually, for more information about node_import click here

Category: PHP and mySQL

Backup/restore mySQL DB using phpMyAdmin/SSH

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

Getting all _post or _get variables, array or per 1

Synchronise database field columns in mysql

Categories

Archives

Tags