View Full Version : Best way to manually edit a SQL table?
heavywater
22-02-11, 23:49
Hey guys,
I'm sorting out a PHP system for automatically updating and formatting match results entered into a table. It's going grand but the trouble is that the system was only implemented after the recording of results began so I need to enter about 100 results manually. It'll probably only take about 20 minutes or so but I can't find a decent way of editing the SQL table.
At the moment it's using PHPmyadmin which has some editing functionality but it's only field by field, no batch editing. I've tried exporting to excel documents but they refuse to load, i just get a corrupted message. The Open Office format does export and can be edited but I can't reuploaded (invalid compression method).
xml and csv work, csv opens in excel with most of the data in the first cell (and other stuff scattered at random about) and xml loads and can be edited fine but will take me about 6 months in notepad. sql also seems to load but will be hell to edit.
Essentially, I need something that will let me give every field in a column consecutive numbering in one go (100-400 or whatever) and let me add rows at the start for the unlogged games. Is there anything that can open sql or xml files into properly editable tables?
you can edit more than one field at the same time, iirc, there is tick box next to each field then you can go to the bottom and click edit
heavywater
23-02-11, 00:17
You can indeed.
How do you sequentially rename a column starting from an integer?
Think he means change
35
32
78
54
to
01
02
03
04
quickly and easily.
Export to excel and do it through there? have no idea really
With a lot of clicking I suppose :s
OR, you could write code to extract the whole table, then place it in a new table with that column as an auto-integer, starting from whatever the number you want is. Bit bloated but could work.
You could copy the data to a new table, delete the old one and rename the new one back to the other tables name.
heavywater
23-02-11, 00:27
Think he means change
35
32
78
54
to
01
02
03
04
quickly and easily.
Export to excel and do it through there? have no idea really
Well, to
170
171
172
173
174
etc
I need to put a ton of entries at the start but preserve the order of the one's already in as the 'id' column is the only way to sort by date. Can't use excel as 1. it can't read the xls files and 2. when the files are modified in any way I can't reimport them.
EDIT- yeah, looks like it's going to be a lot more hassle than I wanted it to. :(
Why do these have to be at the front?
Added after 13 minutes:
OH!
I know what you can do,
Start putting these few into a new table with the same headers etc except the 'id' field should be 'id_2', auto increase etc but otherwise identical, once you've stuck the 170 in manually, copy the contents of the old table into the new resulting in the automagically made id's in id_2, then delete the original id and rename id_2 to id.
:D
heavywater
23-02-11, 01:01
Why do these have to be at the front?
Because they're the old matches and it's sorted and listed chronologically by id, plus there are dependants like win streaks that would be messed up if results went in all over the shop.
But good thinking, I shall give that a go, thanks. :D
If you can get to the sql database which i assume you can, then why not try the SQL server management studio (here's a link to the express edition http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796 )
You can manually edit like an excel spreadsheet if needed, or write a query to read in an excel spreadsheet and copy the entries in, or ..... well once you have a query editor you can do pretty much whatever you want. S.
heavywater
23-02-11, 13:54
It's running MySQL so I'd have to migrate all the files across, then back again, which can be a massive hassle. Also, I doubt I know enough about SQL to pull it off, I've only coded PHP for about 6 hours. :lol:
can it read a csv file created from excel instead of xls....sure most sql things can..
Mr. Grapes
23-02-11, 15:10
If you can get to the sql database which i assume you can, then why not try the SQL server management studio (here's a link to the express edition http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796 )
MS SQL management studio can only talk to MS SQL Server DB's.
however, i thought MySQL had a similar management app?
BTW, management studio looks like this, and this is what i use daily:
http://i.imgur.com/cmcfa.png
you can select a table on the left and 'Edit table' and all the fields (or top 200 in this case) are directly editable
(edit: quick google - http://forums.mysql.com/read.php?10,261237,261238#msg-261238 )
however, i thought MySQL had a similar management app?
Yup, MySQL WorkBench:
http://www.mysql.com/products/workbench/
Personally I'd probably use mysqldump to get the table rows in INSERT statement form, write a 2-minute perl script to munge that and rewrite the IDs as per your requirements, drop the current contents and import the modified data. There's probably a quicker/better way, though. I presume this isn't going to cause you any problems in terms of referential integrity? (Other tables referring to these rows by ID)
heavywater
23-02-11, 17:10
I presume this isn't going to cause you any problems in terms of referential integrity? (Other tables referring to these rows by ID)
Nope, should all be independant I think, it's only sorted by ID, no fields are referred to directly.
Anyways, seems the issues were MySQL having a wobbly at some of the characters in the fields, exported with a CSV using pipes instead of commas/semicolons and it's working grand, I'm editing in Excel at the moment then saving back as a pipe seperated CSV.
PHP, read in all data from the XML or CSV file into an array, apply your ordering for the ID, pull all current data out of the DB, truncate it, merge the arrays, re-insert.
heavywater
24-02-11, 17:33
PHP, read in all data from the XML or CSV file into an array, apply your ordering for the ID, pull all current data out of the DB, truncate it, merge the arrays, re-insert.
But I needed to add almost 200 extra rows and have no idea how to manually input 600 fields by php without a lot of code and hassle.
Plus, if I'm dumping the data into an array, why not just read directly from the SQL table rather than from the CSV or XML?
heavywater
25-02-11, 01:03
Right something completely different this time but I can't be bothered to create a new thread.
I'm trying to create something that will show the max win streak for a given team. Basically, at the moment, it's something like this and doesn't even come close to working:
<?php
$query= mysql_query("SELECT * FROM results ORDER BY id DESC");
while ($r=mysql_fetch_array($query)){
$winstreak = 0;
$maxstreak = 0;
$result = $r["result"];
if ($result == "Win"){
$winstreak = $winstreak + 1;
if ($winstreak > $maxstreak){
$maxstreak = $winstreak;
}
}
else
$winstreak = 0;
}
?>
Not sure if it's something to do with the nested ifs (don't even know if you can do nested ifs...) or whether it's something else I'm being dim with... Basically, I've not a clue what I'm doing. :lol:
EDIT-
ZOMG
Set the variables inside the loop. :o
http://lolpics.se/pics/482.jpg
ZOMG
Set the variables inside the loop. :o
http://lolpics.se/pics/482.jpg
I lol'd.
Also, instead of using mysql_fetch_array(), use mysql_fetch_assoc(). The former is only useful if you want to iterate through it using numeric keys as it works like this:
Your query:
SELECT id, result FROM results
The table contains one set of data:
id: 1
result: "Win"
mysql_fetch_array() produces:
Array (
[0] => 1,
[1] => "Win",
[id] => 1,
[result] => "Win",
)
mysql_fetch_assoc() produces:
Array (
[id] => 1,
[result] => "Win",
)
Just a tip. :)
Also, not sure how your code works as it would appear to me only the last team to be iterated through will have their result stored as you reset max/winstreak to 0 on every iteration.
I think you want something like this:
<?php
$query= mysql_query("SELECT * FROM results ORDER BY id DESC");
$maxstreak = 0;
$winstreak = 0;
while ($r = mysql_fetch_assoc($query)) {
if ($r["result"] == "Win") {
$winstreak++;
if ($winstreak > $maxstreak) {
$maxstreak = $winstreak;
}
}
else {
$winstreak = 0;
}
}
?>
Unless I'm missing something.
heavywater
25-02-11, 13:59
Also, not sure how your code works as it would appear to me only the last team to be iterated through will have their result stored as you reset max/winstreak to 0 on every iteration.
Well, it doesn't reset $maxstreak at the end, just $winstreak. Then, if the current winstreak gets high enough again it'll start overwriting. It seems to be working fine at the moment. As for the teams this was just a general test to get the code working, the proper code has a slightly different query:
<?php $game = 'Call of Duty 4' ?>
<?php include ("Matchesselection.php"); ?>
And in Matchesselection.php is the:
<?php
$query= mysql_query("SELECT * FROM results WHERE game='$game' ORDER BY id DESC");
$winstreak = 0;
$maxstreak = 0;
while ($r=mysql_fetch_assoc($query)){
$result = $r["result"];
if ($result == "Win"){
$winstreak++;
if ($winstreak > $maxstreak){
$maxstreak = $winstreak;
}
}
else
$winstreak = 0;
}
?>
That associative thing looks handy, thanks. Also didn't know about the increment. :D
Well, it doesn't reset $maxstreak at the end, just $winstreak
While that is true, look at your original code. $maxstreak is reset to 0 at the start of the while loop every time. This means that when you add 1 to win streak, thus making it 1, then it will always be greater than max streak which is 0.
heavywater
25-02-11, 15:43
While that is true, look at your original code. $maxstreak is reset to 0 at the start of the while loop every time. This means that when you add 1 to win streak, thus making it 1, then it will always be greater than max streak which is 0.
That's what the facepalm was for, defining the variables inside the loop.
Powered by vBulletin® Version 4.1.12 Copyright © 2012 vBulletin Solutions, Inc. All rights reserved.