Displaying data from database in html text using php

Beginners - Learning PHP
#1

I’m building a website for a housing developer. They have an XML doc on their server which is updated once daily, I use a small php script to update this the same way as cron would and all is well there, the XML file updates just fine. Using a tutorial I found on Disqus have managed to create an add.php file which takes the XML feed and updates a MySQL database, again, this seems to work just fine. Given the time constraints and my lack of php experience I don’t feel comfortable building a database driven website but, because the house/property type prices regularly change, I would like to use a snippet from the database that displays the up-to-date price within the text of the page. Each house type has a unique ID so this snippet would need to reference that (but not display it). In addition to that the database obviously needs to be updated so do I/how do I incorporate that when the specific property page is loaded in the browser?

Any help will be most appreciated.

#2

You need to add more info about how the site works. If it’s not backed by a db currently then how do you show spesific property pages? Do you generate these and create actual files for them?

#3

Static pages in a responsive website, this method works fine because although developments have 30+ properties, the house ‘types’ have banded prices. The market dictates that these prices can rise and fall so I was merely hoping to put that side of things in the hands of the client (I’m drawing this information from a data feed they produce, via a third party, for numerous house buying portals - it comes to me via an XML feed which is updated daily from the the clients own inputting, they don’t seem keen to update their website guy at the same time to input these manually!).

#4

What worries you about a database driven site?

Personally, I would have a cron that loads the XML into the database every night. Read a PDO primer to get a bit more comfortable with database interaction in general and move from there. If you have something that literally can change over night, the database is likely where you want to move too regardless, unless you want to pull that data from the XML document?

#5

The XML feed is updated automatically every night, that’s fine, my server admin didn’t have a cron (script ?) to do the update so they wrote me a php script which works in a similar fashion I guess.

To update the mysql database I currently need to physically open a php page in my browser which then adds the XML info to my database (clunky, but doable - I’d like this to be automated but can live with it at this juncture - maybe this could be integrated into the webpages so that the user initiates it, I’ve seen ‘include’ mentioned elsewhere, maybe that is the solution…?).

One problem I have encountered is that although the database updates, it adds information rather than replaces which means that the same 15 or so items are repeated. I read something about a primary key which suggested I add another row to my database with sequential numbering, I have done this but it now only displays new entries and omits the earlier ones. As each property has a unique number I wonder if I can use that as my primary key?

BTW database driven sites intimidate me rather than worry me, I’m from a design background and anything virtual just baffles!

I also find the jargon used in php (or javascript to a degree) coding more of a disability than actually getting on with the work, there also rarely seems to be any definitive answers to queries which I find annoying, along with the fact that a misplaced semi-colon can have you trying to fathom something out for a week!

#6

All a cron script is, is a script that is run on a timer. So, you need a XML file parsed every night. You would setup a script to do that task, and then tell the cron to run it every day/night at say 1AM. It would do so. Language doesn’t matter, it is just an automated process.

As far as the duplicate data, you could do what is called an upsert in MySQL. It is basically a merge statement. Insert if these values don’t match, if they do update the record.

#7

Thanks for your help so far. If I understand correctly than I can automate the add.php page to update my database instead of doing it manually? Sounds good!

I’ve done a quick ‘google’ for UPSERT and it’s mindblowing! Would I do this in phpadmin or add the command/code into my add.php page? It does sound as though that it is what I need because the XML feed is updated in its entirety so, ideally, the database should be too (so anything that isn’t on the current XML is deleted from the database, anything that is on the XML feed that remains the same as previous remains on the database and any new additions on the XML feed are added to the database. Does that sound feasible?

#8

Here is a possibility. Add an additional field, isActive. When you query in the display, you add a filter

[php]where isActive = 1[/php]

Begin the file and auto set the entire table to isActive = 0;

Now, as you read thru the XML file and do your updates, you change the flag to isActive = 1

That way, you keep track of all the properties and have a historical record of all the properties you have ever stored, rather than doing a hard delete.

Sponsor our Newsletter | Privacy Policy | Terms of Service