checking if a number or entry exists

[jive]

Hello all,
I'm writing a bookwriter application were users can create chapters and write content in those chapters. My database consists of three tables. One of those tables is the chapters table which has the following columns:

ID: int, not null, primary key, auto increment
chap_title : varchar(250)
chap_number: int, not null

I want to be able to assign a chapter title a number which will be my descriptor id, so I can display the chapter number with the chapter title. But I also wan't my code to check and display an error if the chap_number is a non-numeral or already an existing chapter. Here my form:

chapter_add_form.php

<?php
include("header.php" );
?>

Please add new chapter:

<form name="addchapter" method="post" action="chapter_add_qry.php">
Chapter Number:
<input type="text" name="chap_number" size="3" maxlength="5"><br><br>
Chapter Title:
<input type="text" name="new_chapter" size="50" maxlength="100">
<p>
<input type="submit" value="submit" name="submitchapter">

</form>


<?php
include("footer.php" );
?>

heres the query page:

chapter_add_qry.php:

 <?php
include("header.php" );
?>

<?php
//connect to the database
$dbcnx = mysql_connect('localhost','username','password');
mysql_select_db('bookwriter');
//query the database to submit the chapter
if(isset($_POST['submitchapter'])) {
$new_chapter = $_POST['new_chapter'];
$chap_number = $_POST['chap_number'];
$sql = "INSERT INTO CHAPTERS SET
chap_title= '$new_chapter'
chap_number = '$chap_number'"
If (@mysql_query($sql)) {
echo('Your chapter has been added');
}
else {
echo('problems adding joke:' . mysql_error());
}
}
?>
<P>
<a href="chapter_form_act.php">Add Chapter</a>
<?php
include("footer.php" );
?>

how do I add such code?

[rozner]

before inserting into the database, you can check it with mySQL:

$sql = "SELECT * FROM CHAPTERS WHERE chap_title = '$new_chapter' AND chap_number = '$chap_number'";
 
$result = mysql_query($sql);
 
if (mysql_num_rows($result) >0) echo "already exists";
else {
  // add to table
} 

I forgot you also wanted to check for non-numeric. I think PHP has an is_numeric() function. You can use that.

http://ca3.php.net/is_numeric

[Caldus]

For checking to see if a variable is numeric:

if (is_numeric ($num) == true) {
    ...
}

[been]

Shouldn't this responsibility be left to the database system?
If you declare the descriptor_id numeric and unique, you cannot put in non-numeric values or duplicates.
So if an insert query fails, you can act accordingly.

Just a thought.

[jive]

ok I've tried it and it doesn't seem to be working. I am able to post the same number without the error message occuring:

chapter_edit_form.php:

 
<?php
include("header.php");
?>
<?php
//connect to the database
$dbcnx = mysql_connect('localhost','jive','zeroone');
$bookconnect = mysql_select_db('bookwriter');
$chapID = $_GET['chapID'];
 
$chapters = mysql_query("SELECT ID, chap_title, chap_number FROM chapters WHERE ID='$chapID'");
$chapter = mysql_fetch_array($chapters);
$chapID = $chapter['ID'];
$chap_title = $chapter['chap_title'];
$chap_number = $chapter['chap_number'];
?>
Please edit new chapter:
<form name="editchapter" method="post" action="chapter_edit_qry.php">
chapter number:<input type="text" name="chap_number" value="<?=$chap_number?>" size="3" maxlength="5"><br><br>
Chapter title: <input type="text" name="chap_title" value="<?=$chap_title?>" size="50" maxlength="100">
<input type="hidden" name="chapID" value="<?=$chapID?>"
<p>
<input type="submit" value="submit" name="edit_chapter">
</form>
<?php
include("footer.php");
?>

chapter_edit_qry.php:

<?php
include("header.php");
?>
<?php
$dbcnx = mysql_connect('localhost','username','password');
$bookconnect = mysql_select_db('bookwriter');
 
if(isset($_POST['edit_chapter'])) {
$chap_title = addslashes($_POST['chap_title']);
$chapID = $_POST['chapID'];
$chap_number = $_POST['chap_number'];
$checknum = "SELECT * FROM chapters WHERE chap_title = '$new_chapter' AND chap_number = '$chap_number'";
$result = mysql_query($checknum);
if (mysql_num_rows($result)> 0 ){
    echo('chapter number taken');
}
else {
$sql = "UPDATE chapters SET
        chap_title = '$chap_title',
        chap_number = '$chap_number'
        WHERE ID='$chapID'";
}
if(mysql_query($sql)) {
echo('The chapter has been edited');
}
else {
echo('error editing chapter:' . mysql_error());
}
}
?>
<?php
include("footer.php");
?>

perhaps I'm checking it in the wrong place? Also, where can I add this is_numeric function in the code above?

[rozner]

The problem is with your query, I think you forgot to replace the variable $new_chapter with $chap_title

$checknum = "SELECT * FROM chapters WHERE chap_title = '$chap_title' AND chap_number = '$chap_number'"; 

you may also want to try been's suggestion

If you declare the descriptor_id numeric and unique, you cannot put in non-numeric values or duplicates.
So if an insert query fails, you can act accordingly.

for the is_numeric check, you'd probably want that in the chapter_edit_qry.php file around here:

if (isset($_POST['edit_chapter'])) {
   if (!is_numeric($_POST['chap_number'])) {
      // some sort of error message
      // you may want to have an error variable to check against after
   } else {
      // add to table
 
   }
 
} 

[jive]

it's still not working:

<?php
include("header.php" );
?>
<?php
$dbcnx = mysql_connect('localhost','username','password');
$bookconnect = mysql_select_db('bookwriter');

if(isset($_POST['edit_chapter'])) {
$chap_title = addslashes($_POST['chap_title']);
$chapID = $_POST['chapID'];
$chap_number = $_POST['chap_number'];
$checknum = "SELECT * FROM chapters WHERE chap_title = '$chap_title' AND chap_number = '$chap_number'";
$result = mysql_query($checknum);
if (mysql_num_rows($result)> 0 ){
echo('chapter number taken');
}
else {
$sql = "UPDATE chapters SET
chap_title = '$chap_title',
chap_number = '$chap_number'
WHERE ID='$chapID'";
}
if(mysql_query($sql)) {
echo('The chapter has been edited');
}
else {
echo('error editing chapter:' . mysql_error());
}
}
?>
<?php
include("footer.php" );
?>

how do I make the chap_number field unique?

[rozner]

still not working? do you have phpMyAdmin on your server, if so you it's pretty easy to alter the table to have a unique field. Otherwise you have to do it by command line, I've been using phpMyAdmin for too long so I'm not sure off the top of my head how to do that.

[jive]

ok so the column is unique now, but the code is not checking for an already existing chapter and displaying the "chapter number taken" echo when I add a chapter with the same number. Mysql is however not allowing me to add a duplicate number. What am I doing wrong in the code above?

[been]

If the column in your database is unique, you don't have to perform the first SELECT query in your code, that's were it's going wrong:
If you try to insert a chap_number that already exists, your code performs the part of the following if() statement (and NOT it's else{} part where the UPDATE query is situated...)

You can just perform the UPDATE query without the whole code for the SELECT part:

if (isset(...)) {
    // addslashes etc...
    $result = mysql_query(...);
    if (!$result) {
        // ERROR ERROR ERROR number taken...
        //....
    }
    //... 

something like that

[jive]

?? I don't understand why I wouldn't use my query??

[been]

The only reason that you're doing the SELECT query in your code, is to check if a chap_id already exists, right?
You do this because you don't want any duplicate chap_id's in your table, right?
Please note, I'm not trying to be arrogant, I'm just confirming with you.

By defining the chap_id unique in your database, the database system won't allow a client (your code) to insert records with a chap_id that already exists, hence you can just do the update query, if it fails, the chap_id already existed. Of course there could be other problems; the database could be temporarily offline for example, but that's not the point; fact is that the SELECT query to check for unique id now becomes obsolete, and if it's obsolete, why still use it?

[jive]

the reason I want to check for a unique id is to display the error message I wan't and not the regular my sql message that comes if the user enters a already existing Id. All I want is for the error message to display "chapter number already taken" if the person enters and already existing chapter number.

[website]

In reply to pm,
I really haven't had time to look at the problem but from a quick reading, this is somewhat like I think it should be...

<?php
include("header.php" );
?>
<?php
$dbcnx = mysql_connect('localhost','username','password');
$bookconnect = mysql_select_db('bookwriter');

if(isset($_POST['edit_chapter'])) {
$chap_title = addslashes($_POST['chap_title']);
$chapID = (int) $_POST['chapID'];
$chap_number = (int) $_POST['chap_number'];
$checknum = "SELECT COUNT(*) FROM chapters WHERE  chap_number = '$chap_number'";
$result = mysql_query($checknum);
$num = mysql_fetch_row($result);
if ($num[0] > 0){
echo('chapter number taken');
}
else {
$sql = "UPDATE chapters SET
chap_title = '$chap_title',
chap_number = '$chap_number'
WHERE ID='$chapID'";
if(mysql_query($sql)) {
echo('The chapter has been edited');
}
else {
echo('error editing chapter:' . mysql_error());
}
}

}
?>
<?php
include("footer.php" );
?>

Hope this helps...

[jive]

Thanks!