Jump to content


Mr-Chidi

Member Since 20 May 2008
Offline Last Active Jan 21 2016 10:04 AM

Topics I've Started

Getting wron result merging tables

18 January 2016 - 11:51 AM

Hello guys.

I’m having trouble understanding JOIN and GROUP.

I have two tables that I want to merge into one but when I try it, the result is not what is expected and I don’t know where I’m wrong. I want to merge Table A and B to get Table C as shown below:

 

Table A

Client Name        Username        Amount Deposited

John  Doe        joh@doe.com        2500

Julian Cram        jul@cram.com        2000

Peter Stalone        pet@sta.com        1200

Creig Davies        creg@davies.com        3000


Table B

Client Name        Username        Invoice Amount

John  Doe        joh@doe.com        1000

Julian Cram        jul@cram.com        500

Peter Stalone        pet@sta.com        4500

Creig Davies        creg@davies.com        1500


Table C

Client Name        Username        Invoice Amount        Amount Deposited

John  Doe        joh@doe.com        1000            2500

Julian Cram        jul@cram.com        500            2000

Peter Stalone        pet@sta.com        4500            1200

Creig Davies        creg@davies.com        1500            3000

 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
</tr>";
 
$stmt = $pdo->query("
SELECT d.firstname, d.surname, d.username, SUM(d.amt_deposited) AS sum_deposited
FROM ca_my_payments d GROUP BY d.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr><td>";
$client = ucwords($row['firstname'] . " " .$row['surname']);
echo $client;
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo $row['sum_deposited'];
echo "</tr></td>";
}
echo "</table>";
 
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
</tr>";
               
$stmt = $pdo->query("
SELECT p.payee, p.username, SUM(p.total_payment) AS total_invoice
FROM ca_processed p GROUP BY p.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</tr></td>";
}
echo "</table>";
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Current Balance</font></th>
</tr>";
                               
$stmt = $pdo->query("
SELECT a.payee, a.username, b.username, SUM(a.total_payment) AS total_invoice, SUM(b.amt_deposited) AS sum_deposited
FROM ca_processed a
LEFT JOIN ca_my_payments b ON a.username = b.username
GROUP BY a.username
");
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</td><td>";
echo number_format($row['sum_deposited'],2);
echo "</tr></td>";
}
echo "</table>";

Getting only last date of transaction with join

11 January 2016 - 03:44 AM

Hello all.
I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user.

I want to do a select statement that will display all the confirmed user with only the last of their transaction.
But so far all it does is replicate the user and their date of transaction and that is not what i want.

My intention is to get something like:

Firstname    Surname        Date Registered        Last Transaction
andrews        john        12-12-2014        10-10-2015
doe        andy        12-12-2010        12-12-2014

But i'm getting something like:

Firstname    Surname        Date Registered        Last Transaction
andrews        john        12-12-2014        10-10-2015
andrews        john        12-12-2014        10-11-2015
doe        andy        12-12-2010        12-12-2014
doe        andy        12-12-2010        01-12-2014
doe        andy        12-12-2010        12-12-2013

Thanks

$stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date");

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

echo $row['firstname'];
echo $row['surname'];
echo $row['regDate'];
echo $row['lastTrans'];

}

What could be the reason

13 October 2015 - 05:36 AM

Hi all.


I don't know why this is happening.
I have a scrip that backs up database. It works fine on database1 but when i use it on database2, it throws an error?
Fatal error: Call to a member function fetch_row() on a non-object in  $tableshema = $shema->fetch_row() ;
both databases are on same domain.

 

ps: even with another backup script still throws an error in database2

 

thanks

 

 
#####################
//CONFIGURATIONS
#####################
// Define the name of the backup directory
define('BACKUP_DIR', 'cashBackup' ) ;

// Define  Database Credentials
define('HOST', 'localhost' ) ;
define('USER', 'username' ) ;
define('PASSWORD', 'password' ) ;
define('DB_NAME', 'database2' ) ;
/*
Define the filename for the Archive
If you plan to upload the  file to Amazon's S3 service , use only lower-case letters .
Watever follows the "&" character should be kept as is , it designates a timestamp , which will be used by the script .
*/
$archiveName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'&'.microtime(true) . '.sql' ;
// Set execution time limit
if(function_exists('max_execution_time')) {
if( ini_get('max_execution_time') > 0 )  set_time_limit(0) ;
}
 
//END  OF  CONFIGURATIONS
 
/*
 Create backupDir (if it's not yet created ) , with proper permissions .
 Create a ".htaccess" file to restrict web-access
*/
if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ;
// Create an ".htaccess" file , it will restrict direct access to the backup-directory .
$content = 'deny from all' ;
$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
$written = $file->fwrite($content) ;
// Verify that ".htaccess" is written , if not , die the script
if($written <13) die("Could not create a \".htaccess\" file , Backup task canceled")  ;
// Check timestamp of the latest Archive . If older than 24Hour , Create a new Archive
$lastArchive = getNameOfLastArchieve(BACKUP_DIR)  ;
$timestampOfLatestArchive =  substr(ltrim((stristr($lastArchive , '&')) , '&') , 0 , -8)  ;
if (allowCreateNewArchive($timestampOfLatestArchive)) {
// Create a new Archive
createNewArchive($archiveName) ;
} else {
echo '<p>'.'Sorry the latest Backup is not older than 24Hours , try a few hours later' .'</p>' ;
}
 
###########################
// DEFINING  THE FOUR  FUNCTIONS
// 1) createNewArchive : Creates an archive of a Mysql database
// 2) getFileSizeUnit  : gets an integer value and returns a proper Unit (Bytes , KB , MB)
// 3) getNameOfLastArchieve : Scans the "BackupDir" and returns the name of last created Archive
// 4) allowCreateNewArchive : Compares two timestamps (Yesterday , lastArchive) . Returns "TRUE" , If the latest Archive is onlder than 24Hours .
###########################
// Function createNewArchive
function createNewArchive($archiveName){
$mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
if (mysqli_connect_errno())
{
   printf("Connect failed: %s", mysqli_connect_error());
   exit();
}
 // Introduction information
 
$return = "--\n";
$return .= "-- A Mysql Backup System \n";
$return .= "--\n";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
$return .= "--\n";
$return .= "-- Database : " . DB_NAME . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n";
$return .= "-- ---------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
$tables = array() ;
// Exploring what tables this database has
$result = $mysqli->query('SHOW TABLES' ) ;
// Cycle through "$result" and put content into an array
while ($row = $result->fetch_row())
{
$tables[] = $row[0] ;
}
// Cycle through each  table
 foreach($tables as $table)
 {
// Get content of each table
$result = $mysqli->query('SELECT * FROM '. $table) ;
// Get number of fields (columns) of each table
$num_fields = $mysqli->field_count  ;
// Add table information
$return .= "--\n" ;
$return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
$return .= "--\n" ;
$return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ;
// Get the table-shema
$shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
// Extract table shema
$tableshema = $shema->fetch_row() ;
// Append table-shema into code
$return.= $tableshema[1].";" . "\n\n" ;
// Cycle through each table-row
while($rowdata = $result->fetch_row())
{
// Prepare code that will insert data into table
$return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
// Extract data of each row
for($i=0; $i<$num_fields; $i++)
{
$return .= '"'.$rowdata[$i] . "\"," ;
 }
 // Let's remove the last comma
 $return = substr("$return", 0, -1) ;
 $return .= ");" ."\n" ;
 }
 $return .= "\n\n" ;
}
// Close the connection
$mysqli->close() ;
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ;
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ;
//$file = file_put_contents($archiveName , $return) ;
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$archiveName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen ){
$zip->addFromString( $archiveName , "$return" ) ;
    }
$zip->close() ;
$fileSize = getFileSizeUnit(filesize(BACKUP_DIR . "/". $archiveName . '.zip')) ;
$message = <<<msg
  <h4>BACKUP  completed ,</h4>
  <p>
  The backup file has the name of  : <strong>  $archiveName  </strong> and it's file-size is : $fileSize.
  </p>
  <p>
 This zip archive can't be accessed via a web browser , as it's stored into a protected directory.<br>
  It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the file .
  </p>
msg;
echo $message ;
} // End of function creatNewArchive
 
// Function to append proper Unit after a file-size .
function getFileSizeUnit($file_size){
switch (true) {
    case ($file_size/1024 < 1) :
        return intval($file_size ) ." Bytes" ;
        break;
    case ($file_size/1024 >= 1 && $file_size/(1024*1024) < 1)  :
        return round(($file_size/1024) , 2) ." KB" ;
        break;
    default:
    return round($file_size/(1024*1024) , 2) ." MB" ;
}
} // End of Function getFileSizeUnit
 
// Funciton getNameOfLastArchieve
function getNameOfLastArchieve($backupDir) {
$allArchieves = array()  ;
$iterator = new DirectoryIterator($backupDir) ;
foreach ($iterator as $fileInfo) {
   if (!$fileInfo->isDir() && $fileInfo->getExtension() === 'zip') {
        $allArchieves[] = $fileInfo->getFilename() ;
 
    }
}
    return  end($allArchieves) ;
} // End of Function getNameOfLastArchieve
 
// Function allowCreateNewArchive
function allowCreateNewArchive($timestampOfLatestArchive , $timestamp = 24) {
    $yesterday =  time() - $timestamp*3600 ;
    return ($yesterday >= $timestampOfLatestArchive) ? true : false ;
} // End of Function allowCreateNewArchive

cronjob wont send

08 October 2015 - 09:47 AM

Hi all.

 

I have a script that i want to run in a cronjob. but i dont know why it's not running.

i am using godaddy cpanel

 

ps: when i visit the url of the file, it works as expected, and also when i run it via a form button, it works also.

what could be the problem?

 

below is the command i used:

 

/usr/local/bin/php -q /home/username/directory/directory/file-name.php

 

thanks


sending mails to different users with different values from database

29 September 2015 - 07:07 AM

Hi all.

 

How can i send mail to multiple users at the same time with each user getting their own related data.

I'm using php mailer as an engine to send the mail.

Based on this, i'd have loved to setup a cron for it but i do not know how, so i figure i'd just do it manually before i get to know how to setup a cron job.

 

It's just to send a reminder to users and each user has a different subscription expiry time. I want each user to get their respective expiration date, expiry day etc.

 

Thanks

 

 
 if(isset($_POST['send_reminder'])){
    
$sql = "SELECT * FROM users WHERE status = '$status'";
$stmt = $pdo->query($sql);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$name = $row['name'];
$acct_no = $row['acct_no'];
$email_addresses = $row['email'];  
$expiry_date = $row['expiry_date'];
$expiry_day = $row['expiry_day'];

}
 
$message="Hello $name,<br>
<p>
This is to remind you that your subscription will expire in $expiry_day.
</p>
<p>
Details as follows:
Name: $name<br>
Account Number: $acct_no<br>
Email: $email_addresses<br>
Expire in days: $expiry_day<br>
Expiry Date: $expiry_date
</p>
<p>
Thank you
</p>
 
$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'mail.server.com';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@services.cap';                 // SMTP username
$mail->Password = 'password';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to

$mail->From = mails@services.cap';
$mail->FromName = 'Club 404';
$mail->addAddress($email_addresses);     // Add a recipient


$mail->WordWrap = 587;                                 // Set word wrap to 50 characters

$mail->AddEmbeddedImage("../img/logo.png", "my_logo");
$mail->isHTML(true);                                  // Set email format to HTML

$mail->Subject = 'REMINDER ON CLUB EXPIRY DATE';
$mail->Body    = $mess;
$mail->send();
 
}