smd_query: Talk to the database directly via SQL

aslsw66 · 2010-10-31 13:27:10

Still pushing with all of this, so here’s another question…

I now have two neat tables working. One is ign_users and another is a custom table called as_roles which holds different positions and who is assigned to them (on the basis of their user_id in ign_users). People can hold no positions, or one or more positions.

Now I want to display a table of users. No problem with smd_query. I want one column in the table to display any positions the user holds. No problem again, as I nest another smd_query for that row (I assume nesting sqm_query is acceptable – it seems to work but I’m not sure if there is a performance hit). If a user doesn’t hold a position, nothing is displayed.

The little bit extra I’m trying to achieve is to properly display the list. If I output the list separated by a comma, then the last item in the list has a comma too when there is nothing after it. This looks particularly silly if there is only one position.

I have tried to use smd_thisrow as a starting point, to compare where I currently am in the list, but it strangely counts the rows for the outer smd_query – I thought I could use one of the calculator plugins to achieve this.

I have also though of outputting as a list, but if the smd_query returns nothing then I’m still stuck with the enclosing <ul>...</ul>. Is this what the preparse attribute is for?

Bloke · 2010-10-31 19:30:53

aslsw66 wrote:

I assume nesting sqm_query is acceptable

Unfortunately in this case that is an invalid assumption. Although there’s nothing to physically stop you doing it, you will hit the snag you found where replacement variables will only display stuff from the outer query. In order to facilitate this I need to update the plugin to allow you to add a prefix to the counters (and perhaps the rows themselves, although you can do that bit manually in the query so I probably won’t go there).

I shall add that to the plugin when I get a chance. In the meantime, could I ask what your queries are? I have a sneaky suspicion that you may be able to achieve what you want with a cunning JOIN and therefore do it all in one query. I reserve the right to be wrong but from your description I think it’s possible.

If I output the list separated by a comma, then the last item in the list has a comma too when there is nothing after it

Ah, the classic list output problem. It’s pretty horrible isn’t it. Note to self is to improve the way the output can be displayed by adding a {separator} var (or something) that is only populated between items. Without that you need some conditional magic which is an overhead you can do without. Leave that with me but for now let’s get your query optimised; if you can do it in one statement then — for now at least — you will be able to use the conditional logic as you are now because {smd_thisrow} will be correct.

aslsw66 · 2010-11-01 04:35:16

This is my outer query:

<txp:smd_query query="SELECT user_id,surname,RealName,email,phone,field,admin FROM ign_users ORDER BY surname asc">

I’m using a simple table row and cell structure to output all of these elements apart from user_id.

I also use a TXP variable to capture the current user’s id: <txp:variable name="roles_id">{user_id}</txp:variable>

Then, within a cell for each user, I have the inner query:

<txp:smd_query query="SELECT role FROM as_roles WHERE occupied_by=?roles_id">

The as_roles table has a field called occupied_by which captures the user_id of the person occupying the position – all of this is maintained elsewhere (and works thanks to your help).

When I tried to use a LEFT JOIN in the outer query, the query returned additional rows where a user occupies more than one position. For example, with me I occupy two positions in this organisation so all of my details are repeated in a second row for the second position. That’s mostly why I gave up on JOINS, and googling for some ideas didn’t produce much.

This will be important because I also want to set up an extra table where I can capture what training people have completed – again, I want to be able to display all of the qualifications someone has in this (web) table as well ie. it’s a snapshot of everyone’s details, positions, and qualifications.

Thanks,

PS. I changed from using the terms ‘roles’ to ‘positions’ along the way and still haven’t fixed up the table and field names yet!

Bloke · 2010-11-01 10:55:35

aslsw66 wrote:

the query returned additional rows where a user occupies more than one position.

Yes. Don’t get me started on relational databases and wastage!

One way round it is to keep track of the IDs you’ve seen already and only output the whole row when the ID changes. For example if you were displaying articles and comments you can use this delicious lump of code:

<table><tr class="header"><td>ID</td><td>Title</td><td>messages
<txp:variable name="last_id" value="" />
<txp:smd_query query="select * from textpattern LEFT JOIN txp_discuss ON (parentid = ID)">

  <txp:variable name="roles_id">{ID}</txp:variable>

  <txp:if_variable name="last_id" value='<txp:variable name="roles_id" />'>
{message}
  <txp:else />
</td></tr><tr><td>{ID}</td><td>Title</td><td>{message}
  </txp:if_variable>

  <txp:variable name="last_id" value="{ID}" />
</txp:smd_query>
</tr></table>

Things to note:

It only works if you have one ‘duplicate’ thing in each row
It has to be the last item in the row
Missing </td> and </tr> in the header row and on each full row (closed at the start of the <txp:else /> branch)
It’s bloody ugly
It’d be more elegant if I allowed inner queries so I’ll fix that for you asap

aslsw66 · 2010-11-01 13:24:31

Take a deep breath everyone, I have discovered something that the almighty bloke doesn’t know.

MYSQL5 has a new function called GROUP_CONCAT which achieves exactly what I want. It took me a bit of tweaking to get it right, and needed a few tries to get the syntax right. But it even correctly adds a separator but leaves it out for the last item in a list.

But it just shows the power of smd_query that you can feed it any valid SQL statement, and that the replacement tags recognise all of the results.

Bloke · 2010-11-01 13:37:09

aslsw66 wrote:

MYSQL5 has a new function called GROUP_CONCAT

What, no pity on those of us whose hosters still use MySQL 4? :’-(

(actually, now you come to mention it, I think I have used it before on someone’s project — once — but forgot all about it. Thanks for jogging my memory, it is indeed incredibly useful. Glad you got that bit sorted and everyone can ignore the fugly code I posted above . Yay!)

th3lonius · 2010-12-21 00:55:18

Hi, Stef. I need some help with my site and it seems as if smd_query will be the best solution. My only problem: very limited knowledge of database queries.

You availed me your knowledge a few months ago regarding the same thing but at the time I was attempting to accomplish the task with smd_if.

On my film essay website each ‘review’ is an article. For each individual article I have some information (director, screenwriter, year of release, etc.) displayed in a sidebar. Along with this information I want to display a linklist of other films by the same director if, and only if, such articles exist (we’ve reviewed these other films in other words) in the database. I use a custom_field for ‘director’, so I would need smd_query to find this field in the table and return any instances of the same custom_field value (an exact match of the director’s name) and excluding the current article.

The smd_if solution worked, but it was limited to returning a single match only. But I have some directors with as much as 30 other films now.

Please help me, good sir. I will be in your debt if I’m not already.

**the link the smd_if thread takes you to my last post, but your solution is actually a post above it

aslsw66 · 2010-12-21 06:02:37

Not necessarily being an expert, but the starting point should go like this…

Firstly, get the name of the Director for the current article into a variable:

<txp:variable name="director"><txp:custom name="director" /></txp:variable>

Secondly, create a query to select all articles with matching Directors:

<ul>
<txp:smq_query query="SELECT id,title FROM textpattern WHERE custom_1='?director'">
<li><a href="<txp:site_url />/index.php?id={id}">{title}</a></li>
</txp:smd_query>
<ul>

This assumes that the director’s name will be in custom field 1 – I’m not sure whether you could build a query to use the assigned name of the custom field instead of the number. Also, you could create a neater link to the item if you know exactly what the section name is (and use the url_title instead). And finally, you will need to wrap it in whatever HTML tags you need (I have used a simple unordered list).

th3lonius · 2010-12-21 21:31:03

Thanks, Anura! It works brilliantly. You provided the skeleton and I ran with it. Here’s my code if anyone is interested:

<txp:variable name="director"><txp:custom_field name="director" /></txp:variable>
<txp:variable name="thisid"><txp:article_id /></txp:variable>

<txp:smd_query query="SELECT url_title,title FROM textpattern WHERE custom_1 LIKE '%?director%' AND status=4 AND id <> '?thisid'" label="More from this director" labeltag="h3" wraptag="ul" class="taglist">
<li><a href="<txp:site_url />reviews/{url_title}">{title}</a></li>
</txp:smd_query>

Selects permlink and article title from the table where my custom_field contains (hence the LIKE operator) the name of a director. Status=4 restricts the query to live articles only and “id <> ‘?thisid’” excludes the current article from the results.

tye · 2011-03-15 05:12:50

Stef – I have a question about your great plugin :)

I have a date value stored in a Custom Field like this: 2011-03-17, but I need to output it like this 17-03-2011.

Before I was using smd_query I was using this:

<txp:php>echo implode('-',array_reverse(explode('-',$GLOBALS['thisarticle']['date'])));</txp:php>

Which is probably an arse about tit way of going about it (kind of sums up my coding) but it worked :)

Now when I try to do this:

<txp:php>echo implode('-',array_reverse(explode('-',{custom_1})));</txp:php>

I get all sorts of random years (1983, 2006, 1979 etc)

Any ideas how I can format this the way I want?

I currently have this – which is outputting nothing – I’m confused

<txp:php>
$dbdate="{custom_1}";
$arr=implode("-".$dbdate);
$arr=array_reverse($arr);
$date=explode($arr,"-");
echo $date;
</txp:php>

Bloke · 2011-03-15 09:51:49

tye wrote:

probably an arse about tit way of going about it

My lips are sealed :-)

But as an alternative to swapping stuff around in a string, which is prone to error if your input is mangled, you might try one of the following approaches in your smd_query container. They both try and treat the string as a date and then use PHP’s date/strftime functions to alter the format:

Method 1 (if you have smd_calendar installed)

<txp:smd_cal_now now="{custom_1}" format="%d-%m-%Y" />

Method 2

<txp:php>echo date('d-m-Y', strtotime("{custom_1}"));</txp:php>

Notice in the second example that date() requires a UNIX timestamp as its 2nd arg so we convert the string representation of the date into a timestamp using strtotime() first. That’s fine if you are sure your date is always going to be a valid date. If it’s in some way butchered then strtotime will return false and the date() function will either return nowt, will error out, will use ‘today’, or use the UNIX epoch as a start date (can’t remember which). None of those are particularly pretty so you might wish to put some defensive coding in there by doing the strtotime() first, assigning it to a var and then, if it’s not === false, do the date conversion, otherwise take some remedial action or just skip it.

(fwiw, smd_cal_now does all that defensive crap for you under the hood)

Hope that helps.

Last edited by Bloke (2011-03-15 09:53:44)

tye · 2011-03-16 07:16:40

Thanks Stef – I used method 2 as I didn’t have smd_calendar installed, I just haven’t had a good look at it yet.

I should be fine though as it will always be a valid date as it uses the datepicker.

More importantly though, I actually understood pretty much all of what you were saying – which is amazing really :)

I knew it was wrong to post that code… but hey, hopefully it made a few people giggle :)

Textpattern CMS

Textpattern CMS support forum

#253 2010-10-31 13:27:10

Re: smd_query: Talk to the database directly via SQL

#254 2010-10-31 19:30:53

Re: smd_query: Talk to the database directly via SQL

#255 2010-11-01 04:35:16

Re: smd_query: Talk to the database directly via SQL

#256 2010-11-01 10:55:35

Re: smd_query: Talk to the database directly via SQL

#257 2010-11-01 13:24:31

Re: smd_query: Talk to the database directly via SQL

#258 2010-11-01 13:37:09

Re: smd_query: Talk to the database directly via SQL

#259 2010-12-21 00:55:18

Re: smd_query: Talk to the database directly via SQL

#260 2010-12-21 06:02:37

Re: smd_query: Talk to the database directly via SQL

#261 2010-12-21 21:31:03

Re: smd_query: Talk to the database directly via SQL

#262 2011-03-15 05:12:50

Re: smd_query: Talk to the database directly via SQL

#263 2011-03-15 09:51:49

Re: smd_query: Talk to the database directly via SQL

#264 2011-03-16 07:16:40

Re: smd_query: Talk to the database directly via SQL

Board footer