-5

I need to update a MySql database from inside a JS function within a WordPress Woocommerce page. I'm using Ajax to do this. The following code works fine as a stand-alone code but when I put it into the WordPress page (on my localhost) it throws an error 500. Also I put the required data (which will eventually be a variable) onto the end of the url (?test=14230) because I couldn't get it to send the data when using the data: line in the Ajax.

Here's the Ajax:

function db()
{
   
         $.ajax({
      url: 'update_db.php?test=14230',
      type: 'post',
      data: 0,
      success: function(output) 
      {
          alert('Success, server says '+output);
      }, error: function()
      {
          alert('Something went wrong.');
      }
   });
   
}

Here's the update_db.php:

<?php
if(isset($_GET['test']) ){
$id = $_GET['test'];
}
        
    include 'database-handler.php';
    
    $sql = "UPDATE db_name SET column = 'Some Value' WHERE id = $id";

    if(mysqli_query($conn, $sql)){
        //echo ('<p>'."Success.".'</p>');
    } else {
        //echo ('<p>'."Something went wrong. $sql. " . mysqli_error($conn).'</p>');
    }
    
    mysqli_close($conn);  
?>

So I'm just wondering why this works as a stand-alone code but not when it's inside WordPress?

Edit: Here is the error log:

[Wed Nov 09 15:16:47.543162 2022] [php:error] [pid 4564:tid 1828] [client ::1:5888] PHP Fatal error: Uncaught TypeError: mysqli_query(): Argument #1 ($mysql) must be of type mysqli, null given in C:\xampp\htdocs\my-sites\wowcard\wp-content\themes\blocksy-child\woocommerce\single-product\save-card-size.php:17\nStack trace:\n#0 C:\xampp\htdocs\my-sites\wowcard\wp-content\themes\blocksy-child\woocommerce\single-product\save-card-size.php(17): mysqli_query(NULL, 'UPDATE new_card...')\n#1 {main}\n thrown in C:\xampp\htdocs\my-sites\wowcard\wp-content\themes\blocksy-child\woocommerce\single-product\save-card-size.php on line 17, referer: http://localhost/my-sites/wowcard/product/polka-dot-brush-strokes-two-photo-birthday-card-purple/?card=complete&id=14230

Simon
  • 1
  • 1
  • 3
  • A 500 error is a generic error message and covers pretty much every single thing that can go wrong with a PHP script. Check your server error logs to find out the exact error message. – aynber Nov 09 '22 at 14:22
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Nov 09 '22 at 14:42
  • Thank you for the warning re SQL Injections. I will look into the solution you offered. – Simon Nov 09 '22 at 15:14
  • I have now added the error log to my original question. – Simon Nov 09 '22 at 15:24
  • `Argument #1 ($mysql) must be of type mysqli, null given` means that `$conn` failed. You'll need to double-check your database connection in `database-handler.php`, and make sure that the credentials match your live DB credentials. – aynber Nov 09 '22 at 15:37
  • Thank you for heading me in the right direction. I replaced the include with the contents of database-handler.php and it worked! I thought I had already tried that but obviously I hadn't. Not sure why it won't work with the include but as long as it works I'm happy. – Simon Nov 09 '22 at 15:46

2 Answers2

0

Edit:

I believe the issue I had was due to the Ajax being embedded within more than one WordPress function and also a Woocommerce action. The variable I needed was not being passed from my PHP to the Ajax like it normally would be. I used a session variable instead, which fixed that particular issue.

The Ajax now uses the "data:" line to pass the data over to save-card-size.php rather than passing it inside the url. I would show the code but the editor is too glitchy on this site, it wouldn't allow me to copy and paste my code properly.

I also had to replace the include in save-card-size.php with the contents of database-handler.php.

Everything is now working.

Simon
  • 1
  • 1
  • 3
-2

WordPress has its own database handler which is automatically loaded, there are most likely conflicts between your code and WordPress, but without seeing the actual error, I can not give more information.

Check this out https://developer.wordpress.org/reference/classes/wpdb/

Example:

global $wpdb;
$results = $wpdb->query($wpdb->prepare( 'UPDATE db_name SET column = 'Some Value' WHERE id = %d' , $id ));
Richard Leishman
  • 71
  • 4