CODEWITHSUNDEEP
phpmysqlsql
scotho3
scotho3

Reputation: 123

if statement to check if mysql row exists, then selecting next query

I'm attempting to select a query to use based on the number of rows returned by a test result. 

    $id = mysql_real_escape_string(htmlspecialchars($_POST['id']));

    $result = "SELECT FROM Notifications WHERE UserID=$id";
    $r = e_mysql_query($result);
    $row = mysql_fetch_array($r);
    $num_results = mysql_num_rows($result); 
    $result = '';

    if ($num_results != 0) {
        $result =
            "SELECT U.UserID,U.FirstName,U.LastName, " .
            " DATE_FORMAT(U.BirthDate,'%m-%d-%Y') AS BirthDate, " .
            " N.Email, N.Phone,N.ProviderName, N.SubNotifications " .
            " FROM Users U, Notifications N " .
            " WHERE U.LocationID=0 " .
            " AND N.UserID='$id'";
    } else  {
        $result =
            "SELECT UserID, FirstName, LastName," .
            " DATE_FORMAT(BirthDate, '%m-%d-%Y') AS BirthDate " .
            " FROM Users " .
            " WHERE LocationID = 0 " .
            " AND UserID ='$id'";

    }
    echo $result;
    e_mysql_result($result); //Bastardized/homegrown PDO

    if ($row = mysql_fetch_assoc($result)) {
        $retValue['userInfo'] = $row;
    ...

I'm checking the Notifications table to see if the UserID exists there, if it doesn't it loads what does exist from the Users table, if it does, then it loads everything from the Notifications table.

I'm echoing out the $result and the proper statement is loaded, but it doesn't execute. When I run the concatenated query I get from the PHP preview, it returns just fine.

Before I had to if/else this, I was running the first query, loading everything from the Notifications table, and it was loading just fine. What am I missing?

Upvotes: 1

Views: 860

Answers (2)

sn00k4h
sn00k4h

Reputation: 443

You can do the whole thing with one query with a LEFT JOIN. 

$query= "SELECT U.UserID, U.FirstName,U.LastName,  " .
    " DATE_FORMAT(U.BirthDate,'%m-%d-%Y') AS BirthDate,  " .
    " N.Email, N.Phone,N.ProviderName, N.SubNotifications  " .
    " FROM Users U  " .
    " LEFT JOIN Notifications N  " .
    " ON U.UserID = N.UserID  " .
    " WHERE U.UserID = '$id'";

Upvotes: 2

Rakesh Sharma
Rakesh Sharma

Reputation: 13728

You are missing execute a query with mysql_query() on all $result

Also change (query variable should be quoted) so change your all variables $id quoted

$result = "SELECT FROM Notifications WHERE UserID=$id";

to

$result = "SELECT FROM Notifications WHERE UserID='$id'";
$r = mysql_query($result);

Note :- mysql_* has been deprecated use mysqli_* or PDO

Upvotes: 1

Related Questions