Need Help with SQL UPDATE Form

I am enjoying working with CF but I am finding my knowledge lacking to take advantage of some of the more advanced features. I am ok with formatting valid and workable SQL statements but formatting them in such a way that PHP can understand is another story.

Here is my simple form in a nutshell:

- Text box to enter the percent markdown for ad manager classified ads and a Submit button.
- Using the USER_ID of the logged-in user, UPDATE the jos_adsmanager_ads table for the markdown amount
- I have attempted to structure the PHP using examples I have found
- I don't know the way to incorporate the value from the form into the SQL statement

Here's what I have so far:

<?php
$user =& JFactory::getUser();
$userId = $user->get( 'id' );
$db =& JFactory::getDBO();
$query = "

  UPDATE #__jos_adsmanager_ads
  SET ad_lastprice=ad_price
  WHERE userid=$userId AND ad_noadjust!=1;

  UPDATE #__jos_adsmanager_ads
  SET `ad_price`=ROUND(`ad_lastprice`*(100-<<NEED TO INSERT VALUE FROM FIELD TEXT1 IN CF FORM>>)/100) 
  WHERE `userid`=$userId AND `ad_noadjust`!='1'

  UPDATE `#__jos_adsmanager_ads`
    SET `ad_price` = `ad_lowestprice`
    WHERE `userid` = $userId
      AND `ad_noadjust` != '1'
      AND `ad_lowestprice`> `ad_price`;
  ";
$db->setQuery($query);
$db->query();
?>

Any help would be appreciated, thanks, Matt

HI mruetz,

The biggest problem there is tha tyou don't need both #__ and jos_ Joomla replaces #__ with the table prefix for the current site (so that you can have more than one site running off the same database schema.

<?php
$user =& JFactory::getUser();
$discount = JRequest::getInt('text_1', '', 'post');
$discount = (100 - $discount)/100;
//$userId = $user->id;
$db =& JFactory::getDBO();
$query = "
  UPDATE `#__adsmanager_ads`
    SET `ad_lastprice` = `ad_price`
    WHERE `userid`= '".$user->id."'
      AND `ad_noadjust` != 1 ;

  UPDATE `#__adsmanager_ads`
    SET `ad_price` = ROUND(`ad_lastprice` * $discount )
    WHERE `userid` = '".$user->id."' 
      AND `ad_noadjust` != 1 ;

  UPDATE `#__adsmanager_ads`
    SET `ad_price` = `ad_lowestprice`
    WHERE `userid` = '".$user->id."'
      AND `ad_noadjust` != 1
      AND `ad_lowestprice` > `ad_price`;
  ";
$db->setQuery($query);
$db->query();
?>

Bob

Hi Bob-

Thanks for your help and the fast response. The code still does not appear to be working.

I have debug ON and here's the output:

1. Form passed first SPAM check OK
2. Form passed the submissions limit (if enabled) OK
3. Form passed the Image verification (if enabled) OK
4. Form passed the server side validation (if enabled) OK
5. $_POST Array: Array ( [text_1] => 20 [755ee1ca7842b6a67097762843bbe9ad] => 1 )
6. $_FILES Array: Array ( )
7. Form passed the plugins step (if enabled) OK
8. An email has been SENT successfully from .... <addresses deleted>
9. Debug End

I tried placing the suggested code in both the BEFORE and AFTER e-mail options but it doesn't seem to be running the SQL statements.

IS there more debug info that I can provide to be more helpful? Is there a way to determine the errors of the code itself?

many thanks, Matt

Hi Matt,

First step is to turn the site Debug on in Global Configuration and see what SQL is actually being output. If it looks Ok to the eyeball then copy and paste it into PHPMyAdmin and see what errors show up.

Bob

Bob- Thanks for your help. Based on your suggestion I have figured out the problem. It appears that the 3 queries need to be separated, here is the final code that worked:

<?php
$user =& JFactory::getUser();
$discount = JRequest::getInt('text_1', '', 'post');
$discount = (100 - $discount)/100;
//$userId = $user->id;
$db =& JFactory::getDBO();
$query1 = "
  UPDATE `#__adsmanager_ads`
    SET `ad_lastprice` = `ad_price`
    WHERE `userid`= '".$user->id."'
      AND `ad_noadjust` != 1 ;
  ";
$query2 = "  
  UPDATE `#__adsmanager_ads`
    SET `ad_price` = ROUND(`ad_lastprice` * $discount)
    WHERE `userid` = '".$user->id."'
      AND `ad_noadjust` != 1 ;
  ";
$query3 = "   
  UPDATE `#__adsmanager_ads`
    SET `ad_price` = `ad_lowestprice`
    WHERE `userid` = '".$user->id."'
      AND `ad_noadjust` != 1
      AND `ad_lowestprice` > `ad_price`;
  ";
$db->setQuery($query1);
$db->query();
$db->setQuery($query2);
$db->query();
$db->setQuery($query3);
$db->query();
?>

Thanks again for the help and the great component....Matt