I am stuck aint I

Riche

I have a form that builds up list parts (id vid partname and partcost entered into a table called parts_temp)
What i am try to do with this script is pull the whole number of parts used for a repair and then enter them into 1 field in the service table. I will also need to convert back at some stage but one step at a time:

What i am finding is i am only getting the last row of the parts_temp to enter into the service db...

PLease help if you can.

<?

include "variables.php";

if (!$submit){

?>

		<form name="implode.php" action="<?php echo $PHP_SELF; ?>" method="post" enctype="multipart/form-data" type="submit" >
		<table>
		<tr>
		<td width="80%" align="left" valign="top"><input type=submit value="implode" name=submit></td>
		</tr>
		</font></form></table></center>


<?php


}else{

//Add a new service record

	$db = mysql_connect($dbserver,$dbuser,$dbpass);
	mysql_select_db($dbname,$db) or die("Unable to open Database");

	$query_records = "SELECT * FROM parts_temp where vid=5";
	$result = mysql_query($query_records, $db) or die(mysql_error());
	$count = mysql_num_rows($result); 

	while($data = mysql_fetch_assoc($result)){ 

	    $partname = $data['partname']; 
	    $partcost = $data['partcost']; 

	    $parts_array = explode(",", ($partname).'|' .($partcost)); 
	    $parts = '~' . implode('~', $parts_array); 

	    //print_r($parts);
	    //echo "<br />";
	} 


	$parts = $final_array;
	$db = mysql_connect($dbserver, $dbuser, $dbpass);
	mysql_select_db($dbname);
	$query = "INSERT INTO service VALUES (NULL, '$newjobnumber', '$vid', '$cid', '$servicedate', '$mileage', '$parts', '$details', '$totalpartscost', '$labour', '$vat', '$totalinvoice', '1');";
	$result = mysql_query($query);
}

?>

bastien

you keep overwriting the parts each time....you need to declare this outside the loop and then add to it in the loop

$parts = '';
while($data = mysql_fetch_assoc($result)){

        $partname = $data['partname'];
        $partcost = $data['partcost'];

        $parts_array = explode(",", ($partname).'|' .($partcost));
        $parts .= '~' . implode('~', $parts_array);

        //print_r($parts);
        //echo "<br />";
    }

Riche

But I still can't get this to work and it driving me :mad:

So depressing when ive been stuck with this problem for the lastweek and still can't get passed it..

bastien

what doesn't work? have you tried to echo the query out and

looking at it for obvious errors
running it thru the db gui tool of your choice for the db?

Both of these actions will help you in debugging the issue

Roger_Ramjet

No way to say this nicely, your database design is wrong. The whole reason for using 3rd normal form as the basis for a database schema is so that you don't have to jump through hoops like this for what is actually a simple problem. If you have a service item that uses multiple parts then you store the data that is specific to the service job in one table and the list of parts used in second table. Read this article on Database Normalisation and Design and then apply what you learn.

Riche

So what your saying is hold all the parts used in a seperate table and keep them their.

That would make sense tbh and save the headache of using implode() -explode()

I did echo and print but nothing was being generated. Duno why!.

Why I wanted to try and fit the whole service record into 1 table was to keep it tidy. Plus i would be learning another part of php. I guess somestuff is just not ment to be.

Thanks for your help anyway guys.

leatherback

Hi Riche,

Indeed. The idea is tat you store all information only once.

So you'd have a table with all parts that you could potentially need / use.
Another table that stores details about a customer
Another table that stores the current repair being made, with a colum for the customer_id to be stored
And another table, which links the repair to the parts (Which would only require 2 or three colums:
Repair_Identifier; Part_identifier; (Table_Record_id: Not required)

This way if anything changes in your database (Say the customer wants his second name on the bill too), you'd only have to change one thing.

However, once you start using this database as an archive of all the transactions, you might want to create a table to store all individual charges made to a customer, as descriptions and charges may change (Unless you do not UPDATE the records in the PARTS table, but you add new parts to it, whenever costs of a part, or descriptions of parts (e.g., serial nrs) change. Otherwise you can never look back into the table and see what you charged customer X in year Y for repair Z.

So: for current jobs, try to replicate as little information as possible; for archive & bookkepping purposes, think how you want to handle changes in the products you sell, and design your tables accordingly.

You will get to work with imploding etcetc. But for now, take a step back, with a piece of paper, and design the database structure. You might want to post it on this forum too. A faulty database design can cause you a lot of trouble in the long run, and designing it properly is worth your time.