site search help needed

crash_D.

Hi.

I need some basic help with the angorithm of db search please. No code, just suggestions on the algorithm. thnks

I have two related tables in the mysql db, and i need to create a search. The parent table stores brands. The child table stores products, and one its fields is brand_id. I think you get the idea here.

Problem: to create a search that would seach both products and brands, so that if i query nokia 6630 i have this product in the results. Currently, I have it seach only products table; it queries product title (e.g. 6630) fine, but when i add the brand to it in the seach field, it fetches nothing. Logically, the seach is right, couse there is no product title Nokia. Thus, I need to make it search brands too somehow. And I need your help with the algorithm of this search.

Currentlly, it is the following:
if matches found in products table, count them and then for each of them output values else say "no results"

But for this new search i need, I suppose it should be somrthing like,
if matches found in products table, count them and then for each of them output values else search for brands table if mathes found there count and output else say no results
.. or something... Am i right in this? Help please

Here is the snippet (don't mind the encoding problem):
$q1=db('SELECT * FROM new_products WHERE title LIKE \'%'. $q .'%\' ORDER BY title'); if (rows($q1)>0) { $all=rows($q1); echo '<p style="margin-left: 10px">Ïî çàïðîñó &laquo;<var>' .$q.'</var>&raquo; íàéäåí(î) ' .$all. ' ðåçóëüòàò(a/îâ).</p><ol>'; while ($s=fasoc($q1)) { $b=fassoc('SELECT * FROM brands WHERE id='.$s['brand']); echo '...'; // output goes here } }

crash_D.

ok.... i have managed to solve the problem myself... if anybody is interested, i can post the source code

Stash

Go for it, you never know when it may help someone else, and Google indexes this forum...

crash_D.

ok... but i did not pay much attention to the quality of the code, and it was a routine, rather then coding.. so php experts don't blame me...

encoding failed

the algorithm is rather primitive, it goes like:
if match found in products then count and output
else
if match is found in brands count and output
else
if there is a space in the query then explode to q1 and q2 and search brands for q1
if matches found count them and for each search products for q2 if mathes found then count and output

$q1=db('SELECT * FROM new_products WHERE title LIKE \'%'. $q .'%\' ORDER BY title'); if (mysql_num_rows($q1)>0) { $all=mysql_num_rows($q1); echo '<p style="margin-left: 10px">Ïî çàïðîñó &laquo;<var>' .$q.'</var>&raquo; íàéäåí(î) ' .$all. ' ðåçóëüòàò(a/îâ) <small>(ïîèñê ïî ìîäåëÿì)</small>.</p><ol>'; while ($s=fasoc($q1)) { $b=fassoc('SELECT * FROM brands WHERE id='.$s['brand']); echo '<li><img style="margin: 0 11px 0 7px; float: right" src="' .$s['img1']. '" height="50px" alt="' .$b['title']. ' ' .$s['title']. '"><b><a href="/index.php?page=catalogue&pcat=' .$b['category']. '&brand=' .$b['title']. '&model=' .$s['title']. '">' .$b['title']. ' ' .$s['title']. '</a></b><br /> <p>' .(empty($s['synopsis'])? (empty($s['description_small']) ? (substr($s['description'], 1, 100)) : $s['description_small']) : $s['synopsis']). '</p><br style="clear: both">'; } echo '</ol>'; } else { $q2=db('SELECT * FROM brands WHERE title LIKE \'%'. $q .'%\' ORDER BY title'); if (mysql_num_rows($q2)>0) { $all=mysql_num_rows($q2); echo '<p style="margin-left: 10px">Ïî çàïðîñó &laquo;<var>' .$q.'</var>&raquo; íàéäåí(î) ' .$all. ' ðåçóëüòàò(a/îâ) <small>(ïîèñê ïî áðåíäó)</small>.</p><ol>'; while ($s=fasoc($q2)) { echo '<li><img style="margin: 0 11px 0 7px; float: right" src="' .$s['logo']. '" height="50px" alt="' .$s['title']. '"><b><a href="/index.php?page=catalogue&pcat=' .$s['category']. '&brand=' .$s['title']. '">' .$s['title']. '</a></b><br style="clear: right"/></li>'; } echo '</ol>'; } else { if (ereg(' ', $q)) {//echo 'there is a space'; $r=explode(' ', $q); $q3=db('SELECT * FROM brands WHERE title LIKE \'%'. $r[0] .'%\' ORDER BY title'); if (mysql_num_rows($q3)>0) { //$all=mysql_num_rows($q3); //echo '<p style="margin-left: 10px">Ïî çàïðîñó &laquo;<var>' .$q.'</var>&raquo; // íàéäåí(î) ' .$all. ' ðåçóëüòàò(a/îâ).</p><ol>'; while ($s=fasoc($q3)) { $q4=db('SELECT * FROM new_products WHERE title LIKE \'%'. $r[1] .'%\' ORDER BY title'); if (mysql_num_rows($q4)>0) { $all+=mysql_num_rows($q4); echo '<p style="margin-left: 10px">Ïî çàïðîñó &laquo;<var>' .$q.'</var>&raquo; íàéäåí(î) ' .$all. ' ðåçóëüòàò(a/îâ) <small>(ñìåøàííûé ïîèñê)</small>.</p><ol>'; while ($sp=fasoc($q4)) { echo '<li><img style="margin: 0 11px 0 7px; float: right" src="' .$sp['img1']. '" height="50px" alt="' .$s['title']. ' ' .$sp['title']. '"><b><a href="/index.php?page=catalogue&pcat=' .$s['category']. '&brand=' .$s['title']. '&model=' .$sp['title']. '">' .$s['title']. ' ' .$sp['title']. '</a></b><br /> <p>' .(empty($sp['synopsis'])? (empty($sp['description_small']) ? (substr($sp['description'], 1, 100)) : $sp['description_small']) : $sp['synopsis']). '</p><br style="clear: both">'; } echo '</ol>'; } else { echo $empty; } //echo '<li><img style="margin: 0 11px 0 7px; float: right" src="' .$s['logo']. '" height="50px" alt="' .$s['title']. '"><b><a href="/index.php?page=catalogue&pcat=' .$s['category']. '&brand=' .$s['title']. '">' .$s['title']. '</a></b><br />'; } echo '</ol>'; } else { echo $empty; } } else { echo $empty; } } }

jimw

I haven't used mysql a lot but I do have some database experience. In most db you have a way of linking files/tables. I've seen "join" used but I am not that good with mysql. I think this would also be a way to solve your problem.

crash_D.

oh... thanks... ill do some research on that.. .thanks again