Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

site search help needed

edited March 2007 in Vanilla 1.0 Help
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 } }

Comments

  • Options
    ok.... i have managed to solve the problem myself... if anybody is interested, i can post the source code :)
  • Options
    Go for it, you never know when it may help someone else, and Google indexes this forum...
  • Options
    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; } } }
  • Options
    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.
  • Options
    oh... thanks... ill do some research on that.. .thanks again
This discussion has been closed.