Help with MySQL join

Blake · #1 11-Feb-2007, 08:38

I have two MySQL tables, both having a column called ip.

The tables are called gbfilterlog and ban. I want to select all entries in gbfilterlog, ordered by ip, but with the additional constraint that all rows where the value of ip also appears in ban are listed first.

I think I can use a join to do this, but I'm pretty new to MySQL, so I'm not sure.

Is it possible to do this with a join? I think I would need to use a left join, but beyond that I'm lost.

EDIT:

This is almost works, but the rows where the value of ip is not found in ban are not ordered correctly.

Code:

SELECT * FROM gbfilterlog LEFT JOIN ban USING (ip) ORDER BY ban.ip ASC

Blake · #2 11-Feb-2007, 09:04

I think I got it:

Code:

SELECT * FROM gbfilterlog LEFT JOIN ban USING (ip) ORDER BY ban.ip, gbfilterlog.ip ASC

It appears to work. Does anybody see any problems with it?

Now my next question: Suppose I want to display all rows of gbfilterlog, sorted by ip, and in each row, I also want to display whether or not the same value of ip appears in ban. Can that be done with a join? I already have a working system for doing that; At each time I display a row, I do a query to check. However, I think there must be a more efficient way to do it with a join.

TurboPT · #3 11-Feb-2007, 09:45

I think what you have in the post #2 query will work, but change LEFT to RIGHT for the join.
EDIT: no, that does not give all rows from the log table, rather it just gives matches only.

admin · #4 13-Feb-2007, 07:30

A LEFT JOIN, as you have suggested, is the best way to handle this. It will return ALL the rows off `gbfilterlog`. Matching IPs that do not exist in `ban` will simply be NULL. Right?

Now that your SINGLE query already returns ALL the rows off `gbfilterlog` (and `ban`), why do you need "..a query to check..."?

I suppose in your PHP script, where you're displaying your rows, it could look a bit like this:

PHP Code:


<?php

// connecting to db and running the query and handling the result..

while( $row=@mysql_fetch_assoc($result) )
{
    // display the results / rows.
    echo    "<tr>",
            "<td>$row[ip]</td>", // the IP off `gbfilterlog`
            // if no matching `ip` is found in `ban`, $row['ban_ip'] will be NULL 
            "<td>" . ( $row['ban_ip'] ? $row['ban_ip'] : "n/a" ) . "</td>",
        "</tr>";
}

// ... the rest of the script.
?>

Something like that?

TurboPT · #5 13-Feb-2007, 07:50

Thanks for that clarification admin.

From a small sample set that I toyed with, I did see the NULLs in the LEFT JOIN for the ban table and all the first table's records. It appeared that was what the OP originally wanted, so I got confused about what the OP was trying to accomplish.

admin · #6 13-Feb-2007, 08:02

I understand -- trust me, I get confused all the time too. Today, I was trying to write a really (REALLY) simple bash script, and I struggled for a good half an hour trying to remember how to SET A VARIABLE! I felt SO useless...

Back on topic:

Try using the EXPLAIN in your queries sometimes. And don't (ab)use the ORDER BY clause unnecessarily, or do it right.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Moving data from MySQL to MySQL	meurer	MySQL / PHP Forum	0	25-Jan-2007 09:03
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 02:54
CPanel 10, Unlimited Emails, Unlimited Subdomains, Unlimited MySQL. $2.00 per Month	Kalypsoweb	Web Hosting Advertisements & Offers	0	30-Oct-2005 01:51
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

Help with MySQL join

Re: Help with MySQL join

Re: Help with MySQL join

Re: Help with MySQL join

Re: Help with MySQL join

Re: Help with MySQL join