Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

i have this code:

while ($sum<16 || $sum>18){
$totala = 0;
$totalb = 0;
$totalc = 0;
$ranka = mysql_query("SELECT duration FROM table WHERE rank=1 ORDER BY rand() LIMIT 1");
$rankb = mysql_query("SELECT duration FROM table WHERE rank=2 ORDER BY rand() LIMIT 1");
$rankc = mysql_query("SELECT duration FROM table WHERE rank=3 ORDER BY rand() LIMIT 1");
while ($rowa = mysql_fetch_array($ranka)) {
    echo $rowa['duration'] . "<br/>";
    $totala = $totala + $rowa['duration'];
}
while ($rowb = mysql_fetch_array($rankb)) {
        $totalb = $totalb + $rowb['duration'];      
    }       
while ($rowc = mysql_fetch_array($rankc)) {
        $totalc = $totalc + $rowc['duration'];
    }

    $sum=$totala+$totalb+$totalc;
}
echo $sum;

It works fine, But the problem is until "$sum=16" the "echo $rowa['duration']" executes, the question is, is there a away to "echo" only the latest executed code in the "while ($rowa = mysql_fetch_array($ranka))" i this while loop? Because most of the times returns all the numbers until the "$sum=16"

share|improve this question
    
What do yo mean by only the latest executed code? –  vidit Mar 20 '12 at 22:30
1  
Thanks Vidit, Keeyai answered my question. you can check the answer to understand. Thanks. –  user2682025 Mar 20 '12 at 22:43
add comment

2 Answers

up vote 2 down vote accepted

You are explicitly echoing the $rowa['duration'] in the first inner while loop. If you only want to print the last duration from the $ranka set, simple change the echo to $rowa_duration = $rowa['duration'] then echo it outside the loop.

while ($rowa = mysql_fetch_array($ranka)) {
    $rowa_duration = $rowa['duration'];
    $totala = $totala + $rowa['duration'];
}
echo $rowa_duration . '<br/>';
share|improve this answer
    
Thank you very much it's really helped me. –  user2682025 Mar 20 '12 at 22:36
    
What if there was more than one "duration"? let's say if this line was like that "$ranka = mysql_query("SELECT duration FROM table WHERE rank=1 ORDER BY rand() LIMIT 3");" –  user2682025 Mar 20 '12 at 22:52
    
I don't understand your question. Are you wanting to print all the durations again, the same as your problem you initially posted? –  Keeyai Mar 20 '12 at 22:57
    
no, If you taked a look in the code i have a "rand" function soo it's searches in the database until find thoes clips that the sum of ther durations equals to a number (any number), so some times i want 2 clips from rank 1 or 2 clips from ranks 3. and i want to print ther durations. –  user2682025 Mar 20 '12 at 23:05
    
What i really want is to print the latest executed "while ($rowa = mysql_fetch_array($ranka)) { echo $rowa['duration']; }" outside the parent while loop. –  user2682025 Mar 20 '12 at 23:13
show 2 more comments

What you are doing there is bad on multiple levels. And your english horrid. Well .. practice makes perfect. You could try joining ##php chat room on FreeNode server. That would improve both your english and php skills .. it sure helped me a lot. Anyway ..

The SQL

First of all, to use ORDER BY RAND() is extremely ignorant (at best). As your tables begin the get larger, this operation will make your queries slower. It has n * log2(n) complexity, which means that selecting querying table with 1000 entries will take ~3000 times longer then querying table with 10 entries.

To learn more about it , you should read this blog post, but as for your current queries , the solution would look like:

SELECT duration 
FROM table 
    JOIN (SELECT CEIL(RAND()*(SELECT MAX(id) FROM table)) AS id) as choice
WHERE 
    table.id >= choice.id
    rank = 1 
LIMIT 1

This would select random duration from the table.

But since you you are actually selecting data with 3 different ranks ( 1, 2 and 3 ), it would make sense to create a UNION of three queries :

SELECT duration 
FROM table 
    JOIN (SELECT CEIL(RAND()*(SELECT MAX(id) FROM table)) AS id) as choice
WHERE 
    table.id >= choice.id
    rank = 1 
LIMIT 1
UNION ALL
SELECT duration 
FROM table 
    JOIN (SELECT CEIL(RAND()*(SELECT MAX(id) FROM table)) AS id) as choice
WHERE 
    table.id >= choice.id
    rank = 2 
LIMIT 1
UNION ALL
SELECT duration 
FROM table 
    JOIN (SELECT CEIL(RAND()*(SELECT MAX(id) FROM table)) AS id) as choice
WHERE 
    table.id >= choice.id
    rank = 3 
LIMIT 1

Look scary, but it actually will be faster then what you are currently using, and the result will be three entries from duration column.

PHP with SQL

  1. You are still using the old mysql_* functions to access database. This form of API is more then 10 years old and should not be used, when writing new code. The old functions are not maintained (fixed and/or improved ) anymore and even community has begun the process of deprecating said functions.

    Instead you should be using either PDO or MySQLi. Which one to use depends on your personal preferences and what is actually available to you. I prefer PDO (because of named parameters and support for other RDBMS), but that's somewhat subjective choice.

  2. Other issue with you php/mysql code is that you seem to pointlessly loop thought items. Your queries have LIMIT 1, which means that there will be only one row. No point in making a loop.

  3. There is potential for endless loop if maximum value for duration is 1. At the start of loop you will have $sum === 15 which fits the first while condition. And at the end that loop you can have $sum === 18 , which satisfies the second loop condition ... and then it is off to the infinity and your SQL server chokes.

And if you are using fractions for duration, then the total value of 3 new results needs to be even smaller. Just over 2. Start with 15.99 , ends with 18.01 (that's additional 2.02 in duration or less the 0.7 per each). Again .. endless loop.

Suggestion

Here is how i would do it:

$pdo = new PDO('mysql:dbname=my_db;host=localhost', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sum = 0;
while ( $sum < 16 )
{
    $query = 'that LARGE query above';
    $statement = $pdo->prepare( $query );
    if ( $statement->execute() )
    {
        $data = $statement->fetchAll( PDO::FETCH_ASSOC );
        $sum += $data[0]['duration']+$data[1]['duration']+$data[2]['duration'];
    }
}
echo $data[0]['duration'];

This should do what your code did .. or at least, what i assume, was your intentions.

share|improve this answer
    
Hi, I don't know how to tell you how this application works, I know my english is bad but i will try my best. or if it's ok for you i will send you the interface code too, And thanks for this great advices im already trying to learn PDO. –  user2682025 Mar 21 '12 at 21:10
    
I don't really have the time to dig through unfamiliar code. You should just start with the first query ( the shorted one ) and rewrite it so that it works with you existing database. In your case the PDO is fairly minor thing. You can always migrate you code when it actually already works. If the basic algorithm does not change, then migration from mysql_* to prepares statements with PDO will actually count as optimization. Oh .. and read that blog post, which i mentioned. –  tereško Mar 21 '12 at 21:16
    
It's HTML code jut to let you know how the application works, Because i think no one knows what i really want because of my bad english,. Anyway i'll folow your advices for sure. Thanks –  user2682025 Mar 21 '12 at 21:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.