PHP form code is giving error message: SQLSTATE[23000]: Integrity constraint violation: 1452 and referencing old table

[ianc01]

Hello everyone,

 

I am developing a website for a friend's restaurant. For this site, I am setting up the site with their lunch & dinner menus, the ability to reserve a table,  and to do online food ordering. So far, I have the menus working (just have to add photos for each menu item). I am in the process of developing the table reservation and online food ordering pickup/delivery.

I decided to use a new MariaDB database and switch over to the new one. The old database listed all the menu items in a table called "menus" whereas in the new database, I split this into two tables: "lunch_menu" and "dinner" menu. I managed to get the lunch and dinner menus to display on their own pages and they work (getting the menu items and displaying them).

The problem I am having is that in the process of changing the code for the food ordering, part of it still is trying to access the old "menu" table and I can't find where I went wrong. I am missing something somewhere and am wondering if someone would be able to see what I am doing wrong.

Here is the full HTML/PHP code I am using:

When I am testing the page, it displays all the correct menu items from the lunch_menu table. When I fill in the customers information and submit it, I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`thaicook3`.`in_order`, CONSTRAINT `fk_menu` FOREIGN KEY (`menu_id`) REFERENCES `menus` (`menu_id`))

It is still referencing the menus table and I can't see why in the code. If someone would be able to spot where I am going wrong, that would be great.

Thanks in advance.

 

lunch_menu.txt

[mac_gyver]

the attached code displays the menu, but has nothing to do with adding items to an order (cart), entering the customer information, or submitting/saving that information as an order.

some points about the posted code, most of which will greatly simplify it (eliminating more than half of the typing) -

1. use 'require' for things your code must have.
2. don't prepare and execute a query that doesn't have any dynamic value being supplied to it. just use the ->query() method.
3. the settings don't need elseif() logic, just if(), because the option_name can only be one value at any time.
4. Don't Repeat Yourself (DRY.) there are only a few small things different between the corresponding if/else code blocks. the conditional logic should only setup the values for the different things in variables, then simply output them in one instance of the code.
5. don't run queries inside of loops. use a single LEFT JOIN query to get the menu categories and menu items all at once. when you fetch the data, index/pivot it using the category id as the main array index. you can then simply loop over the data using two nested foreach(){} loops to produce the output.
6. don't create unused variables/code.
7. when embedding a php echo statement in html markup, use php's short-open-echo tag <?= and you can leave out the closing ; right before a closing ?> tag, for example - <?=$source?>
8. SELECT queries that can return more than one row need an ORDER BY ... term to insure that the rows are in an expected order.

 

 

Edited February 6 by mac_gyver

[gizmola]

2 hours ago, ianc01 said:

 

I decided to use a new MariaDB database and switch over to the new one. The old database listed all the menu items in a table called "menus" whereas in the new database, I split this into two tables: "lunch_menu" and "dinner" menu. I managed to get the lunch and dinner menus to display on their own pages and they work (getting the menu items and displaying them).

 

So your first idea was to make a non-relational goof up.  

You have a menus table and that should support any and all menus, especially if menu relates to other things.  

Simply adding a menu_type attribute in menus would have let you designate one from the other and to have other menus (like a happy hour, or thanksgiving, etc) as needed in the future.  You literally did what any DBA would tell you not to.  

You should go back to the drawing board on that.

 

Quote

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`thaicook3`.`in_order`, CONSTRAINT `fk_menu` FOREIGN KEY (`menu_id`) REFERENCES `menus` (`menu_id`))

This comes from the database, not from code.   In other words, it is data related. You are trying to insert or change data somewhere in the system, which is not in the code you attached, as was already noted by mac in his reply to you.

Just looking at that code mac provided a lot of excellent code quality and best practice suggestions, but it doesn't address the disconnect between what you seem to understand or not about your database and how constraints work and what they do.

Apparently there is a foreign key constraint on the thaicook3.in_order column, which requires that any value added to the in_order column, must be a menu_id that exists in the menus table.

 

The very names of this table (thaicook3) tells me a lot about how ill conceived this system must be.

Having a non-relational schema in a relational database is the road to system ruin.  

We have a lot of veteran developers that visit phpfreaks who are quite willing to advise and mentor people on how to come up with a proper relational design, that will be easy to use, and allow for a flexible "data driven" system.

Scanning the code you did provide, just having queries that contain things like WHERE id <> 9 is one of many examples of things that have been done due to lack of good design, and coding practices.  

Even if you were to argue that row id 9 in a table is "special" for some reason, you should have that in a constant or at least a boostrapped variable rather than hard coding the magic ID # into queries.    

There are much better ways (adding columns for example) to allow the system to determine if a row has a special property that should include/exclude it.

If your goal is to produce a buggy spaghetti system, then continue on as you are, or you can take the time to socialize the database design with an ERD or even a database dump of the structure, and follow up with some basic requirements.  

 

[ianc01]

Thanks for the replies. As you can tell, I am fairly new to HTML/PHP coding. When I agreed to do this website, I didn't realize how difficult it would be; I bit off more than I could chew.

Since I am new, I used an HTML/PHP template that is opensource from the Internet. This should show you my skill level. Please let me know what are good places to learn this properly since I am interested in website design. I know now that going this route may have seemed like a good idea but has actually been a detriment to me. I hope that I didn't offend anyone by going this route.

mac_gyver, you gave me some great areas to incorporate into my future learning. I am going to relearn everything from the beginning for PHP/HTML/MySQL so that I can have a better understanding of everything. I found a good site to start learning from -> W3Schools.com. If anyone knows other sites to learn from, please let me know.

As for the solution. gizmola gave me an idea when he said:

Quote

This comes from the database, not from code.   In other words, it is data related. You are trying to insert or change data somewhere in the system, which is not in the code you attached, as was already noted by mac in his reply to you.

 

I went and looked at the foreign keys set up in the sql file provided with the template and saw that they did reference the original database provided. I have since edited the keys to suit my changes.

Like I said, I hope that this doesn't reflect badly on me for using code that was originally done by someone else.

Thanks.

[gizmola]

11 hours ago, ianc01 said:

 

I went and looked at the foreign keys set up in the sql file provided with the template and saw that they did reference the original database provided. I have since edited the keys to suit my changes.

Like I said, I hope that this doesn't reflect badly on me for using code that was originally done by someone else.

 

We get this type of question all the time.  Our purpose is to provide a place to mentor and teach people who are genuinely trying to create systems and learn and improve.  Everyone has to start somewhere.

With that said, it doesn't seem as though the thing you started with is very well designed or suitable to what you are trying to do, but then again we don't really know the details of what you've agreed to provide for your friend or on what timeline.

Creating an online reservation system is a non-trivial exercise by itself. OpenTable's cost for their most basic tier of that service is $149/month, so that should tell you something. As a beginner, that is certainly biting off more than you should be in my opinion.  The problem with doing something ill conceived or broken equates to literally lost revenue for the restaurant.  Let's say that it doesn't work right and people attempt to make reservations and the system doesn't allow them to, or erroneously shows that a reservation can't be made, or double books, or overbooks etc.  The result will be people who wanted to come to the restaurant instead going somewhere else.  Operationally, the restaurant staff has to be able to (and will be required to) interact with the system throughout the hours of operation.  It is no small task, and yet you are approaching this in a way that suggests little thought was invested into this project. 

Personally, I'd take that right off the table, as just handling the online menu and ordering is already a large job.

This also will involve payments and interaction with a payment gateway.  Anything less than that, and there is going to be manual entry of things, and storage of credit card data that you aren't legally allowed to perform.

So, yes I would agree that you have greatly underestimated this task.  

From the discussion of this so far, you did not even acknowledge my comments regarding making a lunch_menu and dinner_menu table, which I already told you was a mistake.

You thought this was a good idea because you saw that you needed 2 "types" of menus right now.  ('dinner', 'lunch').

Let's continue this line of thinking then, and apply it to a menu item.  I'm sure that menu items have different categories like ('appetizer', 'beverage', 'main course', 'dessert') etc.  

So that must mean that you will have at least 4 new tables right?  menu_item_appetizer, menu_item_beverage .... etc., right?

And that would be a disaster for your project!

 

Here's the reality:  any database driven (database connected) system is only as good as the underlying database structure.  That is where you should start.  Rushing to build out screens and write/modify code and create sql query strings is the wrong way to do this.

The analogy I like to use for any rdbms based system is that the database design is like the blueprint for a building, that then becomes the basic structure.  If you provide a blueprint for ranch home, you can't then decide later that you really wanted a 3 floor apartment building, and think you're going to have success adding that on top of your existing ranch home.  You take the time to have a blueprint for an apartment building.

You need a verified and validated database structure 1st.

I will say again to be clear:  if you need help with your database design (which is clear to me) then you need to provide us the current database structure so we can examine and advise you ASAP.

Changes to database structure can be accomplished quickly, but the time to do that is at the start of the project, not after you've written a bunch of code which you will need to change. 

Sometimes you do need to make adjustments or improvements to the database structure once you are well into development, but you should have, to the best of your ability a structure that supports your needs from the outset, and you probably don't have that now, and have demonstrated you don't understand database design well enough to try and do this on your own without some experienced people advising you.