We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


INSERT then SELECT Query = Record Locking Maybe??

raggedyboo asked
Medium Priority
Last Modified: 2006-11-18
Hello there...

I am trying to make my own Shopping Basket and I am stuck on something which has been annoying me for days now.

When I add my product to the ShoppingBasket table, the product appears within the table using PHPMyAdmin.


As my product description page then automatically redirects to the Basket View page, after inserting the record (as above) nothing shows up!!

But if I go back and add another item (with a different quantity to see the difference) it appears as though the lock on the first record is released and then is allowed to appear on the Basket View web page, but not the second record.

This continues for as many items that I have in there.

Does this make sense?  How can I ...

1. Stop this from happening initially?
2. Remove lock almost instantly after the record is inserted?

Top marks available to the best and/or quickest answer that fixes the problem, as I'm running out of hair to rip out!!

Cheers Gurus.
Watch Question


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.



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)",
    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.


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?



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

Here... See what I mean...


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.




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.  
did you try to close mysql resource afret insert statment?

mysql_select_db($database_motoiconsphp, $motoiconsphp);
mysql_query($insertSQL, $motoiconsphp) or die(mysql_error());

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Artysystem administrator
Top Expert 2007
Try to add ';' at the end of:

INSERT INTO ShoppingBasket (UniqueNo, ProductNo, ProductQuantity, ProductUnitPrice, SizeID) VALUES ('%s', %s, %s, %s, %s)

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.


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


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` (
`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` )

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!!


Thanks in advance for your help...
Artysystem administrator
Top Expert 2007

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,


Be glad to help

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.