Database query to post/page - GoFuckYourself.com

lollipopx · 05-19-2014, 12:04 PM

Ok, here goes another one of my try explain something rambling questions ....
database query to post/page?
ok, so I go to affiliate to hosted galleries tools, select category etc. hit button says show dump, a whole lot o code is generated, click save dump, save as Coma Separated Values or CSV or Tabbed CSV, open said code in excel( match my database perimeters to the excel ones in top cell row), save and upload to my websites database via cpanel/mysql or mysql wizard, open phpmyadmin, database is in left side panel, select it, it opens in the database on right side so I see its there. clicked on just about every button in there I thought maybe relevant to generate or populate to site...
I have read and read and read for days on how to query to post to the point my eyes are killing me. Tried a bunch of different query codes to no avail.

Now funny story. Just the other day I somehow accidently managed to populate over 2000 post without realizing it. Unfortunately they were all empty as the database table wasn't matched up or something, So I had to delete them all. To top it off, I have no damn clue how the hell I did it to repeat it with a proper database table.
I feel like I am missing some minute little detail somewhere to trigger this

I tried posting in text and view in WP post editor the codes I found for query...no luck
Tried to figure how to post shortcodes or the phpgenerated shortcodes ...no luck

I should be able to generate/populate this to selected post or category page from database but HOW?

qnjgoldman · 05-19-2014, 02:23 PM

Are you sure your WordPress installation is looking at the correct database? And that the user/password specified is actually valid for reading/writing to that database? You'll find that information in wp-config.php.

If you're exporting from a WP database and importing to WP, there's no reason that it should fail to recognize the data. Are you getting any kind of errors?

Also, you may want to hire someone to manage your site for you (a DBA may be prohibitively costly and unnecessary, but a server management company might be able to do the job on the cheap).

lollipopx · 05-19-2014, 07:19 PM

ugghh my brain hurts.

ok so where is wp-config.php to access it. couldn't find it in theme edit.

I am attempting to import to category post/page from affiliate csv dump file, so I have no idea if from a wp database.

as far as hire someone, I am disabled so money to hire is on a no can do yet bases. But I am determined to make this work somehow.

stonehammer · 05-19-2014, 08:05 PM

why do u need to use sql to post something in wordpress...

just do it the normal way from the admin panel...no wonder ur themes keep going haywire

u dont need to populate ur blog with 1000 posts of sponsor content

lollipopx · 05-20-2014, 01:23 PM

Quote:

Originally Posted by stonehammer

why do u need to use sql to post something in wordpress...

just do it the normal way from the admin panel...no wonder ur themes keep going haywire

u dont need to populate ur blog with 1000 posts of sponsor content

ok first off, posting from in wordpress 1 post at a time SUCKS. especialy when facing over oh lets say average of 700+ pages with 40 post per page per category...how many categories is there in porn anywho?....someone shoot me please... per affiliate...

ohhh and lets add to it the almost daily emails from all billion affiliates with even more new content additions...per category etc...

"u dont need to populate ur blog with 1000 posts of sponsor content" who says?
ok, if you have enough GOOD content why anyone wanna go else where than your site?

a lot of affiliates pay per click as well as per sign up, rev share etc....
so why not put their affiliate content up get the traffic flowing and make some dough for you AND your affiliates.

simple math....I think...lets say oh 10,000 clicks x .002 per click = $20.00 not bad if per day once traffic is flowing. now lets say you generate 20 subdomains with anything from porn to cotton candy affiliations....and they all produce $15-20 per day per site through sales as well as clicks.

who the hell needs a job?
that's a rhetorical question.

and a lollipopx tip free, what else is legal that can generate good fast revenue on line? candy, everyone loves candy. thank me later. Halloween is just a few months away, get ready.

big thing for me is figure out datadumps datafeeds which ever WITHOUT effin up my site.

gonna make a back up right now. maybe 3 or 4 just in case lol.

"no wonder ur themes keep going haywire"
uh no, we done figured that out. its due to the self adjusted <iframe> embeds from affiliates that didn't fit so I adjusted there iframe pixel dimensions so no one would have to scroll in side of site to view content. so I had to adjust the style sheet html parameters to move parts of the table around so there was no overlaps, for the most part.
Retail management 101: keep products in simple view. less thinking on consumers part the faster the sale. therefore no scrolling inside of frame is exceptable.

until I figure out how to get this right guess that's how I'm flying. anyone wanna join mile high club? < another rhetorical question and I thought it was funny

the plan would be to be able to add affiliate datafeed/datadumps/galleries to a templated to match server excel spreadsheet then upload easy cheesy to update weekly each category with ALL affiliate category specific code...pls tell me someone understood that.
or use data feeds that auto update. another one for me to figure out.

either this gurl has had too much rum or not enough yet. At this moment I am just happy hostgator was able to restore what I do have almost semi working.

stonehammer · 05-20-2014, 02:44 PM

i think your going haywire

good luck with your site then, sounds like alot of work to get it done.

lollipopx · 05-20-2014, 06:04 PM

Quote:

Originally Posted by stonehammer

i think your going haywire

good luck with your site then, sounds like alot of work to get it done.

lol your probably right

lollipopx · 05-28-2014, 02:16 PM

Ok, so what do I change here to add this and how do I add this to page /post?

Barry-xlovecam · 05-31-2014, 06:52 PM

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Just make the data for the table fields from the excel to a CSV file
FTP the CSV file to the server location that you want (remote server)
Open a terminal or ssh (remote server) windows putty.exe works well in linux use ssh direct from the terminal.

Use mysqlmonitor.

example:

Code:

~$:mysql -u userName -p
Enter password:*******

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TUTORIALS          |
| mysql              |
| mydatabase               |
+--------------------+

 mysql> use mydatabase;

Database changed

mysql> load data LOCAL infile '/home/user/zip_data/zip5.csv' into table zip_location_data fields terminated by ',' lines terminated by '\n' (zip, city, state, lat, lng, county);
Query OK, 41755 rows affected (0.13 sec)
Records: 41755  Deleted: 0  Skipped: 0  Warnings: 0

That fast enough

You need to make the fields in the csv file's lines in the scope of the table's fields (the field's column names) that you are loading the data into.

if you select the database then
mysql>show tables;

mysql>describe 'tableName';

that will give you the field name and schema.

example:

Quote:

mysql> use mydatabase;

Database changed

mysql> show tables;
+-------------------+
| Tables_in_mydatabase |
+-------------------+
| ***** |
| **** |
| ***_**** |
| sic |
| zip_location_data |
+-------------------+
5 rows in set (0.02 sec)

mysql> describe zip_location_data;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| zip | varchar(60) | NO | | NULL | |
| city | varchar(80) | NO | | NULL | |
| state | varchar(80) | NO | | NULL | |
| lat | float(10,6) | NO | | NULL | |
| lng | float(10,6) | NO | | NULL | |
| county | varchar(80) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)

You will notice in the CSV fields descriptors above that 'id' is omitted -- it is auto_increment, the mysql server makes the entry: 1,2,3, ...400 etc id is the primary key for look ups etc. .

A wordpress installation test I did (older version)

Code:

mysql> use planet;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_planet         |
+--------------------------+
| wp001_commentmeta        |
| wp001_comments           |
| wp001_links              |
| wp001_options            |
| wp001_postmeta           |
| wp001_posts              |
| wp001_term_relationships |
| wp001_term_taxonomy      |
| wp001_terms              |
| wp001_usermeta           |
| wp001_users              |
+--------------------------+
11 rows in set (0.00 sec)

mysql> describe wp001_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(20)         | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | text                | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+
23 rows in set (0.00 sec)

mysql> select * from wp001_posts;
+----+-------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------
| ID | post_author | post_date           | post_date_gmt       | post_content|ping_status | post_password | post_name   | to_ping | pinged | post_modified       | post_modified_gmt   | post_content_filtered | post_parent | guid | menu_order | post_type | post_mime_type | comment_count |
----------+----------------+-------------+---------------+-------------+---------+--------+---------------------+---------------------+-----------------------+-------------+------------------------------------------------------------+------------+-----------+----------------+---------------+
|  1 |           1 | 2011-05-11 18:29:53 | 2011-05-11 18:29:53 | Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!  | Hello world! |              | publish     | open           | open        |               | hello-world |         |        | 2011-05-11 18:29:53 | 2011-05-11 18:29:53 |                       |           0 | http://www.****.com/wordpress_design/?p=1       |          0 | post      |                |             1 |
too long:(
5 rows in set (0.04 sec)

mysql>

Have fun

robber · 06-10-2014, 03:30 PM

Hi,

If you're still having issues trying to get this to work, then drop me an email and I will see what I can do for you, depending on how you have the feeds setup it could be something as simple as converting the csv file into an mysql statement you can run against your database, or loading in a script which will do it for you and apply the changes to your database, to display your required content.

If you're going down the mysql statement route, you would be looking at something like

"Insert into ##tablename## ('field1','field2',....) Values ('row1-value1','row1-value2'...),('row2-value1','row2-value2'...),....."

you can create this as an output for you to copy and paste into phpmyadmin or run it directly with a PHP to give you the desired effect.

To build the file, you want to read the csv file, split out the rows and then the fields to provide you with an array which you can then build the datafeed with, to make it flexible to different providers feeds you could have headers on the first row and then match the fields up that way, if you wanted to only include specific ones then add the additional logic for this, or write the post content you need on the fly using the details.

The choice as to how you handle this is all your choice, as there are alot of different methods you could employ.

Kind Regards

Rob
FD Networks

lollipopx · 06-12-2014, 07:47 PM

Thanks for the information. I have been away busy but hope to get back on this and try to make heads and tails of this soon.

Fetus · 06-14-2014, 04:53 PM

Hey Guys I been trying to help lollipopx on this small problem. I don't have enough experience with php and mysql to answer his questions on my own. We have gotten it to the point where all the info is in the database (DB) and we can display the individual DB entries in wordpress as individual posts, which is not the desired effect. we want to display the video entries in the DB as a "tube" style post in wordpress with thumbnails for there respective video.

I believe we are over complicating this procedure. I think we have to add a little php code within the wordpress post area to call or "query" and display the DB as a video "tube". (hopefully that makes some sense.)

If my question was already answered in the previous posts and maybe i have missed or overlooked something I apologize for that.

(lack of experience is clearly the culprit here)

lollipopx · 06-15-2014, 07:11 AM

Quote:

Originally Posted by robber

Hi,

If you're still having issues trying to get this to work, then drop me an email and I will see what I can do for you, depending on how you have the feeds setup it could be something as simple as converting the csv file into an mysql statement you can run against your database, or loading in a script which will do it for you and apply the changes to your database, to display your required content.

If you're going down the mysql statement route, you would be looking at something like

"Insert into ##tablename## ('field1','field2',....) Values ('row1-value1','row1-value2'...),('row2-value1','row2-value2'...),....."

you can create this as an output for you to copy and paste into phpmyadmin or run it directly with a PHP to give you the desired effect.

To build the file, you want to read the csv file, split out the rows and then the fields to provide you with an array which you can then build the datafeed with, to make it flexible to different providers feeds you could have headers on the first row and then match the fields up that way, if you wanted to only include specific ones then add the additional logic for this, or write the post content you need on the fly using the details.

The choice as to how you handle this is all your choice, as there are alot of different methods you could employ.

Kind Regards

Rob
FD Networks

ok so like I have test category(amateur) test csv data in excel and uploaded to test server database in sub domain of my main.
So what "script" is needed to place or call it on individual category page? not as individual posts but as like tube/tabled style per category.

robber · 06-24-2014, 09:00 AM

Hi lollipopx,

You want to do that with your theme, you need to either find one which offers that functionality or build in the required categories page, this goes down the route of wordpress theme development, which isn't overly complicated if you know what you're looking for, to display the whole lot in one long line, you want to basically loop through and build the table as you're going.

depending on how much knowledge you have wordpress codex (it's a site) might give you some pointers about what you need to be able to get the desired effect. If you are stuck then I could give some pointers on it, but I'll need to read up on it first

Rob
FD Networks

05-19-2014, 12:04 PM	#1
lollipopx Confirmed User Industry Role: Join Date: Apr 2014 Posts: 171	Database query to post/page Ok, here goes another one of my try explain something rambling questions .... database query to post/page? ok, so I go to affiliate to hosted galleries tools, select category etc. hit button says show dump, a whole lot o code is generated, click save dump, save as Coma Separated Values or CSV or Tabbed CSV, open said code in excel( match my database perimeters to the excel ones in top cell row), save and upload to my websites database via cpanel/mysql or mysql wizard, open phpmyadmin, database is in left side panel, select it, it opens in the database on right side so I see its there. clicked on just about every button in there I thought maybe relevant to generate or populate to site... I have read and read and read for days on how to query to post to the point my eyes are killing me. Tried a bunch of different query codes to no avail. Now funny story. Just the other day I somehow accidently managed to populate over 2000 post without realizing it. Unfortunately they were all empty as the database table wasn't matched up or something, So I had to delete them all. To top it off, I have no damn clue how the hell I did it to repeat it with a proper database table. I feel like I am missing some minute little detail somewhere to trigger this I tried posting in text and view in WP post editor the codes I found for query...no luck Tried to figure how to post shortcodes or the phpgenerated shortcodes ...no luck I should be able to generate/populate this to selected post or category page from database but HOW?

05-19-2014, 02:23 PM	#2
qnjgoldman Registered User Industry Role: Join Date: Apr 2014 Location: Los Angeles, CA Posts: 6	Are you sure your WordPress installation is looking at the correct database? And that the user/password specified is actually valid for reading/writing to that database? You'll find that information in wp-config.php. If you're exporting from a WP database and importing to WP, there's no reason that it should fail to recognize the data. Are you getting any kind of errors? Also, you may want to hire someone to manage your site for you (a DBA may be prohibitively costly and unnecessary, but a server management company might be able to do the job on the cheap). __________________ QuadraNet - Dedicated Servers, Colocation, and Cloud Hosting Los Angeles, CA \| Dallas, TX \| Miami, FL We make amazing deals! Email [email protected]

05-19-2014, 08:05 PM	#4
stonehammer Confirmed User Join Date: Feb 2008 Posts: 1,430	why do u need to use sql to post something in wordpress... just do it the normal way from the admin panel...no wonder ur themes keep going haywire u dont need to populate ur blog with 1000 posts of sponsor content __________________ GFY Educational Series: Buying Skimmed Traffic for Websites

05-20-2014, 02:44 PM	#6
stonehammer Confirmed User Join Date: Feb 2008 Posts: 1,430	i think your going haywire good luck with your site then, sounds like alot of work to get it done. __________________ GFY Educational Series: Buying Skimmed Traffic for Websites Last edited by stonehammer; 05-20-2014 at 02:47 PM..

05-28-2014, 02:16 PM	#8
lollipopx Confirmed User Industry Role: Join Date: Apr 2014 Posts: 171	Ok, so what do I change here to add this and how do I add this to page /post? __________________ Lollipopx.com Twitter @lollipopx_1, Tumblr lollipop-x, ICQ 669068563, Skype lollipop_130 Tell me your from gfy or i might ignore you.

05-19-2014, 07:19 PM	#3
lollipopx Confirmed User Industry Role: Join Date: Apr 2014 Posts: 171	ugghh my brain hurts. ok so where is wp-config.php to access it. couldn't find it in theme edit. I am attempting to import to category post/page from affiliate csv dump file, so I have no idea if from a wp database. as far as hire someone, I am disabled so money to hire is on a no can do yet bases. But I am determined to make this work somehow.

06-10-2014, 03:30 PM	#10
robber Web Developer Industry Role: Join Date: Jan 2011 Location: UK Posts: 264	Hi, If you're still having issues trying to get this to work, then drop me an email and I will see what I can do for you, depending on how you have the feeds setup it could be something as simple as converting the csv file into an mysql statement you can run against your database, or loading in a script which will do it for you and apply the changes to your database, to display your required content. If you're going down the mysql statement route, you would be looking at something like "Insert into ##tablename## ('field1','field2',....) Values ('row1-value1','row1-value2'...),('row2-value1','row2-value2'...),....." you can create this as an output for you to copy and paste into phpmyadmin or run it directly with a PHP to give you the desired effect. To build the file, you want to read the csv file, split out the rows and then the fields to provide you with an array which you can then build the datafeed with, to make it flexible to different providers feeds you could have headers on the first row and then match the fields up that way, if you wanted to only include specific ones then add the additional logic for this, or write the post content you need on the fly using the details. The choice as to how you handle this is all your choice, as there are alot of different methods you could employ. Kind Regards Rob FD Networks

06-12-2014, 07:47 PM	#11
lollipopx Confirmed User Industry Role: Join Date: Apr 2014 Posts: 171	Thanks for the information. I have been away busy but hope to get back on this and try to make heads and tails of this soon. __________________ Lollipopx.com Twitter @lollipopx_1, Tumblr lollipop-x, ICQ 669068563, Skype lollipop_130 Tell me your from gfy or i might ignore you.

06-14-2014, 04:53 PM	#12
Fetus Registered User Industry Role: Join Date: Jun 2014 Posts: 1	Hey Guys I been trying to help lollipopx on this small problem. I don't have enough experience with php and mysql to answer his questions on my own. We have gotten it to the point where all the info is in the database (DB) and we can display the individual DB entries in wordpress as individual posts, which is not the desired effect. we want to display the video entries in the DB as a "tube" style post in wordpress with thumbnails for there respective video. I believe we are over complicating this procedure. I think we have to add a little php code within the wordpress post area to call or "query" and display the DB as a video "tube". (hopefully that makes some sense.) If my question was already answered in the previous posts and maybe i have missed or overlooked something I apologize for that. (lack of experience is clearly the culprit here) Last edited by Fetus; 06-14-2014 at 04:55 PM..

06-24-2014, 09:00 AM	#14
robber Web Developer Industry Role: Join Date: Jan 2011 Location: UK Posts: 264	Hi lollipopx, You want to do that with your theme, you need to either find one which offers that functionality or build in the required categories page, this goes down the route of wordpress theme development, which isn't overly complicated if you know what you're looking for, to display the whole lot in one long line, you want to basically loop through and build the table as you're going. depending on how much knowledge you have wordpress codex (it's a site) might give you some pointers about what you need to be able to get the desired effect. If you are stuck then I could give some pointers on it, but I'll need to read up on it first Rob FD Networks