Page 1 of 1

MySQL Request

Posted: Sun Nov 06, 2011 8:03 am
by chishikinohito
Dear guys, hI!

I can't understand completely how the request works. Maybe you would just answer my question with ease.

Using PHP + Smarty.

I must make a request to the database so the result would be a comparison of the two following queries (which work fine separately)

A working example of two codes PHP, smarty template and output the result.

1.
PHP

Code: Select all

$sql                = "SELECT * FROM video WHERE type = 'public'";
$rs                 = $conn->execute($sql);
$videos             = $rs->getrows();
TPL

Code: Select all

 {section name=i loop=$answers}
{$answers[i].VID}
{/section}

We get a result with the video IDs, for example, 3, 4, 7, 23, 26, 77, 104

2.
The second request is from the table GROUP_VDO, if the Video ID in the table GROUP_VDO we get the result with those IDs:
PHP

Code: Select all

$sql            = "SELECT * FROM group_vdo WHERE GID = '" .$GID. "'" . " ORDER BY AID DESC LIMIT " .$limit;
$rs             = $conn->execute($sql);
$videosremove         = array();

if ( $conn->Affected_Rows() ) {
    $i  = 0;
    while ( !$rs->EOF ) {
        $sql    = "SELECT * FROM video WHERE VID = '" .$rs->fields['VID']. "' LIMIT 1";
        $rsv    = $conn->execute($sql);
        if ( $conn->Affected_Rows() ) {
            $video                  = $rsv->getrows();
            $videosremove[$i]             = $video['0'];
            $rs->movenext();
            ++$i;
        }
    }
}
TPL

Code: Select all

{section name=i loop=$answersremove}
{$answersremove[i].VID}
{/section}
We get a result with the Video IDs which is in the table GROUP_VDO, for example, 4, 10, 23

.$GID. is fixed and irrelevant to my question

All the above works fine.

I have a question about ADDING of additional feature.

I do not want to display the results of the first request (IDs of the video), which are found in the results of the second. In this example, the first query results must exclude ids 4 and 23, as they appear in the second return!

Or, I should probably build 3 request, so that it requests from the table VIDEO, all videos with VIDs except for those that are found in GROUP_VDO with the current $GID (it's set by the way like this

Code: Select all

$GID    = ( isset($_GET['GID']) && is_numeric($_GET['GID']) ) ? trim($_GET['GID']) : NULL;
How difficult is it to implement?

Thanks in advance,

Sincerely,
Ilia