PHP returns a result from mysql, but if I while loop the result, nothing shows

Question

here is my code:

<?php
    require('...........');
    require('................');

    $search_query = strip_tags(substr($_POST['search_query'], 0, 60));
    $region_query = strip_tags(substr($_POST['region_choice'], 0, 2));
    $class_query  = $_POST['class_choice'];
    $quick_search = strip_tags(substr($_POST['quick_search'], 0, 1));
             ;

    # setup database connection
    $db_ok = 1;
    $dbh = @mysql_connect($GLOBALS['database']['hs'], $GLOBALS['database']['un'],$GLOBALS['database']['pw']);
    if (! $dbh) {
        $db_ok = 0;
    }
    $dbh_selected = @mysql_select_db($GLOBALS['database']['db'], $dbh);
    if (! $dbh_selected) {
        $db_ok = 0;
    }

    if ($db_ok == 1) {
        $query_addition = "";

    if ($region_query > 0) {
        $query_addition = " AND `location` = \"$region_query\" ";
    }   

    if ($class_query > 0) {
        $query_addition .= " AND `class` = \"$class_query\" ";
    }

    $query  = "SELECT ID,date_active,title,location,environment,specialism FROM ";
    $query .= $GLOBALS['database']['jobs_table'] . " WHERE `date_active` < CURDATE()     AND `date_inactive` > CURDATE() 
    $query_addition AND `description` LIKE \"%$search_query%\"  ORDER BY `ID`";
    mysql_real_escape_string($query); 
    //mysql_set_charset('utf-8', $query);
    $result = mysql_query($query,$dbh);

    $table_content = "";
    $row_count = 0;

    if ($result) {  
        while ($row = mysql_fetch_row($result)) {
            $id    = $row[0];
            $loc = stripslashes($row[3]);
            $title = htmlentities(stripslashes($row[2]));           
            $env   = htmlentities(stripslashes($row[4]));           
            $spec  = htmlentities(stripslashes($row[5]));   

            if ($row_count % 2 == 0) {
                $table_content .= "<tr>";
            } else {    
                $table_content .= "<tr class=\"mellon\">";
            }   
            $table_content .= "<td><a href=\"?p=Vacature&ID=$id\" abbr=\"". $title . "\" title=\"" . $title ."\">" . $title . "</a></td>";
            $table_content .= "<td>" . get_location($dbh,$loc) . "</td>";
            $table_content .= "<td>" . $env . "</td>";
            $table_content .= "<td>" . $spec . "</td>";
            $table_content .= "</tr>\n";
            $row_count++;
            if ($row_count == 20) {
                break;
            }           
        }
    }

    if ($row_count > 0) {
        print " 
        <p>U heeft gezocht op: <b>$search_query</b></p>
        <p>Hieronder vindt u een overzicht van gevonden vacatures met een maximum aantal van 20. Er zijn in totaal $row_count vacatures gevonden.</p>
        <table class=\"table_layout\">
          <tbody>
            <tr>
              <th>Titel</th><th>Regio</th><th>Werkomgeving</th><th>Specialisme</th>
            </tr>
            $table_content 
        </tbody></table>";

    } else {
        print " 
        <p>U heeft gezocht op: <b>$search_query</b></p>
        <p><h4>Helaas heeft uw zoekopdracht geen resultaten opgeleverd</h4> </p>
        " ;
    }

} else {
    print "
    <p>U heeft gezocht op: <b>$search_query</b></p>
    <p>Het zoeken is mislukt..</p>
    ";
}   
?>

I've tried everything, if I var_dump I get that he has a result, Only the while loop doesn't seem to work. By the way it only doesn't work when I use a special character like ö, of I use the o it will work. I've tried everything from htmlspecialchar, htmlentities and so on. I also tried to convert the special chars with a str_replace and strtr. Please help, would be thankful

what does mysql_num_rows($resultset) return on your result set ? — Wadih M.
– Wadih M., Commented Dec 2, 2011 at 14:50
Worth mentioning by the way is that when I run the code in mysql it works perfectly. — machie27
– machie27, Commented Dec 5, 2011 at 8:58

Marc B · Accepted Answer · 2011-12-02 15:02:30Z

2

Doing a mysql_real_escape_string on the entire query is the WRONG way to do things. You escape INDIVIDUAL strings you insert, not the whole query. By escaping the whole query, you're breaking it.

$x = mysql_real_escape_string("Miles O'Brien");
$sql = "SELECT something FROM somewhere WHERE username='$x'";

will produce

SELECT something FROM somewhere WHERE username='Miles O\'Brien';

by comparison, you version would product

SELECT something FROM somewhere WHERE username=\'Miles O\'Brien\';

and completely fail.

answered Dec 2, 2011 at 15:02

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

machie27 Over a year ago

thanks guys, but i still have the problem, when I try to search with 'ö', this returns no results, while when I search with a 'o' instead, it works. The string however is when f.e. searching with 'zorgcoördinator' 16chars long and when searching with 'zorgcoordinator' 15 chars. @marc-b

machie27 Over a year ago

I adjusted the mysql_real_escape string THANKS. The problem which I had is solved now, I used utf8_decode which worked! Thanks again all for looking at my problem

Explosion Pills · Accepted Answer · 2011-12-02 14:58:01Z

2

You can't run mysql_real_escape_string() on the entire query, only on query parameters:

$query .= $GLOBALS['database']['jobs_table'] . " WHERE `date_active` < CURDATE()
    AND `date_inactive` > CURDATE()"
    . mysql_real_escape_string($query_addition) . " AND `description`
    LIKE '%" . mysql_real_escape_string($search_query)"%'  ORDER BY `ID`";

The quotes in your query will get escaped if you use it on the whole thing.

answered Dec 2, 2011 at 14:58

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

2 Comments

machie27 Over a year ago

thanks guys, but i still have the problem, when I try to search with 'ö', this returns no results, while when I search with a 'o' instead, it works. The string however is when f.e. searching with 'zorgcoördinator' 16chars long and when searching with 'zorgcoordinator' 15 chars. @tandu

machie27 Over a year ago

I adjusted the mysql_real_escape string THANKS. The problem which I had is solved now, I used utf8_decode which worked! Thanks again all for looking at my problem

Damien Pirsy · Accepted Answer · 2011-12-02 14:59:47Z

2

You seem to be using the mysql_real_escape_string function wrong. You are supposed to use it on the variables you put into the mysql query, not the whole query.

Try this:

$query_addition = "";
if ($region_query > 0) {
    $query_addition .= " AND `location` = '".mysql_real_escape_string($class_query)."' ";
}   

if ($class_query > 0) {
    $query_addition .= " AND `class` = '".mysql_real_escape_string($class_query)."' ";
}

$query  = "SELECT ID,date_active,title,location,environment,specialism FROM ";
$query .= $GLOBALS['database']['jobs_table'] . " WHERE `date_active` < CURDATE()     AND `date_inactive` > CURDATE() 
        $query_addition AND `description` LIKE '".mysql_real_escape_string($search_query)."'  ORDER BY `ID`";

$result = mysql_query($query,$dbh);

Does it help?

edited Dec 2, 2011 at 14:59

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

answered Dec 2, 2011 at 14:58

Sonny

733 bronze badges

2 Comments

machie27 Over a year ago

thanks guys, but i still have the problem, when I try to search with 'ö', this returns no results, while when I search with a 'o' instead, it works. The string however is when f.e. searching with 'zorgcoördinator' 16chars long and when searching with 'zorgcoordinator' 15 chars. @sonny

machie27 Over a year ago

I adjusted the mysql_real_escape string THANKS. The problem which I had is solved now, I used utf8_decode which worked! Thanks again all for looking at my problem

Your Common Sense · Accepted Answer · 2011-12-02 15:04:16Z

1

The query returned no rows.

Print your query out and run it in phpmyadmin/console to be sure and then alter the query to get the query that will return desired rows.

To sort things out you have to separate your task into smaller sub-tasks.
The algorithm is quite simple

Put PHP aside anad work with SQL only in console/phpmyadmin.
Work your query out until make it works. Write this query down for the future reference.
Get back to PHP to assemble the query from
Print this query out and compare with one from the (1)
Work your code out until you have 2 queries identical
Finally, run it with PHP.

PS. I am aware that mysql_real_escape_string($query); was added by accident among other attempts to make it work but anyway you have to remove that line.

edited Dec 2, 2011 at 15:04

answered Dec 2, 2011 at 14:59

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

PHP returns a result from mysql, but if I while loop the result, nothing shows

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related