Jump to content


Photo

SQL INSERT Help


  • Please log in to reply
5 replies to this topic

#1 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 17 October 2006 - 11:01 AM

Hey guys,

I have a table called tbl_article with the following fields:
article_id > autonumber > primary key
article_hits > int
article_approved > boolean
article_image > boolean

and another table called tbl_links with the following fields:

link_id > autonumber > primary key
genre_id > number
user_id > number
link_headline > text
link_description > memo
link_url > text
article_id > number
link_votes > number
link_score > number
link_hits > number
link_date > number

tbl_article:article_id has a one-to-one relationship with tbl_link:article_id

Now, when it comes to the insert statement, how do i do it so i insert a new article_id and field values in tbl_article. But also, get the new article id and put it into tbl_links.

Is there one long statement i could use?

Would i have to insert the record into tbl_article, find the article_id and then insert a record into tbl_link?

I hope this makes sense.

Thanks guys.

Jamie.


#2 marcus

marcus
  • Members
  • PipPipPip
  • Advanced Member
  • 1,842 posts
  • LocationRochester, NY

Posted 17 October 2006 - 11:08 AM

id's are auto increments, they will automatically increase each time you make a new row.

$query = "INSERT INTO tbl_article (`article_hits` , `article_approved` , `article_image` ) VALUES ('$hits' , '$approved' , '$image');";
$result = mysql_query($query);
$query = "INSERT INTO tbl_links (`genre_id` , `user_id` , `link_headline` , `link_description` , `link_url` , `article_id` , `link_votes` , `link_score` , `link_hits` , `link_date`) VALUES ('$gid' , '$uid' , '$hdln' , '$desc' , '$url' , '$aid', '$votes' , '$score' , '$hits', '$date' );";
$result = mysql_query($query);


#3 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 17 October 2006 - 11:12 AM

Yes, i know that.

But that wasn't my question.

I'm relating ArticleIDs. I would like to know how to do a SQL statement, so once i add a new record to tbl_article i can relate that article_id to tbl_link.

Thanks

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 17 October 2006 - 11:13 AM

I think that Jamie meant that he wanted to do it in one statement as opposed to two...

This can be done in Oracle, I believe it can probably be done in MySQL too.  Check this page out:

http://dev.mysql.com.../en/insert.html

and then this one afterwards:

http://dev.mysql.com...ert-select.html

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 17 October 2006 - 11:17 AM

Cheers HuggieBear.

I've just done this SQL, and it works like a treat :P

$sql = "INSERT ALL INTO tbl_article (article_headline, article_body, article_date, article_hits, article_approved) VALUES ('$headline', '$body', ".time().", 0, 1) INTO tbl_link (link_headline, link_description, genre_id);";


#6 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 17 October 2006 - 11:30 AM

Glad to have been of some assistance... I'll put those Oracle books back to the bottom of the dust pile now ;)

Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users