check query results values

[kal]

Hi guys

i have two tables a customer and package table. a customer can have many packages.

how i can i set a status in the customer table to 'complete' if all the packages in the package table that relate to that customer are set to 'paid'?

i can do a simple select statement but how will i know if all the status rows returned from packages are set to 'paid'.

any help would be great.

[Steve Mellor]

This seams a little clunky but it should work. There might be a better way of doing it though

I am assuming you have the two tables that you said and that each customer has a number (id) and each package references that in a field called "cust_id". Also, lets say that "paid" is a field in the packages which is set to 0 if it's not paid and 1 if it is.

PHP: Select all

<?php

    $customer = 1; // Set this to the customer's id number
    
    $fullyPaid = 1; // This variable will be either 0 or 1 depending on if they are fully paid up or not. 1 = fully paid up.

    // We connect to the database
    // Assuming the code: 
    // $Connection = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
    // will be stored in an included database connection file.
    
    mysql_select_db($dbName, $Connection); // variables to be stored in external file
    $query_package = "SELECT * FROM `package` WHERE `cust_id` = '".$customer."' ";
    $package = mysql_query($query_package, $dggb);
    $row_package = mysql_fetch_assoc($package);
    
    while($row_package = mysql_fetch_assoc($package)){ // Set up the loop
        if($row_package['paid'] == 0){ // if any of the packages have not been paid up...
            $fullyPaid = 0; // ... set the variable so show this
        }
    }

    // Then you can use an IF statement to test wether they have paid in full or not.
    // Obviousy you can use SQL statements instead of the echos.
    
    if($fullyPaid == 1){
        echo 'All paid up.';
    }else{
        echo 'Call in the bailiffs, money is owing.';
    }

?>

[Steve Mellor]

Ok, this is slightly cleaner. It removed the need for the loop.

PHP: Select all

<?php

    $customer = 1; // Set this to the customer's id number

    // We connect to the database
    // as before but with a slight change
    
    mysql_select_db($dbName, $Connection); // variables to be stored in external file
    $query_package = "SELECT * FROM `package` WHERE `cust_id` = '".$customer."' AND `paid` = '0' ";
    $package = mysql_query($query_package, $dggb);
    $totalRows_package = mysql_num_rows($package);

    // The SQL pulls out all instances of a package that the customer has not paid for.
    // Then you can use an IF statement to test wether $totalRows_package is higher than 0.
    
    if($totalRows_package > 0){
        echo 'All paid up.';
    }else{
        echo 'Call in the bailiffs, money is owing.';
    }

?>

[masonbarge]

Originally Posted by Steve Mellor

Ok, this is slightly cleaner. It removed the need for the loop.
<?php

$customer = 1; // Set this to the customer's id number

$query_package = "SELECT * FROM `package` WHERE `cust_id` = '".$customer."' AND `paid` = '0' ";
?>

I see this format for SELECT queries using php variables a lot. You can do a simpler, cleaner format once you set the value of $customer:

Code: Select all

 $customer = 1; 
$query_package = "SELECT * FROM package WHERE cust_id = '$customer' AND paid = '0' ";

You can also drop the ` marks. (I'm not following the actual code -- this is just a formatting tip. Also, if 0 was a number, it wouldn't need to be in quotes. I'm not sure about Booleans.)

It may seem counterintuitive to bracket a php variable with single quotes, but doing this in a php/mysql query will output the value of the variable inside literal single quote marks, just as you want it to.

This isn't limited to mysql queries. This code

Code: Select all

  $x = 'Steve Mellor';
    echo "I would like to give thanks to '$x' for his help";

Will output

Code: Select all

I would like to give thanks to 'Steve Mellor' for his help.

This avoids the cringe-inducing format

Code: Select all

echo 'I would like to give thanks to ' . $x . 'for his help.

which can get downright Byzantine in long sections full of variables.

As long as I'm posting on this, I have never liked the unnecessary unscrambling needed for concatenating variables into text sentences. IMO it's far better to enclose echo in double quotes whenever a variable is going to appear.

The only perceived difficulty arises when you have an echo of a html expression where you need a lot of internal tag parameters, such as a form insert or img. You might want to enclose the echo in single quotes, because you want to avoid a great mass of escaped double quotes.

The solution is actually simple once you think to try it. Single quotes work just fine for tag parameters, whether textual or variable.

Code: Select all

echo "<img src='$img' height='118' width='$img_width' 
alt='Naughty photograph of Margaret Thatcher' />";

There is a slight difference in parse/computation speed for echo using single and double quotes, but it's a matter of microseconds in the example given. Or as my lawyer would say, de minimis (usually in reference to his fee).

Understanding these tricks for echo "..."; will save the coder a fair amount of time and trouble in coding, and the resulting code will be a lot easier to read.

Well, as long as I'm editing, here's another trick some people might not know. You can close and reopen php as often as you want, right in the middle of an "if" clause, and the php will work just fine. Simple example:

Code: Select all

<?php
if ($x==8) {
?><li> Badges?  We don't need no stinkin' badges</li>
<a href="./images/baddude.jpg>Want to see who wrote the "stinkin' badges"
 quotes?</a>
<?php  } else {
rsort($commies);
$ladynot = $commies[$i];
?> <p>We are not obligated to 
<?php if ($ladynot!='potpourri') echo "forget '$ladynot' "; ?>
do anything we don't want to! " ' ''  """."
<div><table class="dinnertable"> 
blah blah blah
</table></div>

<?php
}
?>

The example is nutty, but this method can be very helpful, such as in complex form handlers that occur almost entirely inside an "if" statement -- it enables you to have a huge body of plain, easy-to-read html inside an "if" construct (rather than figuring out 50 lines of echoes and/or prints) closed by the line <?php } ?>

heredoc is another simplifying method for long masses of echoes/prints, but the syntax can get tricky cross-browser.

[kal]

thanks for your help guys, all sorted. much appreciated.