INSERT then SELECT Query = Record Locking Maybe??

Post the code of the basket View page but for now I would ask where are you doing the query to display what is in the basket.

Gamebits

ProductDetail.php page (inserts record into the ShoppingBasket table:
**************************************************

//Insert record into the database...
$insertSQL = sprintf("INSERT INTO ShoppingBasket (UniqueNo, ProductNo, ProductQuantity, ProductUnitPrice, SizeID) VALUES ('%s', %s, %s, %s, %s)",
    $sID,
    GetSQLValueString($_POST['hdnProductID'], "int"),
    GetSQLValueString($_POST['txtQty'], "int"),
    GetSQLValueString($_POST['hdnPrice'], "text"),
    GetSQLValueString($_POST['lstSize'], "int"));
                              
    mysql_select_db($database_motoiconsphp, $motoiconsphp);
    mysql_query($insertSQL, $motoiconsphp) or die(mysql_error());

Basically, above...the $sID is a unique code used to identify each visitor to the site.  Other variables are passed in by the same webpage when it POSTs the data.

Basket.php Page (reads the table and displays the results to the page)
**************************************************

    mysql_select_db($database_motoiconsphp, $motoiconsphp);

    $query_rstBasketContent = sprintf("SELECT ShoppingBasket.ShoppingBasketID, ShoppingBasket.UniqueNo, ShoppingBasket.ProductQuantity,      ShoppingBasket.ProductUnitPrice, Products.ProductID, Products.ProductCode, Products.ProductName, Products.ProductDesc, Products.ProductPhotoSmall, Products.BrandID, ShoppingBasket.SizeID, VLSizes.SizeCode, VLSizes.SizeDesc FROM (Products INNER JOIN ShoppingBasket ON Products.ProductID = ShoppingBasket.ProductNo) INNER JOIN VLSizes ON ShoppingBasket.SizeID = VLSizes.SizeID WHERE (((ShoppingBasket.UniqueNo)='%s'));", $sID);

    $rstBasketContent = mysql_query($query_rstBasketContent, $motoiconsphp) or die(mysql_error());
    $row_rstBasketContent = mysql_fetch_assoc($rstBasketContent);
    $totalRows_rstBasketContent = mysql_num_rows($rstBasketContent);

I have tried to simplify this query right down to only the required table (ShoppingBasket) but it really made no odds and the problem re-occurred still.

The code above is about 90% supplied by Dreamweaver MX 2004 using the built in functions to insert records and traverse them from the recordset. I have done this hundreds of times using VBScript, and am still getting to grips with PHP to do the same.  The only thing I can possibly imagine it could be is the MySQL table... Ihave tried to delete the table and upsize it again, trying various thoughts and ideas, but many were scratching the barrel a little.

With regards to the Table itself: ShoppingBasket (MyISAM) is defined as follows in PHPMyAdmin:

   Field                          Type          Attributes     Null    Default         Extra
   ShoppingBasketID     int(11)                             No                      auto_increment              
   UniqueNo               varchar(255)                      Yes     NULL                
   ProductNo                  int(11)                           Yes     NULL                
   ProductQuantity          int(11)                           Yes     NULL                
   ProductUnitPrice       varchar(10)                      Yes     NULL                
   SizeID                        int(11)                          Yes      NULL                
   OrderNo                  varchar(255)                    Yes      NULL                
           

Indexes for ShoppingBasket:  
Keyname      Type           Cardinality          Field
PRIMARY      PRIMARY          0                ShoppingBasketID  
UniqueNo      INDEX           None              UniqueNo  


Hope this helps.

Cheers

I don't think it's a mysql problem, for instance in a script you can unsert something in the db and on the same page right after the insert query the db and it will show the result of the insert.

I think it might be a cache problem from the browser, it display the last page in the cache, if you add something in the basket and it's not showing up when you are redirected if you it the browser's refresh button what happen?

Gamebits

No, tried that.  It's certainly not cached pages, as all are dynamic PHP pages online.

Here... See what I mean...

http://www.moto-icons.co.uk/index.php

This is the website that I am referring to above.  The problem comes when you try to add something to the basket.

Sorry: quick instructions:

On the first page, click a team (Valentino Rossi) and then choose one of the limited product ranges available at present.  Choose a size and quantity and try adding it to the basket...

then go back and add it again...but with a different quantity...

Well I don't know what to say cause it seems to work for me.

The very first try didn't work, I "bought" item 1 and it didn't show up on the next page but after that every time I add or remove something the page show up properly.

????

Gamebits

Yes, thats the problem.  But in fact the basket for you had 4 items in there...

ShoppingBasketID      UniqueNo                                                ProductNo ProductQuantity      ProductUnitPrice      SizeID      OrderNo
43                      58c246a16a5acd082522675f9c582691      3      1                            19.99                         3      
50                      58c246a16a5acd082522675f9c582691      2      1                            19.99                         8      
51                      58c246a16a5acd082522675f9c582691      2      2                             19.99                         8      
52                       58c246a16a5acd082522675f9c582691      1      1                              19.99             8      

I know that I could learn to live with it, but I don't want to if that makes sense?

Also, as the four items are actually in the basket for you (58c246a16a5acd082522675f9c582691) then the four appear later in the buying process just before the information is submitted to Protx for credit card payment.  

you can check also if in mysql server autocommit is set to 1

sql> select @@autocommit;

if it is 0 then after inserting you have to commit changes

sql> COMMIT;

by default autocommit is set to 1 and that is the way it should be.
missing ';' would be problem if you have more that one sql statment.

I thought we nearly had it then... but no.

In order...

Lowsan - You're right,  I wasn't actually closing the connection properly, which I really ought to have been.  But unfortunately, adding this code didn't make any difference.

Nopious

The ';' was there in my code, but I didn't copy it properly into the EE window above.  (my bad)

Lowsan

I didn't previously know about the auto commit setting in MySQL.  If I have run this correctly, the response was 1. (I did it through the SQL window in PHPMyAdmin)


What I did though, in advertantly, was copied Lowsan's code above (about closing the connection) and pasted it in under my existing code, without reading too much of it.  What I had done is made the same SQL code run twice and then close... which made one item appear in my basket, but not the other.

doh!  Come back SQL Server, all is forgiven... (nearly)

Right.  Back to basics:   Am I right in thinking that this would create the table adequate enough to store what I need to store?

CREATE TABLE `ShoppingBasket` (
`ShoppingBasketID` INT( 11 ) DEFAULT '1' NOT NULL AUTO_INCREMENT ,
`UniqueNo` VARCHAR( 255 ) ,
`ProductNo` INT( 11 ) DEFAULT '0' NOT NULL ,
`ProductQuantity` INT( 11 ) DEFAULT '0' NOT NULL ,
`ProductUnitPrice` VARCHAR( 255 ) ,
`SizeID` INT( 11 ) DEFAULT '0' NOT NULL ,
`OrderNo` VARCHAR( 255 ) ,
PRIMARY KEY ( `ShoppingBasketID` )
) TYPE = MYISAM ;

I originally uploaded the tables through my MyODBC connection from an Access database file in MySQL.  I have tried to delete the upsized table and re-created it from scratch using the above SQL code, but the problem persists!!

Aaaaaarrrrrrrrrrrrrrrrrrrrrrrrrrrgggggggggggggggghhhhhhhhhhhhh!!

Thanks in advance for your help...

raggedyboo, it's difficult to loose ';' at the end of SQL statement while copy-pasting your code to EE.
Your problem is really difficult to find, so please check _all) your 'INSERT' statements in your code twice :-)
They all should finish with ';' (not PHP statements, but _strings_ formed by your php code).
If ';' is absend, your 'INSERT' will be executed only after second SQL statement.

Any other reasons seems to me less possible because:
- MySQL doesn't cache any INSERTED data on Client site
- MySQL doesn't hold locks for inserted records
- MySQL automatically do 'commit' after each statement with ';' at the end until you ask him for opposite

Also there is a possible reason with your 'SELECT' where somewhere you may try to join with empty record set. To debug this my suggestion is following (while debugging):
- don't execute your SQL statement in your PHP code, just 'echo' it, COPY-PASTE to your MySQL client  and execute by hand, then look at your database what happens
- also echo all your 'SELECT' statements and execute them. If some of them selects 'empty' sets, bud database is non-empty (I meen your basket), than look carefully to your WHERE statements.

No, honestly, it was there...!!  I was initially only going to copy the SQL string into EE and stopped just before the ';'... god knows why though.

I think that the combination of both of your answers have stumbled me inadvertantly across the problem and again, inadvertantly fixed it!

So, nice one! Cheers.  Much appreciated!

Kind regards,


raggedbyboo.

Be glad to help
Regards

Lowsan