Generate sitemap via PHP, variable error!

[-Rock Lee-]

I am trying to adapt this code to generate a PHP sitemap to be updated as requested. I have been repairing the errors but I can not solve this "$message = array();" it marks me as "unexpected error T_VARIABLE" I have tried several ways but could not solve it. Would someone have an idea?

Code Select Expand

<?php require_once('./SSI.php');if ($modSettings['smfVersion'] < '2.0.15') ob_start('ob_sessrewrite');header('Content-Type: text/plain');$result = $smcFunc['db_query']('', ' SELECT t.id_topic as id_topic, m.poster_time as poster_time, m.id_msg as id_msg FROM {db_prefix}topics as t LEFT JOIN {db_prefix}messages as m ON (t.id_last_msg = m.id_msg) LEFT JOIN {db_prefix}boards as b ON (t.id_board = b.id_board) WHERE t.id_board = {int:id_board} AND {query_see_board} ORDER BY m.poster_time DESC LIMIT 49999' ) $message = array(); while ($row = $smcFunc['db_fetch_assoc']($request)) { $message[] = array( 'id' => $row['id_topic'], 'id_msg' => $row['id_msg'], 'time' => $row['poster_time'], )    }     $smcFunc['db_free_result']($request); $request = $smcFunc['db_query']('', ' SELECT b.id_board as id_board, m.poster_time as poster_time FROM {db_prefix}boards as b LEFT JOIN {db_prefix}messages as m on (b.id_last_msg = m.id_msg) WHERE t.id_board = {int:board} AND {query_see_board} ORDER BY m.poster_time DESC LIMIT 1000'     ) $boards = array(); while ($row = $smcFunc['db_fetch_assoc']($request)) { $boards[] = array( 'id' => $row['id_board'], 'time' => $row['poster_time'], ); } $smcFunc['db_free_result']($request);     echo '<?xml version="1.0" encoding="UTF-8"?' . '><urlset xmlns="https://www.sitemaps.org/schemas/sitemap/0.9/">';        echo ' <url> <loc>', $scripturl, '</loc> <lastmod>', date_iso8601(), '</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>';foreach ($boards as $board){ echo ' <url> <loc>', $scripturl, '?board=' . $board . '.0</loc> <lastmod>', date_iso8601($board['time']), '</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>';}foreach ($messages as $message){ echo ' <url> <loc>', $scripturl, 'topic=' . $topic . '.msg' . $_REQUEST['msg'] . '#msg' . $_REQUEST['msg'], '</loc> <lastmod>', date_iso8601($message['time']), '</lastmod> </url>';}echo '</urlset>';function date_iso8601($timestamp = '') { $timestamp = empty($timestamp) ? time() : $timestamp; $gmt =  substr(date("O", $timestamp), 0, 3).':00'; return date('Y-m-d\TH:i:s',$timestamp).$gmt;}?>


Regards!

[Shambles]

You omitted a semicolon

Code Select Expand

ORDER BY m.poster_time DESC
LIMIT 49999'
);

[-Rock Lee-]

Now he tells me: syntax error, unexpected '}' it seems it is by the while although looking at the documentation is the correct way in theory.

[Shambles]

You have other errors too:

use this instead:

Code Select Expand

<?php require_once('./SSI.php');if ($modSettings['smfVersion'] < '2.0.15') ob_start('ob_sessrewrite');header('Content-Type: text/plain');$result = $smcFunc['db_query']('', ' SELECT t.id_topic as id_topic, m.poster_time as poster_time, m.id_msg as id_msg FROM {db_prefix}topics as t LEFT JOIN {db_prefix}messages as m ON (t.id_last_msg = m.id_msg) LEFT JOIN {db_prefix}boards as b ON (t.id_board = b.id_board) WHERE t.id_board = {int:id_board} AND {query_see_board} ORDER BY m.poster_time DESC LIMIT 49999' ); $message = array(); while ($row = $smcFunc['db_fetch_assoc']($request)) $message[] = array( 'id' => $row['id_topic'], 'id_msg' => $row['id_msg'], 'time' => $row['poster_time'], );       $smcFunc['db_free_result']($request); $request = $smcFunc['db_query']('', ' SELECT b.id_board as id_board, m.poster_time as poster_time FROM {db_prefix}boards as b LEFT JOIN {db_prefix}messages as m on (b.id_last_msg = m.id_msg) WHERE t.id_board = {int:board} AND {query_see_board} ORDER BY m.poster_time DESC LIMIT 1000'     ); $boards = array(); while ($row = $smcFunc['db_fetch_assoc']($request)) { $boards[] = array( 'id' => $row['id_board'], 'time' => $row['poster_time'], ); } $smcFunc['db_free_result']($request);     echo '<?xml version="1.0" encoding="UTF-8"?' . '><urlset xmlns="https://www.sitemaps.org/schemas/sitemap/0.9/">';        echo ' <url> <loc>', $scripturl, '</loc> <lastmod>', date_iso8601(), '</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>';foreach ($boards as $board){ echo ' <url> <loc>', $scripturl, '?board=' . $board . '.0</loc> <lastmod>', date_iso8601($board['time']), '</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>';}foreach ($messages as $message){ echo ' <url> <loc>', $scripturl, 'topic=' . $topic . '.msg' . $_REQUEST['msg'] . '#msg' . $_REQUEST['msg'], '</loc> <lastmod>', date_iso8601($message['time']), '</lastmod> </url>';}echo '</urlset>';function date_iso8601($timestamp = '') { $timestamp = empty($timestamp) ? time() : $timestamp; $gmt =  substr(date("O", $timestamp), 0, 3).':00'; return date('Y-m-d\TH:i:s',$timestamp).$gmt;}?>

[vbgamer45]

I just use this mod https://custom.simplemachines.org/mods/index.php?mod=755 still works on 2.0.x

[-Rock Lee-]

@Sh@mblesYou can see my mistakes now, it marks me the following which is an advance, it will be a question to see how I fix it. Thank you!

Code Select Expand


<div id="fatal_error">
<div class="cat_bar">
<h3 class="catbg">
¡Un error ha ocurrido!
</h3>
</div>
<div class="windowbg">
<span class="topslice"><span></span></span>
<div class="padding">The database value you're trying to insert does not exist: id_board</div>
<span class="botslice"><span></span></span>
</div>
</div>
<div class="centertext"><a href="javascript:history.go(-1)">Atrás</a></div>

And @vbgamer45 I did not remember it, I'm going to look at it, although what I want to achieve is from the main domain, and not from a subfolder. For example www.Mydomain.com/sitemap and not www.Mydomain.com/forum/sitemap but I will try the way you comment, thanks for the suggestion.


Regards!

[Shambles]

You can see my mistakes now, it marks me the following which is an advance, it will be a question to see how I fix it.

Sorry, I've no idea what you're trying to tell/ask me.

[-Rock Lee-]

Sorry, I've no idea what you're trying to tell/ask me.

That you helped me a lot by making me realize my mistakes, now I have to fix to the "id_board" that I'm doing wrong, I think. The line gives me those problems is:  "WHERE t.id_board = {int:id_board}" I have copied some answers but I can not make it work.

[SychO]

you have to give id_board from {int:id_board} a value in the query parameters, something like this

Code Select Expand

<?php$result = $smcFunc['db_query']('', ' SELECT t.id_topic as id_topic, m.poster_time as poster_time, m.id_msg as id_msg FROM {db_prefix}topics as t LEFT JOIN {db_prefix}messages as m ON (t.id_last_msg = m.id_msg) LEFT JOIN {db_prefix}boards as b ON (t.id_board = b.id_board) WHERE t.id_board = {int:id_board} AND {query_see_board} ORDER BY m.poster_time DESC LIMIT 49999', array( 'id_board' => 1, ));

[-Rock Lee-]

If I use it with the array, it returns me "Unknown column 't.id_board' in 'where clause'" and if I do not use the array it returns "The database value you're trying to insert does not exist: id_board" ... I'm not sure why that mistake marks me.


Regards!

[Shambles]

What about the other query that you have?

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT b.id_board as id_board, m.poster_time as poster_time
FROM {db_prefix}boards as b
LEFT JOIN {db_prefix}messages as m on (b.id_last_msg = m.id_msg)
WHERE t.id_board = {int:board}
AND {query_see_board}
ORDER BY m.poster_time DESC
LIMIT 1000'
    );


You haven't assigned a table to "t" nor are you passing in the board id.

[-Rock Lee-]

I was trying to change it in both I get an error even though I think this "t." this bad I was copying from the SMF files because I thought it would work the way I wish but not. The original code is:

Code Select Expand

$request = db_query("
SELECT t.ID_TOPIC as ID_TOPIC, m.posterTime as posterTime, m.ID_MSG as ID_MSG
FROM {$db_prefix}topics as t
LEFT JOIN {$db_prefix}messages as m ON (t.ID_LAST_MSG = m.ID_MSG)
LEFT JOIN {$db_prefix}boards as b ON (t.ID_BOARD = b.ID_BOARD)
WHERE $user_info[query_see_board]
ORDER BY m.posterTime DESC
LIMIT 49999", __FILE__, __LINE__);

$messages = array();
while ($row = mysql_fetch_assoc($request))
{
$messages[] = array(
'id' => $row['ID_TOPIC'],
'ID_MSG' => $row['ID_MSG'],
'time' => $row['posterTime'],
);
}
mysql_free_result($request);

$request = db_query("
SELECT b.ID_BOARD as ID_BOARD, m.posterTime as posterTime
FROM {$db_prefix}boards as b
LEFT JOIN {$db_prefix}messages as m on (b.ID_LAST_MSG = m.ID_MSG)
WHERE $user_info[query_see_board]
ORDER BY m.posterTime DESC
LIMIT 1000", __FILE__, __LINE__);

It works perfectly with the 1.x branch but not with the 2.0.x so I want to see if I update. Although it may be because of the structure change, I am calling the variables wrong .


Regards!

[SychO]

if you're gonna remove the FROM {db_prefix}topics as t part then you have to edit the rest and use something other than t. depending on what you're trying to query

[-Rock Lee-]

if you're gonna remove the FROM {db_prefix}topics as t part then you have to edit the rest and use something other than t. depending on what you're trying to query

I have some doubts although I am reading the documentation to see if I can understand the fault I have, but I still have no luck.


Regards!