Page 1 of 1

sql update

Posted: Sat Nov 15, 2008 4:45 pm
by MMalvin
For future reference, when you post PHP code, please enclose it in appropriate Code tags, as I have done for you below. It makes it much easier for others to read. Thank you.

I'm pretty much an amature at PHP trying to help a friend. Can you look through this script and find why the information entered is not being saved to the sql. I believe I need an sql update but my friend who is a better coder than I said no.

Thanks.
Max

Code: Select all

 
<?php
 
  include('./includes/load_database.php');
  connect_mysql();
  
 $userid = $_GET['uid'];
 
 $sql = "SELECT * FROM `cj_orders` WHERE `sid` LIKE 'zgnw\_%\_$userid\_%\_%'";
    $result = mysql_query($sql);
      $i = 0;
      while($records = @mysql_fetch_array($result)){
       $user_orders["$i"]['rate'] = $records['commissionAmount'];
       $user_orders["$i"]['store'] = ucwords($records['advertiserName']);
       $user_orders["$i"]['date'] = substr($records['eventDate'], 0, 10);
       $user_orders["$i"]['sale'] = '$'.number_format($records['saleAmount'], 2);
       $user_orders["$i"]['sid'] = $records['sid'];
       $user_orders["$i"]['zpoints'] = $records['zpoints'];
       $user_orders["$i"]['status'] = $records['status'];
       $user_orders["$i"]['type'] = 'CJ';
       $i++;
      }
 
    $sql = "SELECT * FROM `pm_orders` WHERE `sid` LIKE 'zgnw\_%\_$userid\_%\_%'";
    $result = mysql_query($sql);
      while($records = @mysql_fetch_array($result)){
       $user_orders["$i"]['rate'] = $records['commissionAmount'];
       $user_orders["$i"]['store'] = ucwords($records['adname']);
       $user_orders["$i"]['date'] = $records['date'];
       $user_orders["$i"]['sale'] = '$'.number_format($records['saleAmount'], 2);
       $user_orders["$i"]['sid'] = $records['sid'];
       $user_orders["$i"]['zpoints'] = $records['zpoints'];
       $user_orders["$i"]['status'] = $records['status'];
       $user_orders["$i"]['type'] = 'PM';
       $i++;
      }
 
    $sql = "SELECT * FROM `revu_orders` WHERE `sid` LIKE 'zgnw\_%\_$userid\_%\_%'";
    $result = mysql_query($sql);
      while($records = @mysql_fetch_array($result)){
       $user_orders["$i"]['rate'] = $records['rate'];
       $user_orders["$i"]['store'] = ucwords($records['name']);
       $user_orders["$i"]['date'] = substr($records['date'], 0, 10);
       $user_orders["$i"]['sale'] = 'Offer'; //number_format($records['saleAmount'], 2);
       $user_orders["$i"]['sid'] = $records['sid'];
       $user_orders["$i"]['zpoints'] = $records['zpoints'];
       $user_orders["$i"]['status'] = $records['status'];
       $user_orders["$i"]['type'] = 'RU';
       $i++;
      }
 
    $sql = "SELECT * FROM `zogo_credits` WHERE `sid` LIKE 'zgnw\_%\_$userid\_%\_%'";
    $result = mysql_query($sql);
      while($records = @mysql_fetch_array($result)){
      $user_orders["$i"]['rate'] = $records['commission'];
       $user_orders["$i"]['store'] = ucwords($records['name']);
       $user_orders["$i"]['date'] = substr($records['date'], 0, 10);
       $user_orders["$i"]['sale'] = 'Credit';
       $user_orders["$i"]['sid'] = $records['sid'];
       $user_orders["$i"]['zpoints'] = $records['zpoints'];
       $user_orders["$i"]['status'] = $records['status'];
       $user_orders["$i"]['type'] = 'ZP';
       $i++;
      }
 
      $sortArr = array();   
       foreach ($user_orders as $key => $value ){
           $sortArr[ $key ] = $value['date'];
       }
      arsort( $sortArr );
       $resultArr = array();
   
 
echo '<form action="?page=userdetails&uid='.$userid.'" method="post">';
echo '<table border="0" width="95%" class="tdstyle1">';
   echo '<tr><td class="datum1"><b>Store:</b></td><td class="datum1"><b>Sale:</b></td><td class="datum1"><b>Commission:</b></td><td class="datum1"><b>Date:</b></td><td class="datum1"><b>Points:</b></td></tr>';
   if(count($user_orders)){
     $i = 0;
     $commission = 0;
     foreach ($user_orders as $key => $value ){
      echo '<tr>';
      echo '<td class="datum1"><input type="text" value="'.$user_orders[$key]['store'].'"></td>';
      echo '<td class="datum1"><input type="text" value="'.$user_orders[$key]['sale'].'"></td>';
      echo '<td class="datum1"><input type="text" value="$'.number_format($user_orders[$key]['rate'],2).'"></td>';
      echo '<td class="datum1">'.$user_orders[$key]['date'].'</td>';
      echo '<td class="datum1"><span class="clgreen"><b><input type="text" value="'.$user_orders[$key]['zpoints'].'"></b></span></td>';
      echo '</tr>';
      $commission += $user_orders[$key]['rate'];
      $i++;
     }
     echo '<tr><td colspan="4" class="datum1"><b>Total Commission: $'.number_format($commission, 2).'</b></td></tr>';
}else{
      echo '<tr><td colspan="4" class="datum1">No recent activity.</td></tr>';
   }
   
   echo '<tr><td colspan="2">'.'<input type="submit" value="Save Details" name="savedetails">'.'</td></tr>';
 
   echo '</table>';
   echo '<input type="hidden" name="func" value="savedetails">';
   echo '<input type="hidden" name="userid" value="'.$userid.'">';
   echo '</form>';
 
   disconnect_mysql();
 
?>
 

Re: sql update

Posted: Sat Nov 15, 2008 7:59 pm
by califdon
So you are searching for records where the 'sid' field contains the userid surrounded by special characters, so if the userid is 1234, you would search for a sid like

Code: Select all

'zgnw_%_1234_%_%'
where the %s are wildcards? Is that what you are trying to do? That looks pretty strange. Wouldn't it work as well to make it just 'zgnw%1234%' ?

Re: sql update

Posted: Sun Nov 16, 2008 8:35 am
by Bill H
...information entered is not being saved to the sql.
You suggest you need a sql update and your friend, whom you consider a better coder, says no.

Consider the meaning of "SELECT * FROM" and "UPDATE ... WHERE" if you will. How is the former going to "save to" the database?

Yes, you need an update.

Re: sql update

Posted: Sun Nov 16, 2008 12:12 pm
by MMalvin
Okay. Should I change the beginning of the code from SELECT to UPDATE? Also how to I tie the input in the form to the information that gets entered into the database. Sorry I am a bit new to PHP. An example of the update function pertaining to the script would be greatly greatly appreciated. :-) Thanks for all the help everyone!

-Max

Re: sql update

Posted: Sun Nov 16, 2008 12:27 pm
by califdon
No, it's far more complicated than changing a few words. And that's not a PHP function, it's SQL (Structured Query Language). And your other questions are pretty much, "How do I write an advanced script in a language I haven't learned?" If you seriously want to learn PHP, start with a very basic tutorial that teaches you how to build up from simple syntax to more complicated actions like you want to do. I would suggest starting with the PHP and SQL tutorials at http://w3schools.com.