Is it possible to re-arrange primary key sequence?


i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g

1 - entry
2 - entry
(3 - deleted no mere here)
4 - entry
5 - entry

When key is not there my AJAX application fails to run properly due to not finding next id.

Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing

so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically....

Posted On: Wednesday 24th of October 2012 09:08:04 PM Total Views:  423
View Complete with Replies

Related Messages:

Help with decimal please.   (137 Views)
I am having the exact same issue as here: Unfortunately I cant find a solution. My webform is entering the data as a numeric. The database is set up to be Decimal (4,1). However anything after the decimal will only show as zero. If I go into phpMysql and enter the decimal there it works fine, so like in the other thread I am assuming that the decimal is being stripped somewhere along the way, I just cant figure out where!!
SELECT with LIKE operator (search)   (140 Views)
, I'm using the following query to search my database after matching records. But whenever the search contains a space, the records doesn't show up. I'm using a GET method from the search page, btw. Code: $query9="SELECT b_id,b_name,b_lan,b_description,b_category FROM bands WHERE b_name LIKE '%$search_b_name%' OR b_description LIKE '%$search_b_name%'"; Maybe I have to split up the search keywords and do multiple searches for each one
Optimize query to find what ranges of items exist   (117 Views)
i'm trying to get a query to tell me what price ranges exist on the site, so i need a query to look at the table and tell me that there are products that are in the $0-$99, $300-$399, and $400-$499 price ranges for example, i don't care how many, just weather they exist or not so far i have this sql Code: Original - sql Code SELECT (products_price/100) & 255 as price_range FROM `products` GROUP BY price_range ORDER BY price_range ASC
Case insensitive string/query   (116 Views)
I've got a website thats all database driven and I'm making a "site search" section where you can type in a keyword like house, beans, bread, cars and it will search the database to see if that words in any of the site content.. Basic Form: Code: Basic spitting out code: Code: $query = "select * from content where content like '%$search%'"; $Mycontent = mysql_query($query); while ($row = mysql_fetch_array($Mycontent)) { echo $row['page_title']; } Problem is that the search is case sensitive. I understand that is because of MYSQL setting and its something to do with the string being in binary but the infomation in the database not or the other way around. I took a look on this site: But I couldn't really make head or tales of it, everything I tired either stopped the search working entirely or it remained case sensitive.
Order by field with value   (110 Views)
I jsut want to order by a field, but not by the content of the field but just placing the fields that have values first.. (but not order by those values) for example, i want to order results of users by placing first the users with photos, but without ordering by the photo names .. first display users with photos and order by newest..
Insert If Condition   (201 Views)
I am trying to optimise my application. One of the parts of it is inserting a value into a table if the number of rows in that table with the same `date` field as the row to be inserted is less than 24 So currently I do two statements, one to check the number of rows and another to insert. Is there any way to INSERT something if condition. I have looked as CASE but it does not seem to do what i want. So a mock up would be: Code: SELECT COUNT(*) FROM `table` WHERE `date`='2006-01-01' If the value is < 24 INSERT INTO `table` VALUES (...)
Switching databases..   (168 Views)
Ok, I have a vbulletin forum right now and would like to switch to phpBB. But I cannot loose any members or posts. Someone told me I can switch the vbulletin database with the phpbb one, or import everything to phpbb from vbulletin(except the hacks/mods). Is this even possible Is there a program for it
distinct categories, with all albums   (124 Views)
i THINK a question i have is very similar to this. I am using MySQL 5.0.24 I have two tables, say category and album mock up of each table and some data category: id | name 1 | cat1 2 | cat2 album id | categoryid | name 1 | 1 | album1 2 | 1 | album2 3 | 2 | album3 I want to get distinct values of category, but all the values of albums when they are joined together. I tried many ways, but don't get what I want Not sure I can do it with one query. I could either do two queries, or a cursor (but read cursors can be slow). basically, i want to print out something like this after looping through the results: cat1 -album1 -album2 cat2 -album3 here are some of the queries i've tried: Code: SELECT DISTINCT gc.category,, ga.album FROM gallery_albums ga INNER JOIN gallery_categories gc ON Code: SELECT DISTINCT gc.category,, ga.album FROM gallery_albums ga INNER JOIN gallery_categories gc ON GROUP BY gc.category but none of them get what i need. not sure if doing a join is the best way to get what i need
Help with Gallery (sorting by categories, desc, max 15)   (132 Views)
, I have a portfolio and for ease, I have created a database so I can just use a mysql query to show multiple thumbnails and other such features. I have about 5 categories (ie. websites, illustrations, stationary, etc.) I want to display 15 thumbnails per category on one page (the portfolio page), in order of newest to oldest. If a category has more than 15 thumbnails, I want to have a 'view all' link in which it would take you to a page such as category.php and show all the thumbnails in that category (which I'm sure I can figure out how to do on my own). how I want my portfolio page to look: Portfolio welcome, please check out my work. websites 15 14 13 12 11 10 09 08 07 06 05 04 03 02 01 > View all illustrations 15 14 13 12 11 10 09 08 07 06 05 04 03 02 01 > View all stationary 15 14 13 12 11 10 This is my table structure: Code: CREATE TABLE portfolio ( ID int(3) default NULL, Category varchar(25) default NULL, Title varchar(50) default NULL, ThumbAltTitle varchar(150) default NULL, AltTitle varchar(150) default NULL, Width int(4) default NULL, Height int(4) default NULL, ImageSize int(5) default NULL, Description varchar(350) default NULL, DateAdded varchar(30) default NULL, ThumbPath varchar(50) default NULL, FilePath varchar(50) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; you can ignore some fields, most of them I just need to create the img tag for the thumbnail and full size image, but I figured I would include it. This is what I have so far: Code: // Initialize Some Variables $id = $_REQUEST['ID']; $cat = $_REQUEST['Category']; // Access Settings $hostname = 'localhost'; $dbname = 'dbname'; $user = 'user'; $pwd = 'pwd'; // Conect to Database $connect = mysql_connect($hostname, $user, $pwd) or die ("Error: No connection to MySQL server\n"); mysql_select_db($dbname,$connect) or die ("Error: MySQL database not selected\n"); // Database Results mysql_select_db($dbname, $connect); $query = "SELECT * FROM portfolio ORDER by id='$id' LIMIT 15 DESC"; $result = mysql_query($query, $connect) or die(mysql_error()); $total = mysql_num_rows($result); // Classic C-Style Syntax For Loop: Initialize; Test; Increment for ($i = 0 ; $i < $total ; $i++) { $row_result = mysql_fetch_array($result); // Database Rows $imgid = $row_result["ID"]; $category = $row_result["Category"]; $title = $row_result["Title"]; $thumbalttitle = $row_result["ThumbAltTitle"]; $alttitle = $row_result["AltTitle"]; $width = $row_result["Width"]; $height = $row_result["Height"]; $imagesize = $row_result["ImageSize"]; $description = $row_result["Description"]; $dateadded = $row_result["DateAdded"]; $thumbpath = $row_result["ThumbPath"]; $filepath = $row_result["FilePath"]; // Products Looping Array for vectors echo ''.$title.''; } > What happens: 15 thumbnails show in ascending order, not descending (because i'm not sure how to do this) and it is not sorted by categories (because I haven't set that up because I'm unsure how). So basically, how can I manipulate what I have so I can have a page with my categories and thumbnails (max of 15) per category displayed with a link to view all if a category exceeds 15 thumbnails (with thumbnails showing descending).
Syncronizing MySQL table with non-MySQL table   (96 Views)
I have built a PHP/C++ http send and receive function for a PDA database to send and receive data from a MySQL table designed to capture all the data. I would like to create some SQL statements to syncronize the data from this table to a separate MySQL table that holds the data from multiple PDA databases. Each PDA has a unique UserID and each record has a unique RecID for that PDA but the same RecID may exist on different PDAs. Is there a way I can build a unique key for the MySQL table based on a combination of the UserID and RecID Also are there any MySQL or SQL articles about how to syncronize a non-MySQL tables with a MySQL table using SQL statements
CASE within an UPDATE - Help with Sub Query   (104 Views)
Hi , I need help with the following sub query. I am not sure if I am even close, but I thought I'd give it a try. Basically, I looking to toggle a value. Rather than doing a select and then using the variable in a subsequent update, I was hoping to do a sub query that allowed me to check for the value of logged_in and then update it based on the value. If the value is 1, then update it with a 0. If the value is 0, then update it with a 1.
PhpMyAdmin - how do I give it my root password?   (282 Views)
I have given root a password in MySQL, but now phpMyAdmin says "access denied". I've put the following line in Code: $cfg['Servers'][$i]['password'] = 'password_goes_here'; but it doesn't seem to make any difference. How do I get phpMyAdmin to recognise my root password
Insert arithmetic operators w/o operation (5/3)?   (165 Views)
I am trying to insert some data from my biolab that takes the form of extended fractions 5/4/3/5/3, ect. The data has already been entered into a filemaker database and I am migrating it. Unfortunately, MySQL automatically reads the data as division and stores it as a decimal. Any way to disable this for certain columns, ect
InnoDB No good? Does it eat memory?   (109 Views)
Hi all, My database server is a beast. 16GB of RAM, four quad Opterons, 2TB hard disk array. Recently MySQL rebooted itself out of the blue. Our Unix admin said: Quote: The server process rebooted itself because it ran out of memory. Those InnoDB tables you use always cause this sort of problem, you should get rid of them and switch back to MyISAM Now, I've NEVER heard of this "InnoDB sucks memory" issue. I use them for insert control, foreign keys, transactions, etc. However, if they're going to cause my server to reboot periodically, that's unacceptable. What do you think Should I switch all my tables back to MyISAM I don't really NEED transactions, I can check for them manually and do it all by hand, and I can still use indexes and keys on is there enough truth behind his statement to get me to switch -Dan
Replace ascii character with unicode   (120 Views)
How do I replace the ascii version of a character in a field with the unicode version
Tuple with NULL value   (122 Views)
I have a table as below called user...and there is one tuple with NULL value of age... when i query select * from user where age =NULL; it says 0 results and also select * from user where age NULL gives 0 result... where i am do i find the tuple with NULL age Thank you id name rankng age | 22 | dustin | 7 | 45 | | 29 | brutus | 1 | 33 | | 31 | lubber | 8 | 55.5 | | 32 | andy | 8 | 25.5 | | 58 | rusty | 10 | 35 | | 64 | horatio | 7 | 35 | | 71 | zorba | 10 | 16 | | 85 | art | 3 | 25.5 | | 95 | bob | 3 | 63.5 | | 99 | horatio | 23 | 45 | | 55 | harish | 22 | NULL |
Trouble with full-text search   (255 Views)
I've a table with a field named 'AddrCode', the amounts of this field are something like: 'SF' , 'S0F' , 'S00F' , 'S10F' , 'S11F' , etc. I wana select all rows that begin with 'S0', but I can't use full-text becase of it's word oriented algorithm. I know that I can done this job with 'like' state but because of the size of my db (maybe millions of records) I can't use this method without index. Do u have any solution for this problem
Relevance, without full text   (196 Views)
dear all, I would like to display results sorted by relevance, but not necisarily using fulltext. basically, what I am trying to achive is this. a user wants to add a new client to the system, so they enter the first and surname in a single field. i want to then query the client table and show them any exact or close matches to the data they entered... so if they entered John Smith, the following page would show them all the John Smiths in the database, then all the other Smiths and all the other Johns, in that order. I am currently using WHERE fname LIKE %John% OR sname LIKE %John% OR sname LIKE %Smith% OR fname LIKE %Smith%, which gives me the correct reulsts but not ordered in the way I want. Can any help Many
Use where in query so it returns rows that has less than 10 characters in the word   (124 Views)
I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing. I can't really explain so I will give you an example, which is obviously wrong. Quote: mysql_query("select games from gaming where gamename < 10 characters");
Count() like columns without grouping   (280 Views)
Could someone tell me whether or not this is possible. I have a very long query and I want to count the resulting rows by a certain field, but I don not want to group by that field. Grouping will throw off the results I am shooting for. here is an example: Code: +----------------------------------------+ | the_table | +--------+----------------+--------------+ | id | animal | name | +--------+----------------+--------------+ | 1 | dog | jocko | | 2 | dog | max | | 3 | cat | mr pipper | | 4 | rabbit | cottontail | | 5 | cat | hank | | 6 | dog | spot | | 7 | snake | monty | +--------+----------------+--------------+ SQL: SELECT *, count(animal) AS total FROM the_table +-------------------------------------------------+ | the_results | +--------+----------------+--------------+--------+ | id | animal | name | total | +--------+----------------+--------------+--------+ | 1 | dog | jocko | 3 | | 2 | dog | max | 3 | | 3 | cat | mr pipper | 2 | | 4 | rabbit | cottontail | 1 | | 5 | cat | hank | 2 | | 6 | dog | spot | 3 | | 7 | snake | monty | 1 | +--------+----------------+--------------+--------+