hgmme@wa Posted December 23, 2007 Share Posted December 23, 2007 Is it possible to search for two specific rows with one query?Here's examples:Query 1: SELECT * FROM wp_UserAllow WHERE viewed_ID=0 AND reviewers_ID=0 Query 2: SELECT * FROM wp_UserAllow WHERE viewed_ID=4 AND reviewers_ID=1 I'm to combine the above two query's, each of which should only return one row as they are now, into one query. I'd also tag on the appropriate tag to order it by "viewed_ID", but I'll worry about that later... Is it possible to do that? Link to comment Share on other sites More sharing options...
aalbetski Posted December 23, 2007 Share Posted December 23, 2007 you can use union SELECT * FROM wp_UserAllow WHERE viewed_ID=0 AND reviewers_ID=0 unionSELECT * FROM wp_UserAllow WHERE viewed_ID=4 AND reviewers_ID=1 order by viewed_ID you could also refine your select statment SELECT * FROM wp_UserAllow WHERE viewed_ID in (0,4) AND reviewers_ID in (0,4) order by viewed_ID --that would also give you viewed_ID = 4 when reviewers_ID = 0, may not be what you want or this SELECT * FROM wp_UserAllow WHERE viewed_ID in (0,4) AND reviewers_ID = case viewed_ID when 0 then 0 when 4 then 1 endorder by viewed_ID Link to comment Share on other sites More sharing options...
hgmme@wa Posted December 23, 2007 Author Share Posted December 23, 2007 you could also refine your select statmentSELECT * FROM wp_UserAllow WHERE viewed_ID in (0,4) AND reviewers_ID in (0,4) order by viewed_ID --that would also give you viewed_ID = 4 when reviewers_ID = 0, may not be what you want If that returns what I'm thinking it does then your right, it doesn't return what I want.you can use unionSELECT * FROM wp_UserAllow WHERE viewed_ID=0 AND reviewers_ID=0 unionSELECT * FROM wp_UserAllow WHERE viewed_ID=4 AND reviewers_ID=1 order by viewed_ID or this SELECT * FROM wp_UserAllow WHERE viewed_ID in (0,4) AND reviewers_ID = case viewed_ID when 0 then 0 when 4 then 1 endorder by viewed_ID I think either of the two above would work, but which one would be more efficient?Edit: or would what I originally showed be just as efficient?Is there documentation somewhere on that last query, as this is the first time I've seen that, so I'd like to read up on it. Link to comment Share on other sites More sharing options...
zppblood Posted December 24, 2007 Share Posted December 24, 2007 Why not just use ORviewed_ID=0 AND reviewers_ID=0 OR viewed_ID=4 AND reviewers_ID=1 Link to comment Share on other sites More sharing options...
hgmme@wa Posted December 25, 2007 Author Share Posted December 25, 2007 Because I need both rows if they're there. Cause you see the row with 0 for values is going to contain default values that I need to compare the other row, that there's no telling what will have for value, against. Link to comment Share on other sites More sharing options...
hgmme@wa Posted December 28, 2007 Author Share Posted December 28, 2007 Now I need to know the best way to put the the results from the two different rows and the individual columns into variable/array. should I do something like this? <?php$query = mysql_query("SELECT share_info, share_email, share_website FROM wp_UserAllow WHERE viewed_id=0 AND reviewers_id=0UNIONSELECT * FROM wp_UserAllow WHERE viewed_id=1 AND reviewers_id=1ORDER BY viewed_id ASC") or die("Failed: " . mysql_error());while ( $rows = mysql_fetch_assoc( $query )) { $num = 1; $results = array ( $num=>array ( "share_info"=>$rows['share_info'], "share_email"=>$rows['share_email'], "share_website"=>$rows['share_website'] ) ) $num++};echo "and now I can just echo " . $results['1']['share_info'] . " or " . $results['2']['share_email'] . " or whatever I want to do with the different array parts???";//I shouldn't ever have more than two rows return. I might throw an extra if in to check the number of rows, but I haven't decided yet.?> I'll actually be using the stuff in if statements, but hopefully you get the idea of what I need... So, will it work? Link to comment Share on other sites More sharing options...
hgmme@wa Posted December 28, 2007 Author Share Posted December 28, 2007 After I posted the last question it occurred to me that it should be in the php section so I'm going to move it there.It can be found here Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.