Create Google Image Sitemap Using Mysql and PHP DOM

Hello friends once again i am here with a new article for creating google image sitemap using PHP. I was working for my website www.tellmequotes.com and i need a image sitemap so that i will create a XML sitemap and submit it to google. As all my images are coming form my website database and my website is having more than 5 Lakh pages. Its very tough to create a sitemap for that much data.

For creating the sitemap we have many options like free sitemap generator’s, Paid sitemap generators etc. But using paid is quite expensive so i thought to create a script which will create the sitemap for images using the mysql database.

I was having 3 tables which is having images so i thought to create a temporary table and save all the images URL to 1 table.

Below we have created the functions for creating 1 table from 3 tables and remove the sitemap file for update and empty the table at last.

// createDataDump function will call the tables 1 by 1 and save the data to a new table.
// When we run createDataDump function it will follow these step's
// 1. Empty the image table and remove the sitemap. 2. Next three lines will collect the data and insert the data into the new file. 3. 5th line will create the sitemap using the Image Table. 4. Now final line will empty the Image table.
public function createDataDump(){
        $this->truncateImageSitemap();
        $this->createAllImageSitemap("table1");
        $this->createAllImageSitemap("table2");
        $this->createAllImageSitemap("table3");
        $this->generateImageSitemap();
        $this->truncateImageSitemapQuery();
    }
// truncateImageSitemap will empty the image table which we have created and delete the sitemap file.
private function truncateImageSitemap(){
 $query="truncate imagesitemap";
 $result=mysqli_query($this->con, $query);
 if($result){
 unlink("imagesitemap.xml");
 }
 else
 {
 }
 }
 
// truncateImageSitemapQuery function will only empty the image table.
 private function truncateImageSitemapQuery(){
 $query="truncate imagesitemap";
 mysqli_query($this->con, $query);
 }
 
// createAllImageSitemap this function will insert the images url from different tables into 1 table "Image Table".
 private function createAllImageSitemap($tablename){
 $search="select Images from $tablename where Images!=''";
 $result=mysqli_query($this->con,$search) or die(mysqli_error($this->con));
 $count=mysqli_num_rows($result);
 if($count < 1)
 {
 }
 else
 {
 while($row= mysqli_fetch_array($result)){
 $imagePath="http://www.tellmequotes.com/quoteImages/$row[Images]";
 $insert="Insert into imagesitemap(Url) values('$imagePath')";
 mysqli_query($this->con,$insert); 
 }
 }
}

Now we have the table with all the url’s and we will create the sitemap using the table.

Note – ONLY 1000 LINKS ARE ALLOWED IN 1 IMAGE SITEMAP.

public function generateImageSitemap(){
$select="select Url from imagesitemap where Url !='' Order By Id DESC";
$siteresult=mysqli_query($this->con,$select);
$count=  mysqli_num_rows($siteresult); // it is giving 432
$totalXml=  ceil($count/1000); // its giving 5
$no_of_records_per_page=1000;

echo $totalXml.",".$no_of_records_per_page.",".$count;

for($i=0;$i<$totalXml;$i++){
    $startfrom=($i)*$no_of_records_per_page;
    echo $selectmap="select * from imagesitemap Where Url!='' Order By Id ASC Limit $startfrom , $no_of_records_per_page";
    $mapresult=mysqli_query($this->con,$selectmap);
    $mapcount=  mysqli_num_rows($mapresult);
    if(!$mapcount < 1){
        
 // For creating the image sitemap namespace
$domDocument = new DOMDocument('1.0', 'utf-8');
$domElement=$domDocument->createElement('urlset');

$domAttribute =$domDocument->createAttribute('xmlns');
$domAttribute ->value="http://www.sitemaps.org/schemas/sitemap/0.9";
$domElement->appendChild($domAttribute );
$domDocument->appendChild($domElement );

$domImageAttribute =$domDocument->createAttribute('xmlns:image');
$domImageAttribute ->value="http://www.google.com/schemas/sitemap-image/1.1";
$domElement->appendChild($domImageAttribute );
$domDocument->appendChild($domElement );


$domElement=$domDocument->appendChild($domElement);
$node=$domDocument->createElement('url');
$node=$domElement->appendChild($node);
$loc=$domDocument->createElement('loc');
$loc=$node->appendChild($loc);
$ntext = $domDocument->createTextNode('http://www.tellmequotes.com');
$ntext = $loc->appendChild($ntext);
        while($row=  mysqli_fetch_array($mapresult))
{
$img=$domDocument->createElement('image:image');
$img=$node->appendChild($img);

$imgloc=$domDocument->createElement('image:loc');
$imgloc=$img->appendChild($imgloc);

$text = $domDocument->createTextNode(mysqli_real_escape_string($this->con,$row["Url"]));
$text = $imgloc->appendChild($text);
}
    $domDocument->save ("imagesitemap".'.xml');
    echo "done";
}
}
}

Here is the final function which will create the sitemap. Here we are using the for loop but at last we are not using its value. In next tutorial we will explain that how we can create the sitemap if we are having more than 1000 Links. Hope you like my tutorial’s if you have any suggestion please comment below. Hope to see you in next tutorial 🙂 .