Page 1 of 2

update or add to MYSQL not working

Posted: Thu Sep 04, 2008 4:13 pm
by pagegen

Code: Select all

$query = "SELECT * FROM TblBasket";
                $result = mysql_query ($query);
                
                while($row = mysql_fetch_array($result,MYSQL_ASSOC))
                {
                if ($row[id] = $item AND $row[userEmail] = $Busername){
                    $qunt = $row[qun]; 
                    $qunt = $qunt + 1;
                    $query = "UPDATE TblBasket SET qun = $qunt WHERE id = '$item'";
                    $result = mysql_query($query) or die(mysql_error());
                }
 
                else{
                $query = "INSERT INTO TblBasket (id, userEmail, itemId, CartDate) VALUES ('NULL','$Busername','$item', NOW())";
                $result =  mysql_query ($query);
                    
                }
                }
 
 
 
i want to check is a user has added somet in the basket already and if they have just add the quntity up
else add a new item..
my code above not working..

can some1 plz help

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 7:19 pm
by andyhoneycutt
I'd love to help but I don't understand the problem. Could you be more specific and include whatever error messages you may be receiving?

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 7:59 pm
by paperplate
First of all line 6, you are setting values instead of comparing. You want == instead of =.

It will also be helpful for you to just print out $query before you actually run it (or instead of running it).

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 8:28 pm
by benyboi
Also you need && instead of AND in your IF statement.

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 8:44 pm
by paperplate
benyboi wrote:Also you need && instead of AND in your IF statement.
Good catch..didn't even see that. The bad thing is that's legal VB code minus the variable names/specifiers.

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 8:45 pm
by andyhoneycutt
I could have sworn that "and" and "or" worked in php... I don't use them but a co-worker does.

Re: update or add to MYSQL not working

Posted: Thu Sep 04, 2008 9:11 pm
by paperplate
andyhoneycutt wrote:I could have sworn that "and" and "or" worked in php... I don't use them but a co-worker does.
You're right they are valid keywords but they are different than || and && mainly because of precedence. They have lower precedence than '=' while || and && have greater precedence.

$result = $this or $that;
is really:
($result = $this) or ($that);

while

$result = $this || $that;
is really
$result = ($this || $that);

Same goes for 'and'. See logical operators precedence.

But it seems you can achieve the same thing with either and/&&, or/|| just by getting your parentheses right...correct me if I'm wrong. I personally just stick to || and &&.

In conclusion, I guess just the = to == conversion is all that is needed for it to work. Learn something new everyday.

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 1:05 am
by andyhoneycutt
I never mix logical operators with "words". It's too confusing. I will totally check out the precedence article tomorrow morning at work-- I'd love to run some benchmarks (if it hasn't already been done) against using and/or vs. &&/||, respectively, to see if there is any (even marginal) difference.

That said, are there bitwise operand equivalents to & / | / ^ that are text based that you know of?

-Andy

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 3:13 am
by pagegen

Code: Select all

 
// SQL query to select the rows
                $query = "SELECT * FROM TblBasket";
                $result = mysql_query ($query);
                
                while($row = mysql_fetch_array($result,MYSQL_ASSOC))
                {
                if ($item == "$row[itemId]" && $Busername == "$row[userEmail]"){
                    $qunt = $row[qun] + 1; 
                    $query2 = "UPDATE TblBasket SET qun = $qunt WHERE itemId = '$item' && userEmail = '$Busername'";
                    $result2 = mysql_query($query2) or die(mysql_error());
                    $action = '1';
                }
                }
                If ($action == '2'){
                $query2 = "INSERT INTO TblBasket (id, userEmail, itemId, qun, CartDate) VALUES ('NULL','$Busername','$item', '1', NOW())";
                $result2 =  mysql_query ($query2);
                    
                
                }               
 
 
 
my new code -- the above code works but isit right cos its a bit slow at loading?

I am trying to check if a product is in the database, if it is add the quantity up by 1
if its not in the database then add the item

Thanks for all your help

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 4:36 am
by onion2k
You aren't escaping your input data. That's going to break things at some point in the future. Use mysql_real_escape_string().

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 4:49 am
by pagegen
onion2k wrote:You aren't escaping your input data. That's going to break things at some point in the future. Use mysql_real_escape_string().
hey dude, thanks for helping, kinda new at php

ive never ered of that statment b4 and have no idea where to use it?

also at the moment the code is working but really slows down


EDIT: thanks for the escape function, i searched it up n its some good stuff

" Escapes special characters in the unescaped_string , taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL. "

but i dont understad y my add function is slow. it works but plz tell me if code is right

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 5:00 am
by onion2k
pagegen wrote:ive never ered of that statment b4 and have no idea where to use it?
Which is why you're going to look it up in the PHP manual, right?

EDIT: Ah, see, you are. Good. Well done.

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 5:04 am
by onion2k
The reason why your code is slow is because of $query = "SELECT * FROM TblBasket";. You're iterating through every record in the basket table. Add a WHERE clause so it only selects the records that belong to the basket of the user you're updating.

Also, I think you're going to run into problems with the design of your application.. You appear to be using the user's email address to identify their shopping cart. What if the user buys some stuff.. and then a week later comes back to buy some more stuff? How will you know which is the old cart and which is the new cart?

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 5:05 am
by pagegen
onion2k wrote:
pagegen wrote:ive never ered of that statment b4 and have no idea where to use it?
Which is why you're going to look it up in the PHP manual, right?

EDIT: Ah, see, you are. Good. Well done.
hehe it sounded like a good statment so had to google n see what it does..

i still need advice from you on the code. any ideas why its slow at loading?

Re: update or add to MYSQL not working

Posted: Fri Sep 05, 2008 5:07 am
by pagegen
onion2k wrote:The reason why your code is slow is because of $query = "SELECT * FROM TblBasket";. You're iterating through every record in the basket table. Add a WHERE clause so it only selects the records that belong to the basket of the user you're updating.

Also, I think you're going to run into problems with the design of your application.. You appear to be using the user's email address to identify their shopping cart. What if the user buys some stuff.. and then a week later comes back to buy some more stuff? How will you know which is the old cart and which is the new cart?

Code: Select all

                $yesterday = date('Y-m-d H:i:s', mktime(0,0,0, date('m'), date('d') - 1, date('Y')));
                $sql = "DELETE FROM TblBasket WHERE CartDate < '$yesterday'";
                mysql_query($sql) or die(mysql_error());
am using that to clear the cart every night so it wont be a problem.. the field is called email but really its there session number.. but if user logs in that it will become there email..