Wordpress Custom SQL Table with UserID Filter for results

Question

Wordpress Custom SQL Table with UserID Filter for results

Trev

2022年5月4日 21:49

UPDATE: I have realised that I need to use a plugin like PHP Snipping or Insert PHP to make this work, I have updated my screen shots to reflect where Im upto from my research.

The code is working and outputting the right User ID but the SQL is not echoing the Table data, if you have some advice on where I might of gone wrong would be great.

?php
global $wpdb;
$uid = get_current_user_id();

$sql = $wpdb-prepare(SELECT * FROM  sr3_characters WHERE wp_id = $uid );
$results = $wpdb-get_results( $sql);
?
html
body
table border=1
tbody
tr
thName/th
thRace/th
thGender/th
thAge/th
/tr
tr
td?php foreach ($results as $value) { echo $value-char_namebr; }; ?/td
td?php foreach ($results as $value) { echo $value-char_racebr; }; ?/td
td?php foreach ($results as $value) { echo $value-char_genderbr; }; ?/td
td?php foreach ($results as $value) { echo $value-char_agebr; }; ?/td
/tr
/tbody
/table
/body
/html

Topic mysql php Wordpress

Category Web

bosco · Accepted Answer · 2022年5月4日 21:49

The evolution of this question makes it difficult to succinctly answer, but in brief summary:

Using $wpdb->prepare() to construct arbitrary SQL queries to execute against the database helps to mitigate injection vulnerabilities by escaping inserted variables. To use it, instead of concatenating or directly inserting variables into the query, use a placeholder for the appropriate type of data, then pass the variable which be inserted in that location in a respectively indexed array as the second argument.
If this character data is input by end-users, you should also consider sanitizing the inputs prior to inserting them into the database, and possibly escaping the values prior to displaying them on the front-end to further mitigate the risk of injection vulnerability and to prevent end users from displaying arbitrary HTML/JavaScript when their character data is displayed.
Queries executed via $wpdb->get_results() return an array of objects, with the properties on the objects being named after the columns in the SQL table. If you prefer to work with arrays instead (either associative or numerically indexed) you can change this behavior by passing one of the predefined constants in as the second argument.
When printing a non-primative data structure such as an object, PHP won't automatically serialize the object into a string for display. print_r() is capable of printing most common data structures, but var_dump() is a invaluable development aide when you're not sure of the type of the value.

All of that said, since each object in the array represents one row from your database table and since HTML tables are constructed one row at a time, you can simply use a single foreach loop to place the relevant values from each item into a cell:

<?php
global $wpdb;
$uid = get_current_user_id();

$sql = $wpdb->prepare( "SELECT * FROM `sr3_characters` WHERE `wp_id` = %s", array( $uid ) );
$results = $wpdb->get_results( $sql );
?>
<html>
<body>
  <table border="1">
    <thead>
      <tr>
        <th scope="col">Name</th>
        <th scope="col">Race</th>
        <th scope="col">Gender</th>
        <th scope="col">Age</th>
      </tr>
    </thead>
    <tbody>
      <?php foreach( $results as $row ) { ?>
      <tr>
        <th scope="row"><?php echo esc_html( $row->char_name ); ?></th>
        <td><?php echo esc_html( $row->char_race ); ?></td>
        <td><?php echo $row->char_gender; ?></td>
        <td><?php echo $row->char_age; ?></td>
      </tr>
      <?php } ?>
    </tbody>
  </table>
</body>
</html>

Digitalchild · Accepted Answer · 2022年4月25日 07:51

Without seeing the values of your custom table, wp_get_current_user() is probably returning the wrong user compared to what is in your table. Assuming this is running on the frontend, and you want to check the current user, it would be better to use get_current_user_id() and parse that to your query.

I would also suggest that you sanitise all your queries before putting them through get_results. See here https://codex.wordpress.org/Data_Validation#Database

Wordpress Custom SQL Table with UserID Filter for results

About