PHP & MySQL Dynamic Image Sitemap Code Help

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:

$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=""

// 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'];


     <loc>' . $url .'</loc>
<image:loc>' . $img .'</image:loc>

    echo '</urlset>';?>

In the image field all the images related to the same article should be showing too

Hi wesaml, welcome to the forum!

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.

Hi fretburner,

Thank you for your feedback.

The problem is that I don’t have any errors and still cant get results (check here)

When extracting data from one table it works perfectly fine but once I try getting data from two tables I don’t get any results…

Have you tried entering your query manually (using phpMyAdmin or similar) to see if it actually returns any records?


Which is it?

Ha, good to have an extra pair of eyes - well spotted, Drummin

Drummin you are correct now its fixed thanks guys.

One more thing. How can I show all the images related to an article under the same url? Currently it shows one url and one image only (check here)

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.

Thanks fretburner
I guess I will give it a try the way it is