PHP Database

[thescientist]

also, you may want to consider an alternative to deleting the record outright as there is an optional method of just "disabling" the record instead. In this way, you add an additional column, something like "active" that you set to 0 (false) or 1 (true). Start every task with a default value of 1, and when you want to "delete" the task, you can just flip the value to 0. When you pull from the table, and a where clause to your query to only pull active tasks, i.e.

SELECT * FROM tasks WHERE active = 1

depending on the nature of your data, it might be helpful to persist this data as opposed to outright deleting it. For instance in a users table, you may disable a user if they leave, and then just re-enable them if they come back, simply by flipping a switch as it were. Just a thought.

Edited November 9, 2012 by thescientist

[ProSpartan]

As for the auto-increment, there's no reason to reset it. Just let it keep increasing, there's no problem with that.

Okay, but then if the table is empty, i don't really want to be adding task 57 of 1 task.

also, you may want to consider an alternative to deleting the record outright as there is an optional method of just "disabling" the record instead.

Okay, i'll keep this in mind for when i make a database with users. But over time, the database will get bigger, taking up more space, so deleting the entry will help with disk space management. So back to my three question that i asked,1. which was answered.2. Resetting AUTO_INCREMENT3. From tasks x, how do i decrease the numbers up from there, tasks x, up.eg for 3. I delete tasks x. I want tasks x+1, x+2, x+3, x+4 to be decreased down to x, x+1, x+2, x+3. how?

You can use COUNT to get the number of records in a table: SELECT COUNT(*) AS num FROM table

As for this, do i assign it to a variable, '$num = "SELECT COUNT(*) AS num FROM table" ', or is 'num' the variable created that contains the count, and called via '$num'? Edited November 10, 2012 by ProSpartan

[justsomeguy]

Okay, but then if the table is empty, i don't really want to be adding task 57 of 1 task.

But why does that really matter? You can have another column, called "sort_order" or "display_order" or "task_num" or whatever that you can number sequentially, but in practice it shouldn't matter what the ID of the record in the table is. The ID is supposed to be unique, it wouldn't be unique if you kept resetting it. If you want to change the auto-increment value then you need to use an ALTER TABLE query to do that. I wouldn't recommend it though, if you need to reset that ID then you're probably not using an auto-increment correctly. You probably don't need an auto-increment. I've never needed to reset an auto-increment column, it doesn't matter in my applications what the actual ID is. If I need the records sorted in a user-defined order then I use a column just for the sort order. The major reason why you use unique IDs is for foreign keys in your tables. Using users as an example instead of tasks, each user has a unique numeric ID. They can also have a username or email address or whatever else, but they need a unique ID that isn't going to change. If you poke around in this forum you'll see that my user ID is 4190, but that isn't important to me using the forum, I never need that number. The forum uses it though. When I make a post somewhere, it sets the author of that post to 4190, not my username or anything else. There are also permission tables where it lists which permissions I have, and it uses my ID there also. The reason it does that is because my ID never changes. If you're going to reset IDs then you also need to go around updating all of the other tables that list those IDs for whatever reason. Since my ID is stored instead of my username, I can change my username and it doesn't need to go around updating all of the other tables because those tables use my unique unchangeable user ID instead of my changeable username. The point is to avoid having to update a bunch of other tables when things change, so there's no reason to reset the ID and have to do additional work. Even if your database doesn't use the ID as a foreign key in other tables, it's just a bad habit to get into.

From tasks x, how do i decrease the numbers up from there, tasks x, up. eg for 3. I delete tasks x. I want tasks x+1, x+2, x+3, x+4 to be decreased down to x, x+1, x+2, x+3. how?

You need to get the task number for the task that you just deleted and use an update query to decrement anything higher than that. e.g.: UPDATE table SET task_num = task_num - 1 WHERE task_num > X

As for this, do i assign it to a variable, '$num = "SELECT COUNT(*) AS num FROM table" ', or is 'num' the variable created that contains the count, and called via '$num'?

num is the name of the column returned by MySQL, you access it the same way you access any other column from a database query.

[ProSpartan]

Thanks. That's all the question i have for now. Thanks everyone who helped me on this project. Couldn't have done it without all y'all!

[ProSpartan]

I'm back with a new problem, haha. When i enter text into the textarea of my html page to submit it to the list, but what it does not like is when i use an apostrophe. the error i get is: Error: You have an error in your SQL syntax; check tge manual that corresponds to your MySQL server version for the right syntax to use near ' some text about the input ) ' at line 1. Any thoughts of what could be done?

Edited November 12, 2012 by ProSpartan

[thescientist]

yes. and this is something I meant to point out earlier. you should never blindly take user submitted data from a form without validating it. At the very least use mysql_real_escape_string on any data going into your database that is user supplied. http://php.net/manua...cape-string.php even better though it use mysqli or PDO, depending on what your webserver supports.

Edited November 12, 2012 by thescientist

[ProSpartan]

Okay, thanks. It worked.