SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 42
  1. #1
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with deleting records and auto_increment

    After spending a couple hours over there at MYSQL, I'm told I can "fix" this with PHP. My problem is this: I have thumbnails which relate to each record. WHen I delete a record, say record #2, the thumbnails go all haywire and no longer relate to the correct (numeric) order. If you go look here and then click on the thumbs you'll see what I mean http://www.bchoc.com/shdb/Clay_view.php. Then, of course, when I insert a new record after deleting, there's no telling where it'll end up, but it's sure not going to be at the end. At the bottom in light gray is a record count, it would be nice if somehow the records could match these numbers without having to input anything. Another acceptable scenario is that if a record is skipped, say record #2, it's quite alright for the thumbnails to order 1, 3, 4, 5 too just so long as they correlate to their proper larger pic on the right hand side.

    PHP Code:
    @mysql_select_db($database) or die("Unable to select database");
    $result mysql_query("SELECT * FROM Clay")
    or die(
    mysql_error());

    $columns 5;

    $i 0;
    echo 
    "<table cellpadding='2'>\n";
    while(
    $row mysql_fetch_array$result )) {
        
    $pic $row['MainPic'];

        if ((
    $i $columns) == 0) { // % is the modulous operator, NOT percentage
            
    echo "  <tr>\n";
        }

        echo 
    "    <td><a href='./Clay_view.php?FirstRecord={$row['PrimaryKey']}'><img src='./images/$pic' width='29' height='29'></a></td>\n";

        if ((
    $i $columns) == ($columns 1)) {
            echo 
    "  </tr>\n";
        }

        
    $i++;

    Opportunity favors the prepared mind.

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's really a mysql question
    anyway, i presume your database table "clay" has an auto_increment primary key column
    which i am guessing is called PrimaryKey

    just change your sql query to
    Code:
    "SELECT * FROM Clay ORDER BY PrimaryKey ASC"
    hth
    dave

  3. #3
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems as it would be as easy as this but it doesn't work. Nothing changes
    Opportunity favors the prepared mind.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Chri
    I have thumbnails which relate to each record.
    how, exactly, do they relate? i mean, by filename, or what? and how does the id figure into this relationship?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like what you said is actually working, so this must be the problem:

    PHP Code:
        echo "    <td><a href='./Clay_.php?FirstRecord={$row['PrimaryKey']}'><img src='./images/$pic' width='29' height='29'></a></td>\n"
    The thumbs aren't matching the actual record number.


    .
    Opportunity favors the prepared mind.

  6. #6
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, the ID is what I first tried to use to order them.
    Opportunity favors the prepared mind.

  7. #7
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs don't match record number after deleting or inserting

    The thumbs aren't matching the actual record number. But work just fine if no deletes or new record inserts. Pls help.
    PHP Code:
    mysql_connect('localhost',$username,$password);
    @
    mysql_select_db($database) or die("Unable to select database");
    $result mysql_query("SELECT * FROM Clay ORDER BY PrimaryKey ASC")
    or die(
    mysql_error());

    $columns 5;

    $i 0;
    echo 
    "<table cellpadding='2'>\n";
    while(
    $row mysql_fetch_array$result )) {
        
    $pic $row['MainPic'];

        if ((
    $i $columns) == 0) { // % is the modulous operator, NOT percentage
            
    echo "  <tr>\n";
        }

        echo 
    "    <td><a href='./Clay_.php?FirstRecord={$row['PrimaryKey']}'><img src='./images/$pic' width='29' height='29'></a></td>\n";

        if ((
    $i $columns) == ($columns 1)) {
            echo 
    "  </tr>\n";
        } 
    Opportunity favors the prepared mind.

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What's a thumb, how's it stored, and how is it related to the "actual record number"?
    Last edited by Dan Grossman; Apr 25, 2006 at 06:04.

  9. #9
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I looked at your site, and as things currently stand the MainPic appears to be empty. Do a "view source" of the page, and you'll see:

    Code:
      <tr>
        <td><a href='./Clay_view.php?FirstRecord=1'><img src='./images/' width='29' height='29'></a></td>
        <td><a href='./Clay_view.php?FirstRecord=2'><img src='./images/' width='29' height='29'></a></td>
    As I understand it, the /images/ is supposed to be followed by some file name.

    I'd suggest you look in your database and see if you have somehow corrupted the data.

    In particular, what statement did you execute for the delete operation you mentioned?

    Also, on a different note, PLEASE avoid doing "select * from table." I don't know what's in the table from reading your code, and a few months from now you won't remember, either. Get in the habit of spelling out what fields you expect to get back: "select MainPic, Id, PrimaryKey from table ..."

    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  10. #10
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I thought it was pretty obvious that the thumb is 29 x 29. They are varchar. And I don't know how it's related to the actual record number. Can you see I'm lost?
    Opportunity favors the prepared mind.

  11. #11
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't think I had anyone's interest so I deleted all the records and put blanks in--in effect giving up and not allowing delete and insert by the customer into the database. But I will try your advice about being particluar with the select command. Thanks.
    Opportunity favors the prepared mind.

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, you've given code that produces two columns of images from a table called Clay. That's as much as can be said based on this thread. You haven't given your goal, or problem, or what you want help with.

  13. #13
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've gotta have more patience. It looks like about 4 hours between then and now--not nearly enough time to give up.

    In general this is something you know can be done, and whatever site you are working on will likely need some very similar function somewhere else. You might as well work out how to do it.

    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  14. #14
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Really? I thought 4 hours was absurb. Actually I spent about 8. I'm thinking the whole time, surely people don't spend this amount of time on one little line of script...
    Opportunity favors the prepared mind.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Chri
    I have thumbnails which relate to each record.
    how, exactly, do they relate? i mean, by filename, or what? and how does the id figure into this relationship?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think that is my problem, actually, I don't know. They are supposed to match the pic in the record detail. And I CAN do that, but then when I delete and subsequently insert a new record, the order remains ASC, which is what I want, but the thumbnails dont coincide with that order.
    I'm getting ready to populate this new section with a few and then I'm going to delete and add to show you http://www.bchoc.com/shdb/Cake_view.php
    PHP Code:
    $result mysql_query("SELECT * FROM Cake ORDER BY PrimaryKey ASC")
    or die(
    mysql_error());

    $columns 5;

    $i 0;
    echo 
    "<table cellpadding='2'>\n";
    while(
    $row mysql_fetch_array$result )) {
        
    $pic $row['MainPic'];

        if ((
    $i $columns) == 0) { // % is the modulous operator, NOT percentage
            
    echo "  <tr>\n";
        }

        echo 
    "    <td><a href='./Cake_view.php?FirstRecord={$row['PrimaryKey']}'><img src='./images/$pic' width='29' height='29'></a></td>\n";

        if ((
    $i $columns) == ($columns 1)) {
            echo 
    "  </tr>\n";
        }

        
    $i++;
    }


    $remainder $columns - ($i $columns);

    if (
    $remainder 0) {
        while (
    $remainder 0) {
            echo 
    "    <td>&nbsp;</td>\n";
            
    $remainder--;
        } 
    Opportunity favors the prepared mind.

  17. #17
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman
    Well, you've given code that produces two columns of images from a table called Clay. That's as much as can be said based on this thread. You haven't given your goal, or problem, or what you want help with.

    " The thumbs aren't matching the actual record number" after deleting a record and subsequently inserting one.
    Opportunity favors the prepared mind.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, could you please run this query in mysql (not via php, directly in mysql) and paste the results here --

    SHOW CREATE TABLE Cake
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql = 'SHOW CREATE TABLE Cake';
    Opportunity favors the prepared mind.

  20. #20
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do you have phpMyAdmin? It's pretty standard with most web hosts now. That's a good place to run that query so you can get the output Rudy wants to see.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that's a php statement

    what i meant was, show us the CREATE TABLE statement for the Cake table

    you can get mysql to display this CREATE TABLE statement by executing the query
    Code:
    SHOW CREATE TABLE Cake
    it's easiest to execute this query not from within php, but directly in mysql (via the command line or through some front end app like phpmyadmin, sqlyog, mysql-front, etc.)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's just the same as what you wrote:

    SQL query:
    SHOW CREATE TABLE Cake
    Opportunity favors the prepared mind.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, you actually have to run or execute that SHOW command, and see what it produces -- it should produce a CREATE TABLE statement that should look something like
    Code:
    create table Cake ( id ...
    what i'm looking for is to see what columns are in that table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict Chillijam's Avatar
    Join Date
    Nov 2003
    Location
    England
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, back to basics. Is there any programatic link between the two tables? eg - does your "Clay" table contain a reference to the id of the image, or are you relying on blind luck to get the right image?
    Your mind is like a parachute. It works best when open.
    (HH The Dalai Lama)

  25. #25
    SitePoint Evangelist
    Join Date
    May 2005
    Location
    Houston, Texas, USA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    no, you actually have to run or execute that SHOW command, and see what it produces -- it should produce a CREATE TABLE statement that should look something like
    Code:
    create table Cake ( id ...
    what i'm looking for is to see what columns are in that table

    I don't see an execute command in phpmyadmin
    Opportunity favors the prepared mind.


Bookmarks

Posting Permissions

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