check if primary key exists

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
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

check if primary key exists

Post 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 . . . )
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

COUNT
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

Post 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.
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

Post 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

      }
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

why dont you count them in query?

SELECT COUNT(*) FROM....
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

read the bible
http://dev.mysql.com/doc/mysql/en/counting-rows.html

edit| damnit dethron.. twice now :wink:
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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....
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

Post 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?
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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)
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

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

Post 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'];
mikeycorn
Forum Newbie
Posts: 23
Joined: Thu Jun 23, 2005 9:27 pm

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