SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
-
Jul 20, 2003, 08:43 #1
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
checking if a number or entry exists
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 Code:<?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" );
?>
chapter_add_qry.php:PHP Code:<?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" );
?>
-
Jul 20, 2003, 09:09 #2
- Join Date
- Oct 2002
- Location
- Paris
- Posts
- 1,058
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
before inserting into the database, you can check it with mySQL:
PHP Code:$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
}
http://ca3.php.net/is_numeric
-
Jul 20, 2003, 09:35 #3
- Join Date
- Apr 2003
- Location
- Chicago, USA
- Posts
- 417
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
For checking to see if a variable is numeric:
Code:if (is_numeric ($num) == true) { ... }
-
Jul 20, 2003, 09:43 #4
- Join Date
- May 2002
- Location
- Gent, Belgium
- Posts
- 284
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Per
Everything works on a PowerPoint slide
-
Jul 20, 2003, 14:07 #5
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 Code:
<?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");
?>
PHP Code:<?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");
?>
-
Jul 20, 2003, 15:59 #6
- Join Date
- Oct 2002
- Location
- Paris
- Posts
- 1,058
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
The problem is with your query, I think you forgot to replace the variable $new_chapter with $chap_title
PHP Code:$checknum = "SELECT * FROM chapters WHERE chap_title = '$chap_title' AND chap_number = '$chap_number'";
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.
PHP Code: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
}
}
-
Jul 20, 2003, 16:25 #7
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
it's still not working:
PHP Code:<?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" );
?>Last edited by jive; Jul 22, 2003 at 10:05.
-
Jul 20, 2003, 16:34 #8
- Join Date
- Oct 2002
- Location
- Paris
- Posts
- 1,058
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 20, 2003, 18:48 #9
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jul 22, 2003, 02:24 #10
- Join Date
- May 2002
- Location
- Gent, Belgium
- Posts
- 284
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
PHP Code:if (isset(...)) {
// addslashes etc...
$result = mysql_query(...);
if (!$result) {
// ERROR ERROR ERROR number taken...
//....
}
//...
Per
Everything works on a PowerPoint slide
-
Jul 22, 2003, 08:49 #11
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
?? I don't understand why I wouldn't use my query??
-
Jul 22, 2003, 12:39 #12
- Join Date
- May 2002
- Location
- Gent, Belgium
- Posts
- 284
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Per
Everything works on a PowerPoint slide
-
Jul 22, 2003, 13:34 #13
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 23, 2003, 02:02 #14
- Join Date
- Oct 2002
- Location
- Iceland
- Posts
- 1,238
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 Code:<?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" );
?>- website
-
Jul 23, 2003, 06:27 #15
- Join Date
- Mar 2003
- Location
- Greenville, SC
- Posts
- 388
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks!
Bookmarks