CODEWITHSUNDEEP
phpmysqlsql
php_javascript_html_dev
php_javascript_html_dev

Reputation: 396

Check if row exists in MYSQL

i would like to display different messages on php page, based on value in mysql table row

    if (Select * from subscription_details where student id=session id of student) count = 0

    message: Please sign-up for a subscription package

    else if 

(select * from subscription_details where ((student id = session_id of student) count > 0)  AND ((status=active) count < 1)

    message: Your subscription has expired, please renew subscription

need help with writing the SQL in these scenarios.

Thanks in advance,

Upvotes: 0

Views: 331

Answers (6)

no.
no.

Reputation: 2376

Could you not do something along the lines of the following?

$query = mysql_query("SELECT * FROM `subscription_details` WHERE (`student_id` = '$session_id_of_student' AND `status` = 'active')") or die(mysql_error());

$amount = mysql_num_rows($query);

if ($amount == 0) {

    // Do something

} else if ($amount > 0 ) {

    // Do something else

}

Upvotes: 0

Johan
Johan

Reputation: 76753

$student_id = mysql_real_escape_string($_SESSION['id']);
$query = "SELECT 
            count(*) as NumberOfSubcriptions
            count(s2.id) as NumberActive
          FROM subsciption_details s1
          LEFT JOIN subsciption_details s2 ON (s1.id = s2.id)
          WHERE s1.student_id = '$student_id' 
            AND s2.active = 'active' ";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
if ($row['NumberOfSubcriptions'] >= 1) {.....}
else {}
if ($row['NumberActive'] >= 1) {do stuff with active subscriptions} 
else {....

Upvotes: 1

Alienz
Alienz

Reputation: 118

Maybe something like this?

    $student = mysql_query("Select * from subscription_details where student_id=$session_id;");

    $status = mysql_query("select status from subscription_details where student id = session_id AND status=active");

   if (count(mysql_fetch_row($student)))
          print "message: Please sign-up for a subscription package";

   else if (count(mysql_fetch_row($status)))
          print "message: Your subscription has expired, please renew subscription";

Upvotes: 1

mellamokb
mellamokb

Reputation: 56779

get in a single query and read the two variables out:

select
    case when not exists (
        select * from subscription_details sd1
        where sd1.student_id = @session_id
    ) then 1 else 0 end as needsSignup,

    case when not exists (
        select * from subscription_details sd2
        where sd2.student_id = @session_id
        and status = 'active'
    ) then 1 else 0 end as isExpired

Demo: http://sqlize.com/n0amP9Uxb5

PHP code:

// connect to db and run query above
// read first row into $needsSignup and $isExpired.

if ($needsSignup)
{
    // signup code
}
else if ($isExpired)
{
    // expired code
}

Upvotes: 2

Andrej
Andrej

Reputation: 7504

$pdo = new PDO(...);

$result = $pdo->query("Select status from subscription_details 
                       where student_id=session_id limit 1")->fetch();

if (empty($result)) {
    echo "message: Please sign-up for a subscription package";
} else if ($result['status'] != 'active') {
    echo "message: Your subscription has expired, please renew subscription";
}

Upvotes: 1

p.campbell
p.campbell

Reputation: 100647

To detect for sign-up:

SELECT COUNT(*) AS NumSubscriptions
FROM subscription_details 
WHERE student_id=123;

To detect for expiry

SELECT COUNT(*) AS NumActive
FROM subscription_details 
WHERE student id = 123
AND status=inactive;

Upvotes: 0

Related Questions