Jump to content


Mr-Chidi

Member Since 20 May 2008
Offline Last Active Today, 11:51 AM

Topics I've Started

Perform a join

06 April 2015 - 09:19 PM

Hi all.
I have two tables where the username is what they have in common. i want to perform a join for both tables but i'm having problems with mysql joins.

//to get the desire result individually i did

//table one
$stmt = $pdo->query("SELECT * FROM tableone WHERE username = '$_GET[id]'");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$credit_score = $row['credit_score'];
$acct_num =  $row['acct_num'];
$acct_name = ucwords($row['surname']) ." ". ucwords($row['firstname']);
$username = $row['username'];

if($credit_score ==3){
    $bill_limits = 2000;
}elseif($credit_score ==2){
    $bill_limits = 1000;
}elseif($credit_score ==1){
    $bill_limits = 500;
}

//table two
$stmt=$pdo->query("SELECT SUM(amt) as bill FROM tabletwo WHERE username = '$_GET[id]' AND relationship = 'PARENT'");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$bill = $row['bill'];
$service_charge_for_limits = '0.05' * $bill;
$tax_rate_for_limits = '0.13' * $service_charge_for_limits;
$bill_sum = $tax_rate_for_limits + $service_charge_for_limits + $bill;


Approved Bill Limits = $<?php echo number_format($bill_limits,2); ?>
<br>
Bill Limits Used = $<?php  echo number_format($bill_sum,2); ?>
<br>
<?php $available_limits = $bill_limits - $bill_sum; ?>
Bill Limits Available = $<?php echo number_format($available_limits,2); ?>

The above gives me the correct result, but now i have another page where i want to all the clients and their corresponding available limits, used limits and approve limits form table two and other information from table one

On the page i have

$stmt = $pdo->prepare("SELECT * FROM tableone WHERE status = 'COMPLETED' ORDER BY id DESC LIMIT $start, $limit");
    $stmt->execute();
    $num_rows = $stmt->rowCount();

echo "<table width='100%' class='table-responsive table-hover table-condensed table-striped'>";
echo "<tr>
    <th bgcolor='#444444' align='center'><font color='#fff'>Account Number</th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's Name</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Limits ($)</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>View Profile</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Delete Account</font></th>
    </tr>";
// keeps getting the next row until there are no more to get
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['acct_num'];
    echo "</td><td>";
    echo ucwords($row['surname']." ". $row['firstname']);
    echo "</td><td>";
    echo $row['username'];
    echo "</td><td>";
    $credit_score = $row['credit_score'];
    if($credit_score ==3){
    $bill_limits = 2000;
    }elseif($credit_score ==2){
    $bill_limits = 1000;
    }elseif($credit_score ==1){
    $bill_limits = 500;
    } echo number_format($bill_limits, 2);
    echo "</td><td>";
    echo "<a href='view-client-profile.php?id={$row['username']}'>view more</a>";
    echo "</td><td>";
    echo "<a href='delete-account.php?id={$row['username']}'>Delete Account</a>";
    echo "</td></tr>";

    //echo "</td><td>";
    //echo "<a href='settle.php?id={$row['acct_num']}'>Points</a>";

}
echo "</table>";

How ca i join tableone and tabletwo (plus sum)


What could be wrong

14 March 2015 - 12:14 AM

Hello all.

 

I am trying to get values from two tables but i am getting the wrong output regardless of the type of join i use.

The two query have some similar columns/rows. I want to fetch only the specified columns/rows with their unique data but my query is giving me on a column the value of just another column. For instance if a description column in  table A has a value of INVOICE and on table B the description is RECEIPT all my query show is either INVOICE or RECEIPT in all the columns regardless of whether the description is receipt or not and also i want all the fields that is not on the other table to be blank and not replicate columns eg. description. image refuse to show when attached and wouldn't allow me to use the image link on the editor, said i am not allowed.

SELECT table1.trans_ref, table1.description, table1.date_paid, table1.recurring, table1.amt, table1.bill_code, table2.trans_ref, table2.description, table2.amt_deposited, table2.deposit_date FROM table1 RIGHT JOIN table2 ON table1.username = table2.username

ps: would want amount and deposit amount to be on same column and also use a where username = username.

 

or maybe i am not doing the right thing? is it possible to a select on two tables without using a JOIN? so that you can echo only the needed values in a loop.

 

thanks


News Ticker from database

09 March 2015 - 02:50 AM

Hi all. how can I use the results from my database query as a list items for a news ticker?

thanks

my code:

 $stmt = $pdo->query("SELECT *
FROM ca_updates  ORDER BY date
DESC");
while($row = $stmt->fetch
(PDO::FETCH_ASSOC)) {
 echo '<div id="example">';
echo '<ul>';
echo '<li>'; 
    echo "<div class='update-
heading'>";
    echo "<h4><u>" . ucwords
($row['heading']) . "</u></h4>";
    echo "</div>";
    echo "<div class='update-
date'>" . $row['date'] . '</
div>';
    echo "<p class='update-
contents'>" . $row
['contents'] . "</p>";
echo "</li>";
echo "</ul>
echo "</div>

Add Admin Access to All Accounts

26 February 2015 - 10:52 AM

Hi all,

 

I am wondering how to get this to work or if it is possible. I have an application that was not built with the admin having access to all the users account but now i want it to have access to all accounts.

 

Thanks. (though i've not tried anything yet, just dont know how to start and i dont want to start afresh: advice)

 

My current login code is:

 

<?php
    
if(isset($_POST['login'])){

$username = stripslashes($_POST['username']);
$password = stripslashes($_POST['password']);


$stmt = $pdo->prepare("SELECT password FROM tablename WHERE username=:username");
$stmt->bindValue(':username', $username, PDO::PARAM_STR);
$stmt->execute();

if($stmt->rowCount()<1){

echo '<div class="signals"><p class="bg-warning text-center warning"><button type="button" class="close" aria-label="Close"><span aria-hidden="true">&times;</span></button>INVALID USERNAME OR PASSWORD</div></p>';

}else{

list($hash) = $stmt->fetch(PDO::FETCH_NUM);

if (password_verify($password, $hash)) {
//$_SESSION['username'] = $username;

$status1 = "COMPLETED";
$status2 = "PROCESSING";

//$stmt = $pdo->query("SELECT status FROM ca_confirmed WHERE username ='$_SESSION[username]'");
$stmt = $pdo->query("SELECT status FROM tablename WHERE username ='$username'");
$check = $stmt->fetch(PDO::FETCH_ASSOC);
$status = $check['status'];

$_SESSION['username'] = $username;
if(strcmp($status, $status1) == 0){

header("location: completed/index.php");
exit();
}elseif(strcmp($status, $status2) == 0){

header("location: uncompleted/index.php");    
//exit();
}
}else{
    
echo '<div class="signals"><p class="bg-warning text-center warning"><button type="button" class="close" aria-label="Close"><span aria-hidden="true">&times;</span></button>INVALID USERNAME OR PASSWORD again</div></p>';

}    
}
}
?>  

login into two different areas on one login form

20 February 2015 - 10:52 PM

Hi all.

I'm trying to get user login into two different areas on one login form based on a criteria. The problem I'm having is that if the correct passwords are provided, everything works fine but when a wrong password is provided, nothing happens, it doesn't even echo the password error alert! What could be wrong and is my code okay?

Thanks

if(isset($_POST['login'])){

$username=$_POST['username'];
$password=$_POST['password'];

$username = stripslashes($username);
$password = stripslashes($password);
$username = $username;
$password = $password;

//$pass = md5($password);

$stmt = $pdo->prepare("SELECT password FROM table WHERE username=:username");
$stmt->bindValue(':username', $username, PDO::PARAM_STR);
$stmt->execute();

if($stmt->rowCount()<1){

echo '<div class="signals"><p class="bg-warning text-center warning"><button type="button" class="close" aria-label="Close"><span aria-hidden="true">&times;</span></button>INVALID USERNAME OR PASSWORD</div></p>';

}else{
$password = $_POST['password'];
list($hash) = $stmt->fetch(PDO::FETCH_NUM);

if (password_verify($password, $hash)) {
$_SESSION['username'] = $username;

$status1 = "COMPLETED";
$status2 = "UNCOMPLETED";

$stmt = $pdo->query("SELECT status FROM table WHERE username ='$_SESSION[username]'");
$check = $stmt->fetch(PDO::FETCH_ASSOC);
$status = $check['status'];

if(strcmp($status, $status1) == 0){

header("location: completed/index.php");
exit();
}elseif(strcmp($status, $status2) == 0){

header("location: uncompleted/index.php");    
exit();
}else{
    
echo '<div class="signals"><p class="bg-warning text-center warning"><button type="button" class="close" aria-label="Close"><span aria-hidden="true">&times;</span></button>INVALID USERNAME OR PASSWORD again</div></p>';

}    
}
}
}

 

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com