...

View Full Version : Editing db records which were inputted as an array



galahad3
03-02-2010, 03:30 PM
I have a site page that adds (via a table in a form) values for car MOT history, for a specific car ID. For example ID 600 can have various values for MOT Date and for odometer (mileometer) readings, so it's a simple table with dates and values for each date.

The "Add New Details" form works fine, with an array, but I've hit some problems trying to pull that data out of mySQL so that it can be edited (and new additional values added, which will be more frequent).

The code for adding the new details was:



<?php

include ('../inc/dbconnect.php');

$caridquery = "SELECT carid FROM cars ORDER BY carid DESC LIMIT 1";

$numresults=mysql_query($caridquery);
$numrows=mysql_num_rows($numresults);
// get results
$result = mysql_query($caridquery) or die("Couldn't execute query");
// display the results returned
while ($row= mysql_fetch_array($result)) {
$caridvalue = $row["carid"];

$count++ ;
}

?>

<input type="hidden" name="addnewmot" value="1">
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="300" colspan="2"><?php echo 'Add MOT Details for car with ID <textarea name="_CarID" rows="1" cols="5" readonly> ' . $caridvalue . '</textarea>' ?></td>
</tr>
<tr>
<td width="150">MOT Date</td>
<td width="150">Odometer</td>
</tr>
<? // loop to create table
$output = "";
$desired_row_count = 20; // how many available entries do we want?

for ($i=0; $i<$desired_row_count; $i++) {
$output .= '
<tr>
<td width="150"><input type="text" name="mot_date['.$i.']" size="20" /></td>
<td width="150"><input type="text" name="mot_odometer['.$i.']" size="20" /></td>
</tr>';
}
echo $output;
?>
<tr>
<td width="150"><input type="submit" value="Add MOT Details" /></td>
</tr>
</table>
</form>


So it builds a table and the various values inputted get entered into rows in mySQL each with the same Car ID.

The problem is I need to output those values as not only editable but with extra table rows within the sending form, so that the admins can not only edit the details pulled from the db, but also fill in new rows (for example when the car gets a new MOT)

I tried this as a page which outputs the existing values from the db into the form table (which it does successfully) but it brings up an error on submit:



//Query for the MOT table
include ('../inc/dbconnect.php');

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

echo "<form action=\"motdetails_updatedb.php\" method=\"post\" name=\"updatemot\">
<table width=\"450\" border=\"1\" bordercolor=\"#000\" cellspacing=\"0\" cellpadding=\"0\" class=\"cardetailsauto\"><tr><td colspan=\"2\" align=\"center\"><strong>MOT History</strong></td></tr><tr><td width=\"150\"><strong>MOT Date</strong></td><td width=\"150\"><strong>Odometer</strong></td></tr>";

$count = 1 + $s ;

// Build SQL Query
$query2 = 'SELECT * FROM mothistorytable WHERE carid='.$refnumber;
echo $query2;
// specify the table and field names for the SQL query
$result2 = mysql_query($query2) or die("The System is undergoing maintenance at the moment and will be available shortly");
while ($row = mysql_fetch_assoc($result2))
{
$carid = $row["carid"];
$title48 = $row["motdate"];
$title49 = $row["motodometer"];

echo "<tr><td width=\"150\"><input type=\"hidden\" name=\"_CarID\" value='$carid' /><input name=\"_MOTDate\" value='$title48' /></td><td width=\"150\"><input name=\"_OdometerDate\" value='$title49' /></td></tr>";

$count++;
}
// loop to create table
$output = "";
$desired_row_count = 20; // how many available entries do you want?

for ($i=0; $i<$desired_row_count; $i++) {
$output .= '
<tr>
<td width="150"><input type="text" name="mot_date['.$i.']" size="20" /></td>
<td width="150"><input type="text" name="mot_odometer['.$i.']" size="20" /></td>
</tr>';
}
echo $output;

echo "</table><input type=\"submit\" value=\"Submit MOT Changes >>>\" name=\"updatemot\" /><p>&nbsp;</p></form>";
?>


The mySQL error is:



UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = ''(,"1.1.10","23457") //this is from my $echo $q

Warning: Cannot modify header information - headers already sent by (output started at /adminarea/motdetails_updatedb.php:29) in /adminarea/motdetails_updatedb.php on line 33


The code page that handles the inputting is:



<?php

if(isset($_POST['updatemot'])) //If the form was sent
{
//mysql_connect(DB_SERVER, DB_USER, DB_PWD) or die(mysql_error());
//mysql_select_db(DB_NAME) or die(mysql_error());

include ('../inc/dbconnect.php');

$carid = $_POST['_CarID'];
$motDate=$_POST['mot_date'];
$motOdometer=$_POST['mot_odometer'];

//echo $carid;
//echo $motOdometer;

//$q = 'INSERT INTO mothistorytable (`carid`,`motdate`,`motodometer`) VALUES ';

$q = "UPDATE mothistorytable SET carid = '$carid', motDate = '$mot_date', motOdometer = '$mot_odometer' WHERE carid = '$carid'";

// loop through motDate array and add to INSERT statement
for ($i=0; $i<count($motDate); $i++) {
if ($motDate[$i]!="" && $motOdometer[$i]!="") {
$q .= '('.$carid.',"'.$motDate[$i].'","'.$motOdometer[$i].'"),';
}
}
// remove ending comma from $q
$q = substr($q, 0, -1);
echo $q;
mysql_query($q);

//go to the next page
header('location: addnewphotos.php');
exit;

}
?>


It's a real headache as we need the admins to be able to pull out and amend or add to the MOT details db.

Any ideas as an altrenative for how we can go about this?

galahad3
03-02-2010, 05:29 PM
Okay, I removed the echo's (I was using them to see if the query outputted ok), however now what's happening is it appears to run ok when I enter some values into the MOT form table and submit (i.e it goes through to the next page as specified - addnewphotos.php), but I check mySQL and they haven't entered into the db... ???

_Aerospace_Eng_
03-02-2010, 05:48 PM
Change this

mysql_query($q);
to this

mysql_query($q) or die('error:'.mysql_error().'<br>'.$q);
And post your results. If the query was failing you should get the die message.

galahad3
03-02-2010, 06:20 PM
Thanks, I get this error message echoed out:



error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(,"1.1.10","23457")' at line 1
UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = ''(,"1.1.10","23457")


I notice it's trying to set the carid field with the values entered into the motDate and motOdometer fields??

abduraooft
03-02-2010, 06:39 PM
UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = ''(,"1.1.10","23457")
What's your expected value in the where clause, above? Are you using a varchar type for your carid field?

galahad3
03-02-2010, 06:41 PM
I saw there was a mismatch between the form input names between the pages so corrected that, but I still get this error output:



error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = '


carid is a unique value per db record (but not a primary) which is pulled from the db in the first page:




$carid = $row["carid"];

abduraooft
03-02-2010, 06:59 PM
I saw there was a mismatch between the form input names between the pages so corrected that, So, how you current form and php code look like?

UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = ' Did you notice the values of php variables which are being passed to your query?

galahad3
03-02-2010, 07:04 PM
In the form page where the data is inputted / edited:



echo "<tr><td width=\"150\"><input type=\"hidden\" name=\"_CarID\" value='$carid' /><input name=\"_MOTDate\" value='$title48' /></td><td width=\"150\"><input name=\"_OdometerDate\" value='$title49' /></td></tr>";


I noticed the input names didn't match the values at the top of the PHP form handler page, so changed these as follows in the file that gets called by the form:



$motDate=$_POST['_MOTDate'];
$motOdometer=$_POST['_OdometerDate'];


And the query itself:



$q = "UPDATE mothistorytable SET carid = '$carid', motDate = '$mot_date', motOdometer = '$mot_odometer' WHERE carid = '$carid'";


But the values don't seem to be picked up...

abduraooft
03-02-2010, 07:11 PM
$carid = $_POST['_CarID']; Before building the query, make sure that the $_POST contains all the required values, by something like

echo '<pre>';
print_r($_POST);
echo '</pre>';

galahad3
03-02-2010, 07:18 PM
Hi, this outputs as follows (when I place it just before declaring the variables for the UPDATE query)



Array
(
[mot_date] => Array
(
[0] => 1.1.10
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>
[6] =>
[7] =>
[8] =>
[9] =>
[10] =>
[11] =>
[12] =>
[13] =>
[14] =>
[15] =>
[16] =>
[17] =>
[18] =>
[19] =>
)

[mot_odometer] => Array
(
[0] => 23457
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>
[6] =>
[7] =>
[8] =>
[9] =>
[10] =>
[11] =>
[12] =>
[13] =>
[14] =>
[15] =>
[16] =>
[17] =>
[18] =>
[19] =>
)

[updatemot] => Submit MOT Changes >>>
)

error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
UPDATE mothistorytable SET carid = '', motDate = '', motOdometer = '' WHERE carid = '


And if I echo the three variables I create below that, I get no output at all for them, as if they're never sent:



echo $carid;
echo $motDate;
echo $motOdometer;


no output.

_Aerospace_Eng_
03-02-2010, 10:23 PM
You will need to loop through each input if they are meant to be an array and then put each value into a php array then store that.

galahad3
03-03-2010, 10:19 AM
Okay, so more specifically, what would this loop look like and where (as in which file) would I place it in? Would it be placed in the PHP file that handles the form data and sends it into the db- or would it need to go on the form page?

_Aerospace_Eng_
03-03-2010, 07:05 PM
It would go in the file that handles the data. This should help you.

http://www.evolt.org/node/60222

galahad3
03-04-2010, 04:15 PM
Ok, using those examples I tried changing the pages, but I'm getting errors of a different sort.

This is the error screen (I'm echoing the arrays)



Array
(
[mot_date] => Array
(
[0] => 1.1.10
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>
[6] =>
[7] =>
[8] =>
[9] =>
[10] =>
[11] =>
[12] =>
[13] =>
[14] =>
[15] =>
[16] =>
[17] =>
[18] =>
[19] =>
)

[mot_odometer] => Array
(
[0] => 200
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>
[6] =>
[7] =>
[8] =>
[9] =>
[10] =>
[11] =>
[12] =>
[13] =>
[14] =>
[15] =>
[16] =>
[17] =>
[18] =>
[19] =>
)

[updatemot] => Submit MOT Changes >>>
)

s:22:"Submit MOT Changes >>>";error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES('s:22:"Submit MOT Changes >>>";') WHERE carid = '' at line 1
UPDATE mothistorytable SET VALUES('s:22:"Submit MOT Changes >>>";') WHERE carid = '


The query still seems to cut off at the end though I can't see why.

The changes I made:

In the form page where the array data is entered or edited:



$query2 = 'SELECT * FROM mothistorytable WHERE carid='.$refnumber;
//echo $query2;
// specify the table and field names for the SQL query
$result2 = mysql_query($query2) or die("The System is undergoing maintenance at the moment and will be available shortly");
while ($row = mysql_fetch_assoc($result2))
{
$carid = $row["carid"];
$title48 = $row["motdate"];
$title49 = $row["motodometer"];

echo "<tr><td width=\"150\"><input type=\"hidden\" name=\"_CarID\" value='$carid' /><input name=\"_MOTDate\" value='$title48' id=\"motdetails[]\" /></td><td width=\"150\"><input name=\"_OdometerDate\" value='$title49' id=\"motdetails[]\" /></td></tr>";

$count++;
}
// loop to create table
$output = "";
$desired_row_count = 20; // how many available entries do you want?

for ($i=0; $i<$desired_row_count; $i++) {
$output .= '
<tr>
<td width="150"><input type="text" name="mot_date['.$i.']" size="20" id=\"motdetails[]\" /></td>
<td width="150"><input type="text" name="mot_odometer['.$i.']" size="20" id=\"motdetails[]\" /></td>
</tr>';
}
echo $output;

echo "</table><input type=\"submit\" value=\"Submit MOT Changes >>>\" name=\"updatemot\" /><p>&nbsp;</p></form>";


So basically I added id=\"motdetails[]\" to the various inputs in order to make the data part of an array.

And the page that updates the db is now:



if(isset($_POST['updatemot'])) //If the form was sent
{

$motdetails=serialize($_POST['updatemot']); //takes the data from post operation

//mysql_connect(DB_SERVER, DB_USER, DB_PWD) or die(mysql_error());
//mysql_select_db(DB_NAME) or die(mysql_error());

include ('../inc/dbconnect.php');

echo '<pre>';
print_r($_POST);
echo '</pre>';

echo $motdetails;

$carid = $_POST['_CarID'];
$motDate=$_POST['_MOTDate'];
$motOdometer=$_POST['_OdometerDate'];

$q= "UPDATE mothistorytable SET VALUES('$motdetails') WHERE carid = '$carid'" ;


// loop through motDate array and add to INSERT statement
for ($i=0; $i<count($motDate); $i++) {
if ($motDate[$i]!="" && $motOdometer[$i]!="") {
$q .= '('.$carid.',"'.$motDate[$i].'","'.$motOdometer[$i].'"),';
}
}
// remove ending comma from $q
$q = substr($q, 0, -1);
//echo $q;
mysql_query($q) or die('error:'.mysql_error().'<br>'.$q);

//go to the next page
header('location: addnewphotos.php');
exit;

}
?>


So I've tried to follow the example for creating the array but it still isn't working...

abduraooft
03-04-2010, 04:20 PM
I'd recommend you to correct your form design first by finding the source of those repeated and unwanted elements, which come as empty in $_POST. Aren't you aiming an update on a single record/row?

galahad3
03-04-2010, 04:44 PM
Hi, the process should be able to either update records that already exist (and are pulled from the db table by the while loop in the form), or add additional records, all on the condition that the carid is the same always.

And I tried the following mySQL query instead but that doesn't work either:



$q = "INSERT INTO mothistorytable (`carid`,`motdate`,`motodometer`) VALUES (`$motdetails`)";




error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
INSERT INTO mothistorytable (`carid`,`motdate`,`motodometer`) VALUES (`s:22:"Submit MOT Changes >>>";`


In this particular instance there's no existing records matching, so I'm attempting to just add in a single row.

I can't see anywhere in the form page where it's getting the "s" and "22" values from, and the third value is actually the display value of the Submit button!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum