Two Questions: Custom DB Query not working properly

  • Resolved pdxchambers

    (@pdxchambers)


    8 years, 6 months ago

    I’m developing a custom plugin and running into a road block with a function that is supposed to query a custom table and return a list of breweries within a given city. I’m running into two problems… first, while it appears the query is finding all of the results in the table, it is only displaying the last one instead of all of them as intended. The second issue is a bigger issue because it’s breaking the whole thing. I’m using $wpdb->prepare() to escape the SQL query, but when I use that funcion I get no results. I’ve been all over the forums and Google over the last couple of days and can’t put my finger on the issue so I’m hoping someone here can shed some light.

    I posted the code on Pastebin

    I have validated the queries in SQL Workbench so I know the queries themselves are good, so I’m thinking this is more a fundamental misunderstanding of how this works. I have a similar function that works perfectly (unless I escape the query, then it breaks as well)… any advice?

    • This topic was modified 8 years, 6 months ago by pdxchambers.
    • This topic was modified 8 years, 6 months ago by pdxchambers.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter pdxchambers

    (@pdxchambers)

    I think I might have fixed the issue with $wpdb->prepare()… I removed it from here…

    
if ( $selector == 'all' ) {
	$sql = 'SELECT * FROM ' . $db_table;
	$rows = $wpdb->get_results( $sql, ARRAY_A );
}

    I did that because that query isn’t using anything user supplied… I then modified the else clause…

    
else {
	$sql = $wpdb->prepare('SELECT * FROM ' . $db_table . ' WHERE city = %s', $selector);
	$rows = $wpdb->get_results( $sql, ARRAY_A );
}

    ‘$selector’ is input from the user via a form so I felt that really needs to be escaped. This seems to be working, however I’m still getting only one result displayed instead of all of them as in my original post.

    Thread Starter pdxchambers

    (@pdxchambers)

    I just realized that the reason my results aren’t displaying is because I defined my $table variable within the foreach statement. It was doing exactly what it was supposed to be doing, I was just overwriting the variable with each iteration of the loop. That’s why only the last result was being displayed. I moved the variable declaration out of the loop and changed it to concatenate all the way through the loop and it worked perfectly.

    `
    foreach ($rows as $row ) {
    $table .= ‘<table>’; //<–changed this to concatenate instead of just assign.. works now!
    $table .= ‘<tr><th>’ . $row[‘name’] . ‘</th><td>’ . $row[‘phone’] . ‘</td></tr>’;
    $table .= ‘<tr><td>’ . $row[‘website’] . ‘</td><td>’ . $row[‘twitter’] . ‘</td><td>’ . $row[‘instagram’] . ‘</td><td>’ . $row[‘facebook’] . ‘</td></tr>’;
    $table .= ‘<tr><td>’ . $row[‘address’] . ‘</td><td>’ . $row[‘city’] . ‘</td><td>’ . $row[‘state’] . ‘</td><td>’ . $row[‘zip’] . ‘</td></tr>’;
    $table .= ‘<tr><td>’ . $row[‘notes’] . ‘</td></tr>’;
    $table .= ‘</table> <p>Num_Rows = ‘ . $wpdb->num_rows;
    }

    **facepalm**

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Two Questions: Custom DB Query not working properly’ is closed to new replies.

Tags