Support » Developing with WordPress » sql foreach problem

  • Resolved gottfrieds

    (@gottfrieds)


    I use the following code:

        global $wpdb;
         $ot = $_GET["ot"];
         $query = "SELECT ort, wahlberechtigte, ungueltige, gueltige, CDU, SPD, UWG, Gruene, FDP, otix FROM wp_kommunalwahl WHERE ortsteil='" . $ot . "' ORDER BY ortsteil, otix";
         $result = $wpdb->get_results($query);
         $anz = $wpdb->num_rows;
     	foreach ($result as $row) {
    	    $otix = intval($row->otix);
    	    $aort[$otix] = $row->ort;
    	    $awberechtigte[$otix] = intval($row->wahlberechtigte);
    	    $aungueltige[$otix] = intval($row->ungueltige);
    	    $agueltige[$otix] = intval($row->gueltige);
    	    $acdu[$otix] = intval($row->CDU);
    	    $aspd[$otix] = intval($row->SPD);
    	    $auwg[$otix] = intval($row->UWG);
    	    $agruene[$otix] = intval($row->Gruene);
    	    $afdp[$otix] = intval($row->FDP);
    	}
    

    The select query works fine. The data retrieved are okay. The problem: The foreach clause retrieves the data but doesn’t stop at the end of the list. It work until time limit. How to solve the problem??? If there is only one row it works!

    • This topic was modified 2 years, 2 months ago by gottfrieds.
    • This topic was modified 2 years, 2 months ago by gottfrieds.
    • This topic was modified 2 years, 2 months ago by gottfrieds.
    • This topic was modified 2 years, 2 months ago by gottfrieds.
Viewing 3 replies - 1 through 3 (of 3 total)
  • You should put a LIMIT clause on your query.

    This isn’t the entire code, is it? The foreach does not do anything with the data except move it from an object to a bunch of arrays. I assume you still have to process the arrays separately.

    Please don’t use this code on your site until you have put some security into it. As it is now, any hacker could inject SQL into your query through the URL.

    Dion

    (@diondesigns)

    First, please replace these two lines:

    $ot = $_GET["ot"];
    $query = "SELECT ort, wahlberechtigte, ungueltige, gueltige, CDU, SPD, UWG, Gruene, FDP, otix FROM wp_kommunalwahl WHERE ortsteil='" . $ot . "' ORDER BY ortsteil, otix";

    with this single line:

    $query = $wpdb->prepare('SELECT ort, wahlberechtigte, ungueltige, gueltige, CDU, SPD, UWG, Gruene, FDP, otix FROM wp_kommunalwahl WHERE ortsteil = %s ORDER BY ortsteil, otix', $_GET["ot"]);
    

    This will eliminate the SQL injection issue that Joy mentioned. (There are better ways to accomplish this, but the above is the “WordPress Approved” method.)

    The $Wpdb->get_results() function has serious memory and CPU issues if the query returns a large amount of data. Please check your PHP error log to verify whether the issue is CPU or memory related. The fix for your problem is dependent on which type of error is occurring.

    Thread Starter gottfrieds

    (@gottfrieds)

    Hello – I found the error in a following loop. Thanks fór your help.

    • This reply was modified 2 years, 2 months ago by gottfrieds.
Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘sql foreach problem’ is closed to new replies.