[SOLVED] Need help with simple if/then...

[acook]

Hello all,

 

I have a PHP script that will query an ODBC DB and return the results in a simple table.  What I would like to do is, if there are no results, return a message "No results".  My traditional way of doing this in mySQL is not working.  Here is my code:

 

<?php



//NOT NEEDED BECAUSE WINDOWS ODBC IS HANDLING IT

$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');

if ($conn <= 0) {
echo "Error in connection<BR>";
        exit;
        };

//SETTING OF TODAY AND TOMORROW VARIABLES AS WELL AS TIMEZONE

date_default_timezone_set("America/Phoenix");
$fourdaysago = date("Y-m-d",mktime(0,0,0,date("m"), date("d")-4, date("Y")));
$threedaysago = date("Y-m-d",mktime(0,0,0,date("m"), date("d")-3, date("Y")));
$twodaysago = date("Y-m-d",mktime(0,0,0,date("m"), date("d")-2, date("Y")));
$yesterday = date("Y-m-d",mktime(0,0,0,date("m"), date("d")-1, date("Y")));
$today = date("Y-m-d");
$tomorrow = date("Y-m-d",mktime(0,0,0,date("m"), date("d")+1, date("Y")));
$twodaysfromnow = date("Y-m-d",mktime(0,0,0,date("m"), date("d")+2, date("Y")));
$threedaysfromnow = date("Y-m-d",mktime(0,0,0,date("m"), date("d")+3, date("Y")));
$fourdaysfromnow = date("Y-m-d",mktime(0,0,0,date("m"), date("d")+4, date("Y")));

//THE QUERY


$query = "SELECT Account_s__Affected, Assignee_Group, Assignee_Name__, Assignee_Phone, Change_Type, Change_Id, End_Date_and_Time,  Short_Description, Start_Date_and_Time, Status, Risk_Level FROM Change_Management WHERE (Start_Date_and_Time>={ts '$today 00:00:00'} AND Start_Date_and_Time<={ts '$tomorrow 00:00:00'})";



//THE EXECUTION

$result = odbc_exec($conn, $query) or die("Query failed, could not connect to table.  Are you sure it exists?");



?>


<table style="border-collapse: collapse" border=".5em" bordercolor="#000000" cellspacing="1" cellpadding="0">
        <tr>
            <td><font face="Tahoma" size="1"><b>Change ID</td>
            <td><font face="Tahoma" size="1"><b>Start Date and Time</td>
            <td><font face="Tahoma" size="1"><b>Assignee Name</td>
            <td><font face="Tahoma" size="1"><b>Assignee Phone</td>
            <td><font face="Tahoma" size="1"><b>Assignee Group</td>
  <td><font face="Tahoma" size="1"><b>Change Type</td>
  <td><font face="Tahoma" size="1"><b>Risk Level</td>
  <td><font face="Tahoma" size="1"><b>Status</td>
  <td><font face="Tahoma" size="1"><b>End Date and Time</td>
  <td><font face="Tahoma" size="1"><b>Short Description</td>
        </tr>

<?php


//THE LOOP


while(odbc_fetch_row($result))
{
  $actaffected=odbc_result($result, 1);
  $assigneegrp=odbc_result($result, 2);
  $assigneename=odbc_result($result, 3);
  $assigneephone=odbc_result($result, 4);
  $changetype=odbc_result($result, 5);
  $changeid=odbc_result($result, 6);
  $enddate=odbc_result($result, 7);
  $shortdes=odbc_result($result, ;
  $startdate=odbc_result($result, 9);
  $status=odbc_result($result, 10);
  $risklvl=odbc_result($result, 11);

//PRINTING THE RESULT

  print("<tr><td width=80><font face=Tahoma size=1>$changeid</td><td width=63><font face=Tahoma size=1>$startdate</td><td width=90><font face=Tahoma size=1>$assigneename</td><td width=90><font face=Tahoma size=1>$assigneephone</td><td width=90><font face=Tahoma size=1>$assigneegrp</td><td width=90><font face=Tahoma size=1>$changetype</td><td width=70><font face=Tahoma size=1>$risklvl</td><td width=80><font face=Tahoma size=1>$status</td><td width=65><font face=Tahoma size=1>$enddate</td><td width=200><font face=Tahoma size=1>$shortdes</td></tr>");
  
  


}


//CLOSING THE CONNECTION


odbc_close($conn);
?>
</table>

 

I'm sure it's simple, I just can't get it... any help would be appriciated.  Thank you.

[akitchin]

try using odbc_num_rows(), since you're running the query anyway (rather than just selecting the whole table just to see if there are any entries).

 

if (odbc_num_rows($result) == 0)
{
  exit('No rows returned from query.');
}

 

toss that in just above your table output.

[acook]

Hmm,

 

It seems that I just get the normal blank table output.  I would also like to mention that I am connectiong to a Sybase DB and I've thought I read somewhere that it counts the first row as -1 or something?  Any other ideas?

[akitchin]

good point.  read in the comments section of the online PHP manual for odbc_num_rows() for some sample solutions.  it seems most people will simply use odbc_fetch_into() and check whether it returns anything.  alternatively, you could make a dummy while() loop to check whether anything's in the resultset, although this is far from an ideal solution:

 

$RESULTS_FOUND = 0;
while (odbc_fetch_row($result))
{
  $RESULTS_FOUND = 1;
  break;
}

if ($RESULTS_FOUND == 0)
{
  exit('No results found.');
}

[acook]

I was trying to do something similar but was having a hard time implementing it into my code.  Could I add this to my already existing while statement?  Something like:

 

while(odbc_fetch_row($result))
{
  $results_found = 1;
{
  $actaffected=odbc_result($result, 1);
  $assigneegrp=odbc_result($result, 2);
  $assigneename=odbc_result($result, 3);
  $assigneephone=odbc_result($result, 4);
  $changetype=odbc_result($result, 5);
  $changeid=odbc_result($result, 6);
  $enddate=odbc_result($result, 7);
  $shortdes=odbc_result($result, ;
  $startdate=odbc_result($result, 9);
  $status=odbc_result($result, 10);
  $risklvl=odbc_result($result, 11);

}
if (results_found== 0)
{
exit('No results.');
}

print("......");

}

[akitchin]

the problem with trying to incorporate it into your current loop is that you've already echoed the table headers by then.  you COULD move the while() loop to above the table and store all your results within an array.  then you'd check if the array was empty and exit with a "no results found" message if it is, or echo the table headers then process through the array to echo the results.  this seems like a bit of a waste of resources however, as you have to first process through the resultset, then process through the array.

[acook]

Ok, I think I've got it... except, how could I store my results in an array?  Sorry, I'm still a bit new to PHP. 

[akitchin]

no problem, everyone starts somewhere.  try this:

 

$results_array = array();
while(odbc_fetch_row($result))
{
  $results_array[] = array(
    'actaffected' => odbc_result($result, 1),
    'assigneegrp' => odbc_result($result, 2),
    'assigneename' => odbc_result($result, 3),
    'assigneephone' => odbc_result($result, 4),
    'changetype' => odbc_result($result, 5),
    'changeid' => odbc_result($result, 6),
    'enddate' => odbc_result($result, 7),
    'shortdes' => odbc_result($result, ,
    'startdate => odbc_result($result, 9),
    'status => odbc_result($result, 10),
    'risklvl => odbc_result($result, 11)
  );
}

if (empty($results_array))
{
  exit('No rows.');
}
else
{
  // echo the headers.
  // process through the array
  foreach($results_array AS $this_row)
  {
    echo "<tr><td width=80><font face=Tahoma size=1>{$this_row['changeid']}</td>"; // etc.
  }
}

 

what it does is assigns $results_array to an empty array to start (it's always good to initialize your variables).  then, for each row found, it plugs those values as an array into the bigger $results_array array.  essentially what you end up with at the end is an array which contains all the rows found; each of the rows is one array within $results_array containing all the row's values.

 

for more about arrays (especially multi-dimensional arrays), have a look in the PHP manual.  use print_r($results_array); to see what kind of structure is returned.

[acook]

that did it!!  thank you so much for all of your help!!

 

SOLVED!