Skipping empty rows on SQL update

  • Resolved evg.stanev

    (@evgstanev)


    8 years, 6 months ago

    Hello,

    I have custom edit.php page where you can edit some details for each product in the “inventory” database. I use form with inputs and placeholders to display current product details in each form field. Here is the current code which i use to collect the data and update the product info:

    include ('db.php');
 if (isset($_POST['submit']))
 {
 $prod_num = mysql_real_escape_string(htmlspecialchars($_POST['prod_num']));
 $label = mysql_real_escape_string(htmlspecialchars($_POST['label']));
 $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
 $description = mysql_real_escape_string(htmlspecialchars($_POST['description']));
 $note = mysql_real_escape_string(htmlspecialchars($_POST['note']));
 $category = mysql_real_escape_string(htmlspecialchars($_POST['category']));
 $public_url = mysql_real_escape_string(htmlspecialchars($_POST['public_url']));
 $date = date('Y-m-d H:i:s');
 global $mydb;
 $mydb->update("products", array(
   "prod_num" => $prod_num,
   "label" => $label,
   "stock" => $quantity,
   "description" => $description,
   "category" => $category,
   "note" => $note,
   "public_url" => $public_url,
   "modified" => $date,
),
   array( id => $edit_item)
);

    If you leave one or two fields empty the existing sql value for the same is automatically set to null. How can i skip all empty fields in the form and keep the existing (old) values in the database? I can add “if is not empty” check for each field:

    if(isset($_POST['label']) &&!empty($_POST['label']){

    then execute the update query, but that means 8 update queries …

    Please let me know if you have any better solution. Any help is appreciated.

    Thank you for your time!

Viewing 7 replies - 1 through 7 (of 7 total)
  • zota marius

    (@zota-marius)

    8 years, 6 months ago

    first you made a new variable.
    then you check all the fields, if they are not empty concatenate that variable. after that you can run the query . and will run only once.
    please chek the code for any php syntax error first!!! , i din`t test it.

    include ('db.php');
 if (isset($_POST['submit']))
 {
 $prod_num = mysql_real_escape_string(htmlspecialchars($_POST['prod_num']));
 $label = mysql_real_escape_string(htmlspecialchars($_POST['label']));
 $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
 $description = mysql_real_escape_string(htmlspecialchars($_POST['description']));
 $note = mysql_real_escape_string(htmlspecialchars($_POST['note']));
 $category = mysql_real_escape_string(htmlspecialchars($_POST['category']));
 $public_url = mysql_real_escape_string(htmlspecialchars($_POST['public_url']));
 $date = date('Y-m-d H:i:s');

$args='array(';

if (!empty($prod_num)) {
$args	 .= '"prod_num" => $prod_num,';
 }
 if (!empty($label)){
$args		 .= '"label" => $label,';
 }
 if (!empty($quantityl)){
$args		 .= '"stock" => $quantity,';
 }
  if (!empty($description)){
$args	 .= '"description" => $description,';
 }
  if (!empty($note)){
$args	 .= '  "note" => $note,';
 }
  if (!empty($categoryl)){
$args	 .= ' "category" => $category,';
 }
  if (!empty($public_url)){
$args .= '"public_url" => $public_url,';
 }
 if (!empty($date)){
$args .= '"modified" => $date,';
 }
$args .=')';

global $mydb;
 $mydb->update("products",$args ,
   array( id => $edit_item)
);

    Thread Starter evg.stanev

    (@evgstanev)

    8 years, 6 months ago

    Thanks for the fast and proper response, thats exactly what i’m looking for!!! It creates the correct array and the syntax is OK – if you edit only the description and quantity for example the array looks like:

    echo $args:

    array(“stock” => $quantity,”description” => $description,”modified” => $date,)

    the strange thing is that it does not update any data in the SQL table and it does not return any errors. I’ll look more closely to figure out where the problem is.

    Thanks once again!

    zota marius

    (@zota-marius)

    8 years, 6 months ago

    Sorry mate i was tired when i write the code for you, i look again at what i send and in the end of array $args is missing symbol , so he can be used in query.
    replace this $args .=')'; with $args .='),';

    and use it like this
    $mydb->update(“products”,$args
    array( id => $edit_item) )

    Maybe this will work for you.

    Thread Starter evg.stanev

    (@evgstanev)

    8 years, 6 months ago

    Yes i have noticed that “,” is missing in the end of $args array and i have added it, but unfortunately it does not change anything.

    Thanks for the help!

    Thread Starter evg.stanev

    (@evgstanev)

    8 years, 6 months ago

    I’m trying to get the last query with:

    exit( var_dump( $mydb->last_query ) );

    The result is ‘NULL’ Somehow i managed to print the query in another test enviroment , the array looks like:

    'array(\\"prod_num\\" => $prod_num,\\"modified\\" => $date,),'

    I’m not sure if the issue is caused by these slashes “\\” so i tried two additional functions to strip/remove them from the array:

    function stripallslashes($string) {
    while(strchr($string,'\\')) {
        $string = stripslashes($string);
    }
}
    function removeslashes($string)
{
    $string=implode("",explode("\\",$string));
    return stripslashes(trim($string));
}

    None of them seems to work or the issue is somewhere else.

    zota marius

    (@zota-marius)

    8 years, 6 months ago

    please check if you have any error messages. You can turn them on $wpdb->show_errors();

    Thread Starter evg.stanev

    (@evgstanev)

    8 years, 6 months ago

    Unfortunately im not able to find any errors. WP_DEBUG, SAVEQUERIES, WP_DEBUG_DISPLAY are set to “true” in wp-config. I’ve tried with:

    $mydb->show_errors();
 print_r($mydb->queries);
 print_r($mydb->num_queries);

    There is no output … only “0” which means zero executed queries. If i print $args the ouput is correct:

    array("prod_num" => $prod_num, "label" => $label, "description" => $description, "category" => $category, "modified" => $date, ),

    and if i place this output in the update query instead of $args, everything is fine. Kinda weird … anyway i’ll continue with the tests and i’ll post here if i find any solution 🙂

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Skipping empty rows on SQL update’ is closed to new replies.