CRAZY mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

CRAZY mySQL

Post 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;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

from the useful posts thread: viewtopic.php?t=29151
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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)";
....
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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')";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

move a "AS `User`" to the right of the IF(), remove the other one.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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>		
  <? } ?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read the link I posted originally.. :?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
Post Reply