Re: Need help with mysql prob
Hi,
abdulazeez alugo wrote:
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)',
$conn);
$tbl1_id = mysql_insert_id($conn);
}
Yep that's about it. You said that tbl1_id is an auto-increment column,
why are you including it in the insert query? You should just need
entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id
field.
I hope you've just missed out everything on the $conn line just for
short-hand, because $conn needs to be a valid connection resource
(result from mysql_connect) before passing it to mysql_query. Also on
your MySQL query line, you've started the insert command string with a
quote but terminated it with a single apostrophe, this terminator
should also be a quote. Your $text should also be enclosed with a
single apostrophe, and don't forget to clean your input ($entry and
$text) otherwise you'll be vulnerable to SQL injection and XSS attacks.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Need help with mysql prob
Date: Mon, 20 Apr 2009 08:06:05 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi,
abdulazeez alugo wrote:
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an
auto_increment value in tbl1, mysql is actually generating the values for
it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)', $conn);
$tbl1_id = mysql_insert_id($conn);
}
Yep that's about it. You said that tbl1_id is an auto-increment column,
why are you including it in the insert query? You should just need
entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id
field.
I hope you've just missed out everything on the $conn line just for
short-hand, because $conn needs to be a valid connection resource
(result from mysql_connect) before passing it to mysql_query. Also on
your MySQL query line, you've started the insert command string with a
quote but terminated it with a single apostrophe, this terminator
should also be a quote. Your $text should also be enclosed with a
single apostrophe, and don't forget to clean your input ($entry and
$text) otherwise you'll be vulnerable to SQL injection and XSS attacks.
Andy
Hi Andy,
Thank you very much you have been really very helpful. All those mistakes you
pointed at in the script about the apostrophe and others, are simple mistakes I
just made in the rush of typing the message; and yes $conn is the result from
mysql_connect. In my original script I clean my script to avoid mysql injection.
However, if you look at the code very well, you'll realize that I have stored
the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is
defined within a function newPost_tbl1($id, $entry, $text). My problem now is
how to access that variable in the function that inserts into the child table
so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2.
Thanks in advance.
Alugo.
_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx
Re: Need help with mysql prob
Hi Alugo, abdulazeez alugo wrote: Hi Andy, Thank you very much you have been really very helpful. All those mistakes you pointed at in the script about the apostrophe and others, are simple mistakes I just made in the rush of typing the message; and yes $conn is the result from mysql_connect. In my original script I clean my script to avoid mysql injection. No worries, just thought I'd point it out in case ;-) However, if you look at the code very well, you'll realize that I have stored the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is defined within a function *newPost_tbl1($id, $entry, $text).* My problem now is how to access that variable in the function that inserts into the child table so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2. Why not just return it from the function? $tbl1_id = mysql_insert_id(); return $tbl1_id; Then in the code that calls newPost_tbl1 ... $tbl1_id = newPost_tbl1($id, $entry, $text); newPost_tbl2($tbl1_id, ); Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Need help with mysql prob
Date: Mon, 20 Apr 2009 13:03:14 +0100 From: andy-li...@networkmail.eu To: defati...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: Need help with mysql prob Hi Alugo, abdulazeez alugo wrote: Hi Andy, Thank you very much you have been really very helpful. All those mistakes you pointed at in the script about the apostrophe and others, are simple mistakes I just made in the rush of typing the message; and yes $conn is the result from mysql_connect. In my original script I clean my script to avoid mysql injection. No worries, just thought I'd point it out in case ;-) However, if you look at the code very well, you'll realize that I have stored the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is defined within a function *newPost_tbl1($id, $entry, $text).* My problem now is how to access that variable in the function that inserts into the child table so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2. Why not just return it from the function? $tbl1_id = mysql_insert_id(); return $tbl1_id; Then in the code that calls newPost_tbl1 ... $tbl1_id = newPost_tbl1($id, $entry, $text); newPost_tbl2($tbl1_id, ); Regards, Andy Thank you very much Andy, Now my problem is solved. Cheers. Alugo Abdulazeez. _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
Re: Need help with mysql prob
Hi, Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? MySQL handles this for you. Simply INSERT the value into tbl1, then INSERT the value of tbl1_id in tbl2. Then try INSERT'ing a value into tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the insert will fail. Thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Need help with mysql prob
Date: Sun, 19 Apr 2009 22:50:20 +0100 From: andy-li...@networkmail.eu To: defati...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: Need help with mysql prob Hi, Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? MySQL handles this for you. Simply INSERT the value into tbl1, then INSERT the value of tbl1_id in tbl2. Then try INSERT'ing a value into tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the insert will fail. Thanks, Andy Hi Andy, Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
Re: Need help with mysql prob
Hi Alugo, Hi Andy, Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. Ah, I see your point. I'm guessing by your code you're using PHP? If so call mysql_insert_id() after you've inserted your record into tbl1. That will give you the ID of the auto increment column - see the note in the below link about bigint columns if your data type is bigint. http://uk3.php.net/mysql_insert_id Hope this helps, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with mysql prob
Hi Abdul- you may want to check this thread out regarding the same issue... http://www.webmasterworld.com/php/3565843.htm On Apr 19, 2009, at 3:23 PM, abdulazeez alugo wrote: Hi guys, I'm having a really terrible problem with my mysql coding and I'ld appreciate any help I can get on this cause it's really driving me crazy. I have two tables and I've succeeded in creating a one to many relationship between both table but the problem I'm having is that I dont know how to put the value of the foreign key into the table. The tales are as below: N.B: I'm using php-mysql //Table 1 $result= mysql_query(CREATE TABLE tbl1 (tbl1_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(tbl1_id), entrytitle VARCHAR(50), entrytext TEXT) ENGINE=InnoDB) //Table2 $result = mysql_query(CREATE TABLE tbl2 (tbl2_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(tbl2_id), tbl1_id INT UNSIGNED NOT NULL, name VARCHAR(50), title VARCHAR(50), comment TEXT, INDEX (tbl1_id), FOREIGN KEY(tbl1_id) REFERENCES tbl1(tbl1_id))ENGINE=InnoDB) Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? Thanks in anicipation of your useful suggestions. Alugo Abdulazeez. www.frangeovic.com _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Need help with mysql prob
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an
auto_increment value in tbl1, mysql is actually generating the values for
it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)',
$conn);
$tbl1_id = mysql_insert_id($conn);
}
Is the above code the best way to go about it?
Thanks in advance.
_
Drag n’ drop—Get easy photo sharing with Windows Live™ Photos.
http://www.microsoft.com/windows/windowslive/products/photos.aspx
