Handle MySQL Errors

Hi.

I have an email field that I need to be unique. The form works fine IF the email address entered was never used before. But IF an email address that is already used is submitted on the form, the page goes to one of Joomla's 404 error pages with the MySQL error in the title bar.

How should I handle errors that are from MySQL?

Effectively, I would like the form to return to the form page and tell the user that the email already exists.

Thanks in advance. Any help will be appreciated.
Winchester

404 error is Page not found error. It has nothing to do with MySQL error, which should throw up error 5xx instead. The MySQL error is because CF7 is trying to add a new row with a duplicate email address, but is prevented because email field is UNIQUE.

To do what you wanted, do this:

Page 1 - form, submit to Page 2
Page 2 - "Read Data" to check if {data:email} exists in database. Then under the "Found" green box within it, put Redirect to Page 1 (found means email exists). Under "Not Found", put a Save Data block into it.

Thank you so much. I'm going to give this a try.

ok.. Question. The email field is on Page Two. So, this Read Data should go on that page? If so, what instruction should I set under not_found?


Also, should I set a condition (Run Conditions) for the redirect to check if the renewal field is checked?


I think the biggest part of the difficulty in CF7 is that the terms "field" and "value" are used way too liberally. It becomes a guessing game if it's asking about the form field/value or db table field/value.

Yes, when you submit the form in Page 1, the data goes to Page 2. So on Page 2 you receive the email data, which you use to check the database.

Not found means the email doesn't exist in the database, so you can add Save Data to save a new row. I've never used the Run Conditions so not sure how it'll work..

Actually I'm confused about how your form works. Do you want to check whether the renewal field is checked first, before checking if email exists? What does your process look like? Something like:

1. Form submit

2. Check if renewal field checked
a. if yes, go to 3.
b. if no, go to 4.

3. Check email exists
a. if yes, save new row
b. if no, redirect back to 1

4. Update row

Ok. Give me a few minutes. I'm going to create a flow chart. This will put us on the same page.

Here's the flowchart that we can use. It's a bit simplistic but still explains enough.

https://pasteboard.co/xgQ4wqTpoIcm.png

Also, here's a screenshot of my 1st attempt to set up the actions for this.

https://pasteboard.co/qmIohRXYBqDJ.png

Will try to explain what I'd do coherently..

Page 1:
HTML block
Button (next page)

Page 2:
Form block
Checkbox field (for renewal)
- Value = 1
- Under Data, add "Ghost Field", and set Ghost value = 0
Button (submit to Page 3)

Page 3:
Read Data (checks email)

If found:
Save Data (update) - Run Conditions ({data:renewal} = 1)
Redirect (back to Page 2) - Run Conditions ({data:renewal} =0)

If not found:
Save Data (insert)

The only caveat is I've never used Run Conditions but that is what I presume it'll do. We seem to be missing a "Switch" block in CF7, but if really stuck you can just use PHP code instead to check {data:renewal}.

This is fantastic, Wbuk. Thank you so much.

tonight and tomorrow I will be working on upgrading the 2nd website to J4. As soon as I'm finished, I will go back to the form and implement your actions.

Thank you so much, I truly appreciate it.

Just check the Run Condition on the actions.

On Save Data, under Advanced there is a Where Conditions as well as Run Conditions under Data. Where Conditions gives you the brackets, "()", while Run Conditions doesn't.

On Redirect, it only has Run Conditions under Data.

Once question about your action solution. What about the end_page? I don't need to put any actions there?

Where Conditions is for the SQL query. Eg SELECT id FROM #__users WHERE 'name' = 'Joe';

Run Conditions is not part of the SQL query. I've not tested but I think it's for the block to run if the conditions are met, eg {data:renewal} = 0.

You can either use Page 3 as the last page, or you can do another check to check if the data has been saved successfully. Eg put a redirect after Save Data to end_page, and on end_page check whether the form data is saved successfully.