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??????




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.....



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!

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.

hi laidback!

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

Cheers!

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.

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

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-

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);

?>

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:



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.

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!

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

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

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

sounds good buddy, hope to here from you soon.

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!

Now that is what I'm talking about! Good Job.