SLSCoder

I've got a problem with this. The oc_order will have a filter on it and the fields don't match the fields being requested. I realize I could make sure all the filter fields are included in the derived table but that gets involved and I'd rather not. My intention is to reverse the JOIN and then add the whole table in another JOIN: . . . FROM oc_order_product INNER JOIN (SELECT order_id FROM oc_order ORDER BY order_id LIMIT 0, 2) AS tblLimit ON oc_order_product.order_id = tblLimit.order_id INNER JOIN oc_order ON oc_order_product.order_id = oc_order.order_id WHERE oc_order.lastname = 'Norder'; I could alternatively add the requested fields to the derived table, omit the second JOIN and write the filter like: WHERE oc_order_product.order_id IN(SELECT order_id FROM oc_order WHERE oc_order.lastname = 'Norder') I'm thinking that using the second JOIN will be at least as fast and the second JOIN would be easier for me. Can I do it this way? Is the second JOIN at least as fast as the alternative using IN?

Thanks for responding - again. The code is pretty complex due to the options given to the user but I think I can pull this off. Once we simplified it to a basic orders -> items query it was much easier to see. I'm kind of kicking myself for not seeing it. The solution you gave me is easy to implement. Thanks again for your help.

Thanks for responding. I'm not sure I understand. Are saying just use PDO, and make the user inputs PDO labels and let the code recreate it every time it's used? The query will change (slightly) each time it's used due to the paging. I think I"m going to use LIMIT and not an ID range for paging.

Barand: Thanks for responding: No, it's not a spreadsheet - quote "the query includes joins from multiple tables." I would think for an experienced database programmer it should be easy to see. Again, the 'code' is generated dynamically and pretty complex but think about it: imagine a report on companies, each with multiple employees. Each 'record' is a company including all its employees. or a report on purchase orders, each order including multiple items. Each 'record' is an order including all its items.

My reporting app dynamically creates a rather complex query including fields and a filter derived from client side user input. The query would include joins from multiple tables. It pulls records, 20 at a time so paging will be used: My Post About Paging I'm concerned about 2 things: speed and SQL injection. Since the app will use paging I'd like to create a stored procedure that can be called repeatedly. It would be created dynamically, based on user input. The fields and the filter in the stored procedure would be created based on user input but would not change once the report is created. I'd rather NOT make the parameters of the filter MySql 'parameters' because they won't change (and I want it to be fast) but I'm concerned about potential SQL injection. The only 'parameters' that will change are the RecordIDs in the range I need for the next page. Is there any way I can create a stored procedure and make the filter (from user input) NOT require parameters that must be included every time the procedure is called? That is, is there a way to write the filter from user input into the stored procedure without the filter values being variables and not have to worry about SQL injection?

My reporting app dynamically creates a rather complex query including fields and a filter derived from client side user input. It pulls records, 20 at a time so paging will be used. Each report "record' will potentially include more than 1 row from the query results. Of course, the query includes joins from multiple tables. I have to use 'last id', not just LIMIT to determine where to start each subsequent page and use 'id' to determine how many rows to pull. So, it'll look a bit like 'WHERE my_table.RecordID > 324 AND my_table.RecordID <= 357 <plus the filter>'. One reason for paging is to speed the app up. Only display 20 records -> only pull 20 records. Again, I can't just use LIMIT because each 'record' will potentially include more than one row. Also, the result RecordIDs will not be sequential because of the filter. That is, if the last ID was 324 I can't query for records up to ID 344. The only way I know to determine the correct RecordID range is to query the database using the filter and query ALL RecordIDs after the last ID and then count 20. That's SLOW. I have to query the database twice, • once querying for just the RecordID but ALL RecordIDs (not just 20 of them) after the last ID, • count the first 20 and • re-query the database and include all the fields and include the RecordID range I need for the 20 in the filter (as shown above). My question: Is there a better way to do paging, given my circumstances?

I told you once, I've been writing database apps using web technology for about 25 years. About 20 years ago I gave up on identifying foreign keys in the database because apps kept crashing on me. You suggested that I try it again. Well, the app I'm working on is probably about 50K lines & 80 tables. To do that would be some work BUT - my app allows users to create tables and dependent (slave) tables for them. There could be up to 20 user defined tables. Coding for orphans and missing IDs was becoming a problem. Because of your suggestion, the thought immediately occurred to me - hey, use foreign keys & cascades. So, I did the work (and it wasn't that bad). Now my whole database uses foreign keys and SET NULL or CASCADE as needed. I scrapped a BUNCH of code that handled orphans & missing IDs. The app is working great. Thanks so much for pushing that issue.

OK Thanks. We work and work and till wee hours and forget about what else is going in in the world. So, I forgot to say: Merry Christmas to you and yours. I hope you and your family have a wonderful holiday..

Thanks. I think I'll give it a try. It'll be interesting to see what my current orphan status is. I'm guessing real low or even non-existent. I'm pretty careful about it. Real easy to find out; just try to create the foreign key Yea my database is normalized. Some of my IDs are replicated in more than one table but that's just to avoid deeply nested joins, which definitely slows things down. I've got about 80 tables in the one I'm working on now. Some foreign keys are 3 or more tiers. It'll take some work. OK, I'll try it. It may be awhile but I'll let you know what I conclude.

I think you've got me convinced about AUTO_INCREMENT. It's just that bigint makes a slower/bigger DB than int makes a slower/bigger DB than smallint. I'm thinking if I can keep the ID lower I can use a smaller number which means a smaller, faster database. Yea, I get orphans. I just clean them out programmatically. Like in a deleteCompany function I'll add a DELETE FROM people WHERE CompanyID NOT IN (SELECT RecordID FROM company) after I delete the company. You're suggesting an auto-cascading delete by using foreign keys? I'm thinking. My way is slower I suppose but I never get into trouble. When I tried using foreign keys in the past it seemed impossible to keep the database happy. It kept throwing errors that I could avert by doing it manually. It was admittedly a long time ago. I don't really worry about the reverse engineering/understanding thing. RecordID->CompanyID is pretty clear and I use that convention consistently. "There are right/better ways to do things and Foreign Keys in an RDMS is one of them." As I recall, that originated with Larry Ellison & Oracle with the first real relational database. I've heard/seen that for years but after trying it I was never convinced it was true. Sorry, I *never* do things just because everybody says it's right. The Holy Bible is *not* infallible even though the Christians insist that it is. You've kind of got me revisiting it. So far, the only thing I see that you've got is orphans (which is something to consider). Anything else to add? Oh, and I do appreciate your input. I rarely get to chat with professional database programmers. Most of them really are just hacks.

Thank you. I use relationships all the time and I'm aware of deleting records that are foreign keys in other tables. Where I'm deleting the records all that is easily handled. I don't set foreign keys up in the database. I've done it for many years, all the way back to dbase. I tried setting up foreign keys once, cascading delete, etc. What a mess. I never have any problem with relationships now. My primary key is always RecordID and foreign key is <tablename>ID. RecordID, not ID because I ran into trouble years back programming C#.NET. Microsoft uses ID and it kept conflicting with my ID. I gave up and changed my ID to RecordID. The primary key really is a primary key. The foreign keys are not. Can you give me a good reason to set the relationships up in the database? Maybe you can talk me into doing it.

Are you just busting my chops now? Nope, I don't see it in this thread anywhere. I created the query in Navicat. It did occur to me that maybe there really is no such thing as a 'saved query' in mysql, like a stored procedure. Maybe Navicat is just storing the sql in a file somewhere? That would explain why you guys are so confused. I'm not asking about sql. Clearly, I know the sql for it and I do know how to truncate a table (which I do not want). I guess if that's the case then I just need to change the 'query' to a stored procedure.

Guys, I think you *completely* misunderstood me. All I asked for was how you call an existing mysql query, needing no parameters, using mysqli. Does it really matter that much why I need it? My users delete records (that are irrelevant once they delete them), and sometimes in high quantities. I do want those records deleted and I want to reset the AUTO_INCREMENT. If I set it to 1 mysql sets the AUTO_INCREMENT to the lowest one possible. If records still exist (and they usually will) it won't end up being one. It'll be the ID of the last record not deleted. That's what I want. If I hadn't shown you the query we wouldn't have gotten into all this. There may be other times when I need to just call a query that I already saved. Maybe it would have been easier if I had just made it a full blown stored procedure? I looked everywhere on the internet and I just can't find it.

Thanks but I don't want to delete all the records in a table. What I asked was, how do I call a saved query that does not require parameters using mysqli?

I usually get really great answers here. Today you guys just aren't going to are you? I've been doing this 25 years now. Noob Schnoob. I want to reset it when I delete records. I want to delete the records. Ya know, phpforum.com is pretty much useless. You guys were my only hope.

Can you give me an answer please? I need to run it when records get deleted. Why does that matter to you?

I created a query (not stored procedure) in mysql. It does not require any parameters. How do I call with a mysqli object? The query name is setQBAutoIncrement The code is: ALTER TABLE question_banks AUTO_INCREMENT = 1; ALTER TABLE qb_sequences AUTO_INCREMENT = 1; ALTER TABLE qb_sequences_rl AUTO_INCREMENT = 1; ALTER TABLE qb_questions AUTO_INCREMENT = 1; ALTER TABLE questions AUTO_INCREMENT = 1; ALTER TABLE q_cx_columns AUTO_INCREMENT = 1; ALTER TABLE q_cx_rows AUTO_INCREMENT = 1; ALTER TABLE q_predefined_answers AUTO_INCREMENT = 1; ALTER TABLE q_tdt_rows AUTO_INCREMENT = 1; ALTER TABLE scales AUTO_INCREMENT = 1; ALTER TABLE scales_rl AUTO_INCREMENT = 1;