problems with database update

  • Resolved beaglebreath

    (@beaglebreath)


    6 years, 1 month ago

    Hello,
    ( before we start… yes, I will prepare and sanitize my dynamic fields after I troubleshoot this problem.).

    I have been trying to use $wpdb->update and $wpdb->query(“with an update query here”) to update a custom table in a custom database. I have been able to get wpdb->insert to work, but update is not cooperating.

    The symptoms include; an update will update the field in the correct row, but it is also inserting (creating) a new row (with new unique primary field, but duplicate of all other data fields).

    I have tried typing my query with a variety of single quotes, double quotes, tics, etc etc. I can manually type the update query in MySQL Workbench and it executes as expected, but when I run the query from wordpress, I get this combination of insert and update.

    here is the latest version of my query. 

    $myupdate_customers_name = $formData['customers_name'];
 $mydb->query("	update
                        customers 
		set 
			customers_name = '$myupdate_customers_name'
		where 
			customers_id = '1584798494'
	");
Viewing 6 replies - 1 through 6 (of 6 total)
  • potentdevelopment

    (@potentdevelopment)

    6 years, 1 month ago

    Try this:

    
$mydb->query($mydb->prepare("UPDATE $table_name SET time='$current_timestamp' WHERE userid=$userid"));
    Thread Starter beaglebreath

    (@beaglebreath)

    6 years, 1 month ago

    I tried as you suggested above, and did not have any luck.

    I tried a few different ideas. I tried using $wpdb->update in the following ways; (the commented lines at the end are my failed attempts.)

    if($form->title = 'update_customers') {
		$myupdatetable = 'customers';
		$myupdatedata = array('customers_name'=>$formData['customers_name']);
		$myupdatewhere = array('customers_id'=>$formData['customers_id']);
		//$mydb->update($myupdatetable, $myupdatedata, $myupdatewhere, array('%s'), array('%s'));
		//$mydb->update($myupdatetable, $myupdatedata, $myupdatewhere);
		//$mydb->update($myupdatetable, $myupdatedata, array('customers_id'=>'1584798494'));
	}

    Each of these attempts creates a new record in my customers table, with a new customers_id (which is also the primary key for the table) as well as updating the customers table record with the correct customers_id.

    later I tried the following;
    $mydb->query($mydb->prepare("update customers set customers_name = $myupdate_customers_name where customers_id = $myupdate_customers_id"));
    but that also created a new record as well as updating.

    finally I got rid of the variables in the query and tried this;
    $mydb->query($mydb->prepare("update customers set customers_name = 'xxx' where customers_id = '1584798494'"));
    this query actually worked, but i need to concatenate variables into the query.

    • This reply was modified 6 years, 1 month ago by beaglebreath.
    Thread Starter beaglebreath

    (@beaglebreath)

    6 years, 1 month ago

    This update fiasco is inside a function in my functions.php file. at the beginning of the function, I have the following two lines. Am I using these correctly?

    	global $wpdb;
	$mydb = new wpdb('user','password1','calibration_data','192.168.1.100');

    all of my subsequent sql interactions are through $mydb.

    Moderator bcworkz

    (@bcworkz)

    6 years, 1 month ago

    You mean global $mydb; I presume. Yes, that’s fine. It must be or you wouldn’t be getting any result at all.

    I suggest looking at the context around which you make the query. It’s apparently running more than once and the variable values are apparently changed between calls enough that a new record is created. It’s natural to expect action hooks to fire once per request. Frequently that is not the case, even when multiple firings would be completely illogical. For action callbacks that do something where running only once is important, the callback should remove itself from the call stack to ensure it only runs once per request. I don’t even know if you are using hooks to execute your query, but it’s a common enough approach to assume it is.

    Thread Starter beaglebreath

    (@beaglebreath)

    6 years, 1 month ago

    uh no, actually I do have
    global $wpdb;
    I’ll change that and see what happens.

    uh yes, actually I am running this from an action hook and I do have a variety other queries around my update query which, if ran, would create new records. Also these insert functions are creating entries in related child tables which are also being generated from the same function.

    I think you are correct. My html page has several wpfluent forms on the same page. I have a single action hook function which gets run when any of the submit buttons on any of the forms are clicked. I tried to use an if{}elseif{}else to run only the appropriate query. But if all the forms on the same page are running through my function, (and I’m using the same variable names in each of the different queries) then all the queries for all the forms would be run. …and that would jive with the behavior in my database.

    Thread Starter beaglebreath

    (@beaglebreath)

    6 years, 1 month ago

    Thank you bcworkz and potentdevelopment for your help.

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘problems with database update’ is closed to new replies.