Results 1 to 9 of 9

Thread: MYSQL / PHP Problem - Unsure of Solution

  1. #1
    Senior Member
    Join Date
    Jul 2003
    Posts
    1,066
    Thanks
    1
    Thanked
    0 times in 0 posts

    MYSQL / PHP Problem - Unsure of Solution

    Long story short - I have a column in a table that has type INT and auto increments with each record that is added, it is also the primary key.

    If I deleted a record how would I go about changing the other records with an INT that is higher than it to one less? ie. take away one from all the INTs higher up the table.

    I am cycling through these records in one instance with a "record + 1" piece of code and say I delete record 5 and I'm on record 4 it will produce an error as the next record is 6.

  2. #2
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable
    write your code in such a way that it doesn't anticipate the next primary key, read the index number for each record if you need to make use of it.. and to be honest you shouldn't need to if your SQL queries are in good nick, use select 'field1','field2','field3'.. etc with a join so your php code only needs to deal with the data it needs rather than having to manually sort through keys and the like.. If this isn't enough help, then please supply more information.
    Quote Originally Posted by Agent View Post
    ...every time Creative bring out a new card range their advertising makes it sound like they have discovered a way to insert a thousand Chuck Norris super dwarfs in your ears...

  3. #3
    Senior Member ajbrun's Avatar
    Join Date
    Apr 2004
    Location
    York, England
    Posts
    4,840
    Thanks
    4
    Thanked
    25 times in 13 posts
    I don't know why you'd need to do that, but since you asked, I'll do my best. As far as I know, I think this should work, or at least come close to it .

    You have 5 entries with primary keys of 1,2,3,4 and 5. You delete entry number 3, so you need 4 to be 3 and 5 to be 4.

    $key = 3;
    That's the number of the primary key that's to be deleted.

    DELETE FROM table WHERE primaryKey = $key;
    That deletes the 3rd record.

    $query = "SELECT * FROM table WHERE primaryKey = $key";
    $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

    That should find all entries that have a primary key that's greater than the one you've just deleted (3).

    $num=mysql_numrows($result);
    That finds how many entries there are with a primary key greater than 3.

    while $num > 0
    {
    $key++;
    UPDATE table SET primaryKey = $key-1 WHERE primaryKey = $key;
    $num--;
    }


    I don't actually know if that'll work as it's looking for a record with a certain field, then changing that same field to something else. But the theory is that you have a loop that loops round and finds all entries with a primary key greater than the one that's been deleted, and subtracts one from that field. There's probably a better way of doing it, but I don't know of it for now .

    Also, I'm not certain, but I think that even once all the records are 'set' and in order, it won't actually be right. I'm pretty sure that if you were to add another field after it was sorted, the primary key would be 6 and not 5, even though you'd only have 4 records after removing one. To get round this, I suppose you could just count how many fields you have before inserting a record, and adding one to that number to get the new primary key.

    I'm sure someone could come up with a better solution, and I'm equally sure I should be in bed now .

  4. #4
    Comfortably Numb directhex's Avatar
    Join Date
    Jul 2003
    Location
    /dev/urandom
    Posts
    17,074
    Thanks
    228
    Thanked
    1,027 times in 678 posts
    • directhex's system
      • Motherboard:
      • Asus ROG Strix B550-I Gaming
      • CPU:
      • Ryzen 5900x
      • Memory:
      • 64GB G.Skill Trident Z RGB
      • Storage:
      • 2TB Seagate Firecuda 520
      • Graphics card(s):
      • EVGA GeForce RTX 3080 XC3 Ultra
      • PSU:
      • EVGA SuperNOVA 850W G3
      • Case:
      • NZXT H210i
      • Operating System:
      • Ubuntu 20.04, Windows 10
      • Monitor(s):
      • LG 34GN850
      • Internet:
      • FIOS
    don't use "record + 1" maths. read multiple records into a recordset, then page through them (can't remember how to do this in php)

  5. #5
    Senior Member
    Join Date
    Jul 2003
    Posts
    1,066
    Thanks
    1
    Thanked
    0 times in 0 posts
    Thanks for the replies guys - I had a feeling that was the incorrect way to go about things.

    Before I go any further - cheers ajbrun! Way beyond the call of duty mate

    To explain the situation further I am making a site for a friend who does a comic. This is for the navigating of the comics with the "forward" link moving to the next comic in the database if there is one or just sitting there if the comic is the latest one.

    The reason I asked this was because the model I was using was majorly constraining the whole system and it was really just down to very poor planning on my part.

    From what I gather the best bet is to keep the id as a the primary key but dont order the comics by it, rather a date or something defined by the user. For cycling I'll look into the recordsets - I vaguely remember using them in ASP using vbscript.

  6. #6
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    first off, stored procedures. They are good, they not only improve database performance and security but also improve code maintability. However, mysql dosen't lend itself to this at all.

    a simple sql query would be

    SELECT TOP 1 FROM tblFoobar WHERE id > @currentindex ORDER BY id;

    simple eh?

    but, it would be bennefical to have a "next cartoon" colum (and a previous cartoon one too), this would link to the next cartoon. You do this as if you add a comic and want it to go inbetween, its not a problem.

    also stored procedures, they're worth it.
    throw new ArgumentException (String, String, Exception)

  7. #7
    Senior Member
    Join Date
    Jul 2003
    Posts
    1,066
    Thanks
    1
    Thanked
    0 times in 0 posts
    In the end I went for a slightly different solution than I had originally intended.

    The id column is still the primary key but seeing as the comics also have an original date I used that column to order them. The id can then technically be anything at all as is it simply used to pass around the different pages.

    Incidently you can have a look at the final site if you feel so inclined

    http://www.ians-world.net

  8. #8
    Comfortably Numb directhex's Avatar
    Join Date
    Jul 2003
    Location
    /dev/urandom
    Posts
    17,074
    Thanks
    228
    Thanked
    1,027 times in 678 posts
    • directhex's system
      • Motherboard:
      • Asus ROG Strix B550-I Gaming
      • CPU:
      • Ryzen 5900x
      • Memory:
      • 64GB G.Skill Trident Z RGB
      • Storage:
      • 2TB Seagate Firecuda 520
      • Graphics card(s):
      • EVGA GeForce RTX 3080 XC3 Ultra
      • PSU:
      • EVGA SuperNOVA 850W G3
      • Case:
      • NZXT H210i
      • Operating System:
      • Ubuntu 20.04, Windows 10
      • Monitor(s):
      • LG 34GN850
      • Internet:
      • FIOS
    Quote Originally Posted by TheAnimus
    first off, stored procedures. They are good, they not only improve database performance and security but also improve code maintability. However, mysql dosen't lend itself to this at all.
    http://dev.mysql.com/doc/refman/5.0/...rocedures.html

    however, RoGuE|SaBeR's hit upon a simple enough solution

  9. #9
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    i was refering to performance which when i had a play, i found no disernable increase in and the fact it took until version 5! In short i think very little of mysql 5, and don't get its hudge popularity.
    throw new ArgumentException (String, String, Exception)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. DVI problem, pc won't start! help needed.
    By snowwolf in forum Graphics Cards
    Replies: 1
    Last Post: 09-04-2010, 04:11 PM
  2. Some problem needs solution :)
    By Thanos in forum PC Hardware and Components
    Replies: 0
    Last Post: 16-12-2004, 02:49 PM
  3. Another MySQL Help Thread
    By r1zeek in forum Software
    Replies: 2
    Last Post: 11-07-2004, 07:32 PM
  4. php problem...
    By Joel in forum Software
    Replies: 2
    Last Post: 12-10-2003, 11:39 PM
  5. Replies: 4
    Last Post: 19-09-2003, 08:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •