Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Editing db records which were inputted as an array

    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 Code:
    <?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 ($rowmysql_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:

    PHP Code:
    //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 $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:

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

    WarningCannot modify header information headers already sent by (output started at /adminarea/motdetails_updatedb.php:29in /adminarea/motdetails_updatedb.php on line 33 
    The code page that handles the inputting is:

    PHP Code:
    <?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($q0, -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?

  • #2
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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... ???

  • #3
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Change this
    PHP Code:
    mysql_query($q); 
    to this
    PHP Code:
    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.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #4
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks, I get this error message echoed out:

    PHP Code:
    error:You have an error in your SQL syntaxcheck 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??

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I saw there was a mismatch between the form input names between the pages so corrected that, but I still get this error output:

    PHP Code:
    error:You have an error in your SQL syntaxcheck 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:

    PHP Code:

    $carid 
    $row["carid"]; 

  • #7
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #8
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    In the form page where the data is inputted / edited:

    PHP Code:
    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:

    PHP Code:
    $motDate=$_POST['_MOTDate'];    
    $motOdometer=$_POST['_OdometerDate']; 
    And the query itself:

    PHP Code:
    $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...

  • #9
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    $carid = $_POST['_CarID'];
    Before building the query, make sure that the $_POST contains all the required values, by something like
    PHP Code:
    echo '<pre>';
    print_r($_POST);
    echo 
    '</pre>'
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #10
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi, this outputs as follows (when I place it just before declaring the variables for the UPDATE query)

    PHP Code:
    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 syntaxcheck 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:

    PHP Code:
    echo $carid;
    echo 
    $motDate;
    echo 
    $motOdometer
    no output.
    Last edited by galahad3; 03-02-2010 at 07:23 PM.

  • #11
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    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.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #12
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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?

  • #13
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    It would go in the file that handles the data. This should help you.

    http://www.evolt.org/node/60222
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #14
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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)

    PHP Code:
    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 syntaxcheck 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:

    PHP Code:
    $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:

    PHP Code:
    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($q0, -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...

  • #15
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •