Page 1 of 1

Checking if a mysql query was unsuccessful

Posted: Tue Nov 14, 2006 2:07 pm
by impulse()
How would I write a switch statement to check if a mysql returned no results?

At the moment I have

Code: Select all

if ($_GET["page"] == "open") {
    $query = mysql_query("SELECT *
                          FROM customer, jobs
                          WHERE customer.customerid = jobs.customerid
                          AND customer.customerid = '$_SESSION[username]'
                          AND status = 'Not Delivered'");

  }

  if ($_GET["page"] == "closed") {
    $query = mysql_query("SELECT *
                          FROM customer, jobs
                          WHERE customer.customerid = jobs.customerid
                          AND customer.customer = '$_SESSION[username]'
                          AND status = 'Delivered'");

  }

  echo "<table border>";
  echo "<td bgcolor = 'FFFFCC' valign = 'top'>";
  echo "<h2> <b> <u> Delivery Information </u> </b> </h2> <br>";



  while ($res4 = mysql_fetch_array($query)) {

  switch ($res4) {
    case (empty($res4)):
      echo "There are no open deliveries";
      break;
    case (empty($res4)):
      echo "There are no closed deliveries";
      break;
  }
But nothing is ever returned, whether results are returned or not.

Posted: Tue Nov 14, 2006 2:19 pm
by John Cartwright
I'm not exactly sure why you would want to do that. Typically we don't want our queries to fail, ever. We simply want to check if any rows were returned.

Something along the lines of

Code: Select all

switch ($_GET['page']) 
{
   case 'open' :
      $status = 'Not Delivered';
      break;
   case 'closed' :
      $status = 'Delivered';
      break;
   
   default :
      $status = 'Not Delivered';
}

$result = mysql_query("SELECT *
           FROM customer, jobs
           WHERE customer.customerid = jobs.customerid
           AND customer.customerid = '".$_SESSION['username']."'
           AND status = '".$status."'") or die(mysql_error());

echo "<table border>";
echo "<td bgcolor = 'FFFFCC' valign = 'top'>";
echo "<h2> <b> <u> Delivery Information </u> </b> </h2> <br>";

if (mysql_num_rows($result) == 0) 
{
   echo 'There are no .' ($status == 'Not Delivered') ? 'open' : 'closed' .' deliveries';
}
else
{
   while ($row = mysql_fetch_assoc($result)) 
   {
      //display rows?
   }
}

Posted: Tue Nov 14, 2006 2:49 pm
by RobertGonzalez
Maybe something like...

Code: Select all

<?php
// Lets make sure the get var is set first
if (isset($_GET['page'])) {
    // Create a generic query to customize in a bit
    $sql = "SELECT *
            FROM customer, jobs
            WHERE customer.customerid = jobs.customerid
            AND customer.customerid = '$_SESSION[username]'
            AND status = '@@STATUS@@'";

    // Now lets figure out what we want to query by
    if ($_GET["page"] == "open") {
        $status = 'Not Delivered';
    } elseif ($_GET["page"] == "closed") {
        $status = 'Delivered';
    } else {
        $status = 'MAKE A DEFAULT VALUE';
    }

    // Build the query
    $sql = str_replace('@@STATUS@@', $status, $sql);
	
    // Try the query
    if (!$result = mysql_query($sql))
    {
        die(mysql_error());
    }
	
    // This is your query result
    $rows = mysql_fetch_array($result);
    
    // This is the number of row returned in the result
    $row_count = mysql_num_rows($result);
    
    // echo some stuff out
    echo "<table border>";
    echo "<td bgcolor = 'FFFFCC' valign = 'top'>";
    echo "<h2> <b> <u> Delivery Information </u> </b> </h2> <br>";
}
?>

Posted: Tue Nov 14, 2006 6:38 pm
by impulse()
Would you mind explaining
AND status = '@@STATUS@@'";
please?

Posted: Tue Nov 14, 2006 6:41 pm
by John Cartwright
impulse() wrote:Would you mind explaining
AND status = '@@STATUS@@'";
please?
just a marker so str_replace knows what to replace with the "status", although this is kind of unnecessary (see my last post).

Posted: Tue Nov 14, 2006 6:49 pm
by impulse()
Ahh, I see. Sorry Jcart I've only just realised how your script works, that's quite impressive :) I'll implement that and see how it goes tomorrow.

Thanks for that,

Posted: Wed Nov 15, 2006 10:13 am
by RobertGonzalez
Dude, all I can say is 'doh! what was I thinking?' I must have been tired, because I have l never done it the way I coded the example. In almost all cases I do it like Jcart did it.

Man, I really need to make sure I have coffee in me before I post code again. :oops: