Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
Quote by ppmnt:Looks like you're listing the table names twice...
Yes, I am, but that's not the problem.
Notice where the error statement begins: between the DELETE and the first table. You may, somehow, have an extra space between "DELETE" and "gl_daily...." (probably not the problem however)

It may be a mySQL version error (more likely). this is a multitable delete statement and requires at least mySQL 4.0.0. You'll have to check your mySQL version for compatability.

your next option requires a bit of memory. did you update the status on this quote without also updating the status on any category that was created along with it? I doubt this is the problem, but we'll see. Is this the first delete you've attempted? any successes prior to this?

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Quote by machinari:
It may be a mySQL version error (more likely). this is a multitable delete statement and requires at least mySQL 4.0.0. You'll have to check your mySQL version for compatability.


I see... I wasn't aware of the multitable syntax, I was thinking of standard SQL (i.e. "delete from table_name where etc"). You're right, my mySQL version is 3.23.56, so this will not work for me. I can update my own box where I test my site, but this is a hosted server, and I'm afraid other folks might run into this as well...

I ended up manually deleting the quote form both tables.

Just FYI: no new category was created with the anonymous quote.

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
Quote by ppmnt: this is a hosted server, and I'm afraid other folks might run into this as well...

You might try requesting that your host updates their very antiquated version of mySQL.

There are other statements here and there that also require more up to date versions of the mySQL server as well as PHP.

I don't know that I want to go and make it all backward compatable--I'm not that good and the plugin is already more bulky than it should be.
Any suggestions?

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Couple of ways:

You could change sql statements to be standard SQL syntax. That would make sure it'll work for everybody. MySQL 4 is significantly different from MySQL 3 (kind of like Apache 1.3 -> 2.0), so many hosts have not udpated yet (same with Apache). In the Geeklog documentation they recommend MySQL 3.23 or 4, so it would be nice for your plugin, as for all plugins, to work with all those versions as well. It would definitely give your plugin more visibility and it could easily be one of those common, almost standard plugins.

So that would be my recommendation. I wouldn't bother trying to check for the version and using different SQL each time, just use the standard syntax that'll work with both.

The other option would be to put in your readme or documentation that MySQL 4.0 is a requirement. That would probably restrict the audience though. Many people don't have control over what's installed on web servers, and plenty folks won't even know how to check it... They'd probably just like to know that if Geeklog works for them then plugins will work too...

I know that's probably not what you wanted to hear as it would require some re-writing and testing... As for me, I can handle it. If you decide against changing it I'll probably just write some shell scripts to deal with deletes, or I'll just have to tweak your code (which I'm trying to avoid though).


Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
After much thought, I've decided not to support mySQL 3.23. My reasons:
the short version: mySQL 3.23 is five years old.
the long version: in order to support the table design necessary for the dailyquote features, first and foremost, multiple categories, functions such as delete and update need to, at the very least, be able to reference multiple tables. This leaves behind mySQL versions prior to 4.0.4, which is already 2 years old. I've had to make concessions, not big ones, in order to support 4.0.

My apologies to those of you who are stuck with lazy hosts. Feel free to flame me. Better yet, inform me of ways that these issues may be resolved peacefully.

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Quote by machinari:
My apologies to those of you who are stuck with lazy hosts. Feel free to flame me. Better yet, inform me of ways that these issues may be resolved peacefully.


Like I said, I myself will just write something myself if you don't support MySQL pre-4. Regarding suggestions for "peaceful resolution" (I was never planning to flame you) though, one-to-many relationships have been around for a long time, so one quote having several categories is possible to do with standard SQL (enterprise DB systems like Oracle still don't have multi-table deletes. Well, I can't speak for Oracle 9i).

It would just have to be one SQL statement per table, that's all...

[ ... update ... ]

In fact, I just modified my manage.php so it'll work for me. I just changed the del_quote function from:
Text Formatted Code

function del_quote($qid){
    global $_DQ_TABLES, $LANG_DAILYQUOTE;

    $sql = "DELETE {$_DQ_TABLES['dailyquote_quotes']}, {$_DQ_TABLES['dailyquote_lookup']}";
    $sql .= " FROM {$_DQ_TABLES['dailyquote_quotes']}, {$_DQ_TABLES['dailyquote_lookup']}";
    $sql .= " WHERE {$_DQ_TABLES['dailyquote_quotes']}.ID=$qid";
    $sql .= " AND $qid={$_DQ_TABLES['dailyquote_lookup']}.QID";

    if (!DB_query($sql)){
        $retval = "<p align="center" style="font-weight: bold; color: red;">" . $LANG_DAILYQUOTE['delerror'] . "</p>";
        COM_errorLog("An error occured while deleting a quotation",1);
    } else {
        $retval = "<p align="center" style="font-weight: bold; color: red;">" . $LANG_DAILYQUOTE['delsuccess'] . "</p>";
        COM_errorLog("Deleted Quotation and related data.  Q.ID = $qid",1);
    }

    return $retval;
}

 


to:
Text Formatted Code

function del_quote($qid){
    global $_DQ_TABLES, $LANG_DAILYQUOTE;

    // 20041001 make delete MySQL 3 compatible
    $del_error=0;

    $sql = "DELETE FROM {$_DQ_TABLES['dailyquote_quotes']} WHERE ID=$qid";

    if (!DB_query($sql)){
        $del_error=1;
    } else {
        // continue only if first SQL was successful
        $sql2 = "DELETE FROM {$_DQ_TABLES['dailyquote_lookup']} WHERE QID=$qid";
        if (!DB_query($sql2)){
            $del_error=1;
        }
    }

    if ($db_error == 0){
        $retval = "<p align="center" style="font-weight: bold; color: red;">" . $LANG_DAILYQUOTE['delsuccess'] . "</p>";
        COM_errorLog("Deleted Quotation and related data.  Q.ID = $qid",1);
    } else {
        $retval = "<p align="center" style="font-weight: bold; color: red;">" . $LANG_DAILYQUOTE['delerror'] . "</p>";
        COM_errorLog("An error occured while deleting a quotation",1);
    }

    return $retval;
}

 


It's only a few more lines and works fine, tested with MySQL 3.23.56

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
that's all good.. and I am actually talking about a many to many relationship. however, I did the same thing, but the update function is a bit more involved. Have you used the update yet? does it work as is? That's the one where I stopped looking for a solution. I need to reference more than one table even if I only update one table at a time. Take a look at that one and let me know what you think.

anyway, presently, you'll have to deal with another multitable delete in the update function if you remove a category from a quote.

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
notice that if a quotation does belong to more than one category, only one is displayed on the index and search page. -this has been fixed. multiple cats now display.
-a few spelling errors fixed.
-language file is cleaned up a bit.
-moderation page has been added with options on the config page.
-images for links have been commented out leaving the alt text. style as you please.
-I bought a lawn mower!

question: should i throw a link per quote on the index page for those with edit rights that shoots the person and the particular quote right to the manage page? or would that clutter up the listing?

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
I see. I hadn't messed with update yet and didn't know where else you might have multi-table statements.

Just taking a quick look I just noticed the gl_dailyquote_submitter table. I'm not sure if that's even needed, couldn't you just use the GL user table? You use the same uid, can look up the name, and anonymous submitters don't get to enter a name anyway.

The only other thing in that table is the IP address, but that would not necessarily be unique. The same user may want to submit/update quotes from different computers. This doesn't matter if you only use it for the error.log, but since you have it in the DB I'm assuming that maybe you're planning to use it at a later time, e.g. have it be visible to the admin user when viewing quotes. In that case the IP should probably be stored with the quote, not the user.

As for the rest of the update function, I'll have to look at it later, running out of time right now. But I'll think about it.

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
By the way, I just noticed that if you have a quote that's a member of several categories, then it shows up that many times on the index page. So for example my test quotes is showing up 4 times in a row right now...

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
yes, that was fixed with the display of multiple categories. just a matter of separating the category select from the the distinct quote select.

the idea behind the submitter table was to list only those that actually contributed quotes, not all members of the site. much like the forum's member list or the author exclusion list in the gl account prefs. there is a drop down here and there with the user list, eg, to sort by contributor i believe, and if you want to change contributor on the manage or moderation page.

re the IP: it was for the admin like you say, but since there is a submission queue it is not necessary and will be taken out. thanks for reminding me.

and by the way, I appreciate you taking the time to troubleshoot this with me. It helps alot.

Status: offline

Blaine

Forum User
Moderator
Registered: 07/16/02
Posts: 1232
Just as a note: You won't find any MySQL 4.0+ syntax in Geeklog. MySQL 3.23 is still a very popular database and there were multiple updates in 2003 with the most recent release 3.23.58 just over a year ago.

You often find ISP's running very old releases of PHP and MySQL and then again, you will find ISP's that feel they must run the latest. Both cases make it hard to support these apps and contributed projects. And let's not get started on browsers

Needless to say - I don't want to suggest it should or not support MySQL 3.23. It takes a lot of time to support these plugins in the long-run so do what you feel is best.

Geeklog components by PortalParts -- www.portalparts.com

Status: offline

Dirk

Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Quote by Blaine: You often find ISP's running very old releases of PHP and MySQL and then again, you will find ISP's that feel they must run the latest.

Just to chime in on that: My hosting service is a bit on the conservative side and they feel that MySQL 4 isn't ready for production environments yet. So for my own sites, I'm stuck with MySQL 3.23.xx for the foreseeable future.

I'm by no means a database expert, but since my hosting service has a reputation of being reliable (and I pay them for that ...), I'm going to trust them on that.

geeklog.net is currently running on MySQL 4.0.17, but then again we do have occasional hickups here and I have to wonder where they come from ...

bye, Dirk

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Quote by machinari:the idea behind the submitter table was to list only those that actually contributed quotes, not all members of the site. much like the forum's member list or the author exclusion list in the gl account prefs. there is a drop down here and there with the user list, eg, to sort by contributor i believe, and if you want to change contributor on the manage or moderation page.


Machinari,

You can still achieve that without the extra table, by just storing the UID with the quote. Then, when you need to populate a list of just users who submitted quotes, you can do something like this:

select username from gl_users where uid in (select distinct uid from gl_dailyquote_quotes);

That may be a little more flexible in the future.

Quote by machinari:and by the way, I appreciate you taking the time to troubleshoot this with me. It helps alot.


No problem, I'm glad I can help some.


Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
released dailyquote version 0.9.1 for testing. download it here

updated config; added moderation page and submission queue; fixed a number of reported bugs; now displays multiple categories properly; database changes. I have not provided an update script so if you have added many quotes that are not in the sample files provided, back them up because you'll have to run the install script again. In otherwords, if you don't know what you're doing, wait for version 1.0.

next version: will be mySQL 3.23 compatable; will drop the contributors table making more use of GL's user tables; documentation.
I have a line on how to work the syntax in my update, and approve functions in order to make them 3.23 compatable, but I thought that I would release this version before I make all those changes so that I could get advice from any that care to give it.

Status: offline

shetzel

Forum User
Chatty
Registered: 03/19/04
Posts: 39
First, let me say thanks. I used to have a random quote generator running on my old site, but I gave up on it when I migrated to Geeklog. It's the most requested feature among my users, so thanks very much for taking the initiative. You're saving me a ton of work, so the least I can do is help you debug.

1) About 0.9.1 .... what happened to functions.inc? It's not included in the packaging. We kinda need it.

2) What's the purpose of the categories? Will there be a user inclusion/exclusion feature in the future? Where a user could "turn off" certain categories from appearing on his/her page? Just wondering.

3) What's up with your package timestamps? The filedates are Dec 31, 1969 across the board. Not a big deal, but he Linux version of tar that I use complains about each and every file.

Thanks again, I got 0.9 last night and I'll be working with you until you get 1.0 out the door. For the moment, I'll start digging deeper.

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Machinari,

That's great news about your planned features for 1.0!

So I tried installing 0.9.1. It seems that functions.inc is missing from the tar ball.

I copied the old functions.inc from 0.9 into the 0.9.1 directory. Then, during the install, the following happens:

It creates all tables successfully, then:
[ ... ]
Sun Oct 3 09:51:09 2004 - Success - Created table
Sun Oct 3 09:51:09 2004 - Inserting default data into dailyquote_settings table
Sun Oct 3 09:51:09 2004 - 1136: Column count doesn't match value count at row 1. SQL in question:
Sun Oct 3 09:51:09 2004 - Error inserting default data into dailyquote_settings table
Sun Oct 3 09:51:09 2004 - Attempting to unregister the Daily Quote plugin from Geeklog
[ ... ]
Following this are successful uninstall messages (removing tables, etc)

Thoughts?

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
oh crap! sorry folks. my bad for the missing file. and yes I know exactly where that error is.. i'll fix it right now. look for 0.9.1.1 in an hour or so...

done. get it here

oh and about those timestamps.. who knows.. wintarball screwed them up. thanks for noticing. i'll put up a .rar instead

Status: offline

ppmnt

Forum User
Chatty
Registered: 06/09/04
Posts: 53
Quote by machinari: oh and about those timestamps.. who knows.. wintarball screwed them up. thanks for noticing. i'll put up a .rar instead


Er, don't mean to be a pain, but rar complicates things for some of us. For me for example it would add the step of downloading it to my Windows box, unrarring, then tarring it back up with cygwin and gzipping it, then uploading to my host... Whereas when you were using .tgz or tar.gz or even zip I can just wget it straight to my host. I think generally .tgz or .zip are more compatible with all hosts...

Personally, I don't care about time stamps, but one tip, if you want unix/linux type capabilities on your windows box, I highly recommend _cygwin_

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
Quote by shetzel: What's the purpose of the categories? Will there be a user inclusion/exclusion feature in the future? Where a user could "turn off" certain categories from appearing on his/her page?

I have never actually thought of any other purpose for categories beyond simply displaying related quotations together. If you have ideas for further uses of the categories, I'm glad to hear them. Your idea sounds good and I think it would be easy to implement. I'll put it on my notepad.

this brings me to a known bug in 0.9.1.1: in order to display multiple categoris properly when they belong to a singular quote, I've had to separate one select into two selects. This means that you will not be able to sort by category for the time being. You'll get errors if you try. That's first on my list for later today.

Page navigation