MySQL Newbie: SQL Error MSG, code worked well in yabbse...

[Flat-Jack]

i used this code to make an own little index of the boards - worked well with yabbse, but with the new SMF i get an MySQL Error...

the error msg:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs/test/newindex.php on line 34



part of the code:

while ($row_board = mysql_fetch_array($result_boards))
{
   if ($row_board['ID_BOARD'] == "5" || $row_board['ID_BOARD'] == "4" || $row_board['ID_BOARD'] == "7" || $row_board['ID_BOARD'] == "6" || $row_board['ID_BOARD'] == "12" || $row_board['ID_BOARD'] == "15"  || $row_board['ID_BOARD'] == "8" || $row_board['ID_BOARD'] == "17")
   {
   $currentboard = $row_board['ID_BOARD']; $numReplies = 0; $maxmessagedisplay = 10;
   $result3 = mysql_query("SELECT m.subject,t.ID_TOPIC,t.numReplies FROM {$db_prefix}topics as t LEFT JOIN {$db_prefix}messages as m ON (m.ID_MSG=t.ID_LAST_MSG) LEFT JOIN {$db_prefix}members as mem USING (ID_MEMBER) WHERE (t.ID_BOARD=$row_board[ID_BOARD]) ORDER BY m.posterTime DESC LIMIT 0,3");
   if (mysql_num_rows($result3) > 0) {   list ($subject,$topicID,$numReplies) = mysql_fetch_row($result3); }
   $subject = str_replace (array('&quot;','&#039;','&amp;','&lt;','&gt;'), array('"',"'",'&','<','>'), $subject);
   $subject = (strlen($subject) > 44)? htmlspecialchars(substr($subject,0,42) . '...'):htmlspecialchars($subject);
   $startPage1 = (floor(($numReplies)/$maxmessagedisplay)*$maxmessagedisplay);



unfornunately, i havge no sql experience, perhaps someone can take a look, it should only be in the firstline???

thanks!

[LancelowDowell]

That's a message saying you have a bad sql statment.  Could you post your sql statement that your running? 

[Flat-Jack]

ok, here a little bit more code :-)

Code Select Expand

mysql_select_db($db_name);

$result = mysql_query("SELECT COUNT(*) as totalm FROM {$db_prefix}messages as m,{$db_prefix}boards as b,{$db_prefix}topics as t,{$db_prefix}categories as c WHERE (m.ID_TOPIC=t.ID_TOPIC && t.ID_BOARD=b.ID_BOARD && b.ID_CAT=c.ID_CAT$condition)");
  $temp = mysql_fetch_row($result);
    $totalm = $temp[0];
    $result = mysql_query("SELECT COUNT(*) as totalt FROM {$db_prefix}topics;");
  $temp = mysql_fetch_row($result);
    $totalt = $temp[0];
    $settings[7] = $status;
$curforumurl = $curposlinks ? "<a href=\"$scripturl\" class=\"nav\">$mbname</a>" : $mbname;

$result_boards = mysql_query ("SELECT c.name as catName, c.ID_CAT, b.ID_BOARD, b.name as boardName, c.memberGroups FROM {$db_prefix}categories as c LEFT JOIN {$db_prefix}boards as b ON (b.ID_CAT=c.ID_CAT)$condition ORDER BY c.catOrder, c.ID_CAT, b.boardOrder, b.ID_BOARD");

$curcat = -1;
while ($row_board = mysql_fetch_array($result_boards))
{
if ($row_board['ID_BOARD'] == "5" || $row_board['ID_BOARD'] == "4" || $row_board['ID_BOARD'] == "7" || $row_board['ID_BOARD'] == "6" || $row_board['ID_BOARD'] == "12" || $row_board['ID_BOARD'] == "15"  || $row_board['ID_BOARD'] == "8" || $row_board['ID_BOARD'] == "17")
{
$currentboard = $row_board['ID_BOARD']; $numReplies = 0; $maxmessagedisplay = 10;
$result3 = mysql_query("SELECT m.subject,t.ID_TOPIC,t.numReplies FROM {$db_prefix}topics as t LEFT JOIN {$db_prefix}messages as m ON (m.ID_MSG=t.ID_LAST_MSG) LEFT JOIN {$db_prefix}members as mem USING (ID_MEMBER) WHERE (t.ID_BOARD=$row_board[ID_BOARD]) ORDER BY m.posterTime DESC LIMIT 0,3");
if (mysql_num_rows($result3) > 0) { list ($subject,$topicID,$numReplies) = mysql_fetch_row($result3); }
$subject = str_replace (array('&quot;','&#039;','&amp;','&lt;','&gt;'), array('"',"'",'&','<','>'), $subject);
$subject = (strlen($subject) > 44)? htmlspecialchars(substr($subject,0,42) . '...'):htmlspecialchars($subject);
$startPage1 = (floor(($numReplies)/$maxmessagedisplay)*$maxmessagedisplay);

$mnum = $msub = $startpage = "0";
$result = mysql_query("SELECT t.ID_LAST_MSG,t.ID_TOPIC,t.numReplies,t.locked,m.posterName,m.ID_MEMBER,t.numViews,m.posterTime,m.modifiedTime,t.ID_FIRST_MSG,t.isSticky,t.ID_POLL,mes.posterName as mname,mes.ID_MEMBER as mid,mes.subject as msub,mes.icon as micon FROM {$db_prefix}topics as t, {$db_prefix}messages as m,{$db_prefix}messages as mes WHERE (t.ID_BOARD=$currentboard AND m.ID_MSG=t.ID_LAST_MSG AND mes.ID_MSG=t.ID_FIRST_MSG) ORDER BY m.posterTime DESC LIMIT 0,2");
while ($row = mysql_fetch_array($result)){ $mid = $row['mid']; $msub = $row['msub']; $mnum = $row['ID_TOPIC']; $numReplies = $row['numReplies'];}
$msub = str_replace (array('&quot;','&#039;','&amp;','&lt;','&gt;'), array('"',"'",'&','<','>'), $msub);
$msub = (strlen($msub) > 44)? htmlspecialchars(substr($msub,0,42) . '...'):htmlspecialchars($msub);

[LancelowDowell]

I think I see what's going on... try taking the ; out from inside the quotes on this statement after topics.  That's probaby what's throwing it off.  Give that a shot

Code Select Expand

$result = mysql_query("SELECT COUNT(*) as totalt FROM {$db_prefix}topics;");

change it to this....

Code Select Expand

$result = mysql_query("SELECT COUNT(*) as totalt FROM {$db_prefix}topics");

If you're still getting the error after that, what line of code is line 34?

[Flat-Jack]

no, unfortunately its the same error still...

line 34 ist this one with the while loop:

while ($row_board = mysql_fetch_array($result_boards))

[LancelowDowell]

Ok, I see now.  Theres no longer a table by the name of memeberGroups in categories.  I'm not sure if you're using it anywhere else or not, but I didn't see you using that column anywhere in the code you posted.  If that's the case, just change that statement to:

Code Select Expand

$result_boards = mysql_query ("SELECT c.name as catName, c.ID_CAT, b.ID_BOARD, b.name as boardName
                       FROM {$db_prefix}categories as c LEFT JOIN {$db_prefix}boards as b
                       ON (b.ID_CAT=c.ID_CAT)$condition
                       ORDER BY c.catOrder, c.ID_CAT, b.boardOrder, b.ID_BOARD");

[[Unknown]]

You're going to need to rewrite a lot of your queries... SMF uses a different database structure.  I'll walk you through the basic process I use to update YaBB SE queries to the new SMF conventions and database.

Let's take this one:
SELECT COUNT(*) as totalm FROM {$db_prefix}messages as m,{$db_prefix}boards as b,{$db_prefix}topics as t,{$db_prefix}categories as c WHERE (m.ID_TOPIC=t.ID_TOPIC && t.ID_BOARD=b.ID_BOARD && b.ID_CAT=c.ID_CAT$condition)

Eew.  Unreadable, but there are other problems I'll get to.  First, let's make it readable:
SELECT COUNT(*) as totalm
FROM {$db_prefix}messages as m, {$db_prefix}boards as b, {$db_prefix}topics as t, {$db_prefix}categories as c
WHERE (m.ID_TOPIC=t.ID_TOPIC && t.ID_BOARD=b.ID_BOARD && b.ID_CAT=c.ID_CAT$condition)

Much better.  So, you're getting the total messages in all boards they can see, right?  Okay... let's do this to make it more readable:
SELECT COUNT(m.ID_MSG) AS totalm
FROM {$db_prefix}messages AS m, {$db_prefix}boards AS b, {$db_prefix}topics AS t, {$db_prefix}categories AS c
WHERE m.ID_TOPIC = t.ID_TOPIC
   AND t.ID_BOARD = b.ID_BOARD
   AND b.ID_CAT = c.ID_CAT
   $condition

Now, let's identify changes made in SMF with this:
1. Instead of using mysql_query, including SSI.php and using db_query would be better.  But, we won't do that just now.
2. Now, the messages table can link directly to boards, saving a join.
3. Access permissions are stored in the boards table, not the categories one.  Another less join!
4. The "$condition" method of limiting access was... well, not very good.  A better one is now employed.

To address them, we change it like so:
SELECT COUNT(m.ID_MSG) AS totalm
FROM {$db_prefix}messages AS m, {$db_prefix}boards AS b
WHERE m.ID_BOARD = b.ID_BOARD
   AND $user_info[query_see_board]

Wait, where does $user_info come from?  If you load SSI.php, this will be filled for you.  It queries the access permissions to see boards, as long as a boards table is in the query with the alias "b".

We also have this query:
SELECT COUNT(*) as totalt FROM {$db_prefix}topics

It doesn't even account for access permissions!  If it should, I would make it:
SELECT COUNT(t.ID_TOPIC) AS totalt
FROM {$db_prefix}topics, {$db_prefix}boards AS b
WHERE t.ID_BOARD = b.ID_BOARD
   AND $user_info[query_see_board]

Now let's look at:
SELECT t.ID_LAST_MSG,t.ID_TOPIC,t.numReplies,t.locked,m.posterName,m.ID_MEMBER,t.numViews,m.posterTime,m.modifiedTime,t.ID_FIRST_MSG,t.isSticky,t.ID_POLL,mes.posterName as mname,mes.ID_MEMBER as mid,mes.subject as msub,mes.icon as micon FROM {$db_prefix}topics as t, {$db_prefix}messages as m,{$db_prefix}messages as mes WHERE (t.ID_BOARD=$currentboard AND m.ID_MSG=t.ID_LAST_MSG AND mes.ID_MSG=t.ID_FIRST_MSG) ORDER BY m.posterTime DESC LIMIT 0,2

Ouch.  Long long query.... hard to read again...
SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, mes.posterName AS mname, mes.ID_MEMBER AS mid, mes.subject AS msub, mes.icon AS micon
FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m, {$db_prefix}messages AS mes
WHERE t.ID_BOARD = $currentboard
   AND m.ID_MSG = t.ID_LAST_MSG
   AND mes.ID_MSG = t.ID_FIRST_MSG
ORDER BY m.posterTime DESC
LIMIT 0, 2

Okay, so, first off it'd be much faster to order on ID_MSG, since that is the primary key.  I'd also change the aliases, because "m" and "mes" don't really mean much to me .
SELECT
   t.ID_LAST_MSG, t.ID_FIRST_MSG, t.ID_TOPIC, t.ID_POLL, t.numReplies, t.numViews, t.locked, t.isSticky,
   ml.posterName, ml.ID_MEMBER, ml.posterTime, ml.modifiedTime,
   ms.posterName AS mname, ms.ID_MEMBER AS mid, ms.subject AS msub, ms.icon AS micon
FROM {$db_prefix}topics AS t, {$db_prefix}messages AS ml, {$db_prefix}messages AS ms
WHERE t.ID_BOARD = $currentboard
   AND ml.ID_MSG = t.ID_LAST_MSG
   AND ms.ID_MSG = t.ID_FIRST_MSG
ORDER BY m.ID_MSG DESC
LIMIT 2

Subtle changes, but the do make a difference in readability and maintainability.

Does that help at all?  The queries I noted should all work with the SMF database structure with the changes I noted....
(and no, I'm not trying to patronize you at all, I just don't have any idea what you're knowledge level might be, except that you said you had no sql experience... hopefully, this will give you a better chance at any other queries you might have problems with.)

-[Unknown]