Doodles Tutorial - Can't create tables

5,888 Posts

Bruno17 Reply #21, 13 years, 10 months ago

you will have less issues when allways using an autoincrementing field with name 'id' as primary key-field
and use the same prefix for your custom-tables as your modx-tables

for example modx_custom_news

after this changes use this schema for your table:

<object class="CustomNews" table="custom_news" extends="xPDOSimpleObject">
    <field key="departmentID" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
    <field key="title" dbtype="varchar" precision="100" phptype="string" null="false" />
    <field key="short_description" dbtype="varchar" precision="500" phptype="string" null="false" />
    <field key="url" dbtype="varchar" precision="200" phptype="string" null="false" />
    <field key="news_date" dbtype="date" phptype="date" null="false" />
    <field key="active_yn" dbtype="varchar" precision="1" phptype="string" null="false" />
 
    <index alias="departmentID" name="departmentID" primary="false" unique="false" type="BTREE" >
        <column key="departmentID" length="" collation="A" null="false" />
    </index>
</object>

don't forget to run 'parse schema' after making changes on your schema!

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

45 Posts

jsteffan1 Reply #22, 13 years, 10 months ago

I have taken the advise you have given me and I have removed the index tag with the primary information. My add button works perfectly, but I still see the "No items found" where it should be displaying all of the rows in my table.

Thank you for helping me so much! and Thank you for creating this plugin! It is going to save me a ton of time and learning!!

5,888 Posts

Bruno17 Reply #23, 13 years, 10 months ago

if you have firefox and firebug running, try this for debugging the issue:

load the CMP.
go to the firebug-console.
clear the console.
refresh the grid by clicking the refresh-button under the grid.
what is the response in the firebug-console?

then try to uncomment this line:

//$c->prepare();echo $c->toSql();

->

$c->prepare();echo $c->toSql();

in core/components/migx/processors/mgr/default/getlist.php

click the refresh-button under the grid again.

what do you get then as response in the firebug-console?

finally don't forget to comment the line above again!

perhaps we can see the issue when seeing the generated sql-query.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

45 Posts

jsteffan1 Reply #24, 13 years, 10 months ago

ah... I found the problem... the order by is ordering it by id, but my id column is called departmentID. Is there a way to select what it is ordered by in the MIGX configs?

refresh the grid by clicking the refresh-button under the grid.
what is the response in the firebug-console?

{"total":"34","results":[]}

in core/components/migx/processors/mgr/default/getlist.php
click the refresh-button under the grid again.
what do you get then as response in the firebug-console?

SELECT `CustomDepartments`.`departmentID`, `CustomDepartments`.`locationID`, `CustomDepartments`.`department`, `CustomDepartments`.`alias_tv`, `CustomDepartments`.`resource_num`, `CustomDepartments`.`phone_number`, `CustomDepartments`.`suite`, `CustomDepartments`.`hours` FROM `cc_custom_departments` AS `CustomDepartments` ORDER BY id ASC LIMIT 10 {"total":"34","results":[]}

5,888 Posts

Bruno17 Reply #25, 13 years, 10 months ago

MIGXdb doesn't work correctly, with tables, which have a primary-key-field named other than 'id' , by default.

If possible, add a autoincrementing-primary-key-field to your table with name 'id' or rename the DepartmentId - field to id.
Its good practice to have the primary-key-field on all tables named 'id'

and make sure you have: extends="xPDOSimpleObject"
in your schema for that table.

and yes its possible to change the default-sort-field and direction in the migxdb-configuration.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

45 Posts

jsteffan1 Reply #26, 13 years, 10 months ago

Thank you so much for your help!!!! I can definitely see why this plugin was created with the autoincrementing-primary-key-field as id. Unforchantly I am not able to change that in my tables because of they way they are structured and interact with each other. In the MIGX-Configs under the MIGXdb-Setting is the getlist defaultsort field. Is there specific syntax that I need to use to get this to work properly? If a put one of my fields in the field, it shows the information in my table in the firebug console as an error, but not in the CMP.

5,888 Posts

Bruno17 Reply #27, 13 years, 10 months ago

ok, I see. I think now we at a point, where your will need custom-processors, with your primary-fields other than id.

Create a new folder in the directory of your package
/processors/mgr/default/

and copy the getlist.php of components/migx/processors/mgr/default/getlist.php into it.
Then let me know your current schema for that table.
We will need to make some mods to the getlist.php and to the other processors to get it working with your tables.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

45 Posts

jsteffan1 Reply #28, 13 years, 10 months ago

I have placed the getlist.php file inside /pluginName/processors/mgr/default/. Here is my tables schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="departments" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="CustomDepartments" table="custom_departments" extends="xPDOObject">
	   <field key="departmentID" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
	   <field key="locationID" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
		<field key="department" dbtype="varchar" precision="30" phptype="string" null="false" />
		<field key="alias_tv" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="resource_num" dbtype="int" precision="10" phptype="integer" null="false" />
		<field key="phone_number" dbtype="varchar" precision="15" phptype="string" null="false" />
		<field key="suite" dbtype="varchar" precision="150" phptype="string" null="false" />
		<field key="hours" dbtype="varchar" precision="75" phptype="string" null="false" />

		
<index alias="locationID" name="locationID" primary="false" unique="false" type="BTREE" >
			<column key="locationID" length="" collation="A" null="false" />
		</index>
	</object>
</model>

5,888 Posts

Bruno17 Reply #29, 13 years, 10 months ago

could you also post a mysql-dump of the table-structure, please?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!