I am trying to create a dynamic image sitemap scrip using PHP and MySQL. The issue I am having is that I need to grab data from two different tables as follows:
Table 1 called mt_news I want to get the sef_link from it
Table 2 called mt_news_images I want to get the news_images from it
The images in the second table are related to the first one and the only common thing between both tables is news_id
The code I have developed is the following:
<?php
$host = "localhost"; // host name
$user = ""; // database user name
$pass = ""; // database password
$database = ""; // database name
// connecting to database
$connect = @mysql_connect($host,$user,$pass)or die (@mysql_error());
// selecting database
@mysql_select_db($database,$connect) or die (@mysql_error());
// default header(don't delete)
header("Content-Type: text/xml;charset=utf-8");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">';
// mytable = my content table name
$query = @mysql_query('
SELECT mt_news.sef_link, mt_news.addeddate, mt_news_images.news_images
FROM mt_news
INNER JOIN mt_news_images
ON mt_news.news_id=mt_news_image.news_id');
while($row = @mysql_fetch_array($query)){
// = content url
$url = "http://".$_SERVER['HTTP_HOST']."/newsd-".$row['sef_link'];
// [time] = content date
$date = date("Y-m-d", $row['addeddate']);
// [img] = image url
$img = "http://".$_SERVER['HTTP_HOST']."/cadmin/".$row['news_image'];
echo
'<url>
<loc>' . $url .'</loc>
<image:image>
<image:loc>' . $img .'</image:loc>
</image:image>
</url>
';
}
echo '</urlset>';?>
In the image field all the images related to the same article should be showing too
What is the problem you’re having with the code? Nothing obvious stands out, and your query looks as if it should give you the expected results. Are you getting any output or error messages?
A couple of things I should mention:
Firstly, I notice you’re liberally suppressing any potential errors with the @ operator - this is generally not a good idea. There are better ways to check for and handle potential errors (and the suppression is irrelevant in the case of statements like @mysql_connect($host,$user,$pass)or die (@mysql_error()); as you’re already specifying what to do in the even of an error.)
Secondly, the mysql extension has been depreciated (and will be removed from PHP) so it’s advisable to switch to the [fphp]mysqli[/fphp] or [fphp]PDO[/fphp] extension instead.
The simplest way is probably as you output each row from mt_news, do an additional query to grab the images for that item. So you’d have a loop within a loop, if that makes sense.