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)