Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INSERT then SELECT Query = Record Locking Maybe??

Posted on 2006-04-01
15
Medium Priority
?
532 Views
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.

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.
0
Comment
Question by:raggedyboo
  • 7
  • 3
  • 3
  • +1
15 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 16351597
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16351747
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
 
LVL 28

Expert Comment

by:gamebits
ID: 16351856
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:raggedyboo
ID: 16351865
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16351870
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
 
LVL 28

Expert Comment

by:gamebits
ID: 16352041
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16353341
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
 
LVL 1

Accepted Solution

by:
Lowsan earned 1000 total points
ID: 16362444
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
 
LVL 27

Assisted Solution

by:Nopius
Nopius earned 1000 total points
ID: 16368514
Try to add ';' at the end of:

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

Expert Comment

by:Lowsan
ID: 16369515
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16374619
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16375074
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
 
LVL 27

Expert Comment

by:Nopius
ID: 16376854
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
 
LVL 1

Author Comment

by:raggedyboo
ID: 16378284
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
 
LVL 1

Expert Comment

by:Lowsan
ID: 16378899
Be glad to help
Regards

Lowsan
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

575 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question