SQL help

[drisate]

Hey guys my brain is jaming ...

 

I need to select the last 5 comments from the comment table if the video from where the comment is posted is set to ok ... So theres 2 tables involved ...

 

What i did is this

 

print ('<br><table align="center" bgcolor="#a7a7a9" border="0" cellpadding="0" cellspacing="1" height="1" width="94%">
  <tr>
    <td class="title" cellpadding="0" colspan="2" align="middle" height="1" valign="top">
    <p style="margin-top: 0pt; margin-bottom: 0pt;" align="center"><b>Last 5 
    comments</b></td>
  </tr>
  <tr>
    <td class="subtitle" bgcolor="#f6f6f6" height="1" width="20%"><center><b>Video</b></center></td>
    <td class="subtitle" bgcolor="#f6f6f6" height="1" width="80%"><center><b>Comment posted</b></center></td>
  </tr>');

$result1 = @mysql_query("Select * FROM comment where sort='video' and categ='$_GET[categ]' order by id desc limit 0,5");

while ($last_com = @mysql_fetch_array($result1)) {

$result3 = @mysql_query("Select * FROM video_list where id='$last_com[com_id]'");
$last_vid = @mysql_fetch_array($result3);

if ($last_vid[ok]=="1"){
  print (' <tr>
    <td class="std1" align="left" bgcolor="#f6f6f6" height="1" width="1%" valign="top">
    <table border="0" cellpadding="5" cellspacing="5" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber14">
      <tr>
            <td align="center" width="1%">
            <a href="http://f-fr.com/1/index.php?option=com_interractif&mod=1&Itemid=26&mod=video&categ='.$last_vid[categ].'&video='.$last_vid[id].'">
            <img src="http://img.youtube.com/vi/'.$last_vid[url].'/'.$youtube.'.jpg" width="80"></a></td>
          </tr>
    </table>
    </td>
    <td class="std1" align="left" bgcolor="#f6f6f6" height="1" width="99%" valign="top">
    <table border="0" cellpadding="3" cellspacing="3" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber13">
      <tr>
        <td width="70%" align="left" valign="top">'.$last_com[message].'</td>
        <td width="30%" align="left" valign="top">
        <p style="margin-top: 0; margin-bottom: 0">Date: '.$last_com['date'].'</p>
        <p style="margin-top: 0; margin-bottom: 0">Author: '.$last_com[by].'</td>
      </tr>
    </table>
    </td>
  </tr>');
}
}
  
  print ('</table>');

 

But if a video in the last 5 is not set to ok it takes it out of the result ... so i end up with only 4 instead of 5

[drisate]

This is what i tryed

 

Select * FROM comment where (select video_list.ok='1' where id='comment.com_id') and comment.sort='video' and comment.categ='$_GET[categ]' order by comment.id desc limit 0,5

 

But that returns no result ... Can anybody help me get that select working?

[drisate]

Found it

Select * FROM comment, video_list where video_list.ok='1' and video_list.id=comment.com_id and comment.sort='video' and comment.categ='$_GET[categ]' order by comment.id desc limit 0,5

 

Thx anyway

[Fadion]

Glad u found it, but im refining your query just a tiny bit:

 

<?php
$categ = mysql_real_escape_string($_GET['categ']);
$results = mysql_query("
SELECT comment.com_id, comment.sort, comment.categ, comment.id, video_list.id, video_list.ok
FROM comment, video_list
WHERE video_list.ok=1 
AND video_list.id=comment.com_id 
AND comment.sort='video'
AND comment.categ='$categ'
ORDER BY comment.id DESC LIMIT 5");
?>

 

A little too specific query, but anyway if it works then its ok. I cleaned input from get with mysql_real_escape_string(), added the columns to the select statement (so u dont select unwanted fields with *) and made "LIMIT 5" instead of "LIMIT 0,5".

 

Mark the topic as solved if u have no more questions.

[drisate]

Thanks GuiltyGear ;-)

hmm ... where’s the solve button? ... And why can’t I edit my first post lol