How do I call a saved query?

[SLSCoder]

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;

 

[Barand]

It doesn't look like the sort of query you would ever need to run more than once, so why store it at all?

[SLSCoder]

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

[benanamen]

21 minutes ago, SLSCoder said:

need to run it when records get deleted.

Why?

If you are deleting all the records you can truncate the table if there are no foreign keys. If you are just trying to keep a continuous id number for left over records, don't do that. It is a pointless noob thing and will fail when you start using foreign keys. I tried the same thing when I was new. If you are keeping some real data,  a better practice is to create a deleted flag column and just mark the data as deleted rather than actually wiping it from the DB.

 

23 minutes ago, SLSCoder said:

Why does that matter to you?

We are here to teach people, not just answer an OP's attempted approach to a problem (See XY Problem). You don't know what you don't know that we most likely know and can tell you the better/correct way to solve the real problem. When posting it is best to tell us the real problem you are trying to solve rather than ask how to solve what you think is the way to solve the real problem.

[SLSCoder]

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.

[Barand]

Then, as @benanamen said, truncate the tables. That will remove all the data and reset the auto-inc to 1. It's also far faster the a delete query.

And to answer your question...

SQL file (atest.sql)

TRUNCATE TABLE player
TRUNCATE TABLE roster

PHP

foreach (file('atest.sql') as $sql) {
    $conn->query($sql);
}

 

Edited December 23, 2020 by Barand

[benanamen]

If you truncate ALL the tables in the DB you can use this...

 

<?php

$pdo = new PDO('mysql:host=localhost;dbname=db_example', 'root', '');

$tables = $pdo->prepare('SHOW TABLES');
$tables->execute();

foreach($tables->fetchAll(PDO::FETCH_COLUMN) as $table)
{
    $pdo->query('TRUNCATE TABLE `' . $table . '`')->execute();
}

 

[SLSCoder]

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?

[Barand]

Then why the **** do you want to set the auto_increment back to 1?

Edited December 23, 2020 by Barand

[benanamen]

Well, there is an hour of my life I am never getting back. Why do I even bother?

[SLSCoder]

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.

[Barand]

9 minutes ago, SLSCoder said:

I looked everywhere on the internet and I just can't find it.

Have you looked in this thread - about an hour ago?

[SLSCoder]

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.

[benanamen]

Navicat saves query's within itself as part of the program.

Quote

I do want those records deleted and I want to reset the AUTO_INCREMENT.

Based on the additional info you provided, there is no reason whatsoever to reset the auto-increment. And as I already said, once you start creating a real relational DB with keys you wont be able to do it anyways.

If you actually want to be able to call a "saved" query at will from the DB will you will need a stored procedure or run the saved query in Navicat which actually is a "saved" query.

Edited December 24, 2020 by benanamen

[SLSCoder]

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.

[benanamen]

2 hours ago, SLSCoder said:

Can you give me a good reason to set the relationships up in the database? Maybe you can talk me into doing it.

Sure, if you can tell me why the obsession with resetting the auto-increment.

I will go first to save a post....

#1 reason to use Foreign Keys is to enforce Data Integrity. You can try and manage it yourself but if you get it wrong you will end up with orphan records (Bad Data). Wouldn't surprise me at all if you already have orphans. As soon as you try to set the FK's you will know right away if you do. It also clearly defines the links between tables (That would be the R in RDMS, Relational, AKA Relationships)

2 hours ago, SLSCoder said:

I tried setting up foreign keys once, cascading delete, etc. What a mess.

Then you didn't learn how to do it. It is not complicated and is a great feature, especially when multiple table record deletes are involved at the same time. The more tables involved, the more code needed and the more chance of something breaking. You also unnecessarily increase your technical debt.

Another thing, if anyone, including your future self does a reverse engineer data model it is instantly clear how data is related to other data.

It is not up to me to talk you into anything. I am not the one that has to work with your DB. There are right/better ways to do things and Foreign Keys in an RDMS is one of them.

Edited December 24, 2020 by benanamen

[SLSCoder]

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.

[benanamen]

Here's what I would suggest..

1. Back up your DB and Code-base
2. Clean up your orphans
3. Add the Foreign Keys
4. Learn and implement cascading update/delete
5. Clean up the unnecessary code

You have been doing it "your way" long enough that the difference and time/effort savings will be very apparent.

In a nutshell, but some fresh eyes on it and just try it. There really isn't much more to say about it.

* I am assuming your DB is already normalized. If not, now is the time to fix it.

Edited December 24, 2020 by benanamen

[SLSCoder]

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.

[benanamen]

When you are done, post a sql dump of your db tables and we will review it for you.

[SLSCoder]

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..

[Barand]

2 hours ago, SLSCoder said:

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.

I thought I would check out the impact of the larger key. I creted two tables, one with a smallint PK and the other using bigint.

CREATE TABLE `keytest_si` (
  `id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `keytest_bi` (
  `id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I looked for my largest table that would fit in a smallint. I then wrote the ids from this table to each of the two new tables.

mysql> select count(*) from production_status;
+----------+
| count(*) |
+----------+
|    60000 |
+----------+
1 row in set (0.01 sec)

mysql> insert into keytest_si (id) select id from production_status;
Query OK, 60000 rows affected (1.67 sec)
Records: 60000  Duplicates: 0  Warnings: 0

mysql> insert into keytest_bi (id) select id from production_status;
Query OK, 60000 rows affected (1.86 sec)
Records: 60000  Duplicates: 0  Warnings: 0

That's a difference of 0.2 seconds over 60,000 records. The added cost per record insert is therefore 0.000003 seconds. Do you really think that's worth the worry and the extra processing it's causing you? (Which probably takes far longer than the time saved)

And Merry Christmas to you too.