INSERT then SELECT Query = Record Locking Maybe??

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.

HOWEVER...

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?
or
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.
LVL 1
raggedybooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gamebitsCommented:
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
0
raggedybooAuthor Commented:
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
0
gamebitsCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

raggedybooAuthor Commented:
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.


0
raggedybooAuthor Commented:
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...
0
gamebitsCommented:
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
0
raggedybooAuthor Commented:
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.  
0
LowsanCommented:
did you try to close mysql resource afret insert statment?

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NopiusCommented:
Try to add ';' at the end of:

INSERT INTO ShoppingBasket (UniqueNo, ProductNo, ProductQuantity, ProductUnitPrice, SizeID) VALUES ('%s', %s, %s, %s, %s)
0
LowsanCommented:
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.
0
raggedybooAuthor Commented:
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)
0
raggedybooAuthor Commented:
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...
0
NopiusCommented:
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.

0
raggedybooAuthor Commented:
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.
0
LowsanCommented:
Be glad to help
Regards

Lowsan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.