Updating mySQL table

Megan posted this at 19:32 — 18th September 2007.

Joined: Jun 1999

Hi everyone,

I'm trying to figure out how to update large parts of a mySQL table at once. The tables are similar to the HTML tables on this page.

Sometime next year those fees will change and I don't want to force someone to go into the database to update them. So I'm creating a front-end interface for that. All the values in the table would be put into a form input so they could be edited. The question is, how can I update all those table rows through mySQL without having to go through each row separately?

Any ideas?? I hope I've explained this well enough!!

Megan
Connect with us on Facebook!

pr0gr4mm3r posted this at 21:43 — 18th September 2007.

He has: 1,502 posts

Joined: Sep 2006

Could you post the structure of the table?

The result of this query would help: SHOW COLUMNS FROM table_name

Megan posted this at 13:21 — 19th September 2007.

She has: 11,421 posts

Joined: Jun 1999

Is this what you were looking for?

Field 		Type 		Null 	Key 	Default      Extra 
ID		tinyint(2)	NO	PRI	 	 
roomtype	varchar(100)	NO	 	 	 
rezname		varchar(10)	NO	 	 	 
fall		decimal(20,0)	NO	 	 	 
winter		decimal(20,0)	NO	 	 	 
spring		decimal(20,0)	NO	 	 	 	 

Megan
Connect with us on Facebook!

pr0gr4mm3r posted this at 17:20 — 19th September 2007.

He has: 1,502 posts

Joined: Sep 2006

Here is some (untested) code that will show a form to update the entire table at once.

Code to show the form:

<?php
    /* insert connecting code here */
    
    $result = mysql_query(\"SELECT * FROM table_name\");
    
    echo '<form method=\"post\" action=\"somepage.php\">';
    for ($i = 0; $i < mysql_num_rows($result); $i++)
    {
        $row = mysql_fetch_assoc($result);
        
        /* now output the form fields */
        echo '<input type=\"text\" size=\"30\" name=\"data[' . $row['ID'] . '][roomtype]\" value=\"' . $row['roomtype'] . '\" /><br />';
        echo '<input type=\"text\" size=\"30\" name=\"data[' . $row['ID'] . '][rezname]\" value=\"' . $row['rezname'] . '\" /><br />';
        echo '<input type=\"text\" size=\"30\" name=\"data[' . $row['ID'] . '][fall]\" value=\"' . $row['fall'] . '\" /><br />';
        echo '<input type=\"text\" size=\"30\" name=\"data[' . $row['ID'] . '][winder]\" value=\"' . $row['winder'] . '\" /><br />';
        echo '<input type=\"text\" size=\"30\" name=\"data[' . $row['ID'] . '][spring]\" value=\"' . $row['spring'] . '\" /><br />';
        echo '<br /><br />';
    }
    echo '<input type=\"submit\" value=\"Submit\" />';
    echo '</form>';
?>

Code to process the form:

<?php
    foreach ($_POST['data'] as $field => $value)
    {
        $roomtype = $value['roomtype'];
        $rezname = $value['rezname'];
        $fall = $value['fall'];
        $winter = $value['winter'];
        $spring = $value['spring'];
        $id = $field;
        
        $query = \"UPDATE table_name SET roomtype = '$roomtype', rezname = '$rezname', fall = '$fall', winter = '$winter', spring = '$spring' WHERE ID = $id\";
        
        /* run that query */
    }
?>

Basically use form field arrays to submit multiple rows of data for updating.

Also, if you want a quick and dirty way of doing it, phpMyAdmin can update multiple rows at once.

Megan posted this at 18:18 — 19th September 2007.

She has: 11,421 posts

Joined: Jun 1999

Ooooh, thanks a lot! I was trying to figure out how I could pass two values through the form input.

I could have gone through phpMyAdmin to update this but I didn't want to make someone else do that (I might be leaving my job soon and I don't want to expect the next person to do that).

Megan
Connect with us on Facebook!