Checking if a mysql query was unsuccessful

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Checking if a mysql query was unsuccessful

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
   }
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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>";
}
?>
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Would you mind explaining
AND status = '@@STATUS@@'";
please?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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).
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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,
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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:
Post Reply