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?
Printable View
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?
Yes.
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?Quote:
Originally Posted by dclamp
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:
If the two tables do not have a common field then a JOIN is not possible...Code:Select * From MetaTags MT
Left Join Sites ST on ST.Site=MT.Site
Where MT.Tags='DOGS'
Nor would it even be practical.
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:
where UUID is some ID type.Code:id UUID PRIMARY KEY
site UUID REFERENCES sites
tagname STRING
relevancy REAL(1, 3)
Then the join should be obvious.
here are my tables:
I just made up the fields, but is is how it would be setupCode: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
a pro at joins could teach me over and over again, and i would have no idea what he was talking about :pQuote:
Originally Posted by CornedBee
This has been my only obstacle in SQL/PHP
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.
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.Quote:
Originally Posted by dclamp
That's an oversimplification - but can't you grasp that?
if i took the time... i think i could :pQuote:
Originally Posted by szlamany
ok. i tested the query out in PHPMyAdmin, works great :D
But when i put it on a PHP page i get the error:
I have gotten this error many time, and have been able to fix it... now i cant. Here is my code:Code: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
PHP 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 } ?>
Why? Just because of the possible conflicts in multi-table queries?Quote:
Originally Posted by szlamany
Well...Quote:
Originally Posted by CornedBee
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.
humm can any one answer post #11?
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.
That would be true if we were talking about non-relational databases.Quote:
Originally Posted by kows
But RDBMS is kind of where we are ;)
it works in phpmyadminQuote:
Originally Posted by kows
I am not sure what you mean by not escaping from the query...
you did:
double quote strings can contain variables without breaking out. single quote strings cannot. like so:PHP Code:$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.PHP Code:$sql = "Select * From metatags MT Left Join sites ST on ST.siteid=MT.site_id Where MT.meta_tag LIKE '$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.PHP Code:$q = (isset($_GET['q']) && $_GET['q'] != "") ? mysql_real_escape_string($_GET['q']) : "";
for some reason i am getting the same error. I will post my whole code
PHP 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>
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?
here is the error:
Yet, the table does exist....Code:Table 'dylan_search.metatags' doesn't exist
http://i142.photobucket.com/albums/r...phpmyadmin.png
OFF TOPIC:Quote:
Originally Posted by kows
lol your driving me crazy! stop editing your posts :lol:
in phpmyadmin it looks like you're on the database jphendr_search, but in php, you're on dylan_search.
this is your problem.
:lol:Quote:
Originally Posted by kows
i forgot... i switched servers...phpmyadmin was running slow on the other server
Thanks so much kows!