Show on those available

lazysheepmedia

lazysheepmedia

Member
Folks

I have a database join where I have my properties listed - How can I display only the one that are available?

Would I need to add a yes/no (or another) element and then filter using that element on the db join element?

I have added this query but I am getting a 1054 error (unknown column)

The first table where I get my properties from is called properties ( filtered by {$my->id} ) and the second where I am trying to get the yes/no value is called tenancies

PHP: 
WHERE {thistable}.properties_userid = {$my->id} AND tenancies.tenancy_avail_yesno = 0

Any ideas why this would be is appreciated as always
 
I assume tenancies is an other table?
You have to join it in the "Additional join statement" before you can use it in Concat.
 
Thanks troester where do I do the join? Sorry on my iPhone at the moment


OK I realised what you said - sorry I am back in front of my PC now :)

I have been trying all afternoon to get this to work but it either returns all records, none at all or throws a MySql error

in my Concat I have this

Code: 
WHERE {thistable}.properties_userid = {$my->id} AND tenancies.tenancy_avail_yesno = 0

In the "Additional join statement" I have this

Code: 
LEFT OUTER JOIN properties ON tenancies.id = properties.id

But its not working - Can anyone help at all please?

Thanks as always
 
I think it must be
Code: 
LEFT OUTER JOIN tenancies ON tenancies.id = properties.id
or
LEFT OUTER JOIN tenancies ON tenancies.id = {thistable}.id
assuming properties is the table used in the dbjoin.
 
Thanks Troester

I have changed it and still it is not working?? It is loading the page but the db-join does not have any properties listed (there are some in the table with both 1 & 0 as the entry for tenancy_avail_yesno

In the first where box I have

Code: 
WHERE {thistable}.properties_userid = {$my->id} AND tenancies.tenancy_avail_yesno = 0

In the second join box I have

Code: 
LEFT OUTER JOIN tenancies ON tenancies.id = properties.id

Thanks for your help
 
I don't know your database set up, but joining ON tenancies.id = properties.id seems strange to me: is the relation really id=id (two autoincrement elements)
 
Cracked it!

Instead of using the primary key I have used the property address

Code: 
 LEFT OUTER JOIN tenancies ON tenancies.tenancy_6_months_prop = properties.property_housenumber_street

Thanks Troester for bringing that to my attention :)
 
Strange relation, too. But if it's working...

If you zip folder contains real data you may delete it from the forum.
 
Thanks troester - the zip contains test data :)

What would be the best suggested way of joining the tables out of interest?


Sent via my iPhone using Tapatalk app
 
Now that I have solved the problem with the kind assistance of the members of Fabrikar.com, I have another issue which I hope can be resolved. When I go into my list for the tenancies, I select a property that I want to edit and because I have added the where statement as described above, the property does not appear in the drop down. How can I overcome this?

Thanks
 
I have a database join where I have my properties listed - How can I display only the one that are available?...... When I go into my list for the tenancies, I select a property that I want to edit and because I have added the where statement as described above, the property does not appear in the drop down.
Click to expand...
Seem kind of incompatible to me whilst using one list. Perhaps copy the list and alter the element so that it no longer contains that where statement?
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 114 (members: 1, guests: 113)
    Back
    Top