Using MySql replace function

MySql has a very useful function replace() which allows you to replace substring or characters. It is really useful while working with huge database table where you want to update certain values with another value. Its syntax is quite simple.

   REPLACE ( field_name, 'find_string', 'replacement_string' );

To update some values in database table use query as follows using replace function.

   UPDATE tutorials SET topic = REPLACE (topic, 'Php', 'Php MySql');

You can also run a select query using this function if you don't want to update table but want to fetch data as replaced. This will only output replaced values and there will be no changes in database.

   SELECT REPLACE (topic, 'Php', 'Php MySql') as new_topic 
   FROM tutorials 
   WHERE topic = 'Php';

Mashable Style Drop Down Menu using PHP, Mysql and jQuery

Everyone knows that Mashable is the world’s largest independent website dedicated to news, information, technology and resources for the connected generation. Now I am going to explain you about how to create mashable style dynamic drop down menu using Php, Mysql & jQuery Ajax.

To read more about this article Click Here

For Demo Click Here

To download files Click Here

Facebook style Hashtag System using PHP, MYSQL and jQuery

If you are not aware of Hashtag, then I will say you would not using any social networking sites in a very large extent.  Here I am going to explain you about the advantages of hashtag & how you can implement it in your web projects using php & mysql.

Hashtags are essentially a filter, allowing people to see the conversation associated with that word or phrase. Hashtags is alternative to tags..  Twitter is the first site to introduce the hashtag. But now a days sites like facebook, Instagram, Vine & Tumblr are using this feature in all platforms. If you use the hashtag effectively then you will get the contents across different sites together in a single page.

To read more about this article Click Here

For Demo Click Here 

To download files C

Check and Validate Username Without Page Refresh using PHP and Mysql

1. Checks if a username is available or not (in the database).  2. If the username is available, the program will output “[your_username] is available!” 3. Then if it is not available, “Username already taken” will be printed. 4. This code also has a simple validation that states whether the inputted username is too short or is empty. 

All those tasks will be performed via AJAX, so it is without page refresh. You can expand its validation though.

To read more about this article Click Here

For Demo Click Here

To download files Click Here

Shopping Cart System using PHP Sessions and Mysql

If you want to build your own online shopping cart from scratch, this post can help you get it done because today, we are building a simple website with shopping cart using PHP and MySQL. I also assume you already know something about PHP sessions.

To read more about this article Click Here

For Demo Click Here 

To download files Click Here

Create a Dynamic Pie Chart using PHP and MySQL

Graphical or visual representation of data is usually a requirement for a software, mostly, business apps. Today I'm going to show you the two and free ways to generate dynamic Pie Charts for your web applications. We say "dynamic" because the data that will be shown in the pie chart were pulled from a database which can be updated frequently. 

To more about this article Click Here

For Demo Click Here

To download files Click Here

Web Service Using PHP, MySQL, XML, and JSON

Today Web Services gain much popularity in web world. There are thousands of Web Services availble for updating E-Commerce, schools, stock market database etc. Actually Web services are just Web APIs that can be accessed over a network, such as Internet, and executed on a remote system hosting the requested services. There are three basic platform for We Services, these are SOAP, WSDL and UDDI.

Here we will discuss to create basic web service that provides an XML or JSON response using some PHP and MySQL.

The PHP / MySQL

/* require the user as the parameter */
if(isset($_GET['myuser']) && intval($_GET['myuser'])) {
$number_of_posts = isset($_GET['number']) ? intval($_GET['number']) : 20;
$format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml';
$user_id = intval($_GET['myuser']);
/* connect to mysql database */
$link = mysql_connect('localhost','username','password') or die('Can not connect to the Database');
mysql_select_db('mydb_name',$link) or die('Can not select the Database');
/* select records from the database */
$query = "SELECT post_title, guid FROM posts WHERE wp_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 array of the records */
$posts = array();
if(mysql_num_rows($result)) {
while($post = mysql_fetch_assoc($result)) {
$posts[] = array('post'=>$post);
}
}
/* output in required 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>';
}
/* close database connection */
@mysql_close($link);
}

We will take the following sample URL for example:

http://mydomain.com/web-service.php?user=5&num=3

Below is the possible results of the above URL.
 

This is XML Output 

<posts>
<post>
<post_title>Now YouTube Video Player Uses HTML5</post_title>
<guid>http://www.phpzag.com/?p=1568</guid>
</post>
<post>
<post_title>PHP – Parse YouTube URLS</post_title>
<guid>http://www.phpzag.com/?p=1473</guid>
</post>
<post>
<post_title>What is HTML5?</post_title>
<guid>http://www.phpzag.com/?p=1399</guid>
</post>
</posts>

Now We will go for next sample URL for example:

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

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

The JSON Output

{"posts":[{"post":{"post_title":"Now YouTube Video Player Uses HTML5","guid":"http:\/\/phpzag.com\/?p=1568"}},{"post":{"post_title":"PHP – Parse YouTube URLS","guid":"http:\/\/phpzag.com\/?p=1473"}},{"post":{"post_title":"What is HTML5?","guid":"http:\/\/phpzag.com\/?p=1399"}}]}

Create Ajax Search Using PHP jQuery and MYSQL

This tutorial shows how to create simple and attractive Ajax based search using PHP, jQuery, MySQL and Ajax.

To read more about this article Click Here

For Demo Click Here

Click Here to download files

Super Ajax Polling/Voting System using JQuery, Ajax, PHP and MySQL

Polling system or Voting system is very common in web sites. Voting can be about your site or blog or some other thing just to get the user attention and get your idea about your product. Hope You like it and don’t forget to subscribe and give your feed back in comments. Thanks !

To read more about this article Click Here

For Demo Click Here

To download files Click Here

PHP’s Built-In MYSQL Functions

The MySQL database server is one of the most popular open source databases in the world. Most of php application developed using mysql database. PHP has many built-in MySQL functions which works with MySQL to execute queries. these functions help you to manage your database and speed-up development.

 

Below is the list of most used PHP/MYSQL built in functions in php.

To read more about this article Click Here

 

Making a Donation Center With PHP, MySQL and PayPal’s APIs

PayPal provides numerous APIs and integration options for third-party developers. One of these is the Donation button, which you can generate straight from PayPal’s site and include directly into your pages. Conversion rate for these buttons is typically minimal, but the right incentive can make a big difference.

 

The idea is to have a dedicated Donation Center. This is a place, where you get to see what a difference your donation would make, and a list of people who have already donated.

To read more about this article Click Here

For Demo Click Here

 

PHP & MySQL File Download Counter

It has been a while since we’ve done a proper PHP & MySQL tutorial here, at Tutorialzine, so today we are creating a simple, yet robust, file download tracker. Each file will have a corresponding row in the database, where the total number of downloads is saved. PHP will update the MySQL database and redirect the visitors to the appropriate files.

 

To track the number of downloads, you just need to upload your files to the files folder, and use a special URL to access them.

To read more about this article Click Here

For Demo Click Here

 

Create a JSON string from mysql database

JSON stands for JavaScript Object Notation which is basically a data-interchange format. JSON is used where we want to send data as an object, its either be an array or string. When compared to XML it is easily parsable and mostly used where we need to transfer data.

There are mainly two JSON functions used while dealing with it.

json_encode() and json_decode()

json_encode() -
It returns a string containing the JSON representation of value.

json_decode() -
It takes a JSON encoded string and converts it into a PHP variable. When second parameter to json_decode is set to true it return an array otherwise it returns an object.

Let us create a JSON string from php mysql database.

   $sql="SELECT * FROM articles";
    $res=mysql_query($sql);
    $rows = array();
    
    while($row = mysql_fetch_assoc($res)) {
        $rows[] = $row;
    }
    $jsonString = json_encode($rows);

$jsonString contains the JSON string. Again to get it back as php variable we have to decode it as follows.

   $back = json_decode($jsonString,true);
    
    foreach($back as $records){
      echo "<br />";
      print_r($records);
    }

In above example the second parameter of json_decode() function is set to true. So it decodes the JSON string as an associative array. It decodes the string as an object by default. Skip the second parameter if you want to get the result as an object or set it to false.


The third function of JSON family is json_last_error()
It returns the last error (if any) occurred during the last JSON encoding/decoding. You can read more about it here.

Thumbs up and down rating system with jQuery, PHP and MySQL

In this series we take a look at how to create a rating system with thumbs up and down. The ratings will be saved to the MySQL database using PHP PDO class.

To read more about this article Click Here

For Demo Click Here

 

Create a shoutbox using PHP and AJAX (with jQuery)

Continuing with the tutorials about AJAX and jQuery we will create a stunning and dynamic shoutbox based in PHP and AJAX (using jQuery).

We will learn how to create a dynamic ajax based shoutbox with jQuery from scratch. It will be very interesting to know how to use the ajax function of jQuery and how it can be used to insert and recover data from a MySQL database via PHP in a way asynchronous.

To Read more about this article Click Here

For Demo Click Here

Using full-text search in php mysql

Want to search the text stored in MySQL database. Here php full-text search works for us. So first the question is, What is full-text search and what is it all about?

A full-text search is a built in functionality in MySQL that allows us to search for any string through the tables. We always perform our searches with simple 'like' statement but this doesn't give perfect result. Thanks to full-text search.

Features of full-text search

• Searches are not case sensitive
• Short words are ignored, the default minimum length is 4 characters.
• Very common words like “i”, “in”, “on”, also  called stopwords are ignored. You can see a list of the standard MySQL stopwords here.
• You can disable stopwords by setting the variable in the MySQL configuration.
• Fulltext searches can only be made on “text” fields.
• If a word is present in more than 50% of the rows, it will have a weight of zero. This means that there will be no search results. This is mostly a problem if you’re testing with a limited dataset
• MySQL requires that you have at least three rows of data in your result set before it will return any results.
• By default, your search query must be at least four characters long and may not exceed 254 characters.

As we discussed above full-text search works only on text fields. So make sure that our fields are of type text.
Now we have to create a full-text index on the fields on which we want to perform our search operation. So to proceed further we must alter our table with this code.

    ALTER TABLE articles ADD FULLTEXT(title, description);

Now our table is ready for search query. A simple search query..

    SELECT * from articles WHERE MATCH (title, description) AGAINST('computer');

Further now we can modify our query so that the best matches will be displayed first.

   SELECT *, MATCH(title, description) AGAINST ('computer')
   AS score from articles WHERE MATCH (title, description)
   AGAINST('computer') order by score desc;

Mysql also gives us IN BOOLEAN MODE modifier to perform boolean full-text searches. A boolean search allows us to narrow our results through the use of Boolean operators.

One thing to note here that
Boolean searches can work even without a FULLTEXT index.

   SELECT * from articles WHERE MATCH (title, description)
   AGAINST('computer technology' IN BOOLEAN MODE);

Above example will search all the rows which contains either 'computer' or 'technology'.

Now let us use boolean operators in our query to narrow our results.
The most commonly used operators are

+
A leading plus sign indicates that this word must be present in each row that is returned.

-
A leading minus sign indicates that this word must not be present in any of the rows that are returned.

"
Double quotes at the beginning and end of a phrase, matches only rows that contain the complete phrase, as it was typed.


Examples of  Boolean operaters used in query:

'+computer +technology'
Find rows that contain both words 'computer' and 'technology'

'+computer -technology'
Find rows that contain 'computer' but not 'technology'

'"computer technology"'
Find rows that contain exact phrase 'computer technology'

   SELECT * from articles WHERE MATCH (title, description)
   AGAINST('+computer -technology' IN BOOLEAN MODE);

MySQL password hashing

Whenever you upgrade your MySQL installation, make sure to upgrade any client that uses it.

In some cases, clients that use a version prior to 4.1 will have a problem authenticating against the MySQL database if the latter has a post 4.1 version.

The trick is that after 4.1 (i.e. 4.11 and up), MySQL changed the way it stores the passwords in the user table inside the mysql system database.

Password hashes are now 41 bytes long instead of the old 16 bytes.

So for example, if your MySQL server is 5.0, while your php-mysql library is 4.1, your web applications will fail to connect to the database. As such, it is recommended that you upgrade the client.

In any case, MySQL offers a way to change the hash back into the old format. For the sake of argument, assume the user in question is john, and you want to be able to connect using password dummy. In this case, connect to your MySQL server from the prompt (SSH and use ‘mysql -u root -p mysql’ on linux, or go to your mysql/bin windows directory and execute the same query), then issue the following queries:

update user set Password=OLD_PASSWORD(‘dummy’) where User=’john’;
flush privileges;



the OLD_PASSWORD() function will generate the old 16 bytes hash. The first query will eventually update the user password to use this hash. The second query is necessary in order for the MySQL service to re-read the new user privileges.


PS: if your root password is not working, refer as well to the guide on resetting it.


Alternatively, if your database has many users and you didn’t keep track of them, you can use the following query and it will return usernames that are using the new hash


select distinct(User) from User where LENGTH(Password)!=41;

Resetting your mysql root password

It is quite frequent that an administrator simply forgets his mysql's root password.

Luckily, it is quiet easy to reset it, here are the steps:

1. SSH as root to your machine
2. Turn off the mysqld daemon if running
• RedHat/Fedora users can do so by executing:  service mysqld stop
3. Run safe_mysqld by executing:
• safe_mysqld --skip-grant-tables
  (this will run allow you to connect without a password)
4. Open a second shell / SSH again and execute:
• mysql mysql
  (to directly connect and select the mysql database which contains the user authentication data)
5. On the mysql prompt, execute:
• update user set password=password('newpassword') where user='root';
  where newpassword is your newly chosen password.
6. That's it! close everything and start your mysql daemon again:
• service mysqld start

Setting up MYSQL Database Replication

Setting up a database replication is one of many steps that should be taken in order to preserve data, preventing any loss and making disaster recovery easier.Luckily, it’s easy with MySQL. So let’s suppose we have two servers running MySQL, one called host1 and the other host2.

Replication can be either master-master or master-slave. With a master-slave replication, the slave always replicates what the master database is executing. In master-master replication, both databases synchronize with each others.

For the purpose of this tutorial, a master-slave (here host1 and host2 respectively) scenario is examined.

First of all, open the mysql config file on host1 (usually found at /etc/my.cnf on linux, and c:\windows\my.ini on windows), and uncomment (remove the hash of) the following line:

#skip-networking

Secondly, you need to specify the file where the master (host1) should log (write) the queries it’s executing. This will enable the slave (host2) to read these queries and execute them as well. As such, add a line such as:

log-bin = /path/to/mysql-bin.log

where the value above is the path to file where MySQL should be doing the logging. You could very well create a separate directory or use the default mysql installation directory (such as c:\program files\mysql\ on windows or /var/lib/mysql on linux)

Then, you need to specify the name of the database in question. So if you’re setting up replication for one of your MySQL databases called ‘work_data’, then, this is the line you need to add to your MySQL config:

binlog-do-db = work_data

Finally, you need to specify a server id, which says that this is the master server

server-id=1

Save the config file and exit.

Now you need to give host2 the permission to replicate the data. As such, a MySQL query needs to be issued on the master.

So on host1, login to the MySQL prompt (mysql -u root -pyour_root_password) (or PHPMyAdmin, etc… whatever you use), and issue the following statement:

grant replication slave on *.* to ‘username’@'%’ identified by ‘password’;

Make sure to replace username and password with a credential of your choice. Do keep the single quotes though.

The % sign means that the slave can connect from any host. If you want it to be more secure, replace that with host2 (the slave’s hostname).

After all the above is done, restart the MySQL service (service mysqld restart (linux) or, net stop mysql, net start mysql (on windows)).

If the database had data earlier, make sure you dump it and load it on the slave before doing any of the above. Dumping data is easy and can be done by cd’ing to the MySQL bin directory and issuing:

mysqldump -Q -u root -pyour_root_password databasename > database_dump.sql

(replace the password and database name with the correct login). The whole database will now be in the file called database_dump.sql

To import it on host2, cd to the mysql bin directory and issue:

mysql -u root -pyour_root_password databasename < /path/to/the/file/database_dump.sql

The MySQL config file on host2 should have the following lines:

server-id=2

master-host = host1

master-user = username

master-password = password

master-port = 3306

where host1 is the master’s hostname/IP, and username and password are the credentials you used when granting replication access a few steps above. 3306 is the port MySQL is running on (which is the default)

Then start the slave process on host2 by issuing at the MySQL prompt:

start slave;

To make sure replication is working, issue the following SQL query on host1:

show slave status \G

(Slave_SQL_Running and Slave_IO_Running should report ‘Yes’)

Good luck

Export MYSQL data into Excel/CSV via php

Today i came across a functionality where i need to Export the MYSQL data into CSV/Excel file via PHP function/script. There are such requirement where client needs to Export the MYSQL data (Order data,Member data,Newsletter emails etc) into Excel sheet or CSV file for future reference or need to send to other team for future work.

You can give a button or link from where client can click on it and get a Excel or CSV file with all data from MYSQL database tables using(through) PHP.

Here i am sharing a function using which you can easily export the MYSQL data into Excel/CSV with a single click on button or link. If you are looking to export the filtered data than you can pass parameters into function and make a sql query accordingly. Do you know how to Import CSV/Excel data into MYSQL ?

<?php
function export_excel_csv()
{
    $conn = mysql_connect("localhost","root","");
    $db = mysql_select_db("database",$conn);
    $sql = "SELECT * FROM table";
    $rec = mysql_query($sql) or die (mysql_error());
    $num_fields = mysql_num_fields($rec);
    for($i = 0; $i < $num_fields; $i++ )
    {
        $header .= mysql_field_name($rec,$i)."\\t";
    }
    while($row = mysql_fetch_row($rec))
    {
        $line = '';
        foreach($row as $value)
        {                                            
            if((!isset($value)) || ($value == ""))
            {
                $value = "\\t";
            }
            else
            {
                $value = str_replace( '"' , '""' , $value );
                $value = '"' . $value . '"' . "\\t";
            }
            $line .= $value;
        }
        $data .= trim( $line ) . "\\n";
    }
    $data = str_replace("\\r" , "" , $data);
    if ($data == "")
    {
        $data = "\\n No Record Found!\n";                        
    }
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=reports.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\\n$data";
}
?>

What you need to do is…
1) Copy above function and paste it into your file.
2) Change MYSQL connection settings in mysql_connect("localhost","root","").
3) Change database name in mysql_select_db("database",$conn)
4) Change table name in $sql = "SELECT * FROM table".
5) Thats it.

Let me know your thoughts for the same. If you face any problem in this than let me know via comment