How to pull product datafeeds using PHP and MYSQL and display into your Webpage
Affiliate Project recently received a comment on this post – Niche site launched using data feeds and decided to create this post to help any other affiliates with displaying product data feeds.
We initially struggled to get product data feeds working. We found it hard to find how to get this to work, so thought we would document this on our blog and this could also help other affiliates.
There was 3 basic steps to get the data feeds to be displayed on the page.
Before you do this, you will need to download your data feeds and then create a new database using phpmyadmin (often from cpanel tools). Create the field names as per your datafeed or merchants datafeed.
1st step:
Create a php page template. So in the php file you will specify template name, pull in the header and title, then when you login to wordpress and add a new page, you can specify this new page template (your_template_name).
EXAMPLE:
<?php
/*
Template Name: your_template_name
*/
?>
<?php get_header(); ?>
<div id="content">
<span class="breadcrumbs">Browse: <strong><a href="<?php echo get_option('home'); ?>/">Home</a> » Your Template Name</strong></span>
<h2 class="title"><?php the_title(); ?></h2>
<?php
2nd step:
Put the php code that has the mysql statements that query the database which holds the datafeed
EXAMPLE:
// Connect to Database
$con = mysql_connect("localhost","database_name","database_password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// Select the Database
mysql_select_db("database_name", $con);
// Select the data from table_name
$result = mysql_query("SELECT field_a, field_b, field_c, field_d, field_e FROM table_name WHERE field_f='category_name'");
3rd Step:
Display the data from the database in table format, then loop the products. After that close the connection and display the sidebar and the footer
EXAMPLE:
// Display the table
echo "<table border='0' cellspacing='16'>";
// Put results into table
$counter = 0;
while($row = mysql_fetch_array($result))
{
echo "<td WIDTH='112' align='left' valign='top'><a href=\"" .$row['field_c'] . "\">
<span class='producttable'><img width='140' src=\"" . $row['field_d'] . "\" border=1 alt=\"" . $row["field_d"] . "\" title=\"" . $row["field_b"] . "\"></span>
<br><span class='producttext'>$row[field_a]</span></br><br>$row[field_b]</br></span></a>";
echo "<br><span class='producttext'><strong>£$row[field_e]</strong></span></br>";
$counter++; # increment count every iteration
if ( $counter % 3 == 0 )
{
echo "<tr />";
}
}
echo "</td>";
echo "</table>";
// Disconnect from Database
mysql_close($con);
?>
</div>
<?php get_sidebar() ?>
<?php get_footer() ?>
You can style the output how you wish with css styling.
w3schools.com is a great resource for website coding such as html / php / mysql.
Hope this helps other affiliates.
Similar Posts:
- None Found
Comments
17 Responses to “How to pull product datafeeds using PHP and MYSQL and display into your Webpage”
Leave a Reply
-
Sponsors / Adverts
Cheers for the above guys, I will give it a try on a site I have in mind.
Do you need to clean the datafeeds prior to adding theem to your database i.e. remove unrelated items in the feeds etc or can they all be added to the database and filtered from within their?
As you can tell I have not really used datafeeds much in the past.
Darren, glad we have been of assistance. Hope it is useful for you. In response to your question it is really up to you whether you want to clean the product feeds or add them in as a whole. Basically what you will do will depend on how easy it is to structure SQL statements to pull out the relevant information from the database once the product feed is in there and how the product feed is structured. For example if the product feed is neatly done with a specific category field and you can query using SQL and pull out specific parts of the product feed using the field that already exist then this is fine. However if your looking for something quite specific in the product feed you might want to manually pull out only the products you want and only upload those into the database and maybe add your own field to categorize the products and query on that field.
Another factor is the size of the product feed as this will obviously effect the size of your database. If you are pulling down a huge product feed but only want a few items is it worth having all the other products in the database.
Hope this helps and do not hesitate to contact us again should you have any further questions.
Thanks for this, this is something I have been meaning to have a go at for ages! Just one question how did you get the data from the feed into the database? is it just copied and pasted?
Hi Chris, you just need to go to phpMyAdmin if you use Mysql, usually found in Cpanel hosting. From here you first create your table with the exact field names then you will just need to click Import and select your csv or xml feed supplied from the network. However, you may want to first filter out rows from the feed that are no related. You can then use different mysql select statements to pull out what data you want.
Hope this helps
Thanks
Gary
Thanks Gary,
One more thing, when you want to update your feeds do you just drop the data then re-import it?
Cheers,
Chris.
Hi Chris,
Yes we are just dropping the contents and replacing with the new data each time at the moment.
I’m sure there are probably better ways of doing this, but it works for us until we can find a more automated way to do this
Cheers
Gary
Hi Gary
I have recently tried to use the above on a new site but ran into a problem.
Do you have any advice on setting up a table within phpMyAdmin i.e. what needs to go in the different sections for each field (Field, Type, Collation, Attributes, Null, Default, Extra)?
I assume you just need to create a new field for each column in your feed that matches each column name?
If you know of any useful online resources then that would be great.
Thanks
Darren
Hi Darren, sorry for the late reply, its been so busy, getting some new sites down and doing lots of content for our sites.
I used this site as a reference for understanding the different fields:
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html
You will just need to create field names and match the number of fields to the number of columns from your datafeed csv file from the network. You don’t need all the network datafeed columns though.
You will also need to make sure the order of the fields match to make it easy to automate updating the table.
Hope this helps
Gary
Hi Gary
Thanks for the update. I will take a look at the reference provided.
How do you deal with different networks feeds, I assume they would be in different formats and therefore not fit in the table created?
Feel free to email me if its easier.
Thanks
Darren
Hi Darren, for different network feeds you will need to put their datafeeds into different tables in your mysql database. Try to keep the main field names to same as the other tables because when it comes to displaying these these $rows from php echo it will make things easier. Also not sure that you can union with different field names!
Also the field names don’t need to match the names of the csv columns from the datafeed. Only the number of fields need to match the number of columns.
Hope this makes sense
Cheers
Gary
I just wanted to leave a message to say a big thankyou for this post and follow up replies. Using this as a basis i managed to eventually display products from one database table on a test page, then after further research and tinkering i am now at a point where i’m displaying products from 4 different feeds, all arranged by price and a few other condtions, albeit heavily modified from your original example now.
I’m nearly ready to go site-wide with it, just a bit more tarting up to do and then it will just be a case of finding out how to automate a bit more the updating of the database.
I’ve thoroughly enjoyed the whole challenge and want to say thanks for the inspiration to make a start!
Glad you managed to get it working stuart. Would be good to compare scripts at some point mate. I look forward to seeing this updated in your site and I must say its a very nice design there.
Cheers bud, not sure my script would be any better than yours though, but drop me a line later and i’ll link you to the page i’m working on.
Think i’ve figured out a way to get merchant logos displaying so will implement and test that out later too.
[...] up his product data feeds into his niche websites using PHP and MYSQL. He found us through our How to pull product datafeeds using PHP and MySQL and display into your Webpage post. Since helping Stuart with some basic guidance,he has gone on and very quickly figured out [...]
This is awesome stuff guys! I’ve often wondered the easiest way to work with data feeds and this seems to be it. This is definitely something I am comfortable tinkering with so thanks for sharing this info!
Great script – thanks for this!
I was worried about having to edit lots of pages if i ever wanted to change the layout at all. So I’ve done a bit of tinkering, and rather than hard-coding the sql query in to the page template and having a template for each page, I’ve created a new custom field for pages in Wordpress, called sql-query, to store the sql in. You can then retrieve the query from the wordpress database to put in to your script as follows:-
Replace this:-
// Select the data from table_name
$result = mysql_query(”SELECT field_a, field_b, field_c, field_d, field_e FROM table_name WHERE field_f=’category_name’”);
With:-
// Select the data from table_name
$result = mysql_query(get_post_meta($post->ID, “sql_query”, true));
Adding a custom field to a wordpress page is really easy… if you edit a page, under the main html editor for the content, there’s a section called Custom Fields. Just enter ’sql-query’ in the Name box, and enter your sql query in to the value. Press Add and that’s it. The next time you want to add the custom field to a page, you don’t need to type the name in, just select it from the drop down box instead. Simples!
This means I now only need 1 template and can create as many pages as I like from that page, but still with a different selection of products from the datafeed – it’s great if i ever need to change the layout of the page – only one template to edit!
Taking it a bit further, I was a bit conscious about having duplicate content on my page as it would be purely data from the datafeed which will no doubt be shown on thousands of affiliates’ sites worldwide.
So I then went about putting the original page content back in to the template, so that I can add text to the page as you would with any normal wordpress page.
The code you need to enter in to your template will be slightly different depending on your theme, but it should start something like this:-
You can put it in the template wherever you like – before the feed or after the feed – up to you. But if you then write perhaps 200-300 words about that page, it should give Google the unique content it likes to see.
This post really helped me, so I hope this helps others!
Tom
Hi Tom,
Good idea and thanks for sharing. Glad you liked the post and it seems to have helped quite a few people out there.
Cheers
Gary