[RESOLVED] Inner Join?

[dclamp]

Ok, i am making a search engine. I need to query the "meta_tags" tab for site that have (lets say the tag "dogs") and then display the sites from the table "sites"

Would this be an innerjoin?

[CornedBee]

Yes.

[dclamp]

well i have been looking around (google) and i cant seem to figure it out

I only get examples for getting results where the 2 tables have something in comment (lets say "id") and it will display it

How do i query from `metatags`, then from that result, query `sites`?

[szlamany]

well i have been looking around (google) and i cant seem to figure it out

I only get examples for getting results where the 2 tables have something in comment (lets say "id") and it will display it

How do i query from `metatags`, then from that result, query `sites`?

something in comment? you really meant something in common - right?

You really need to tell us more about the columns in each of these tables - but I'm guessing that both have SITES in them...

So:

Select * From MetaTags MT
 Left Join Sites ST on ST.Site=MT.Site
 Where MT.Tags='DOGS'

If the two tables do not have a common field then a JOIN is not possible...

Nor would it even be practical.

[CornedBee]

Well, obviously you have to associate metatags with the sites somehow. If you don't have that, how can you possibly know which site the metatag applies to?

So I imagine metatag would look something like this:

id UUID PRIMARY KEY
site UUID REFERENCES sites
tagname STRING
relevancy REAL(1, 3)

where UUID is some ID type.

Then the join should be obvious.

[dclamp]

here are my tables:

METATAGS
============================
ID    | SITEID    | TAG
----------------------------
0     | 1         | Social Network
1     | 2         | Visual Basic Forum
2     | 2         | Forum
3     | 3         | Videos



SITES
=================================
ID | TITLE | DESC | URL | CLICKS 
---------------------------------
1  | MySpa | Soc..| ... | 100
2  | VBF   | For..| ... | 10
3  | YouTu.| Vid..| ... | 8

I just made up the fields, but is is how it would be setup

[dclamp]

Then the join should be obvious.

a pro at joins could teach me over and over again, and i would have no idea what he was talking about

This has been my only obstacle in SQL/PHP

[szlamany]

ID is not a good field name - change it now...

MetaId should be the first column of the first table.

SiteId should be the first column of the second table.

Otherwise the JOIN is how I suggested - just change the column name SITE in my query to SITEID.

[szlamany]

a pro at joins could teach me over and over again, and i would have no idea what he was talking about

This has been my only obstacle in SQL/PHP

All a JOIN is doing is taking two rows that have a related value and "putting them together" as if they were in fact one row. Two tables become a single "virtual" table.

That's an oversimplification - but can't you grasp that?

[dclamp]

All a JOIN is doing is taking two rows that have a related value and "putting them together" as if they were in fact one row. Two tables become a single "virtual" table.

That's an oversimplification - but can't you grasp that?

if i took the time... i think i could

[dclamp]

ok. i tested the query out in PHPMyAdmin, works great

But when i put it on a PHP page i get the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/jphendr/public_html/search/includes/web_search.php on line 25

I have gotten this error many time, and have been able to fix it... now i cant. Here is my code:

<?PHP
$q = $_GET['q'];
$sql = "Select * From metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '".$q."'";
$result = mysql_query($sql);
//$count = mysql_num_rows($result);
?>
// ... some html here ...
<?PHP while ($Result_Array = mysql_fetch_array($result)) { ?>
            <table width="100%" border="0">
                <tr>
                    <td><a href='navigate.php?url=<?=$Result_Array['url']?>&id=<?=$Result_Array['siteid']?>'><?=$Result_Array['title']?></a></td>
                </tr>
                <tr>
                    <td><?=$Result_Array['description']?></td>
                </tr>
                <tr>
                    <td><div class='siteurl'><?=$Result_Array['url']?></div></td>
                </tr>
            </table>
            <?PHP } ?>

[CornedBee]

ID is not a good field name - change it now...

Why? Just because of the possible conflicts in multi-table queries?

[szlamany]

Why? Just because of the possible conflicts in multi-table queries?

Well...

It's not a reserved word - so it is valid.

But it's just so vague.

My experience from maintaining and enhancing large database systems is that "common" fields should have "common" names between tables.

It's more a convention that makes sense to me - really just my opinion

And with the continuing growth of third party tools that low-end users can attack their data with - it's just more helpful to them to have that consistent and common naming...

But conflicts between multi-table queries has nothing to do with it - as we live by an "ALIAS everything" rule in our shop.

[dclamp]

humm can any one answer post #11?

[kows]

echo out $sql before querying it and then copy and paste it into a query in phpmyadmin; see if it works or not. from what I can gather, your $q variable is probably breaking your query seeing as how you're not manipulating it in any way before shoving it into the query. if you're not going to do anything at all, you should at least add a call to mysql_real_escape_string. also, there's no real point in you breaking out of your query to use that variable unless you're calling a function when inserting the variable.

also: field names are relevant to the tables they are in. if you have a table named sites, what's the point in having a field named siteid? of course it's the site's ID, the table is called sites. unless you have multiple ID fields in the table you don't need to be specific like that; at least, that's my opinion.

[szlamany]

field names are relevant to the tables they are in.

That would be true if we were talking about non-relational databases.

But RDBMS is kind of where we are

[dclamp]

echo out $sql before querying it and then copy and paste it into a query in phpmyadmin; see if it works or not. from what I can gather, your $q variable is probably breaking your query seeing as how you're not manipulating it in any way before shoving it into the query. if you're not going to do anything at all, you should at least add a call to mysql_real_escape_string. also, there's no real point in you breaking out of your query to use that variable unless you're calling a function when inserting the variable.

also: field names are relevant to the tables they are in. if you have a table named sites, what's the point in having a field named siteid? of course it's the site's ID, the table is called sites. unless you have multiple ID fields in the table you don't need to be specific like that; at least, that's my opinion.

it works in phpmyadmin

I am not sure what you mean by not escaping from the query...

[kows]

you did:

$sql = "Select * From metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '".$q."'"; 

double quote strings can contain variables without breaking out. single quote strings cannot. like so:

$sql = "Select * From metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '$q'"; 

even so, you should be defining $q better.

$q = (isset($_GET['q']) && $_GET['q'] != "") ? mysql_real_escape_string($_GET['q']) : ""; 

edit: if you copied and pasted the value outputted by $sql and it worked in phpmyadmin, then I have no idea what you're doing wrong. make sure your script is connecting to the database correctly and then run other queries (SELECT * FROM metatags, maybe) to make sure. if other queries work, then something is wrong with your query and your $q variable is, again, the most likely culprit.

[dclamp]

for some reason i am getting the same error. I will post my whole code

<!-- WEB_SEARCH.PHP -->
<?PHP include("includes/conn.php"); ?>
<?PHP include("includes/pager.class.php"); ?>
<?PHP
// get the pager input values 
//$page = $_GET['page']; 
//$limit = 20; 
//$result = mysql_query("select count(*) from metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '".$q."'"); 
//$total = mysql_result($result, 0, 0); 

// work out the pager values 
//$pager  = Pager::getPagerData($total, $limit, $page); 
//$offset = $pager->offset; 
//$limit  = $pager->limit; 
//$page   = $pager->page; 

 $q = (isset($_GET['q']) && $_GET['q'] != "") ? mysql_real_escape_string($_GET['q']) : ""; 
$sql = "Select * From metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '$q' ORDER BY `clicks` DESC"; 
echo $sql;
$result = mysql_query($sql);
//$count = mysql_num_rows($result);
?>
<link href="style.css" rel="stylesheet" type="text/css" />
<table width="100%" border="0">
    <tr>
        <td bgcolor='#0099CC'>
            <table width="100%" border="0">
                <tr>
                    <td><font size='+1'><b>Web Results</b></font></td>
                    <td align='right' nowrap>Results <b># - #</b> of about <b><?=$count?></b> for <b><?=$q?></b></td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<table width="100%" border="0">
    <tr>
        <td width="75%">
            <!--RESULTS-->
            <?PHP while ($Result_Array = mysql_fetch_array($result)) { ?>
            <table width="100%" border="0">
                <tr>
                    <td><a href='navigate.php?url=<?=$Result_Array['url']?>&id=<?=$Result_Array['siteid']?>'><?=$Result_Array['title']?></a></td>
                </tr>
                <tr>
                    <td><?=$Result_Array['description']?></td>
                </tr>
                <tr>
                    <td><div class='siteurl'><?=$Result_Array['url']?></div></td>
                </tr>
            </table>
            <?PHP } ?>
        </td>
        <td width="25%" valign='top'>
            <!--ADS-->
            <br /><br /><div class='footer' align='center'>Sponsored Links</div><br />
            Google Ads Any One?
        </td>
    </tr>
</table>
<center>
<br /><br />
<?PHP
    // use $result here to output page content 

    // output paging system (could also do it before we output the page content) 
    if ($page == 1) // this is the first page - there is no previous page 
        echo "Previous"; 
    else            // not the first page, link to the previous page 
        echo "<a href=\"search.php?page=" . ($page - 1) . "\">Previous</a>"; 

    for ($i = 1; $i <= $pager->numPages; $i++) { 
        echo " | "; 
        if ($i == $pager->page) 
            echo "Page $i"; 
        else 
            echo "<a href=\"search.php?page=$i\">Page $i</a>"; 
    } 

    if ($page == $pager->numPages) // this is the last page - there is no next page 
        echo "Next"; 
    else            // not the last page, link to the next page 
        echo "<a href=\"search.php?page=" . ($page + 1) . "\">Next</a>"; 
?> 
</center>
<table width="100%" border="0">
    <tr>
        <td bgcolor='#0099CC'>
            <center>
            <form action='search.php' method='GET' name='Search'>
            <table width="100%" border="0">
                <tr>
                    <td align='center'><input name="q" type="text" width="300" value='<?=$q?>' /> <input type='submit' value='Search' /></td>
                </tr>
            </table>
            </form>
            </center>
        </td>
    </tr>
</table>

[kows]

add an "or die(mysql_error())" to your call to mysql_query, as well. look at where the error is happening in your query.

edit: posting all of your code isn't relevant.. none of the other code is having a problem as far as I know.

did you try another query? what have you done so far?

[dclamp]

here is the error:

Table 'dylan_search.metatags' doesn't exist

Yet, the table does exist....

[dclamp]

add an "or die(mysql_error())" to your call to mysql_query, as well. look at where the error is happening in your query.

edit: posting all of your code isn't relevant.. none of the other code is having a problem as far as I know.

did you try another query? what have you done so far?

OFF TOPIC:

lol your driving me crazy! stop editing your posts

[kows]

in phpmyadmin it looks like you're on the database jphendr_search, but in php, you're on dylan_search.

this is your problem.

[dclamp]

in phpmyadmin it looks like you're on the database jphendr_search, but in php, you're on dylan_search.

this is your problem.

i forgot... i switched servers...phpmyadmin was running slow on the other server

Thanks so much kows!