A table with 30 columns huh?
-
Comment -
Is the data you are trying to put into your database in CSV format, by chance?Comment -
Hmm...seems like a misunderstanding. To put it another way, how are you obtaining your rows of data that you want to go into your mySQL database? CSV file? Scraping from a website?
You answered that it was 'mySQL' format, which could mean an .sql file, which is basically just a text file that has a bunch of SQL commands in it. That doesn't seem like it would make sense in the context of this discussion.Comment -
30+ columns in a table... is a whole lot... especially it being a sports related database...Comment -
I'm sorry. I wasn't clear enough. It is a .sql file that I'm going to run. I usually prefer .csv but this time it didn't work out that way.
I'm not using 60+ fields when I construct my model. It's just that, for the purposes of aligning data from separate sources and whatnot, I prefer keeping the extra fields for now.Comment -
Good luck with thatComment -
Sorry but another problem came up that is really specific and impossible to search for.
When I try to add the new values it isn't adding them alongside the old ones. Like, say I originally had 5 columns and 30 rows. Then I added 5 new columns and that went smoothly, but when I try to add 30 values that correspond to the 5 new fields it adds them at the bottom by adding 30 new rows.Comment -
are you INSERTing or UPDATEingComment -
It seems he's trying to update the old records using INSERT
illfuuptn, you need to use UPDATE and identify the rows to be updated. If you don't have some specific ID, you can use date, home, away as identifiers. So PHP code will look like this:
PHP Code:mysql_query("UPDATE mlbdata SET col25='$d[10]',col26='$d[11]',col27='$d[12]' WHERE date='$date' AND home='$home' AND away='$away'");Comment -
Thank you for the reply. But, since there's a few hundred-thousand rows in my db, how do I do this? Is there a way to say via code "start at row 1 and continue on down."?It seems he's trying to update the old records using INSERT
illfuuptn, you need to use UPDATE and identify the rows to be updated. If you don't have some specific ID, you can use date, home, away as identifiers. So PHP code will look like this:
PHP Code:mysql_query("UPDATE mlbdata SET col25='$d[10]',col26='$d[11]',col27='$d[12]' WHERE date='$date' AND home='$home' AND away='$away'");Comment -
You could load the the data into an array and then use foreach (http://www.tizag.com/phpT/foreach.php)Comment -
literally all I want to do is take table 2 and put it alongside table 1. I keep searching but I can't find the answerComment -
Do you have a way of converting table 2 into CSV? You could always export table 1 from phpmyadmin as a CSV file, load it into Excel, open table 2 in Excel, copy and paste the table 2 data into the table 1 spreadsheet, export as a CSV, then use this site to generate SQL statements from the newly combined CSV file:
Some versions of phpmyadmin will allow you to import the CSV without using the above site. It depends on your version.Comment -
The tables are way too big for excelComment -
An Excel spreadsheet can go over a million rows starting with Excel 2007. Whether you be able to actually open and use it, I'm not sure; it would depend on your computer. I've opened spreadsheets with tens of thousands of rows before and it worked, albeit a bit slowly.
Other than that, the only other way I can think of doing it would be with PHP loops, such as foreach which I mentioned above. It's actually not at all hard to do (and this is coming from someone who is NOT a genuine programmer, rather someone who has learned just enough to hack his way through using PHP and mySQL), and spending an hour to learn it now would save you many, many hours later, as you can use loops to do a shit ton of different things regarding databases. That said, if your database really does have hundreds of thousands of rows, it would take a pretty long time for the PHP script to complete, so you might need to adjust your max_execution_time setting to keep the script from timing out.
Someone else might know another way. This thread is tiresome.Comment -
I suppose your scraper will be running and updating the table. Since the O(n) of UPDATE is probably not much worse than linear, you don't need to optimize the process. Most of the execution time will be spent on http connections unless you don't parallelise.Thank you for the reply. But, since there's a few hundred-thousand rows in my db, how do I do this? Is there a way to say via code "start at row 1 and continue on down."?Comment -
What if I did something like:
UPDATE TABLE1 ADD FIELDS ...... so then I have the new fields in. I can already do this no problem.
Then for the values in the new fields which are named, for example, fields 30 through 35:
INSERT INTO TABLE1(field 30,field 31,field 32,field 33,field 34,field 35) VALUES(here is where I comma-separate 180 values if I have 30 rows. 6 fields*30 rows) WHERE field 1 NOT NULL
I said field 1 not null because that would make every field true. I feel like I'm close with this but not there for sure.
OR
What if I convert the 2nd table(fields 30-35 and their values) into a .csv and then use some sort of mysql statement to insert it. I know jgil mentioned .csv and excel but is there a way I can do it without excel so it's easier to automate down the road?Comment -
How many times do you need to be told the same fukking thing?
jgilmartin told you exactly what your options were. If you're not willing to learn the shit on your own, then fukk off. Nobody is going to do your work for you. And you have absolutely ZERO chance of creating any type of profitable model when you're not even willing to learn the basics of building a simple database on your own.
READ A MOTHERFUCKING BOOK, DUMBASS.Comment -
LOLComment -
And the iron fist of MonkeyFocker slams down!!Comment -
Go with a AWS (amazon) server and scale up as your needs build.
PHP/MySQL - save yourself time and money and hire a 'good' offshore developer who has good timely communication, track record to back up his work and a good price per hour.
Its worked well for me when I was where you were at 5 years ago.
Comment
