Query to create a category page?

14 posts by 3 authors in: Forums > CMS Builder
Last Post: July 16, 2014   (RSS)

By Perchpole - July 15, 2014

Hello, All -

The addForm.php and uploadForm.php files are great for creating new records but how can I adapt one to create a new category? I have tried to construct a query which will allow me to do this but without success.

 $query = "INSERT INTO `{$TABLE_PREFIX}category` SET
                        name            = '".mysql_escape( $_REQUEST['name'] )."',
                        createdDate      = NOW(),
                        etc...
                        ";  
      
  mysql_query($query) or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
    
  $recordNum = mysql_insert_id();

I have tried every possible configuration of the above code - but it just won't work! Clearly there must be some special rules governing the creation of categories using this method.

I'd be grateful for any help!

:0)

Perchpole

By claire - July 15, 2014

Hi Perch, what kind of errors are you getting? And what format are your categories in right now?

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

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 15, 2014

Hi, Claire

Thanks for your help. I'm not seeing any errors. The query just doesn't have any effect. However if I use identical code (with a slight modification) and point it at a record table it works flawlessly.

Im not sure what you mean by "format".

Perch

By claire - July 15, 2014 - edited: July 15, 2014

Sorry, bad choice of words.

What does the working query look like?

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

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 15, 2014

The query for making a normal record is like this:

$query = "INSERT INTO `{$TABLE_PREFIX}record` SET
                      title            = '".mysql_escape( $_REQUEST['title'] )."',
                      content          = '".mysql_escape( $_REQUEST['content'] )."',
                      dragSortOrder    = '".time()."',
                      createdDate      = NOW(),
                      updatedDate      = NOW(),
                      createdByUserNum = '0',
                      updatedByUserNum = '0'";  
      
  
  mysql_query($query) or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
    
 $recordNum = mysql_insert_id();

This works flawlessly every time. If I change the table to point at my category table, on the other hand, nothing happens.

I've gone through the fields to make sure I'm not trying to INSERT the wrong data. There are no problems there.

However, I feel that perhaps Categories may need to have some default data set in the query - that's why it isn't working?

:0/

Perch

By claire - July 15, 2014

Well, the most obvious place to look is the category table itself. Do any records show up? Are there any other fields there that need to be filled in? Are you suppressing errors, by any chance?

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

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - July 15, 2014

I think I may have found the problem. Bear with me...!

By Perchpole - July 15, 2014

Claire -

I've nailed it! I added/changed the code in red below...

  $recordNum       = null; // you must set either $recordNum or $preSaveTempId to null
 
  $query = "INSERT INTO `{$TABLE_PREFIX}category` SET
                    name            = '".mysql_escape( $_REQUEST['name'] )."',                   
                    createdDate     = NOW(),
                    etc...
                   
  
  mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
  $newRecordNum = mysql_insert_id();

...and that seems to have made all the difference!

I've been using the query code from the old addForm.php file - but there have been a  number of updates since it was first made available on this forum. The more recent Uploadform.php includes the modification hi-lighted above.

Perch

By Perchpole - July 15, 2014

Claire -

The only thing that doesn't work well is the "lineage" setting.

It would be nice to set this with the query. Unfortunately you don't get a recordNum to use until after the new row has been inserted.This is bad news because even if you assign a parent number in the query the lineage does not update (automatically) until you open and close the record in CMSB - which sort of defeats the object of the exercise!

:0/

Perch