2

This is actually my very first code. Im trying to add new posts to my Wordpress Site.
Mysql query will return 4035 rows that have all the details to insert the new post.
The problem : Loop wont ever stop! It will reach 4035 row and then start all over from the 1st row

Things I tried to make it stop:

  1. Added - if ($loopstimes > 4400) {break;}
  2. Limited mysql query to 4050 rows

The weird parts:

  1. When i limit mysql query to LIMIT 900, Everything works PERFECTLY. The loop stops when 900 post are added, if i add 950, the script will add 943 posts and then will give me Fatal error "Maximum execution time reached". Then the script stops.
  2. I increase the "Maximum execution time" to 300 Sec and increase the memory from 128 to 256MB, i use my query without LIMIT and then the Loop wont ever stop.

Is my code problematic? Or is server issue?

<?PHP 
$username = "xxxx";
$password = "xxxx";
$hostname = "localhost"; 

$loopstimes = 0;

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("xxxx_xxxx",$dbhandle) 
or die("Could not select examples");

//execute the SQL query and return records
// Query to get all names
$result = mysql_query("SELECT DISTINCT names_list.* ... LIMIT 4050");

if($result === FALSE) { 
    die(mysql_error()); // TODO: better error handling
}

//fetch the data from the database
while ($row = mysql_fetch_array($result)) {
    $p_name= $row["p_name"];
    $category= $row["gcategory"];
    $subcategory= $row["subcategory"];
    $ase_date= $row["ase_date"];

    //Fix the (') to prepare it for mysql query

    $pp_name = $p_name; 
    $newp_name = str_replace("'","''",$p_name);

    //Query to count results
    $minprice = mysql_query("SELECT ... WHERE Product_p_name = '$newp_name' AND ORDER BY product_price_usd");
    $countss = mysql_num_rows($minprice);

    if($minprice === FALSE) { 
        die(mysql_error()); // TODO: better error handling
    }

    $rowm = mysql_fetch_array($minprice);
    $minprice = '$'.$rowm["product_price_usd"];

    // Create post object
    $my_post = array(
        'post_title' => 'My title...',
        'post_status' => 'publish',
        'post_author' => 1
    );

    // Insert the post into the database
    $post_id = wp_insert_post( $my_post );

    // Add Advanced Custom field values
    update_field( "field_552fc3f84b43r", $p_name, $post_id ); 

    //Regex the date to keep just the year
    if (preg_match("/20.*/", $ase_date, $matches)) {
        $ase_year = $matches[0];
    }

    //Insert Tags for Custom Taxonomies

    wp_set_post_terms( $post_id, $audio_subcategory, 'subcategory-cpt', false );
    wp_set_post_terms( $post_id, $fex_pro_rg, 'fex-pro-cpt', false );

    $loopstimes++;

    if ($loopstimes > 4400) {break;}
    echo 'Post added!, '.$post_id.'<br>';
    echo $loopstimes;
}

//close the connection
mysql_close($dbhandle);
echo 'Finish';
?>
Rolando Isidoro
  • 4,983
  • 2
  • 31
  • 43
James
  • 55
  • 1
  • 7
  • 3
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 22 '15 at 17:15
  • first of all try to use mysqli_* because mysql_* is depricated now. – Alive to die - Anant Apr 22 '15 at 17:16
  • 1
    Chuck Norris eats `mysql_*` functions for breakfast. – Jens A. Koch Apr 22 '15 at 17:18
  • Without seeing the SQL statement that is feeding the `while` loop I'm not sure we can really answer this – Machavity Apr 22 '15 at 17:37

2 Answers2

0

Sure you already know that you shouldn't use mysql_* functions anymore.

But just to help you pass over the issue right now:

//Query to count results
$minprice_query_result = mysql_query("SELECT ... WHERE Product_p_name = '$newp_name' AND ORDER BY product_price_usd");
$countss = mysql_num_rows($minprice_query_result);

if($minprice_query_result === FALSE) { 
    die(mysql_error()); // TODO: better error handling
}

$rowm = mysql_fetch_array($minprice_query_result);
$minprice = '$'.$rowm["product_price_usd"];

mysql_free_result ( $minprice_query_result );

And by the way, why do you need this subquery? to get $minprice = '$'.$rowm["product_price_usd"]; ? but you never use that variable in your code!

Alex
  • 16,739
  • 1
  • 28
  • 51
  • Thanks for your time, well the $minprice i use it in a custom field. I have up to 10 custom fields like **update_field( "field_552fc3*****", $minprice, $post_id );** That i remove them from the current code just to keep it smaller. Ill try your suggestion and tell you if works asap. – James Apr 22 '15 at 18:00
  • you can't post not full fragment of code to get real help. Nobody can help if you hide part of your code. – Alex Apr 22 '15 at 18:02
  • I did change what you suggested but the script gave me warrnings. I downloaded the whole website + dbases and install it on my pc with xampp. I used my original code and the scrip works Perfect. So now i believe the problem is the host, any idea what might cause this endless loop? – James Apr 22 '15 at 19:18
  • did you do my suggestion? to rename variable and free mysql results? – Alex Apr 22 '15 at 19:27
0

I run the script using XAMPP and it worked perfect. To me, that means my code is correct and the problem is with my host. The way i solved this issue was to LIMIT 500 and updated mysql query to exclude the names that was already a post assigned with. Then i run the script X times till all names was assigned to a post.

BTW i changed from mysql_* to mysqli_* Thanks for the advice!

James
  • 55
  • 1
  • 7