Problem with deleting records and auto_increment

[Chri]

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?

Right now, I'm only working with one table. Either Clay or Cake. The ID field is just a number for the product shown. It doesn't need to have any specific meaning. If no one can spamm or do anything detrimental, I can post the username and password for phpmyadmin

[r937]

I don't see an execute command in phpmyadmin

paste the SHOW command into the SQL window and push the Go button

[Austin_Hastings]

If no one can spamm or do anything detrimental, I can post the username and password for phpmyadmin

DON'T DO THAT!!!

[Austin_Hastings]

When you work with phpMyAdmin, you start by logging in.

Then, on the first screen there's a way to select what database to use. (If your hosting provider only gives you one database, you might skip this step.) On the left-hand side is a pull-down listing the databases. Select the database containing this table.

Once you've chosen a database, you'll see a series of tabs across the top of the right-hand panel. One of those tabs is labeled "SQL".

Select the SQL tab, then type a SQL statement into the box and click on Go or Run or whatever it's called.

SHOW CREATE TABLE cake (or clay, or whatever the name was)

That should give you a description of table. If it doesn't work, try using

DESCRIBE cake

and see what that produces.

=Austin

[Chri]

Here it is

SQL query:
DESCRIBE Clay
[ Edit ] [ Create PHP Code ]


row(s) starting from record #
in mode and repeat headers after cells

Full Texts
Field Type Null Key Default Extra
MainPic varchar(150) YES NULL
InsetPic varchar(150) YES NULL
ID int(11) YES NULL
Category text YES NULL
Title text YES NULL
Size text YES NULL
Date text YES NULL
Comments text YES NULL
Blank text YES NULL
Blank2 text YES NULL
Blank3 text YES NULL
PrimaryKey int(10) unsigned PRI NULL auto_increment

[Chri]

I found this while trying to find a remedy:

'When reading in physical sequential order, FirstRecord may not retrieve the first possible record position if records have been deleted and the space is currently unused.'

I'm half sure this FirstRecord line is the culprit. But I don't know enough about this to know what it could change to

[r937]

okay, that's good -- some of the datatypes are inappropriate (e.g. do you really need the category to be as large as 65K?) but the structure looks workable

now, i wonder where the thumbnails come from?

could you please run this query and show the results --

select MainPic
     , InsetPic 
     , ID
     , PrimaryKey
  from Clay
order
    by PrimaryKey desc limit 7

[Chri]

the link that this relates to is http://bchoc.com/sh/Clay_view.php

  Full Texts  	  MainPic   	  InsetPic   	  ID   	  PrimaryKey
	Edit 	Delete 	A3Cupcakes.jpg 	NULL 	       11 	7
	Edit 	Delete 	Bz632SheptonStreet.jpg 	NULL 	10 	6
	Edit 	Delete 	C1WideoWomanLeft.jpg 	NULL 	7 	5
	Edit 	Delete 	E1LucysBlanket.jpg 	NULL 	3 	4
	Edit 	Delete 	D484BentTreeDrive.jpg 	NULL 	1 	1

[r937]

that's excellent

what were you planning for InsetPic? i would've probably used that for the thumbnail name

where are the thumbnail names, anyway?

also, what role do ID and PrimaryKey play, apart or together -- are they related?

[Chri]

The thumbnail is not a separate file. It's just a resized version of the MainPic, as the earlier code shows--that bit about "FirstRecord". With me being a beginner, I thought it best to keep things "simple"

ID and PrimaryKey aren't related at all. Either one could be used to order, but they work about the same (not very well for ordering!)

The inset is just an optional pic in case it's needed.

[r937]

well, that's interesting, the thumbnail image is actually the main image, and the ID and PrimaryKey values are unrelated

i am completely at a loss to understand, then, how you could possibly get a thumbnail pointing at the wrong main image after one of the rows has been deleted

i'm sorry to have sidetracked you, the problem has to be in your php code, not your use of the auto_increment

[Chri]

well, that's interesting, the thumbnail image is actually the main image, and the ID and PrimaryKey values are unrelated

i am completely at a loss to understand, then, how you could possibly get a thumbnail pointing at the wrong main image after one of the rows has been deleted

i'm sorry to have sidetracked you, the problem has to be in your php code, not your use of the auto_increment

Yes, they kicked me out of mysql forum, but I thought I had to use that field to order with. I think the problem is in that line with "FirstRecord". Is there any other term that could be used to call records in asc order? And also, sidetracked or not, your assistance is greatly appreciated.

[r937]

i'm not a php programmer so i don't know what "FirstRecord" is

if you want a particular sequence of results from mysql, use the ORDER BY clause of the SELECT statement

are you even using a SELECT statement?

[Chri]

@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) { 
        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";
    } 

[Chri]

...

@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) { 
        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";
    } 

[Austin_Hastings]

Chri,

Looking at your site, it seems you've got things displaying. Did the problem get worked out, or are you still having troubles?

=Austin

[Chri]

No, I just rigged it. Thanks for asking, though.