[Wraith]

I got this:

PHP Code:

$query = mysql_query("SELECT * FROM `tbl_cheats` WHERE `Letter` = '".$l."' in(

SELECT DISTINCT `Game` FROM `tbl_cheats`')") or die(mysql_error()); 

I get this error: You have an error in your SQL syntax near 'SELECT DISTINCT `Game` FROM `tbl_cheats`')' at line 2.

All help will be greatfull!

-Neo

[Sabu]

PHP Code:

  $query = mysql_query("SELECT * FROM `tbl_cheats` WHERE `Letter` = '".$l."' in('SELECT DISTINCT `Game` FROM `tbl_cheats`')") or die(mysql_error()); 

Try that. While you CLOSED the second query with an apostrophe ( ' ), you never opened it.

[Wraith]

Ok thanks. But Now, its not getting any data. Dont get me wrong, but I want to do this:

• Get all records, but only one of each game.
• Then get only the ones with the letter $l (which for testers is R)

Now, just looking at my signiture, you can see these more than one cheat. all 4 being the same - RS3 letter R.

Goto http://home.prcentral.co.uk/neo/gs/index.php?p=cheats and check for yourself if you like.

Thanks All - I really need to get this finished (look at the front page and you'll notice im 11 days behind!).

Thanks
-Wraith aka NeoPuma

[moronovich]

hello wraith,
i hesitate the select statement will work as you wish. you tried to execute sub query which is unavailable till MySQL 4.1.x.

[Wraith]

Originally Posted by moronovich

hello wraith,
i hesitate the select statement will work as you wish. you tried to execute sub query which is unavailable till MySQL 4.1.x.

Ah...Thanks .

Ok, now how would I do what I want (in php)? I have a rough idea like this but I ain't tryed it...

Would: SELECT DISTINCT * FROM `tbl_cheats` work?

[Wraith]

Sorry couldn't find the edit button!

I have tried my idea, and it has actually worked (change ).

PHP Code:

    $query = mysql_query("SELECT * FROM `tbl_cheats` WHERE `Letter` = '".$l."'") or die(mysql_error()); 

    if(mysql_num_rows($query) > 0) {

        echo '<table width="100%" border="0">

          <tr>

            <td><div align="center"><font size="1"><font face="Verdana, Arial, Helvetica, sans-serif">Game

                Title</font></font></div></td>

            <td><div align="center"><font size="1"><font face="Verdana, Arial, Helvetica, sans-serif">Consoles

        </font></font></div></td>

            <td><div align="center"><font size="1"><font face="Verdana, Arial, Helvetica, sans-serif">Number

            Of Cheats</font></font></div></td>

          </tr>';

        while($row = mysql_fetch_array($query)) {

            if(strpos($games, $row['smlGame']) === false) {

                $games .= $row['smlGame']. ' ';

                $result2 = mysql_query("SELECT `smlGame` FROM `tbl_cheats` WHERE `smlGame` = '".$row['smlGame']."'");

                echo '<tr>

                    <td><div align="center"><font size="1"><font face="Verdana, Arial, Helvetica, sans-serif">

                <a href="index.php?p=cheats&a=view&game='.$row['smlGame']. '">'.$row['Game'].'</a></font></font></div></td>

                <td><div align="center"><font size="1"><font face="verdana, Arial, Helvetica, sans-serif">'.$row['Console'].'</font></font></div></td>

                    <td><div align="center"><font size="1"><font face="Verdana, Arial, Helvetica, sans-serif">'.mysql_num_rows($result2).'</font></font></div></td>

                  </tr>';

            }

        }

        echo '</table>';

    } else {

        echo 'We havn\'t got any cheats at the moment! Why not sign up and add some?';

    }