Connecting to two tables not working anymore

cre8tivemedia 04 Dec, 2012

After updating to the newer Joomla and updating Chronoforms to the latest version my connections aren't working the way they used to.

I can see that one of the tables is updating when the form is submitted but the other one is not. Not exactly sure where it's falling apart so maybe someone can point me in the right direction....

This is how I have the form wizard set up:

[attachment=2]chrono.png[/attachment]

Here are the areas in question (I think :? )

for "Custom Code - DB2_Connect" I have:

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$cat = mysql_real_escape_string($_POST[category_id]);
$mid = mysql_real_escape_string($_POST[misc_id]);
$sql="INSERT IGNORE INTO c7stn_redshop_product_category_xref (category_id, misc_id)
VALUES
('$cat','$mid')"; 

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

The above seems to be working correctly because the "c7stn_redshop_product_category_xref" data is getting filled in when I submit the form...

Next is "DB Save" -
[attachment=1]chrono2.png[/attachment]

Then "DB Record Loader" -
[attachment=0]chrono3.png[/attachment]

Like I said, not sure what I'm missing but it seems to only be writing to the "c7stn_redshop_product_category_xref" table but I need it to write to the "c7stn_redshop_product" table as well.

Currently using:
Joomla 2.5.8
Chronoforms Component 4.0 RC3.5.1
Chronoforms Plugin V4 RC3.0

Hopefully that's enough info to go on....

Any help out there would be appreciated
Thanks, Matt :wink:

cr

cre8tivemedia 07 Dec, 2012

I did a little more digging to see if I could figure out what's going on... Still lost but I did find this:

In the "DB Save" tab under advanced I noticed something that may be a problem -

Here is a screenshot when I first open it up, shows the table the way it should be (I think). I haven't changed anything from before when everything was working correctly...

[attachment=1]advanced-1.png[/attachment]

Now, if I click on "Refresh Tables" it shows "DB Connection Failed!"

[attachment=0]advanced-2.png[/attachment]

I've double checked the database name, username and password. They all are correct...

Can anyone out there tell me what I'm missing?

Gr

GreyHead 12 Dec, 2012

Hi cre8tivemedia,

I'm not sure why you are using that tab at all - it looks as though you are updating a table in the normal Joomla! database in which case you can leave all of these boxes empty.

Bob

cr

cre8tivemedia 12 Dec, 2012

Hi Bob,

I took all of the info out of the advanced tab and still having same results... Can you see anything in my first post that may be entered wrong? Kind of at a loss of what to do next

thanks for the help
Matt

Gr

GreyHead 15 Dec, 2012

Hi cre8tivemedia,

I just built a little test form to make sure that the DB Save is working correctly in the latest release - I used RC 3.5.1 for J1.6+. Everything seemed to be OK.

Please try moving the DB Save before your Custom Code and see if that makes any difference.

Bob

cr

cre8tivemedia 17 Dec, 2012

Thanks Bob that did the trick!

Now that I have that working correctly my redirect isn't working....
This is what I have in there

<?php
$mainframe->redirect(JURI::root().'index.php/stock-boxes/rsc/'.$form->data['product_id'].'/175/regular-slotted-carton-rsc/'.$form->data['product_url']);
?>

and I have a hidden field for the product_id loaded with the form. I can see the product_id in the database so I know that it's being generated correctly. Just unable to pull the information from the DB. I'm using the DB Record Loader to retrieve it... You can see the settings for that in my previous post

This is the only part that isn't coming through on the redirect, everything else looks correct. I haven't changed anything with how it connects, so I'm not sure if something changed with the upgrade in Chronoforms or Joomla maybe?....

Any suggestions?
Thanks again, Matt

Gr

GreyHead 19 Dec, 2012

Hi cre8tivemedia ,

If in doubt - add a Debugger action temporarily (or several of them) to see exactly what data is in the $form->data array.

Bob

cr

cre8tivemedia 19 Dec, 2012

Hi Bob,

Been trying to figure out how to use the debugger all morning... Sorry, I feel foolish that I can't figure out how to activate it. I've tried placing the debugger in various areas in the form events but when I test the form I don't see any results it just submits the form and does the redirect. Is there some code I need to add into an event to view the results? I tried looking through the forum and the Faqs but got even more lost. Is there a tutorial for the debugger?

Thanks,
Matt

Gr

GreyHead 20 Dec, 2012

Hi Matt,

You probably need to comment out the redirect with // to see the debug output.

Or use the ChronoForms ReDirect URL and ReDirect User actions, the Debugger knows how to handle them.

Bob

cr

cre8tivemedia 20 Dec, 2012

Ok, commented out the redirect and this is what I got 😑

[list]
Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: Invalid argument supplied for foreach() in /libraries/gantry/core/gantrytemplate.class.php on line 646

Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in /libraries/gantry/core/gantrytemplate.class.php on line 607

Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: Invalid argument supplied for foreach() in /libraries/gantry/core/gantrytemplate.class.php on line 646

Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in /libraries/gantry/core/gantrytemplate.class.php on line 607

Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: mysql_real_escape_string(): 29 is not a valid MySQL-Link resource in /libraries/joomla/database/database/mysql.php on line 150

Warning: Invalid argument supplied for foreach() in /modules/mod_roknavmenu/lib/RokNavMenu.php on line 72

Fatal error: Call to a member function getDefaults() on a non-object in /modules/mod_roknavmenu/lib/librokmenu/RokMenu.php on line 64
[/list]

The only thing I recognize in there is the "mysql_real_escape_string" which I am using when I connect to the a second table. This is what I have in there:

<?php
$con = mysql_connect("localhost","myusername","mypassword");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydatabase", $con);

$cat = mysql_real_escape_string($_POST[category_id]);
$mid = mysql_real_escape_string($_POST[misc_id]);
$sql="INSERT IGNORE INTO c7stn_redshop_product_category_xref (category_id, misc_id)
VALUES
('$cat','$mid')"; 

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

Could this be messing something up? It seems to be writing to the table the way it's supposed to...

Gr

GreyHead 21 Dec, 2012

Hi cre8tivemedia,

I'm afraid that if you write custom MySQL and choose not to use the Joomla! classes then you get to debug your own code :-(

The only line that is a problem there is this one

Fatal error: Call to a member function getDefaults() on a non-object in /modules/mod_roknavmenu/lib/librokmenu/RokMenu.php on line 64

the rest are just warnings. My guess is that something in your code is getting linked into the Joomla! MySQL queries, maybe an unclosed query of some kind and it then breaking the other queries on the page. I've never seen anything quite like this before.

Bob

cr

cre8tivemedia 21 Dec, 2012

Hi Bob,

Well, that scares me that you've never seen anything like that....
Thanks for letting me know which line is the problem. At least I will have a starting point.

Maybe I'm making this more complicated than it needs to be?

Here's what my goal is:

I need to be able to write to 2 tables but I need to query them to see if a (misc_id) field already exists. This field is generated in the form from user input. If it exists, I need to pull the (product_id) field from the table into the form allowing me to use it for the redirect action.

If it does not exist I need to write to the first table creating a new (product_id) which is set to auto increment. Then, I assume I need to query that table to get the newly generated (product_id) so I can write it into the second table as well as use it for the redirect.

Is there a simpler, less messy way to do this in Chronoforms?

Thanks,
Matt

cr

cre8tivemedia 21 Dec, 2012

Update:

Looks like I got rid of the fatal error by updating the "roknavmenu" extension...

Now here is what I am showing when I use the debugger and turn off the redirect action -

The redirect isn't getting the "product_id", which I think is supposed to be coming from the DB Record Loader

As a test I changed "product_id" in the redirect to be "chronoform_data_product_id" and the redirect works. But only when I create a new "misc_id". If the "misc_id" already exists in the DB then it fails

Still lost on why it's not loading the product_id into the form

Thanks,
Matt

FYI: My previous post explains what I'm trying to do with this form....

cr

cre8tivemedia 22 Dec, 2012

Think I may have figured it out 🙂

I'll post my fix once I get some testing done and make sure it's working the way I need it to

Matt

cr

cre8tivemedia 23 Dec, 2012

Ok, not sure if this was the right way to go about it, but this is what I did and it seems to be working the way I need it to -

Moved the DB Record Loader before the DB Save, this way if the record was already created in the past it would pull up the existing data and load the product_id into the form allowing the redirect to work the way it's supposed to. After testing this I found that it worked only with existing records but if I was creating a new record then it would fail. So, I decided to add another DB Record Loader right after the DB Save and sure enough it worked.

I may be doing more queries to the DB then I probably need to, but it works and that's all that matters right now...

Thanks again Bob for the help in leading me in the right direction!
Matt