Page 1 of 1

CRAZY mySQL

Posted: Mon Aug 15, 2005 5:39 pm
by Todd_Z
I'm trying to figure out a query to select all the `posts` that have not been reviewed yet. SO -> I want to select * from posts where reviews.project == posts.id and [there is no entry yet in reviews `by` a value]

Code: Select all

CREATE TABLE `Reviews` (
  `For` int(11) NOT NULL default '0',
  `By` int(11) NOT NULL default '0',
  `Rating` smallint(6) NOT NULL default '0',
  `Review` tinytext NOT NULL,
  `Project` int(11) NOT NULL default '0',
  `Date` int(11) NOT NULL default '0',
  `ID` int(11) NOT NULL auto_increment,
  UNIQUE KEY `ID` (`ID`)
) TYPE=MyISAM;

Code: Select all

CREATE TABLE `Posts` (
  `Title` tinytext NOT NULL,
  `Webmaster` int(11) NOT NULL default '0',
  `Programmer` int(11) NOT NULL default '0',
  `Skills` tinytext NOT NULL,
  `Budget` tinytext NOT NULL,
  `Limits` tinytext NOT NULL,
  `Start` int(11) default NULL,
  `End` int(11) default NULL,
  `Length` int(3) unsigned NOT NULL default '0',
  `Top_Priority` char(1) NOT NULL default '0',
  `ID` int(11) NOT NULL auto_increment,
  UNIQUE KEY `ID` (`ID`)
) TYPE=MyISAM;

Posted: Mon Aug 15, 2005 5:49 pm
by feyd
from the useful posts thread: viewtopic.php?t=29151

Posted: Mon Aug 15, 2005 7:04 pm
by nielsene
What version of MySQL are you running?

Normally I'd do this with a sub-select, but I'm not sure which versions, if any of MySQL support it:

Code: Select all

SELECT post.ID FROM Posts WHERE ID NOT IN (SELECT Project FROM Reviews)
If your version chokes on that you can alwasy simulate it by:

Code: Select all

$query="SELECT DISTINCT Project FROM Reviews";
$result=$db->query($query);
// Create an array of all IDs
$idList = arrayToCSV($arr) // create a comma seperated list
$query="SELECT Id FROM Posts WHERE Id NOT IN ($idList)";
....

Posted: Mon Aug 15, 2005 11:38 pm
by Todd_Z
Okay, got that part down. How do I select `Webmaster` AS `User` if `Programmer` = '3' else `Programmer` AS `User`?

Code: Select all

$sql = "SELECT `Title`, `ID`, IF ( `Webmaster` = '3', `Programmer` AS 'User', `Webmaster` AS 'User' ) FROM `Posts` WHERE `Programmer` IS NOT NULL AND ( `Webmaster` = '3' OR `Programmer` = '3' ) AND `ID` NOT IN ('5')";

Posted: Tue Aug 16, 2005 7:01 am
by feyd
move a "AS `User`" to the right of the IF(), remove the other one.

Posted: Tue Aug 16, 2005 2:33 pm
by Todd_Z
Is it possible to combine these two sqls? and get rid of all between the comments?

Code: Select all

<?
////////////////
  $sql = "SELECT `Project` FROM `Reviews` WHERE `By` = '{$_SESSION['uid']}'";
  $mySQL->execSQL( $sql );
  while ( $row = $mySQL->nextObject() )
    $idList .= ", '{$row->Project}'";
  $idList = substr( $idList, 2 );
  if ( $mySQL->countRows() > 0 )
    $idList = " AND Posts.ID NOT IN ($idList)";
//////////////
  $sql = "SELECT `Title`, Posts.ID, Users.Name, IF ( `Webmaster` = '{$_SESSION['uid']}', `Programmer`, `Webmaster` ) AS 'User' FROM `Posts` JOIN `Users` ON ( IF ( `Webmaster` = '{$_SESSION['uid']}', `Programmer`, `Webmaster` ) = Users.ID ) WHERE `Programmer` IS NOT NULL AND ( `Webmaster` = '{$_SESSION['uid']}' OR `Programmer` = '{$_SESSION['uid']}' )$idList";
		
  $mySQL->execSQL( $sql );
		
  if ( $mySQL->countRows() > 0 ) { ?>
   <div class="sT">Freelancers to Review</div>
   <div class="sC">
    <? while ( $row = $mySQL->nextObject() )
      echo "<p><a href=\"/Review/{$row->ID}/\">{$row->Name}</a> [<a href=\"/ProjectPost/{$row->ID}/\" style=\"font-size: 10px;\">{$row->Title}</a>]</p>"; ?>
   </div>
   <div class="sB"></div>		
  <? } ?>

Posted: Tue Aug 16, 2005 2:37 pm
by feyd
read the link I posted originally.. :?

Posted: Tue Aug 16, 2005 2:55 pm
by Todd_Z

Code: Select all

$sql = "SELECT `Title`, Posts.ID, Users.Name, IF ( `Webmaster` = '{$_SESSION['uid']}', `Programmer`, `Webmaster` ) AS 'User' FROM `Posts` JOIN `Users` ON ( IF ( `Webmaster` = '{$_SESSION['uid']}', `Programmer`, `Webmaster` ) = Users.ID ) WHERE `Programmer` IS NOT NULL AND ( `Webmaster` = '{$_SESSION['uid']}' OR `Programmer` = '{$_SESSION['uid']}' ) AND Posts.ID NOT IN ( SELECT `Project` FROM `Reviews` WHERE `By` = '{$_SESSION['uid']}' )";
I'm stoopid. I made a little mistake before, so I couldn't get that to work. But now it does.

Why can't I have:

Code: Select all

$sql = "SELECT `Title`, Posts.ID, Users.Name, IF ( `Webmaster` = '{$_SESSION['uid']}', `Programmer`, `Webmaster` ) AS `User` FROM `Posts` JOIN `Users` ON ( `User` = Users.ID ) WHERE `Programmer` IS NOT NULL AND ( `Webmaster` = '{$_SESSION['uid']}' OR `Programmer` = '{$_SESSION['uid']}' ) AND Posts.ID NOT IN ( SELECT `Project` FROM `Reviews` WHERE `By` = '{$_SESSION['uid']}' )";
It seems unneccessary to have the two IF statements if i just give that value to `User`

I get the unknown column User error

Posted: Tue Aug 16, 2005 3:08 pm
by feyd
if memory serves, the join is performed before the selection information is really processed (since the logic is based on the data found)