SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    PHP Code:
    <?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)){

    // [url] = 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

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,407
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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 mysqli or PDO extension instead.

  3. #3
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,407
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Have you tried entering your query manually (using phpMyAdmin or similar) to see if it actually returns any records?

  5. #5
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    733
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    mt_news_images
    VS
    mt_news_image

    Which is it?

  6. #6
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,407
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by Drummin View Post
    mt_news_images
    VS
    mt_news_image

    Which is it?
    Ha, good to have an extra pair of eyes - well spotted, Drummin

  7. #7
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  8. #8
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,407
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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.

  9. #9
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks fretburner
    I guess I will give it a try the way it is


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •