Re: [PHP] validation inserts not working
Lex Braun wrote:
PJ,
On Tue, Mar 10, 2009 at 3:46 PM, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
}/* --- IF THIS LIKE IS DELETED, THE PAGE DOES NOT DISPLAY
So, if I select insert on the page, only the first query is executed
since the rest is commented out. Thus, I get onle the book table
inserted but not other tables, like author, book_author, or
publishers,
book_publisher or categories, book_categories...
Is there something wrong with what follows immediately...
like, do I have the brackets right? I've tried about every
combination possible with no change.
//Check if Author is entered exists
if( (strlen($_POST[first_nameIN]) 0)
(strlen($_POST[last_nameIN]) 0) ) {
$sql2 = SELECT (first_name, last_name)
FROM author WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN)';
LIKE is going to do full-text search, which isn't what you want when
searching for a specific author. You can have the query return the ID
for that specific author (to use in your $sql2a query).
$sql2 = SELECT id FROM author WHERE first_name = '$first_nameIN' AND
last_name = '$last_nameIN' ;
$result2 = mysql_query($sql2);
if (mysql_num_rows($result2) 0) {
$row = mysql_fetch_assoc($result2); // gives you the row
return from $sql2
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id http://author.id WHERE
(first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'),
book.ID WHERE book.title LIKE '$titleIN'), '1';
With the change in $sql2 and the fact that the bookID is stored in
$autoid, this becomes:
$sql2a = INSERT INTO book_author (authID, bookID, ordinal) VALUES (
. $row['id'] . , . $autoid . , '1');
$result2a = mysql_query($sql2a, $db);
}
elseif (mysql_num_rows($result2) = 0) {
$sql2b = INSERT INTO author (first_name, last_name)
VALUES ('$first_nameIN', '$last_nameIN');
$result2b = mysql_query($sql2b, $db);
$authorID = mysql_insert_id($result2b); // gives you
the id of the newly inserted author for use in your book_author table
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id http://author.id WHERE
(first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'), book.ID
WHERE book.title LIKE '$titleIN'), '1';
With the addition of $authorID and the fact that bookID is stored in
$autoid, your $sql2c should now be:
$sql2c = INSERT INTO book_author (authID, bookID, ordinal) VALUES (
. $authorID . , . $autoid . , '1');
$result2c = mysql_query($sql2c, $db);
}
}
- Lex
Thanks for your suggestions. They helped me solve several errors and
explained some things like LIKE that were not clear.
It takes me some time to ferret out some of the problems on this
particular php page and I try to do what I can with limited knowledge
and experience but I am learning. :-)
For some reason I cannot understand, the $autoid and the $authorID =
mysql_insert_id ($result) do not work.
What I do not understand is when a query is successful and I seen in the
database that the data has been inserted and the id field has been
auto-inserted, the myssql_insert_id() returns what appears to be null
since nothing is echoed.
Here is some code:
snip ...
else { $sql1 = INSERT INTO book
( title, sub_title, descr, comment, bk_cover,
copyright, ISBN, language, sellers )
VALUES
('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN', '$bk_coverIN', '$copyrightIN',
'$ISBNIN', '$languageIN', '$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
$sql2 = INSERT INTO author (first_name, last_name) VALUES
('$first_nameIN', '$last_nameIN');
$result2 = mysql_query($sql2, $db);
$authorID = mysql_insert_id($result2);
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$authorID', '$autoid', '1');
$result2a = mysql_query($sql2a, $db);
$sql2b = INSERT INTO author (first_name, last_name) VALUES
('$first_name2IN', '$last_name2IN');
$result2b = mysql_query($sql2b, $db);
$author2ID = mysql_insert_id($result2b);
$sql2c
Re: [PHP] validation inserts not working
PJ,
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
Re: [PHP] validation inserts not working
Lex Braun wrote:
PJ,
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
I tried this (from the link above)
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id();
echo $autoid;
works...
but now, I have another problem... I am trying to debug this thing by
going 1 query at a time ( I comment out the rest): every time I do an
INSERT INTO book... the insert works fine but the ID is increased not
from the highest id value but from the last id inserted. So my highest
at the moment is 11; I insert test data and it goes in as id = 15; I
delete this field and redo an insert and it goes in as id = 16. How can
I change this so the auto_increment continues from the 11 ?
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] validation inserts not working
On Wed, Mar 11, 2009 at 4:29 PM, PJ af.gour...@videotron.ca wrote:
Lex Braun wrote:
PJ,
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
I tried this (from the link above)
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id();
echo $autoid;
works...
but now, I have another problem... I am trying to debug this thing by
going 1 query at a time ( I comment out the rest): every time I do an
INSERT INTO book... the insert works fine but the ID is increased not
from the highest id value but from the last id inserted. So my highest
at the moment is 11; I insert test data and it goes in as id = 15; I
delete this field and redo an insert and it goes in as id = 16. How can
I change this so the auto_increment continues from the 11 ?
In MySQL,
ALTER TABLE [tablename] AUTO_INCREMENT = [value];
HTH,
--
// Todd
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] validation inserts not working
haliphax wrote:
On Wed, Mar 11, 2009 at 4:29 PM, PJ af.gour...@videotron.ca wrote:
Lex Braun wrote:
PJ,
  snip
  $sql1 = INSERT INTO book ( title, sub_title, descr,
        comment, bk_cover, copyright, ISBN, language,
sellers )
      VALUES ('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN',
        '$bk_coverIN', '$copyrightIN', '$ISBNIN',
'$languageIN',
        '$sellersIN');
    $result1 = mysql_query($sql1, $db);
    $autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
I tried this (from the link above)
$result1 = mysql_query($sql1, $db);
  $autoid = mysql_insert_id();
  echo $autoid;
works...
but now, I have another problem... I am trying to debug this thing by
going 1 query at a time ( I comment out the rest): every time I do an
INSERT INTO book... the insert works fine but the ID is increased not
from the highest id value but from the last id inserted. So my highest
at the moment is 11; I insert test data and it goes in as id = 15; I
delete this field and redo an insert and it goes in as id = 16. How can
I change this so the auto_increment continues from the 11 ?
In MySQL,
ALTER TABLE [tablename] AUTO_INCREMENT = [value];
HTH,
Ok, but why is it doing that? I like to understand - I must have asked
too many questions when I was a kid... it stuck. :-)
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] validation inserts not working
On Wed, Mar 11, 2009 at 5:44 PM, PJ af.gour...@videotron.ca wrote:
haliphax wrote:
On Wed, Mar 11, 2009 at 4:29 PM, PJ af.gour...@videotron.ca wrote:
Lex Braun wrote:
PJ,
  snip
  $sql1 = INSERT INTO book ( title, sub_title, descr,
        comment, bk_cover, copyright, ISBN, language,
sellers )
      VALUES ('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN',
        '$bk_coverIN', '$copyrightIN', '$ISBNIN',
'$languageIN',
        '$sellersIN');
    $result1 = mysql_query($sql1, $db);
    $autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It
should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
I tried this (from the link above)
$result1 = mysql_query($sql1, $db);
  $autoid = mysql_insert_id();
  echo $autoid;
works...
but now, I have another problem... I am trying to debug this thing by
going 1 query at a time ( I comment out the rest): every time I do an
INSERT INTO book... the insert works fine but the ID is increased not
from the highest id value but from the last id inserted. So my highest
at the moment is 11; I insert test data and it goes in as id = 15; I
delete this field and redo an insert and it goes in as id = 16. How can
I change this so the auto_increment continues from the 11 ?
In MySQL,
ALTER TABLE [tablename] AUTO_INCREMENT = [value];
HTH,
Ok, but why is it doing that? I like to understand - I must have asked
too many questions when I was a kid... it stuck. :-)
It's doing that because an auto-incrementing column keeps incrementing with
each insertion. Thus, say you insert an entry into ID #10 and delete it
before another entry is made. MySQL will still have the auto_increment set
to 11 (one more than the last insertion) rather than subtracting one because
you deleted #10. It's the database method of ensuring you aren't
over-writing pre-existing auto_increment values.
NOTE: This doesn't take into account someone entering their own value into
the ID field greater than the current auto_increment value.
Re: [PHP] validation inserts not working
PJ wrote:
Lex Braun wrote:
PJ,
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
You're actually sending mysql_insert_id() the wrong parameter. It should be:
$autoid = mysql_insert_id($db); // you send the resource of your MySQL
connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
This should be corrected in everywhere you call mysql_insert_id()
-Lex
I tried this (from the link above)
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id();
echo $autoid;
works...
but now, I have another problem... I am trying to debug this thing by
going 1 query at a time ( I comment out the rest): every time I do an
INSERT INTO book... the insert works fine but the ID is increased not
from the highest id value but from the last id inserted. So my highest
at the moment is 11; I insert test data and it goes in as id = 15; I
delete this field and redo an insert and it goes in as id = 16. How can
I change this so the auto_increment continues from the 11 ?
I'm not sure you can, and for good reason. MySQL keeps track of last id
in an autoincrement even if you deleted the record. You don't want ID
clashes even with records that have been deleted. What if they were
accidentally deleted and need to be restored from backup? Now you will
have an ID conflict because an old record and new record will have same
ID. That's why mysql will not use a record id in autoincrement that has
been used before, even if it appears to be available.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] validation inserts not working
Sorry to be a nuisance again, but I cannot understand why my code is not
functioning correctly.
I have tested the separate elements of the code and they work fine. But
when I put it all together something goes off the rails. I am using
sessions and cookies.
After verifying that the required fields are entered, the script
continues as below:
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
}/* --- IF THIS LIKE IS DELETED, THE PAGE DOES NOT DISPLAY
So, if I select insert on the page, only the first query is executed
since the rest is commented out. Thus, I get onle the book table
inserted but not other tables, like author, book_author, or publishers,
book_publisher or categories, book_categories...
Is there something wrong with what follows immediately...
like, do I have the brackets right? I've tried about every
combination possible with no change.
//Check if Author is entered exists
if( (strlen($_POST[first_nameIN]) 0)
(strlen($_POST[last_nameIN]) 0) ) {
$sql2 = SELECT (first_name, last_name)
FROM author WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN)';
$result2 = mysql_query($sql2);
if (mysql_num_rows($result2) 0) {
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'),
book.ID WHERE book.title LIKE '$titleIN'), '1';
$result2a = mysql_query($sql2a, $db);
}
elseif (mysql_num_rows($result2) = 0) {
$sql2b = INSERT INTO author (first_name, last_name)
VALUES ('$first_nameIN', '$last_nameIN');
$result2b = mysql_query($sql2b, $db);
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'), book.ID
WHERE book.title LIKE '$titleIN'), '1';
$result2c = mysql_query($sql2c, $db);
}
}
I have tried to format the code for display on this page, but as e-mail
pages tend to be unreliable because of character encoding and page
widths, I hope it is understandable.
I could post an URL to view the code and the output page from the web.
There is something in the code that escapes me totally.
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] validation inserts not working
PJ,
On Tue, Mar 10, 2009 at 3:46 PM, PJ af.gour...@videotron.ca wrote:
snip
$sql1 = INSERT INTO book ( title, sub_title, descr,
comment, bk_cover, copyright, ISBN, language, sellers )
VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN',
'$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN',
'$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
}/* --- IF THIS LIKE IS DELETED, THE PAGE DOES NOT DISPLAY
So, if I select insert on the page, only the first query is executed
since the rest is commented out. Thus, I get onle the book table
inserted but not other tables, like author, book_author, or publishers,
book_publisher or categories, book_categories...
Is there something wrong with what follows immediately...
like, do I have the brackets right? I've tried about every
combination possible with no change.
//Check if Author is entered exists
if( (strlen($_POST[first_nameIN]) 0) (strlen($_POST[last_nameIN])
0) ) {
$sql2 = SELECT (first_name, last_name)
FROM author WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN)';
LIKE is going to do full-text search, which isn't what you want when
searching for a specific author. You can have the query return the ID for
that specific author (to use in your $sql2a query).
$sql2 = SELECT id FROM author WHERE first_name = '$first_nameIN' AND
last_name = '$last_nameIN' ;
$result2 = mysql_query($sql2);
if (mysql_num_rows($result2) 0) {
$row = mysql_fetch_assoc($result2); // gives you the row return
from $sql2
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'),
book.ID WHERE book.title LIKE '$titleIN'), '1';
With the change in $sql2 and the fact that the bookID is stored in $autoid,
this becomes:
$sql2a = INSERT INTO book_author (authID, bookID, ordinal) VALUES ( .
$row['id'] . , . $autoid . , '1');
$result2a = mysql_query($sql2a, $db);
}
elseif (mysql_num_rows($result2) = 0) {
$sql2b = INSERT INTO author (first_name, last_name)
VALUES ('$first_nameIN', '$last_nameIN');
$result2b = mysql_query($sql2b, $db);
$authorID = mysql_insert_id($result2b); // gives you the id
of the newly inserted author for use in your book_author table
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES (author.id WHERE (first_name LIKE '$first_nameIN'
last_name LIKE '$last_nameIN'), book.ID
WHERE book.title LIKE '$titleIN'), '1';
With the addition of $authorID and the fact that bookID is stored in
$autoid, your $sql2c should now be:
$sql2c = INSERT INTO book_author (authID, bookID, ordinal) VALUES ( .
$authorID . , . $autoid . , '1');
$result2c = mysql_query($sql2c, $db);
}
}
- Lex
