Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » different results in queries
different results in queries [message #624] Mon, 12 February 2007 19:44 Go to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Was trying to call mysql_query, mysql_fetch_array in _cm_post_updateRecord

Can this be done? Seems like calling those does not give me any results or error messages.

I went back to mysql functions because radicore does not give me the correct results. Attached is a script to implement Van Tulder's Tree http://www.sitepoint.com/print/hierarchical-data-database.

One version is using radicore and the other using his script.

Test data:
role_id role_id_snr lft rgt
GLOBAL NULL 1 10
TESTING GLOBAL 2 9
TESTING2 TESTING 3 8
TESTING3 TESTING2 4 7
TESTING4 TESTING3 5 6

Test Scenerio (Reparent TESTING2 to GLOBAL)
role_id role_id_snr lft rgt
GLOBAL NULL 1 10
TESTING GLOBAL 2 3
TESTING2 TESTING 4 9
TESTING3 TESTING2 5 8
TESTING4 TESTING3 6 7


  • Attachment: Script.txt
    (Size: 2.07KB, Downloaded 859 times)

[Updated on: Mon, 12 February 2007 20:24]

Report message to a moderator

Re: different results in queries [message #625 is a reply to message #624] Tue, 13 February 2007 04:25 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
Radicore simply executes the sql queries that you give it, so if you are getting different results then there must be something different in the queries.

You need to produce a trace of each sql query and its results at every step through each method to find out where the difference exists.


Re: different results in queries [message #626 is a reply to message #625] Tue, 13 February 2007 08:58 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Yep, I did that.


// get all children of this node
$this->sql_select = 'role_id';
$this->sql_from = 'mnu_role';
$where = "role_id_snr='".$role_id."'";
$children_rowdata = $this->_dml_getData($where);

Assuming if two rows were returned.
"TESTING"
"TESTING2"

I refer to array returned explicitly
$children_rowdata[0]['role_id'] //value is "TESTING"

$children_rowdata[1]['role_id'] //value is "" should be "TESTING2" correct?
Im new to php, Very Happy is this the correct way to refer to a 2nd row?
Re: different results in queries [message #627 is a reply to message #626] Tue, 13 February 2007 09:16 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
Yes, the output from _dml_getData() is an array which is indexed by row number, so $rowdata[0] would give you the first row and $rowdata[1] would give you the second row.

What you need to do is to print a trace from each of your two pieces of code to see exactly what sql query was issued and what results were returned. Only when you can compare the two traces will you be able to see where the dfference is.

You need to include the following lines somewhere in your code:
echo 'where= ' .$where;

and
print_r($children_rowdata);


Re: different results in queries [message #628 is a reply to message #627] Tue, 13 February 2007 15:09 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
echo and print_r would not work if redirected to another page. So can't really see whats happening in cm_post_recordupdate.

I found a way to output the variable values into sql files.

// get all children of this node
$this->sql_select = 'role_id';
$this->sql_from = 'mnu_role';
$where = "role_id_snr='".$role_id."'";
$children_rowdata = $this->_dml_getData($where); <*** query A

$A = count($children_rowdata); <*** count A
$this->sql_select = 'role_id';
$this->sql_from = 'mnu_role';
$where = 'role_id_snr='.$A;
$children_rowdata3 = $this->_dml_getData($where); <**** query B

SELECT SQL_CALC_FOUND_ROWS role_id FROM mnu_role WHERE role_id_snr='GLOBAL' ORDER BY mnu_role.role_id asc LIMIT 1 OFFSET 0 LOCK IN SHARE MODE=>Count=2
SELECT SQL_CALC_FOUND_ROWS role_id FROM mnu_role WHERE role_id_snr=1 ORDER BY mnu_role.role_id asc LIMIT 1 OFFSET 0 LOCK IN SHARE MODE=>Count=0

Seems "query A" found 2 records, but when I count the records returned in "count A" its say only 1 record. Something is not right here, I sense this could be a bug in _dml_getData. What do you think?
Re: different results in queries [message #629 is a reply to message #628] Tue, 13 February 2007 20:02 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
Use print_r() to display the contents of the array produced by _dm_getData(). There may be a difference between what is actually being returned and what you think is being returned.



Re: different results in queries [message #630 is a reply to message #629] Tue, 13 February 2007 20:36 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
like I said Tony print_r cannot display the contents because your framework redirects the page. This is absolutely not within my control.

I remarked out the calling of rebuild_tree, to eliminate any program logic related error.

As you can see in the csv file. There are two records with role_id_snr = 'GLOBAL'

SELECT SQL_CALC_FOUND_ROWS role_id FROM mnu_role WHERE role_id_snr='GLOBAL' ORDER BY mnu_role.role_id asc LIMIT 1 OFFSET 0 LOCK IN SHARE MODE=>Count=2 <**** in sql trace file

But the query only returns one, you can see this in the sql trace file.

SELECT SQL_CALC_FOUND_ROWS role_id FROM mnu_role WHERE role_id_snr='TESTING' ORDER BY mnu_role.role_id asc LIMIT 1 OFFSET 0 LOCK IN SHARE MODE=>Count=0
SELECT SQL_CALC_FOUND_ROWS role_id FROM mnu_role WHERE role_id_snr='' ORDER BY mnu_role.role_id asc LIMIT 1 OFFSET 0 LOCK IN SHARE MODE=>Count=0 <***** role_id_snr=''
  • Attachment: Feb14.zip
    (Size: 2.09KB, Downloaded 1260 times)

[Updated on: Tue, 13 February 2007 20:37]

Report message to a moderator

Re: different results in queries [message #631 is a reply to message #630] Wed, 14 February 2007 04:34 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
Yes, it is in your control. You insert debugging statements into the code to display various details, then you exit() to stop further execution. This will stop any redirect which destroys your debug output.

Alternative you could try using the error_log() function to write all debugging text to a file instead of the browser.


Re: different results in queries [message #632 is a reply to message #631] Wed, 14 February 2007 08:13 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Thanks for the debugging tips.

It is still returning one record even though there are two. The data is attached in csv file in the previous reply.

Attached are the results. I still keep the rebuild_tree function remarked in order not to complicate things
  • Attachment: Feb14 - 2.zip
    (Size: 28.06KB, Downloaded 1314 times)
Re: different results in queries [message #633 is a reply to message #632] Wed, 14 February 2007 08:56 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
You still misunderstand me. Change
print_r($children_rowdata[0]['role_id']);
to
print_r($children_rowdata);
so that it prints out the ENTIRE contents of the array, not just selected parts. That will show you EVERYTHING returned by the call to _dml_getData().


Re: different results in queries [message #634 is a reply to message #633] Wed, 14 February 2007 09:02 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Here you go.

//to update TESTING to GLOBAL
role_id_snr='GLOBAL'
Array ( [0] => Array ( [role_id] => TESTING ) )
Exit

//to update GLOBAL to TESTING
role_id_snr='GLOBAL'
Array ( [0] => Array ( [role_id] => TESTING ) )
Exit
Re: different results in queries [message #635 is a reply to message #634] Wed, 14 February 2007 09:47 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
You're getting there, but you are still not doing as I originally asked. You need to provide a trace from each of the two different sets of code. You need to display the contents of each sql SELECT clause as well as the data that it returns. Then you can compare the traces from each of the two pieces of code to see where it is different.

Re: different results in queries [message #636 is a reply to message #635] Wed, 14 February 2007 10:22 Go to previous messageGo to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Here you go. Laughing

Check out the arrows and comments in the Sql trace.

Not sure why there is a LIMIT 1 in the one with count 2.

Hope it helps. Its a bug alrite.
  • Attachment: Feb14 - 3.zip
    (Size: 24.80KB, Downloaded 1280 times)
Re: different results in queries [message #637 is a reply to message #636] Wed, 14 February 2007 10:50 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
Senior Member
No, it is not a bug. Each call to _dml_getData() causes the current setting of $this->rows_per_page to be used in the LIMIT clause. Your code appears to be running inside an UPDATE1 pattern, and if you look inside file 'std.update1.inc' you will see the line:
$dbobject->setRowsPerPage(1);

which tells the system to extract and display 1 record at a time. This setting is still in operation when your custom code is being executed. To remove all limits you must insert the following line before any calls to $this->_dml_getData():
$this->rows_per_page = 0;

Do not forget to reset it aferwards.


Re: different results in queries [message #638 is a reply to message #637] Wed, 14 February 2007 11:03 Go to previous message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Hurray, Tony solved it once again. Laughing

Geez, didn't know that its just one little line of code. Very Happy
Previous Topic: dml_updateSelection
Next Topic: not able to search 'Task Id'
Goto Forum:
  


Current Time: Mon Nov 25 08:53:37 EST 2024

Total time taken to generate the page: 0.04421 seconds