Mysql / PHP update table with array ?
08-20-17, 12:19 PM
#1
Member
Thread Starter
Join Date: Jun 2010
Posts: 55
Mysql / PHP update table with array ?
I have a table "Friends":
If I have an array that has new Weight, Height, Sex for everyone, how do I update my table?
Thanks, I've been trying and trying and it's starting to really bug me.
Code:
+-----------------------+--------+--------+-----+ | Name | Weight | Height | Sex | +-----------------------+--------+--------+-----+ | Person 1 | 14 | 52 | M | | Person 2 | 25 | 63 | F | | Person 3 | 18 | 58 | F | | Person 4 | 45 | 61 | M | +-----------------------+--------+--------+-----+
Thanks, I've been trying and trying and it's starting to really bug me.
08-21-17, 12:16 PM
#2
DVD Talk Reviewer/ Admin
Join Date: Sep 1999
Location: Greenville, South Cackalack
Posts: 21,585
Re: Mysql / PHP update table with array ?
I'd prefer to have a more unique identifier than a name to key off of, but:
Personally, my approach would be:
1) Export 'Friends' to a .csv file, including some kind of unique, numeric identifier
2) Make the weight/height/sex changes in Excel
3) Save that to a .csv
4) Use fgetcsv to read the .csv, doing a database update for each line of the file
Code:
$array = array(array("Name"=>"Person 1",
"Weight"=>28,
"Height"=>104,
"Sex"=>'F'),
array("Name"=>"Person 2",
"Weight"=>50,
"Height"=>126,
"Sex"=>'M'),
array("Name"=>"Person 3",
"Weight"=>36,
"Height"=>116,
"Sex"=>'M'),
array("Name"=>"Person 4",
"Weight"=>90,
"Height"=>122,
"Sex"=>'F'));
foreach ($array AS $key=>$value)
{
$query = "UPDATE LOW_PRIORITY Friends " .
"SET Weight='" . mysql_escape_string($value["Weight"]) . "', " .
" Height='" . mysql_escape_string($value["Height"]) . "', " .
" Sex='" . mysql_escape_string($value["Sex"]) . "' " .
"WHERE Name='" . mysql_escape_string($value["Name"]) . "' ";
$result = mysql_query($query);
}
1) Export 'Friends' to a .csv file, including some kind of unique, numeric identifier
2) Make the weight/height/sex changes in Excel
3) Save that to a .csv
4) Use fgetcsv to read the .csv, doing a database update for each line of the file
08-22-17, 07:18 PM
#3
Member
Thread Starter
Join Date: Jun 2010
Posts: 55
Re: Mysql / PHP update table with array ?
I get an "Warning: Illegal string offset" error.
To give more background what I'm trying to accomplish, I have a table where I can input multiple weights at once (the row has brackets around name and weight which aren't showing up below):
I'm not sure why I can't update a bunch of weights and have those values posted to the mysql table.
Currently, if I submit, I do have an array, but I can't get it to post/update into the mysql table.
Array ( [0] => 14 [1] => 25 [2] )
Thoughts? This is so frustrating.
To give more background what I'm trying to accomplish, I have a table where I can input multiple weights at once (the row has brackets around name and weight which aren't showing up below):
HTML Code:
<tr> <td>Person 1:</td><td><input type=time name=" . $row"Name". " value='" . $row"Weight". "'></td> </tr> <tr> <td>Person 2:</td><td><input type=time name=" . $row"Name". " value='" . $row"Weight". "'></td> </tr>
Currently, if I submit, I do have an array, but I can't get it to post/update into the mysql table.
Array ( [0] => 14 [1] => 25 [2] )
Thoughts? This is so frustrating.
08-25-17, 08:31 AM
#4
DVD Talk Reviewer/ Admin
Join Date: Sep 1999
Location: Greenville, South Cackalack
Posts: 21,585
Re: Mysql / PHP update table with array ?
Okay, let's say the table looks like this:
I can't copy/paste the PHP I put together for this in the forum, but you can view the source at http://wittydomainname.net/junk/friends.txt You can see the form in action at http://wittydomainname.net/junk/friends.php In practice, I'd have error checking, would trim out white space, log who made these changes and when, would more consistenly use $_REQUEST instead of $_POST (sorry, still waking up), etc., but you get the general idea.
Code:
CREATE TABLE Friends
(
ID int unsigned auto_increment primary key,
Name varchar(255),
Weight smallint unsigned,
Height tinyint unsigned,
Sex enum('M', 'F')
)
;
INSERT INTO Friends VALUES (NULL, 'Person 1', 14, 52, 'M');
INSERT INTO Friends VALUES (NULL, 'Person 2', 25, 63, 'F');
INSERT INTO Friends VALUES (NULL, 'Person 3', 18, 58, 'F');
INSERT INTO Friends VALUES (NULL, 'Person 4', 45, 61, 'M');
Last edited by Adam Tyner; 08-25-17 at 08:40 AM.
06-27-18, 03:43 PM
#5
Administrator
Join Date: Sep 2015
Posts: 521
Re: Mysql / PHP update table with array ?
Code:
+-----------------------+--------+--------+-----+ | Name | Weight | Height | Sex | +-----------------------+--------+--------+-----+ | Person 1 | 14 | 52 | M | | Person 2 | 25 | 63 | F | | Person 3 | 18 | 58 | F | | Person 4 | 45 | 61 | M | +-----------------------+--------+--------+-----+
