Results 1 to 15 of 15
  1. #1
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Creating a search index
    Hi Guys:

    I'm a little lost on this. I set my columns to full text and it shows a cardinality of 1 even after I repaired table through phpmyadmin. Could this be causing my script to work incorrectly? I am getting no results on all searches. Unless I have something coded wrong.

    <form method="POST" action="search.php">
    Search:<br />
    <input type="text" id="name" name="name" /><br />
    <input type="submit" value="Search!" />
    </form>

    $keyword = mysql_real_escape_string($_POST['name']);

    // Perform the fulltext search
    $query = "SELECT name FROM main WHERE MATCH(name) AGAINST ('$keyword')";

    $result = mysql_query($query);

    // If results were found, output them
    if (mysql_num_rows($result) > 0) {

    printf("Results: <br />");

    while ($row = mysql_fetch_array($result)) {

    printf("Result %s: <a href='search.php?id=%s'>%s</a>",
    ucfirst($row['name']));

    }

    } else {
    printf("No results found");
    }

  2. #2
    Newbie
    Join Date
    November 14th, 2007
    Posts
    4
    try this:

    $query = "SELECT MATCH (name) AGAINST ('$keyword') AS score, name FROM main WHERE MATCH(name) AGAINST ('$keyword')";

  3. #3
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Thx for replying!

    Unfortunately I am still getting an empty result set

  4. #4
    Newbie
    Join Date
    November 14th, 2007
    Posts
    4
    Np, I guess try this and see if there are any errors that show up:

    $result = mysql_query($query) or die(mysql_error());

  5. #5
    Comfortably Numb John Powell's Avatar
    Join Date
    October 17th, 2005
    Location
    Bayou Country, LA
    Posts
    3,432
    I have a full text search and use IN BOOLEAN MODE . The article linked explains it better than I could.


  6. #6
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    thx guys I got it working mostly.

    On a side note does anyone know a way to speed up a category and/or manufacturer listing? I don't know if I can do a count(*) on this query:

    query_count=select distinct category from main order by category

  7. #7
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    I am going to jump into an advanced topic for you - normalization.

    Put your categories in a separate table, and only put it in there one time for each category. Then go back to your original table and make it reference your new category table. If this does not confuse the crap out of you let me know and I'll give you some more information on how to do this.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  8. #8
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Hi:

    The concept makes sense to me. I just don't understand how you tie the two tables together then. I'm assuming you would want to do the same thing with the manufacturers. Note this is a table for clothing merchants and there are a lot LOL

  9. #9
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    You time them together with what is called a 'key'.

    For the clothing table add a new integer column called categoryid.

    When you create your new category table give it two columns:
    categoryname - varchar(50)
    categoryid - integer (ID column/autonumber/autoincrement)

    Once you start adding categories to your category table mysql will start automatically giving them numbers (1,2,3,4,5,etc) assuming you have the properties on that categoryid column that I mentioned above.

    Then go back to your clothing table and fill in the numbers in the categoryid column that the category table created for you.

    There are ways to do this very fast. For a person with a decent amount of SQL experience this process should take no more than 1/2 hour.

    When all is said and done you should be able to delete the 'category' column from your product table and you will be left with your cool new categoryid column.

    Your new category table should only have ONE entry for each category. This should make queries for the list categories alot smaller.

    Questions?
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  10. #10
    Comfortably Numb John Powell's Avatar
    Join Date
    October 17th, 2005
    Location
    Bayou Country, LA
    Posts
    3,432
    Here is an easy one to try and it's what I use.
    $asql = "SELECT Distinct Category FROM rl_feed ORDER BY Category";


  11. #11
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Thx guys:

    On further study categories seem to come up quickly. Looks like it's the 21 random products that is slowing things down.

    $limit=21;
    $query_count="SELECT count(*) as totalrows from main ORDER BY RAND() LIMIT 21";
    $result_count=mysql_query($query_count);
    $totalrows=mysql_num_rows($result_count);
    $page = $_GET['page'];

    if(empty($page))
    {
    $page = 1;
    }
    $limitvalue = ($page * $limit) - $limit;
    $query = "SELECT * from main ORDER BY RAND() LIMIT 21";


    $result = mysql_query($query) or die("Error: " . mysql_error());

    ?>

    <table width="100%" border="0" align="center" cellpadding="5" cellspacing="5">
    <tr>


    <?php
    $prod_columns=3; // Alter this to change number of columns
    $loop_count = 1;
    while($row=mysql_fetch_array($result)){

    ?>

    <td width="<?php print 100/$prod_columns; ?>%" valign="top" align=center>

    <table width="100%" border="0" align="center" cellpadding="5" cellspacing="5">
    <tr>
    <td valign=top align=center colspan=2>

    <?
    $row['price'] = number_format($row['price'], 2);
    echo "<a href={$row['buyurl']} title='{$row['name']}'><img src={$row['imageurl']} border='0' valign='middle' width='100' alt='{$row['name']}'></a>";
    echo "<tr><td align=left colspan=2 valign=top>";
    echo "<a href={$row['buyurl']} title='{$row['name']}'><b>{$row['name']}</b></a>";
    echo "<br>";
    echo "<font color=red>Price: {$row['price']}</font>";
    echo "<br>Merchant: {$row['merchant']}";
    echo "<a href={$row['buyurl']} title='{$row['name']}'>";
    echo "<br><font color=blue>Click For More Info</font></a> &nbsp;";

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

    echo "</td>";



    if($loop_count == $prod_columns){
    ?>


    </tr>
    </table>


    <table width="100%" border="0" align="center" cellpadding="5" cellspacing="0">
    <tr>

    <?php
    $loop_count = 1;
    }else{
    $loop_count++;
    }
    $count++; // Just for making the while statement work
    }




    ?>


    I've already got count(*) for the query_count so is there another way to speed it up? I like to display 21 products at random on the homepage.

  12. #12
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Is there anyway you can pre generate the random lists at a low volume time, then just use those lists during the peak times?
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  13. #13
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    You can always use EXPLAIN before a SQL statement to get details on how MySQL is processing it and what might be slowing it down. (If you're testing your SQL statements in phpMyAdmin, there's an option to "Explain SQL".) I suspect that the way you're using RAND() will cause MySQL to build a temporary table and do a full scan, and those are two things you want to avoid if at all possible.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  14. #14
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    Hi Michael:

    Do you know of any way around the above? ie the full scan

  15. #15
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    You could get around the temporary table if you create a "Rnd" field and then periodically "UPDATE tablename SET Rnd = RAND()" then select based on the Rnd field rather than RAND(). You might also want to populate the Rnd field every time you create a new record.

    But a quick question... Why do you want random products? It seems like there are much better ways to pick products. Which have been most popular (you are logging clicks, right?)? Which have recent price drops (you are keeping track of price changes, right?)? Which ones are newest? Which ones are the best price compared to similar items? Which ones have thumbnails? Which ones have the best reviews/ratings? Which ones are on the most wishlists? Which ones best match the keywords searched or defined on the page? Which ones come from the most popular categories/subcategories? You get the picture. Create a formula for an aggregate Rank based on whatever criteria you think is most important, and populate that field regularly. Select your sampling based on that.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  16. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Creating an Index
    By jackson992 in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: October 22nd, 2006, 05:11 AM
  2. 301 on the index creating a loop>>>HELP
    By mrbshouse in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: August 24th, 2006, 01:44 AM
  3. Creating a Yahoo Style Index Page
    By nubizmedia in forum WebMerge (Fourthworld.com)
    Replies: 2
    Last Post: September 17th, 2004, 05:11 AM
  4. Creating index pages for multiple/uneven tiers
    By svenhassel in forum WebMerge (Fourthworld.com)
    Replies: 5
    Last Post: July 31st, 2004, 06:54 PM
  5. Creating a custom link on an index page
    By Synozeer in forum WebMerge (Fourthworld.com)
    Replies: 7
    Last Post: December 2nd, 2003, 11:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •