create job and job items 1

[gavinhall]

Hello - i am new to this site so hopefully you will understand what i am trying to do.


I want to add a new row to a "job" table and at the same time add multiple rows to a "job details" in mysql


The job table needs to generate a job number and add a date
the job details table needs to include the job number and a list of notes.

Is there a simple example that i can look at to point me in the right direction?


thanks in advance
gav

 

[Olavxxx]

Hi,

I think I understand that you have 2 tables:
[job]
[job_details]

Then you want a 1:many relationship.
So, what you can do:

1) insert into the job table
> secure your variables, strip them for tags, run htmlentities, etc.
2) use function mysql_insert_id();
> $jobid = mysql_insert_id();
3) insert the data into the other table, setting the fk-field to the $jobid.

How do you do this practically?
You can, if I understand you correct, just have one form.
That form has the job fields, also it has textareas for job-detail.

The textareas you fill into an array upon submit, which means that you can loop them later and run queries based on theire value.

Remember to secure the variables, as per the best practice mysql_real_escape_string();

references:

http://no.php.net/manual/en/function.mysql-real-escape-string.php

http://no.php.net/manual/en/function.mysql-insert-id.php

http://no.php.net/manual/en/function.strip-tags.php

http://no.php.net/manual/en/function.htmlentities.php

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[gavinhall]

Yes thats exactly what im after!!!

Have you an example i could play with, learn and modify?

Many many thanks!

Gav

 

[vacunita]

Something lose to this:

[green]//Create html form[/green]
<form action="dropdown.php" method=POST>
<input type=text name="jobname">
<input type=text name="jobdetail1">
<input type=text name="jobdetail2">
<input type=text name="jobdetail3">
<input type=submit name="send" value="Submit Job">

</form>

<?PHP
if(isset($_POST['send'])){


[green]	//connect to db[/green]

$conn=mysql_connect('host','user','pass') or die(mysql_error());

[green]//Choose  DB[/green]
$db=mysql_select_db("jobs",$conn);
[green]	//Sanitize variables for insertion into DB:[/green]

	if(isset($_POST['jobname']))
		{
		$jobname=mysql_real_escape_string($_POST['jobname']);
		}
	if(isset($_POST['jobdetail1']))
		{
		$jobdetail1=mysql_real_escape_string($_POST['jobdetail1']);
		}

	if(isset($_POST['jobdetail2']))
		{
		$jobdetail2=mysql_real_escape_string($_POST['jobdetail2']);
		}
	if(isset($_POST['jobdetail3']))
		{
		$jobdetail3=mysql_real_escape_string($_POST['jobdetail3']);
		}
[green]//Create insert statement[/green]
$sql="INSERT INTO table_jobs (name_of_job) VALUES ('$jobname')";
[green]//Execute Query [/green]
$res=mysql_query($sql) or die("Could not Insert:". mysql_error());

$newid=mysql_insert_id($conn); [green]//retrieve id of last inserted row.[/green]

[green]//Insert details into detail table:[/green]

$sql2="INSERT INTO job_details (job_id,job_detail) VALUES($newid,'$jobdetail1')";

//execute insert query 

$res=mysql_query($sql2) or die("Could not Insert:". mysql_error());


}//if form submitted

?>

Somewhat straight forward.
Notice that the function mysql_insert_id only works if the id of the table you are inserting into is auto incremented.
However , if its not of that type, then you should have generated the ID previously to this, and know what it is for the later insertion into thew details table.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[gavinhall]

Thanks for replying - will set this up and let you know how i get on!!!

Many thanks!!!

Gav

 

[gavinhall]

Hi it looks like i need to do an update query one at a time for each item in job_detail - is this the best way to do it or could there be a loop to update all that are present?

kindest thanks

Gav

 

[vacunita]

You can create a loop, however, the queries need to be done one at a time. MYSQL does not support simultaneous queries.

If you want to automate it a bit more you can have your job details in an array, and loop through it to create and execute the insert statements.

<form action="dropdown.php" method=POST>
<input type=text name="jobname">
<input type=text name="jobdetail[red][][/red]"> 
<input type=text name="jobdetail[red][][/red]">
<input type=text name="jobdetail[red][][/red]">
<input type=submit name="send" value="Submit Job">
[green]//Change the name of the form elements so PHP can transform them into an array. Notice the [] after the name[/green]
...

[green]//Sanitize the each job detail in the array[/green]
 if(isset($_POST['jobdetail']))
        {
$jobdetails=array();
foreach ($_POST['jobdetail'] as $detail){

$jobdetail[]=mysql_mysql_real_escape_string($detail);
}        
}

...


foreach($jobdetails as $jobdetail){
[green]//Create insert statement[/green]
$sql2="INSERT INTO job_details (job_id,job_detail) VALUES($newid,'$jobdetail1')";

[green]//execute insert query[/green]

$res=mysql_query($sql2) or die("Could not Insert:". mysql_error());
}

With the foreach loops it will iterate through the array of jobdetails, create and execute an insert query for each job detail in the array.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.