9 replies to this topic

[PaulD]

Hi all,

I am trying to access my wordpress database to get the 5 most recent posts for my etomite homepage. I am using getExtTableRows function.

// get data
$host="localhost"; 
$user="my username";
$pass="my password";
$dbase="my database name";
$fields="*";
$from="wp_posts";
$where="post_status='publish'"; 
$sort="post_date";
$dir="DESC";
$limit="5";
$push=true;
$results = $etomite->getExtTableRows($host,$user,$pass,$dbase,$fields,$from,$where,$sort,$dir,$limit,$push); 

I am getting a mysql error reporting a failed query. The database is getting connected to fine (it seems) but the query is failing. The table is named correctly, and the field names etc are all correct too (all default wordpress tables and content).

Am I doing something silly that I just cant see. Have been fiddling with this all night but with no luck.

Any suggestions would be most welcome - thank you.

Paul.

[mikef]

I do this using an appropriate RSS feed from the blog. This has the advantage that I can use the same code on other sites that aren't in the same account, and should survive any major upgrade from WordPress that changes the database structure, and can select on tag or category too.

The snippet I'm using is based on the lifestream snippet, and needs simplepie to be installed too. (Example on mike finley photography (home and news pages) extracting some content from the Rogue Gene Collective blog.)

Edited by mikef, 29 June 2009 - 10:03 AM.

[PaulD]

Hi Mike,

Thanks for that suggestion and I had been thinking about that - although I have had probs with rss readers in the past, although I will look again at it.

I really want to be able to read the external database though.

I have put just this in a snippet (to try another way of doing it) but again it throws an error.

// get data
$host="localhost"; 
$user="my username";
$pass="my password";
$dbase="my db name";
$query="SELECT * FROM `wp_posts` WHERE post_status='publish' LIMIT 5";
$results = $etomite->dbExtQuery($host, $user, $pass, $dbase, $query); 

I know my query statement is correct as I can run it on phpmyadmin and it works fine.

The only solution I can think of is to install wordpress on the same database as etomite, although when I then add a forum and a shop this will be a very messy way of doing it IMHO.

Thanks again though, I shall be reading the links you posted now.

Paul.

PS with the above snippet I get this error now:

Etomite encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed »
      SQL: SELECT * FROM `mydatabase`.etomite_site_snippets WHERE `mydatabase`.etomite_site_snippets.name='MenuBuilder';

Although I suspect this is a 'knock on' error by the snippet failiure. When I don't include the snippet on the page everything is fine again. (I have replaced database name with 'mydatabase' of course, but the name here was to my etomite database, not the wordpress one.)

Mysql error reporting on the page actually now has

Query 9 - 0.0009 s
SELECT * FROM `wp_posts` WHERE post_status='publish' LIMIT 5

I think this means that the query worked?

Oh I am confused. I ALWAYS get stuck with sql statements, they really are not my strong point. Any further help would be most welcome. Thank you.

Edited by PaulD, 29 June 2009 - 10:32 AM.

[PaulD]

Oh dear. I may have found a problem with my wordpress install which might be causing this issue. The problem is along a similar vein where no results are being returned for a database query.

Just thought I would let anyone interested know.

[cathode]

Oh dear. I may have found a problem with my wordpress install which might be causing this issue. The problem is along a similar vein where no results are being returned for a database query.

Just thought I would let anyone interested know.

Yeah- keep us informed... I get asked to do this every once in awhile...

[Ralph]

If you haven't already done so you might consider trying a simpler query just to make sure you are able to abstract a valid data set...

[PaulD]

Just a quick one to say 'I did it'!!!! Yeaaaahhhh!!!! Hurrrraaaay!

After all the weird things that were happening I finally found the cause and it had nothing to do with Etomite, the function, nor wordpress.

My host automatically adds bits to a chosen database name, so I chose (say) dbname, and my host creates web153-dbname-3 or something similar.

I tried all variations of surrounding in quotes or not quotes when I just suddenly noticed that phpmyadmin used a slightly different character for the quotation marks. I cant even find it on my keyboard so had to cut and paste it.

So these did not work

$dbase="web153-dbname-3";
nor
$dbase="'web153-dbname-3'";

And this did

$dbase="`web153-dbname-3`";

So now, reading info from my wordpress tables and (when I choose one) my shop database and forum will be a cinch. (Hopefully) The wordpress part of the site will not be public but I will be using their admin screens, and plugins etc for handling comments, catching spam etc.

I didn't even know they were different characters, just assumed they were a different font. (How much of an amateur am I?)

Without the strange quotes, MYSQL wanted to treat the second '-' as an actual minus sign. That was the cause of all the problems.

I will add an example to the functions list, but for now am over the moon, although am off to play civilisation now.

I thought it was etomite for ages. I should have known better.

Paul.
:-)

[Ralph]

So, was it an issue with backticks, or another character...???

[PaulD]

So, was it an issue with backticks, or another character...???

It was the use of ' or " to encase the database name that caused the problem. The ` character solved it.

I thought they all kind of meant the same thing, ' " `, the last one (is it called a backtick?) was the one that worked.

It was because I had multiple '-' (minus) signs in the database name. (One seems ok, two seems to cause a problem.) This is nothing to do with etomite, but MYSQL. It is not a problem, just I didn't know about the third option, the backtick. Still cant find it on my keyboard so will have to cut and paste it in future as well.


Paul.

[Ralph]

Yes, the ` character is called a backtick and is used by MySQL for DB name and column assignment definitions... Etomite uses `dbName` and some queries use `columnName`='value'... Backticks to the left and single quotes to the right of the comparator sign... Some versions of MySQL, but I'm not sure about all, also have issues with use of double hyphens...