PHP Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsProgrammingPHP Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 12th, 2004, 01:01 PM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
how do i count a string from 1 table and insert that count into another table?

ok folks,

i have 1 table, 'p1_web_counts'

i have a html form page that sends info to a php processing page that inserts this info into the table p1_web_counts

.......so far the info entered into 'job_title_id' is obviously a job title id. this id now becomes a foreign key that relates to another table called 'p1_list_creative' ......and in the table, 'p1_list_creative' the column, 'job_title_id' is also a foriegn key

so lets say someone selected the job title description 'Marketing', which inserts the job title id, 'MRKTG' into p1_web_count.job_title_id, then what i would like to do is........

1. relate p1_web_counts.job_title_id to p1_list_creative.job_title_id

2. then count the job title id's, 'MRKTG' in p1_list_creative.job_title_id

3. then insert that count into p1_web_counts.job_title_count

after playing around and applying as much logic as i could i have the two following php scripts.........

using the first code, i can enter the data into the table but it splits into two rows .....meaning everything from the form is entered into a single row BUT the job title count is entered into a separate row. what i want to do is enter everything into the same row??????

Quote:
<?
// database connect
include("connect.php");

// inputs data into the feedback table
$addenquiry =
"INSERT INTO p1_web_counts
(date_of_count,
country_id,
industry_id,
company_id,
co_size_id,
yearly_rev_id,
company_limit,
job_title_id,
job_title_count,
company_count,
add_telephone,
telephone_count,
add_email,
email_count,
fax_count,
total_count,
req_file_format,
licence_usage,
lists_already_used,
special_req,
first_name,
last_name,
company_name,
job_title,
telephone,email,
city_town,
state,
country,
opt_out_email)
VALUES
(now(),
'".$country_id."',
'".$industry_id."',
'".$company_id."',
'".$co_size_id."',
'".$yearly_rev_id."',
'".$company_limit."',
'".$job_title_id."',
'".$job_title_count."',
'".$company_count."',
'".$add_telephone."',
'".$telephone_count."',
'".$add_email."',
'".$email_count."',
'".$fax_count."',
'".$total_count."',
'".$req_file_format."',
'".$licence_usage."',
'".$lists_already_used."',
'".$special_req."',
'".$first_name."',
'".$last_name."',
'".$company_name."',
'".$job_title."',
'".$telephone."',
'".$email."',
'".$city_town."',
'".$state."',
'".$country."',
'".$opt_out_email."')";

$result = mysql_query($addenquiry);

// insert count
$addcount =
"INSERT INTO p1_web_counts (job_title_count)
SELECT COUNT(*)
FROM p1_list_creative
WHERE job_title_id = '".$job_title_id."'";

$result = mysql_query($addcount);
?>



after thinking about it for a while (like all day, and still thinking) i came up with this code which logically seems like the right thing to do but still does not work.....

Quote:
<?
// database connect
include("connect.php");

// inputs data into the feedback table
$addenquiry =
"INSERT INTO p1_web_counts
(date_of_count,
country_id,
industry_id,
company_id,
co_size_id,
yearly_rev_id,
company_limit,
job_title_id,
job_title_count,
company_count,
add_telephone,
telephone_count,
add_email,
email_count,
fax_count,
total_count,
req_file_format,
licence_usage,
lists_already_used,
special_req,
first_name,
last_name,
company_name,
job_title,
telephone,email,
city_town,
state,
country,
opt_out_email)
VALUES
(now(),
'".$country_id."',
'".$industry_id."',
'".$company_id."',
'".$co_size_id."',
'".$yearly_rev_id."',
'".$company_limit."',
'".$job_title_id."',
SELECT COUNT(*) FROM p1_list_creative WHERE job_title_id = '".$job_title_id."',
'".$company_count."',
'".$add_telephone."',
'".$telephone_count."',
'".$add_email."',
'".$email_count."',
'".$fax_count."',
'".$total_count."',
'".$req_file_format."',
'".$licence_usage."',
'".$lists_already_used."',
'".$special_req."',
'".$first_name."',
'".$last_name."',
'".$company_name."',
'".$job_title."',
'".$telephone."',
'".$email."',
'".$city_town."',
'".$state."',
'".$country."',
'".$opt_out_email."')";

$result = mysql_query($addenquiry);
?>


Please help!!!! coz right now i'm running out of logic and resorting to aggression!

and would be most grateful if you could modify my code with your suggestions

Cheers!

Reply With Quote
  #2  
Old July 13th, 2004, 12:15 AM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
Although it is a quite a bit of code to trudge through, I managed to figure out basically what you want to do.

a. Insert a record
b. Count the number of Job titles and add that number to the record above

What you want to do is fairly simple and there are many ways to do this.

I see you worked up a SubQuery which in most cases should be fine.

The easier (cleaner) way is to create little helper functions to get/put/update data in your database.

Create the following functions:

1 - putFeedBack() // inputs data into the feedback table
2 - updateFeedBackById()
3 - removeFeedBackById()

Create the same type of set of functions for the other tables, then creating this routine should be more clear when you are done with these functions.
__________________
__________________________________________________ _
Wil Moore III, MCP | Integrations Specialist | Senior Consultant
Are You Listed...? | DigitallySmooth Inc.

Reply With Quote
  #3  
Old July 13th, 2004, 01:18 AM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
hi laidback!

can you show me an example of how i would modify my code with your suggestions?

Cheers!

Reply With Quote
  #4  
Old July 13th, 2004, 03:11 AM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
For the insert statement: you have INSERT INTO p1_web_counts... Add the field 'job_title_count ' to this statement, and for the value, you will use the getJobCountById() function.

The getJobCountById() function is one you will create. I will take the Id as a parameter and it will return an integer.

Reply With Quote
  #5  
Old July 13th, 2004, 01:05 PM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
hi laidbak,

i'm a little new to php ...can you show me how the code is stuctured in its full extent, i.e. modify my code with what you have suggested so i can see how it fits. sorry to be a pain.

.....eagerly awaiting your guidance!

cheers

Reply With Quote
  #6  
Old July 13th, 2004, 02:04 PM
kode_monkey kode_monkey is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2003
Posts: 367 kode_monkey User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 21 sec
Reputation Power: 16
Why not just give it a go yourself and see how far you get? Then when you get stuck come back and ask specific questions. You're far more likely to get help that way.

-KM-

Reply With Quote
  #7  
Old July 16th, 2004, 02:22 PM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
OK, to shed some light on the subject i've shortened down my code to inserting just 3 of the fields that i wanna focus on ...date, id and count

the following code seems to me like a very reasonable thing to do, but date_of_count and job_title_id are inserted into one row ....and job_title_count is inserted into another. what i want is for everything to be inserted into the same row. i just can't get my head around this coz i feel like i'm doing the right thing and i've tried so many combinations?????

<?
// database connect
include("connect.php");

// job count variable
$replace_job_title_count = "getJobCountById";

// inputs data into the feedback table
$addenquiry = "INSERT INTO p1_web_counts (date_of_count, job_title_id, job_title_count)
VALUES (now(),'".$job_title_id."', '".$replace_job_title_count()."')";

function getJobCountById() {
$c = "INSERT INTO p1_web_counts (job_title_count)
SELECT COUNT(*)
FROM p1_list_creative
WHERE p1_list_creative.job_title_id = '".$job_title_id."'";
$result = mysql_query($c);
}

$result = mysql_query($addenquiry);

?>

Reply With Quote
  #8  
Old July 16th, 2004, 02:30 PM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
Lets focus on the first thing.

I assume the variable $job_title_id already is getting a value from somewhere, so it actually is a valid Job Title Id.

The function getJobCountById() should take The Job Title Id as a parameter:

Code:
function getJobCountById($jobid)
{
  // return the number of records
}


Reduce your script to just calling this function and print what it returns... if it doesn't return anything, you should focus on fixing that first.

Reply With Quote
  #9  
Old July 16th, 2004, 04:05 PM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Laidbak,

your right ...already i can see that i'm doing something wrong, so i'm gonna try to print the result (count) to my browser until i get it right. give me til tomorrow or by monday to figure it out what the darn i'm doing wrong!

Reply With Quote
  #10  
Old July 16th, 2004, 05:04 PM
AmericanD AmericanD is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2002
Posts: 81 AmericanD User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 16
a bit of an advise for the original poster.

First of all you are most welcome to ask the question or any other questions. It is a source of your and our learning too.

However I would like to advise you if you ask any HOWTO try to get just an "idea" of the solution by putting up "as less as possible but neat" code so we can easily read it. Maybe you could cut the other fields in the sql table and just have put up the tuples that were related to your question. That is up to you to decide whats relevant.

I hope you dont get offended with what i said, just a friendly bit here. good luck
__________________
Hungry for Code

Programming works best with a team over one single person

Reply With Quote
  #11  
Old July 16th, 2004, 07:54 PM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
Quote:
Originally Posted by tarka
Laidbak,

your right ...already i can see that i'm doing something wrong, so i'm gonna try to print the result (count) to my browser until i get it right. give me til tomorrow or by monday to figure it out what the **** i'm doing wrong!
Tarka, I feel you... I know this stuff can get frustrating to say the least, but don't be hard on yourself... Lets also try to keep our posts as clean as possible. We all say it, but never know how many young kids google this forum up and view our posts.

If you don't mind, I'd like to ask that you kindly revise your last post just to take out the words you don't need. Thanks.

On another note, I'll be helping you along the way as you put this script together. It is my pleasure.

Just remember to keep all the pieces of your code small and clean. Remember to create small chunks of code (5 - 10 lines), then test it. Before you know it you have bug free, working code.

Also, no matter how small the job is, it is always good to write out all the step in plain english before hand, then work from that.

pseudocode

Reply With Quote
  #12  
Old July 17th, 2004, 02:43 AM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
yeah, i realised that my code was a little too long and cumbersome for others to pay attention to it, so in future i'll shorten it to whats relevant.

laidbak, i really appreciate your tips and help, so thanks for being patient with me

apologies for my french, in future i'll keep my code and language clean

.......i'll be back

Reply With Quote
  #13  
Old July 17th, 2004, 08:14 AM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
Thumbs up

sounds good buddy, hope to here from you soon.

Reply With Quote
  #14  
Old July 17th, 2004, 09:06 AM
tarka tarka is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 15 tarka User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
laidback,

i've actually figured it out, see the revised code below. the problem was i just wasn't thinking clearly. your tip on taking it 1 step at a time helped me to approach the issue differently. its a good thing you didn't just knock up some code and say here you go, coz otherwise i wouldn't have taken the time to learn it myself.

now i realise where alot of you guys are coming from!

Cheers!

Code:
<?
//database connect
	include("connect.php");

// produce counts based on job_title_id
		$sql_job_title_count = ("SELECT COUNT(*) FROM p1_list_creative
				WHERE p1_list_creative.job_title_id = '".$job_title_id."'");
		$result_job_title_count = mysql_query($sql_job_title_count);

// print job_title_count result to browser
		$num_job_title_count = mysql_result($result_job_title_count,0,0);

// produce counts based on company_id
		$sql_country_count = ("SELECT COUNT(*) FROM p1_list_creative
				WHERE p1_list_creative.country_id = '".$country_id."'");
		$result_country_count = mysql_query($sql_country_count);
		
// print country_count result to browser
		$num_country_count = mysql_result($result_country_count,0,0);

// inputs data into the p1_web_counts table
	$addenquiry = 
	"INSERT INTO  p1_web_counts (
	date_of_count, 
	job_title_id, 
	job_title_count,
	company_count)
	VALUES (
	now(),
	'".$job_title_id."', 
	'".$num_job_title_count."',
	'".$num_country_count."')";
	
	$result = mysql_query($addenquiry);

?>

Reply With Quote
  #15  
Old July 17th, 2004, 12:22 PM
digitallysmooth digitallysmooth is offline
you know how we do
Dev Articles Novice (500 - 999 posts)
 
Join Date: Jun 2002
Posts: 785 digitallysmooth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 34 m 21 sec
Reputation Power: 17
Now that is what I'm talking about! Good Job.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsProgrammingPHP Development > how do i count a string from 1 table and insert that count into another table?


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap