Page 1 of 1

check if primary key exists

Posted: Thu Jun 23, 2005 9:33 pm
by mikeycorn
What I need to do is check to see if various "registration_date" primary key exist in my db.

One solution I found for checking if a primary key exists is to INSERT and see if you get an error, but that won't really work for my purposes because 98% of the time, I'm not interested in inserting a new record but in seeing which times are already taken.

Another solution I found was to use dba_exists, but the resource handle had me perplexed and I didn't know how to use the dba_open as opposed to what I've been using . . . mysql_connect and mysql_select_db.

Finally, in the book I'm working through, I thought I found the following solution, but unfortunately it's still listing all the times as available whether the primary key exists or not . . .

Code: Select all

for ($i = 8; $i < 22; $i += 2) {

  $query = "SELECT * FROM reservations WHERE registration_date='{$primarykeysearch}'";

  If (mysql_query ($query)) {

    // list time as available

  }

}

(I didn't include the code where I built $primarykeysearch, but I ran SQL queries from phpMyAdmin so I know the queries were good . . . )

Posted: Thu Jun 23, 2005 9:41 pm
by dethron
COUNT

Posted: Thu Jun 23, 2005 9:55 pm
by mikeycorn
Thanks for the reply.

Here's the code I have trying to make use of count:

Code: Select all

for ($i = 8; $i < 22; $i += 2) {

      $primarykeysearch = $_GET['date'];
      $primarykeysearch .= "_";
      $primarykeysearch .= str_pad($i, 2, "0", STR_PAD_LEFT);

      $query = "SELECT * FROM reservations WHERE registration_date='{$primarykeysearch}'";

      $result = mysql_query($query) or die('Error, insert query failed');

      echo $query;
      echo "<br />";
      echo count($result);
  
      If (count($result) > 0) {

        $ampm = "pm";
      
        if ($i > 12) {
        
          $itime = $i - 12;
        
        } else {
        
          $itime = $i;
          
          if ($i < 12) {
          
            $ampm = "am";
          
          }
        
        }
  
        echo '<p><a class="times" href="http://www.domain.com/order-form.php?date='.$_GET['date'].
          '&time='.$i.'">'.$itime.' : 00 '.$ampm.'</a></p>';
  
      }

    }
Here's the output:

Code: Select all

SELECT * FROM reservations WHERE registration_date='2005-06-25_08'
1
8 : 00 am

SELECT * FROM reservations WHERE registration_date='2005-06-25_10'
1
10 : 00 am

SELECT * FROM reservations WHERE registration_date='2005-06-25_12'
1
12 : 00 pm

SELECT * FROM reservations WHERE registration_date='2005-06-25_14'
1
2 : 00 pm

SELECT * FROM reservations WHERE registration_date='2005-06-25_16'
1
4 : 00 pm

SELECT * FROM reservations WHERE registration_date='2005-06-25_18'
1
6 : 00 pm

SELECT * FROM reservations WHERE registration_date='2005-06-25_20'
1
8 : 00 pm
But if I run the exact same queries on my db, only two of those time slots exist in the 'registration_date' primary key.

Posted: Thu Jun 23, 2005 10:08 pm
by mikeycorn
Well, I couldn't figure out how to make it work with count, but I found a working solution in mysql_num_rows:

Code: Select all

$query = "SELECT * FROM reservations WHERE registration_date='{$primarykeysearch}'";

      $result = mysql_query($query) or die('Error, insert query failed');

      $num = mysql_num_rows ($result);
  
      If ($num == 0) {

          // key not found, slot is available

      }

Posted: Thu Jun 23, 2005 10:09 pm
by dethron
why dont you count them in query?

SELECT COUNT(*) FROM....

Posted: Thu Jun 23, 2005 10:09 pm
by John Cartwright
read the bible
http://dev.mysql.com/doc/mysql/en/counting-rows.html

edit| damnit dethron.. twice now :wink:

Posted: Thu Jun 23, 2005 10:11 pm
by dethron
since i used "COUNT" in capital letter, i was trying to imply it is in the query. you do not need to request the whole dataset, instead let database tell you the number....

Posted: Thu Jun 23, 2005 10:37 pm
by mikeycorn
dethron wrote:since i used "COUNT" in capital letter, i was trying to imply it is in the query. you do not need to request the whole dataset, instead let database tell you the number....
Newbie here, didn't catch the implied meaning of the all caps but I will next time.
dethron wrote:you do not need to request the whole dataset, instead let database tell you the number....
Okay, just so I'm 100% clear, are you saying using them in a query is going to be quicker / less resources than performing a mysql_num_rows?

Posted: Thu Jun 23, 2005 10:40 pm
by dethron
mikeycorn wrote:Newbie here, didn't catch the implied meaning of the all caps but I will next time.
Kewl, you got that quick, ;).

mikeycorn wrote:Okay, just so I'm 100% clear, are you saying using them in a query is going to be quicker / less resources than performing a mysql_num_rows?
Kewl, you got that quick, ;).

8)

Posted: Thu Jun 23, 2005 10:52 pm
by mikeycorn
Thanks, unfortunately applying it to my code isn't coming so quick . . . :lol:

The "Counting Rows" link Jcart gave was helpful, but sometimes for a newbie, I see them explain the function (like SELECT COUNT) but I still don't have enough skills to apply it to how I want to use it in my code.

Actually, at the bottom of that link, was a User Comment I thought was going to do the trick for me, but I couldn't make it work:

Code: Select all

$query = "SELECT COUNT(*) FROM reservations WHERE registration_date='{$primarykeysearch}'";

      $result = mysql_query($query) or die('Error, insert query failed');

      $num = mysql_fetch_row($result);

      echo $num[0];
arrrgh.

Posted: Thu Jun 23, 2005 11:10 pm
by John Cartwright

Code: Select all

$query = "SELECT COUNT(*) AS `total` FROM `reservations` WHERE `registration_date` = '{$primarykeysearch}'";       

$result = mysql_query($query) or die(mysql_error());       
$num = mysql_fetch_assoc($result);       
echo $num['total'];

Posted: Thu Jun 23, 2005 11:16 pm
by mikeycorn
I do try to RTFM as much as I can, but sometimes I still need a little help putting it into action.

Thank you both - big time!