Search through 2 tables (mysql)

[goaman]

Hiya!
I'm tryin' to search through 2 tables, but doesn't work at all.. plz, check my code n' lemme know how to fix the error plzzz..

thnx!

$query = "(SELECT * FROM news WHERE (news LIKE '".substr($entry, 0, 3)."%') OR (title LIKE '".substr($entry, 0, 3)."%') ORDER by id DESC ) AND 
(SELECT * FROM old_news WHERE (news LIKE '".substr($entry, 0, 3)."%') OR (title LIKE '".substr($entry, 0, 3)."%') ORDER by id DESC )";

$result = mysql_query($query);
$results=mysql_numrows($result);

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /foo/newsearch.php on line 24
matches found for keyword ""

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /foo/newsearch.php on line 44

[bpat1434]

Seems like you're trying to nest the queries when it's not necessary.

Try this:

$query = "SELECT * FROM news a, old_news b 
WHERE ((a.news LIKE '".substr($entry, 0, 3)."%') OR (a.title LIKE '".substr($entry, 0, 3)."%')) 
AND ((b.news LIKE '".substr($entry, 0, 3)."%') OR (b.title LIKE '".substr($entry, 0, 3)."%')) 
ORDER by id DESC";

~Brett

[goaman]

hi Brett,
thanks for the reply, but I'm getting the same errors.. 🙁

I wonder if this part of the code correct for searching through multiple tables, cuz I'm getting errors on these lines..

$result = mysql_query($query);
$results=mysql_numrows($result);

thnx!

[bpat1434]

Well, then you need to echo the result variable. You need to slim down your query and add things one by one to help debug. You should get the following output with the query results:
Resource id #??

If you don't, then there's something with the query.

You may also wish to try changing mysql_numrows() to mysql_num_rows()

~Brett

[goaman]

thanks for the fast reply, I've read that article(about the mysql basics..), didn't help me for searching through 2 or more tables.. I've done many things using mysql but never came to face such as multiple database and table searchings.. these two are harder than the other mysql coding.. I've searched through this forum and the google abt this matter, but didn't have much luck. It'd be nice to see a sample code to learn how this trick is done on php. 🙂

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in quicksearch.php on line 26
SELECT * FROM news a, old_news b WHERE ((a.news LIKE 'Fi%') OR (a.title LIKE 'Fi%')) AND ((b.news LIKE 'Fi%') OR (b.title LIKE 'Fi%')) ORDER by id DESC

matches found for keyword "Firefox"

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in quicksearch.php on line 52

this way I output the result:

while ($rows = mysql_fetch_array($result))
{
echo '<img src="images/a.gif">&nbsp;<a href=index.php?view=' . $rows['id'] . '><font color="#517aff"><u>' . $rows['title'] . '</u></font></a><br>';
}

thnx!

[Weedpacket]

Try using [man]mysql_error[/man] to see what the error is; and have a look too at the Debugging 101 thread in the Echo Lounge for more debugging suggestions.

[goaman]

hey..weedpacket,
thanks for the reply.. nice way to check mysql errors man..

latest error is:

I've used:
echo htmlspecialchars (mysql_errno ());
echo '<br>';
echo htmlspecialchars (mysql_error ());

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in quicksearch.php on line 26
1052
Column: 'id' in order clause is ambiguous

[Weedpacket]

Originally posted by goaman
Column: 'id' in order clause is ambiguous

Both tables have an "id" column, and you haven't said in the query which one you mean.

[goaman]

Does it cause the error because this "id" is not specified from which table it should grab? if so could u show me pls how to specify it?

thnx!

while ($rows = mysql_fetch_array($result))
{
echo '<img src="images/a.gif">&nbsp;<a href=index.php?view=' . $rows['id'] . '><font color="#517aff"><u>' . $rows['title'] . '</u></font></a><br>';
}

[madwormer2]

Instead of id, use news.id and old_new.id (its table.row)

I think 😕

[tripwater]

And change mysql_numrows() to mysql_num_rows() like
bpat1434 suggested that should get rid of at least one of the errors

And you are correct MadWormer2, that is how you create an alias in a table. That should help in distinguishing between the 2 tables.

So select new.ID as NewID, old.ID as OldID

Then $row["NewID"] from table 1
and $row["OldID"] from table 2

[goaman]

Hi guys,
thanks for the replies.. I've made some changes 'n tried to fix it.. damn, couldn't really tell whats wrong.. please take a look at this code and tell me whats wrong with it..

thnx!

<?php
include 'mysqconfig.php';

$search = $_POST['search'];
$entry = $search;

$query = "SELECT * FROM news a, old_news b
WHERE ((a.news LIKE '".substr($entry, 0, 2)."%') OR (a.title LIKE '".substr($entry, 0, 2)."%'))
AND ((b.news LIKE '".substr($entry, 0, 2)."%') OR (b.title LIKE '".substr($entry, 0, 2)."%')) 
ORDER by id DESC ";

$result = mysql_query($query);
$results = mysql_num_rows($result);

echo htmlspecialchars (mysql_errno ());
echo '<br>';
echo htmlspecialchars (mysql_error ());


echo("<table width='100%' cellpadding='2' cellspacing='0' border='0' align='center'>");
if($results == '0')
{
echo('<tr><td align="center" height="70" align="middle" class="style1">');
echo "<br>Searched for <i><font size='2'>$search</font></i>, No matches were found!";
echo '</td></tr>';
}
else
{

echo('<tr height="20"><td align="center" background="images/bo3.jpg" class="Style1">
<font size="1">' . $results . '</font><i> matches found for keyword</i> "<font size="1">' . $search . '</font>" 
</td></tr>');

echo('
<tr>
<td align="left" valign="middle" class="style1"><br>');
while ($rows = mysql_fetch_array($result))
{
echo '<img src="images/z4.gif">&nbsp;<a href=index.php?action=enews&view=' . $rows[id] . '><font color="#517aff"><u>' . $rows[title] . '</u></font></a><br>';
}

}
echo "</td></tr></table>";

?>

[konsu]

the syntax of your sql quesry is invalid. the field "id" used in the query's "order by" clause is ambiguous. the best way to debug your queries is to install mysql on your development machine and run queries from the "mysql" command line tool. also, a tutorial on joining tables would be useful.