hanfdampf
Posts: 5
|
| Posted: 02/24/2009, 11:16 AM |
|
i'm running codecharge studio 4.1.00.032 on a trial version building a php grid with search. this works all very fine as long as i connect four tables in consecutive 1:n relations. but as soon as i add a fifth one, the sql being generated fails to deliver (mysql returns an error).
i have a site that belongs to a village that belongs to a community that belongs to a province that belongs to a region that belongs to a country (or the other way round: a country that has many regions that has many provinces that has many communities ...). i want to be able to filter sites by selecting criteria (i.e. region) from a listbox (there are more conditions to the search as well). up to the level of the province this works perfectly. whenever i add one more table (region), the sql-query fails (and it's actually wrong).
when i change to sql-mode and build the query myself the table in the builder works just fine. but from there i have another problem: if i don't select parameters for all the listboxes in my search, i get empty results.
is there any way to help me? i'd really love to buy and use this tool, but if i don't get this query to work over more than four consecutive tables it is unfit for my purpose.
i really tried hard for a whole day and feeling stupid.
thx for help!
_________________
----------------------------
:o)(o:
using Apache, PHP mySQL on WINxp
newBee |
 |
 |
mrachow
Posts: 509
|
| Posted: 02/24/2009, 11:43 PM |
|
In self handled SQL statements Studio removes a condition bound a search field if the search expression is empty.
In <yourpage>.<script> you should find somewhere the main part of the SQL statement showing you would could bve wrong. At the moment I suppose it's a case for support.
_________________
Best regards,
Michael |
|
|
|
hanfdampf
Posts: 5
|
| Posted: 02/25/2009, 3:43 AM |
|
thanks a lot michael.
i've found my way through: the problem with the costum sql search was, that i had some integer type value in it, so the sql paramater required a default value. of course the default value does not exist in the database column so mysql returns empty results. all i had to do is to add the or statement for the default value...
...
AND fence_year = {s_fence_year} OR {s_fence_year} = 0
...
but still it seems strange to me, that the query builder couldn't do it, because it's a common task isn't it?
i'm happy it works!!
_________________
----------------------------
:o)(o:
using Apache, PHP mySQL on WINxp
newBee |
|
|
|
mrachow
Posts: 509
|
| Posted: 03/02/2009, 9:24 AM |
|
Not quite sure if I got it all but ...
The radio button on SQL parameter subdialog Use default value if parameter is empty works only when a value is entered into the field below.
The default behavior of Studio is to remove a certain column name from SQLs where clause if no parameter/value is given. By that this column is not taken into account when searching records.
I'm using it the following way.
If I would like to have a certain parameter always filled and it is possible for the user for what reason whatsoever to leave this field empty I give an non existing value as default value here. So when this parameter is empty the default value will be used and I'll be sure no record could be selected in this case.
In custom SQL Studio won't remove that column from where clause even if it's empty so you will possibly get an error or no records will be found.
_________________
Best regards,
Michael |
|
|
|
|
