PHP MySQL – Get Next Auto Increment Value of a Table

We do not often need this but sometimes when we need to retrieve the next auto increment value of a table (without incrementing the auto increment value of course), this solution below will help.

<?php
 
$query = mysql_query("SHOW TABLE STATUS WHERE 
name='your_table_name_here'");

if (mysql_num_rows($query)) {
 $result = mysql_fetch_assoc($query);
 echo $result['Auto_increment'];
} else {//error
 //error control here
}
 
?>

Just replace “your_table_name_here” with your mySQL table name.

As a Function

Furthermore, making it a PHP function would make the solution looks clean.

<?php
 
function get_next_value($tablename) {
 $query = mysql_query("SHOW TABLE STATUS 
WHERE name='".mysql_real_escape_string($tablename)."'");

 if (mysql_num_rows($query)) {
  $result = mysql_fetch_assoc($query);
  return $result['Auto_increment'];
 } else {//error
  return false;
 }
}
 
//usage
$nextval = get_next_value('your_table_name_here');
 
?>

Hope this helps.

Source From: http://zenverse.net/php-mysql-next-auto-increment-value

Backup and Restore MySQL Database Using mysqldump

--> mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table, Lock table and INSERT into sql-statements of the source database. To restore the database, execute the *.sql file on destination database.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql 

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

How To Backup MySQL database

1. Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to abc.sql

# mysqldump -u root -p abc > abc.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

2. Backup multiple databases:

# mysqldump -u root -p --databases database1 database2 > database1_database2.sql

3. Backup all the databases:

# mysqldump -u root -p --all-databases > /tmp/all-database.sql

4. Backup a specific table:

# mysqldump -u root -p abcdatabase usertable > /tmp/abc_user_table.sql

# mysqldump -u root -p test t1 --where="1=1 limit 100" > arquivo.sql

 

 

How To Restore MySQL database

1. Restore a database

# mysql -u root -p databasename < /tmp/all-database.sql 

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -p abc | mysql \ -u root -p --host=remote-server -C abc1 [Note: There are two -- (hyphen) in front of host]

Source From: http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/

Manually installing LAMP (Apache 2, MySQL, PHP 5) and phpMyAdmin on Ubuntu 10.10

In this guide I will show you how to install a LAMP system. LAMP stands for Linux, Apache, MySQL, PHP. The guide is intended to help those who have very little knowlegde of using Linux.

First update your ubuntu packages,
1. Open up the Terminal (Applications > Accessories > Terminal).
2. Copy/Paste the following line of code into Terminal and then press enter:

sudo apt-get update

2. The Terminal will then ask you for you're password, type it and then press enter. 

Install Apache

To start off we will install Apache.
1. Copy/Paste the following line of code into Terminal and then press enter:

sudo apt-get install apache2

Testing Apache

To make sure everything installed correctly we will now test Apache to ensure it is working properly.
1. Open up any web browser and then enter the following into the web address:

http://localhost/

You should see a folder entitled apache2-default/. Open it and you will see a message saying "It works!" , congrats to you!

Install PHP

In this part we will install PHP 5.
Step 1. Again open up the Terminal (Applications > Accessories > Terminal).
Step 2. Copy/Paste the following line into Terminal and press enter:

sudo apt-get install php5 php5-dev libapache2-mod-php5 php5-curl php5-gd php5-idn php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ps php5-pspell php5-recode php5-snmp php5-tidy php5-xmlrpc php5-xsl php5-common

Step 3. In order for PHP to work and be compatible with Apache we must restart it. Type the following code in Terminal to do this:

sudo /etc/init.d/apache2 restart

Test PHP

To ensure there are no issues with PHP let's give it a quick test run.
Step 1. In the terminal copy/paste the following line:

sudo gedit /var/www/testphp.php

This will open up a file called phptest.php.
<?php phpinfo(); ?>Step 2. Copy/Paste this line into the phptest file:

Step 3. Save and close the file.
Step 4. Now open you're web browser and type the following into the web address:

http://localhost/testphp.php

Install MySQL

To finish this guide up we will install MySQL. (Note - Out of Apache and PHP, MySQL is the most difficult to set up. I will provide some great resources for anyone having trouble at the end of this guide.)
Step 1. Once again open up the amazing Terminal and then copy/paste this line:

sudo apt-get install mysql-server mysql-client php5-mysql

You will be prompted to create a password for MySQL.

Now just restart Apache and you are all set!

sudo /etc/init.d/apache2 restart

Install Phpmyadmin
Step 1. Once again open up the amazing Terminal and then copy/paste this line:

sudo apt-get install phpmyadmin





You’ll be prompted by a dialog at some point, make sure to choose Apache 2. After that you need to answer a few more dialogs, the default is fine. Set the passwords accordingly and your done.

You can access phpMyAdmin now from this address: http://localhost/phpmyadmin/

To login to phpMyAdmin use root as the user and the password you entered previously.

Don’t forget to restart Apache:

sudo /etc/init.d/apache2 restart                                                                                

Enjoy..........

Create a Basic Web Service Using PHP, MySQL, XML, and JSON

Web services are taking over the world. I credit Twitter's epic rise to the availability of a simple but rich API. Why not use the same model for your own sites? Here's how to create a basic web service that provides an XML or JSON response using some PHP and MySQL.

With the number of persons hitting your web service (hopefully), you'll need to do adequate validation before attempting to connect to the database to avoid injection attacks. Once we get the desired results from the database, we cycle through the results to populate our return results array. Depending upon the response type desired, we output the proper header and content in the desired format.

The PHP / MySQL

/* require the user as the parameter */
if(isset($_GET['user']) && intval($_GET['user'])) {

  /* soak in the passed variable or set our own */
  $number_of_posts = isset($_GET['num']) ? intval($_GET['num']) : 10; //10 is the default
  $format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default
  $user_id = intval($_GET['user']); //no default

  /* connect to the db */
  $link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
  mysql_select_db('db_name',$link) or die('Cannot select the DB');

  /* grab the posts from the db */
  $query = "SELECT post_title, guid FROM wp_posts WHERE post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts";
  $result = mysql_query($query,$link) or die('Errant query:  '.$query);

  /* create one master array of the records */
  $posts = array();
  if(mysql_num_rows($result)) {
    while($post = mysql_fetch_assoc($result)) {
      $posts[] = array('post'=>$post);
    }
  }

  /* output in necessary format */
  if($format == 'json') {
    header('Content-type: application/json');
    echo json_encode(array('posts'=>$posts));
  }
  else {
    header('Content-type: text/xml');
    echo '<posts>';
    foreach($posts as $index => $post) {
      if(is_array($post)) {
        foreach($post as $key => $value) {
          echo '<',$key,'>';
          if(is_array($value)) {
            foreach($value as $tag => $val) {
              echo '<',$tag,'>',htmlentities($val),'</',$tag,'>';
            }
          }
          echo '</',$key,'>';
        }
      }
    }
    echo '</posts>';
  }

  /* disconnect from the db */
  @mysql_close($link);
}

With the number of persons hitting your web service (hopefully), you'll need to do adequate validation before attempting to connect to the database to avoid injection attacks. Once we get the desired results from the database, we cycle through the results to populate our return results array. Depending upon the response type desired, we output the proper header and content in the desired format.

Take the following sample URL for example:

http://mydomain.com/web-service.php?user=2&num=10

The XML Output

<posts>
  <post>
    <post_title>SSLmatic SSL Certificate Giveaway Winners</post_title>
    <guid>http://davidwalsh.name/?p=2304</guid>
  </post>
  <post>
    <post_title>MooTools FileManager</post_title>
    <guid>http://davidwalsh.name/?p=2288</guid>
  </post>
  <post>
    <post_title>PHPTVDB: Using PHP to Retrieve TV Show Information</post_title>
    <guid>http://davidwalsh.name/?p=2266</guid>
  </post>
  <post>
    <post_title>David Walsh: The Lost MooTools Plugins</post_title>
    <guid>http://davidwalsh.name/?p=2258</guid>
  </post>
  <post>
    <post_title>Create Short URLs Using U.Nu</post_title>
    <guid>http://davidwalsh.name/?p=2218</guid>
  </post>
  <post>
    <post_title>Create Bit.ly Short URLs Using PHP</post_title>
    <guid>http://davidwalsh.name/?p=2194</guid>
  </post>
  <post>
    <post_title>Represent Your Repositories Using the GitHub Badge!</post_title>
    <guid>http://davidwalsh.name/?p=2178</guid>
  </post>
  <post>
    <post_title>ZebraTable</post_title>
    <guid>http://davidwalsh.name/?page_id=2172</guid>
  </post>
  <post>
    <post_title>MooTools Zebra Table Plugin</post_title>
    <guid>http://davidwalsh.name/?p=2168</guid>
  </post>
  <post>
    <post_title>SSLmatic: Quality, Cheap SSL Certificates and Giveaway!</post_title>
    <guid>http://davidwalsh.name/?p=2158</guid>
  </post>
</posts>

Take this next sample URL for example:

http://mydomain.com/web-service.php?user=2&num=10&format=json

Now, we can take a look at the possible results of the URL.

The JSON Output

{"posts":[{"post":{"post_title":"SSLmatic SSL Certificate Giveaway Winners","guid":"http:\/\/davidwalsh.name\/?p=2304"}},{"post":{"post_title":"MooTools FileManager","guid":"http:\/\/davidwalsh.name\/?p=2288"}},{"post":{"post_title":"PHPTVDB: Using PHP to Retrieve TV Show Information","guid":"http:\/\/davidwalsh.name\/?p=2266"}},{"post":{"post_title":"David Walsh: The Lost MooTools Plugins","guid":"http:\/\/davidwalsh.name\/?p=2258"}},{"post":{"post_title":"Create Short URLs Using U.Nu","guid":"http:\/\/davidwalsh.name\/?p=2218"}},{"post":{"post_title":"Create Bit.ly Short URLs Using PHP","guid":"http:\/\/davidwalsh.name\/?p=2194"}},{"post":{"post_title":"Represent Your Repositories Using the GitHub Badge!","guid":"http:\/\/davidwalsh.name\/?p=2178"}},{"post":{"post_title":"ZebraTable","guid":"http:\/\/davidwalsh.name\/?page_id=2172"}},{"post":{"post_title":"MooTools Zebra Table Plugin","guid":"http:\/\/davidwalsh.name\/?p=2168"}},{"post":{"post_title":"SSLmatic: Quality, Cheap SSL Certificates and Giveaway!","guid":"http:\/\/davidwalsh.name\/?p=2158"}}]}

Creating a basic web service is very simple and encourages your users to spread the word about your website or service. Want more traffic? Want your website to grow without you putting in all the effort? Create a web service!.

Source from: http://davidwalsh.name/web-service-php-mysql-xml-json