while

[birdie]

[code]
$amount = "0";
while($numrows > '0')
{
$amount++;
$prefix4 = substr($word, 0, $amount);
$sql = "SELECT * FROM ctrade_alpha WHERE phrase LIKE '$prefix4' ORDER BY times DESC LIMIT 1";
$query = mysql_query($sql);
$numrows = mysql_num_rows($query);
}
[/code]

Hi, i dont usually do while() like this so this is a new one. Any idea why this wont work? Ive saw loops that have code in the while() but i'm not sure how it exactly works?

Thanks alot.

[HuggieBear]

This will never work unless you set $numrows outside the while loop first, as this condition [code=php:0]while ($numrows > '0')[/code] will always be false.

Also, it helps if you tell us what you're trying to do  ;)

Regards
Huggie

[Destruction]

It looks like Birdie wants to take a word and run an sql query to return all rows that have a phrase LIKE the word but is doing it in parts, adding a letter to the word in the query each time (the substr part using $amount).  This is alot of work for a database to be doing as it sends a fresh sql query every time so you may want to minimize the amount of work/queries if you can.  Would be best if we knew what you were trying to do and possibly why, so we can help with how to go about it :)

[code]
<?php
$query = mysql_query("SELECT * FROM `ctrade_alpya` WHERE `phrase` LIKE '%$search%' ORDER BY `times DESC");
if(!$query || mysql_num_rows($query) < 1)
{
    //failure - output error, log error, etc
}
else
{
    //loop through all returned rows
    while($row = mysql_fetch_assoc($query))
    {
        //fields/columns from the query are stored in the $row array accessible by $row['columnname']
        echo $row['phrase']."<br/>\n"; //for example, echo out the phrase and put an html br tag after
    }
}
mysql_free_result($query);
?>
[/code]

That's an example of how I would search for any phrases 'like' the given criteria and output each one (no limit included in the example).  Without knowing exactly what you are looking to do, it's a little more difficult to tailor it but it gives a starting point.

Also, VERY IMPORTANT NOTE, be VERY careful not to create an infinite loop.  Using the number of rows will create an infinite loop because it will always be greater than 0 unless no rows were returned.  If you have 1 row in the database and use $numrows > 0 as criteria, you're basically saying "if it's greater than 0, keep going...and going...and going...".  If you ever do something like that, always make sure the variable will increase/decrease as necessary.  If you use mysql_fetch_assoc for example, each loop takes the internal pointer to the next row returned so it will always decrease until there are none left.

Hope this helps,

Dest

[birdie]

Thanks that helps.

Actually, i am aiming to make a "Did you mean.." part for my search engine.
Basically, it also checks for spelling mistakes.

It would do this by ..

while(num rows are more than 1)
{
++ the value for $amount substr($word, 0, $amount)
until there are no more rows
}

Hard to explain.

[Destruction]

Hmm I understand what you want to do though I have my reservations about how to do it to reduce the number of queries being run to the database for the same thing essentially.  The way you want to do it there will search anything that's LIKE the first letter, then the first two letters, etc.  I would say you'll need to set a minimum letter count on the first query otherwise everything containing the given letter will likely be returned (perhaps the first 3-5 letters if there are that many etc).

[code]
<?php
$searchList = array();
//$i = 3 sets the minimum boundary
for($i=3; $i < strlen($word); $i++)
{
    $searchList[] = "'".substr($word,0,$i)."'";
}

foreach($searchList as $search)
{
    $query = mysql_query("SELECT * FROM `ctrade_alpha` WHERE `phrase` LIKE '$search' ORDER BY `times` DESC");
    if(!$query || mysql_num_rows($query) < 1)
    {
        //failure - output error, log error, etc
    }
    else
    {
        //loop through all returned rows
        while($row = mysql_fetch_assoc($query))
        {
            //fields/columns from the query are stored in the $row array accessible by $row['columnname']
            echo $row['phrase']."<br/>\n"; //for example, echo out the phrase and put an html br tag after
        }
    }
    mysql_free_result($query);
}
?>
[/code]

Note the setting of each of the possible searches before the queries database retrieval.  Still a fair number of queries depending on the string.  If you wanted to set a maximum boundary, you can set the for loop to use $i < $max.  Be careful to make sure $max does not exceed strlen($word) however.

I'm sure there's another way that will help reduce the number of queries.  Perhaps draw the lowest common factor as the query, ie: the first or first few from the searchList and make php do the work with the results drawn, though this depends on whether you want mysql or php to do the main bulk of the work.  Also, you may wish to make sure you cache common searches to avoid having to call the database unnecessarily.

Dest

[birdie]

Thanks! very helpful :)