PHP While循环中的多个SQL If语句

Question

I've created a script to run on my database at five minute intervals as a cron job. It's not a well written piece of code, but it's done quickly and should do the job for now.

I'm executing a WHILE loop to execute multiple if statements which in turn have multiple SQL statements within them. Problem is, it's only iterating the WHILE loop once and then stops and i'm not entirely sure why. Code is as below:

<?php

require_once('config.php');

$hashtags = mysql_query("SELECT id, hashtag FROM hashtags WHERE enabled = '1'") or die(mysql_error());

while($row = mysql_fetch_array($hashtags))
{
    $hashtag_id = $row['id'];
    $hashtag = $row['hashtag'];


    //Get id and latest_tweet_id from report log
    $latest_report_tweet_id_query = mysql_query("SELECT id, latest_tweet_id FROM reports_log WHERE name = 'post_count' AND hashtag_id = '".$hashtag_id."' LIMIT 1") or die(mysql_error());

    if (mysql_num_rows($latest_report_tweet_id_query) == 0) { 
        $new_report_tweet_id_query = mysql_fetch_array(mysql_query("SELECT tweet_id FROM tweet_tags WHERE tag = '".$hashtag."' ORDER by tweet_id desc LIMIT 1")) or die(mysql_error());
        $new_report_tweet_id = $new_report_tweet_id_query['tweet_id'];  

        $post_count_query = mysql_fetch_array(mysql_query("SELECT count(tweet_id) as tweet_count FROM tweet_tags WHERE tag = '".$hashtag."' AND tweet_id <= '".$new_report_tweet_id."'")) or die(mysql_error());
        $post_count = $post_count_query['tweet_count'];

        if(mysql_query("INSERT INTO post_count_reports (timestamp, hashtag_id, post_count, latest_tweet_id) VALUES ('".date("Y-m-d H:i:s")."', '".$hashtag_id."', '".$post_count."', '".$new_report_tweet_id."')"))
        {
            //Get just created id of the report
            $report_id_query = mysql_fetch_array(mysql_query("SELECT id FROM post_count_reports WHERE hashtag_id = '".$hashtag_id."' AND latest_tweet_id = '".$new_report_tweet_id."'")) or die(mysql_error());
            $report_id = $report_id_query['id'];

            if(mysql_query("INSERT INTO reports_log (timestamp, hashtag_id, name, latest_tweet_id, latest_report_id) VALUES ('".date('Y-m-d H:i:s')."', '".$hashtag_id."', 'post_count', '".$new_report_tweet_id."', '".$report_id."')")) 
            {
                echo "Successfully created report! NEW";
            }
            else {
                echo "Failed updating report log! NEW";
            }
        }
        else
        {
            echo "Failed making report! NEW";
        }
    }
    else {

        //Set the latest report id
        $latest_report_tweet_id_array = mysql_fetch_array($latest_report_tweet_id_query);
        $latest_report_log_id = $latest_report_tweet_id_array['id'];  
        $latest_report_tweet_id = $latest_report_tweet_id_array['latest_tweet_id'];   

        //Query to get the latest tweet_id in the database        
        $new_report_tweet_id_query = mysql_fetch_array(mysql_query("SELECT tweet_id FROM tweet_tags WHERE tag = '".$hashtag."' ORDER by tweet_id desc LIMIT 1")) or die(mysql_error());
        $new_report_tweet_id = $new_report_tweet_id_query['tweet_id']; 

        //Query to get the new post count from database
        $new_post_count_query = mysql_fetch_array(mysql_query("SELECT count(tweet_id) as tweet_count FROM tweet_tags WHERE tag = '".$hashtag."' AND tweet_id > '".$latest_report_tweet_id."' AND tweet_id <= '".$new_report_tweet_id."'")) or die(mysql_error());
        $new_post_count = $new_post_count_query['tweet_count'];

        $old_post_count_query = mysql_fetch_array(mysql_query("SELECT id, post_count FROM post_count_reports ORDER by timestamp desc LIMIT 1")) or die(mysql_error());
        $old_post_count = $old_post_count_query['post_count'];

        $post_count = $old_post_count + $new_post_count;

        if(mysql_query("INSERT INTO post_count_reports (timestamp, hashtag_id, post_count, latest_tweet_id) VALUES ('".date('Y-m-d H:i:s')."', '".$hashtag_id."', '".$post_count."', '".$new_report_tweet_id."')"))
        {
            //Get just created id of the report
            $report_id_query = mysql_fetch_array(mysql_query("SELECT id FROM post_count_reports WHERE hashtag_id = '".$hashtag_id."' AND latest_tweet_id = '".$new_report_tweet_id."' ORDER by timestamp desc LIMIT 1")) or die(mysql_error());
            $report_id = $report_id_query['id'];

            if(mysql_query("UPDATE reports_log SET id = '".$latest_report_log_id."', timestamp = '".date('Y-m-d H:i:s')."', latest_tweet_id = '".$new_report_tweet_id."', latest_report_id = '".$report_id."' WHERE name = 'post_count'")) 
            {
                echo "Successfully created report!";
            }
            else {
                echo "Failed updating report log!";
            }
        }
        else
        {
            echo "Failed making report!";
        }

    }

}

?>

Answer 1

Massive error on my part, turns out whilst there were three hashtags in the hashtags table there were only rows with one of the hashtags in the tweet_tags table. Wasted a few hours on this one.

Moral of the story, always log and check for errors!