Use query to list data from db and update value on the first

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chrihb07
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 6:10 am

Use query to list data from db and update value on the first

Post by chrihb07 »

What is wrong with this code?
I am workin on a order system that confirms orders. I want to list the oldest order and then confirm the order, To to this I have queried out all the unconfirmed orders and listed the oldest to the screen. Then i have made an update query to change the unconfirmed order in the database to confirmed.

Here are the code:

Code: Select all

 $sql = "SELECT carts.cart_id ,carts.fname, carts.lname, carts.country, carts.city, carts.street, carts.postnum, 
          carts.epost, carts.comm, carts.delivery_name, carts.delivery_country, carts.admin_status,
          carts.delivery_city, carts.delivery_address, carts.delivery_postnum, carts.payment_method, 
        carts.admin_status, cart_prods.cart_id, cart_prods.store_id, cart_prods.pname, cart_prods.pprice, 
         cart_prods.amount, cart_prods.props
        FROM   carts
      INNER JOIN cart_prods
      ON carts.cart_id = cart_prods.cart_id
       WHERE  carts.cart_id > 1
      AND (status ='AUTHORIZED' AND admin_status = 1)
      ORDER BY created DESC";
  
      
      $result = mysql_query($sql);
      
if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "Ingen ordre igjen:-) Godt jobbet!!!";
    exit;
}

$i=0;
while (($row = mysql_fetch_array($result)) && ($i<1)){
$totalt = $row["pprice"] * $row["amount"];
    echo "<br>  Bestilte Produkter: <br> " ;
   echo "<br> Store Id: ";
   echo $row["store_id"];
   echo "<br> Navn: ";
   echo $row["pname"];
   echo "<br> Pris: ";
   echo $row["pprice"];
   echo "<br> Antall: ";
   echo $row["amount"];
   echo "<br> Totalt: ";
   echo $totalt;
   //echo "<br> Properties: ";
   //echo $row["props"];
   echo "<br> ";
   echo "<br>  Personlig informasjon: <br> " ;
   echo " <br> Fornavn: ";
   echo $row["fname"];
   echo "<br> Etternavn: ";
   echo $row["lname"];
   echo "<br>  Land: ";
   echo $row["country"];
   echo "<br>  By: ";
   echo $row["city"];
   echo "<br>  Gateadresse: ";
   echo $row["street"];
   echo "<br>  Postnr: ";
   echo $row["postnum"];
   echo "<br>  E-Post: ";
   echo $row["epost"];
   echo "<br>  Kommentar: <br> ";
   echo $row["comm"];
   echo "<br> Leveringsadresse:<br> ";
   echo "<br>  Navn: ";
   echo $row["delivery_name"];
   echo "<br>  Land: ";
   echo $row["delivery_country"];
   echo "<br>  By: ";
   echo $row["delivery_city"];
   echo "<br>  Etternavn: ";
   echo $row["delivery_address"];
   echo "<br>  Postnr: <br> ";
   echo $row["delivery_postnum"];
   echo "<br>  Betalingsmetode: <br> ";
   echo $row["payment_method"];
   echo "<br> ";
   /*echo "<br>  Status: <br> ";
   echo $row["admin_status"];
   echo "<br> ";
   echo "<br> ";
   */$i++;
   
}


$per = mysql_fetch_array($result);{
$query = mysql_query("UPDATE carts
       Set admin_status = 5
       WHERE 'cart_id' = .'$per'. 'cart_id'");
      }
   
mysql_free_result($result); 

When I run the code it generates all orders that has the status = AuTHORIZED. The update function will not update the value on admin_status. When I check the db the value is still the same. My plan is to add a line in the first query, so that the query only list out status = AUTHORIZED and admin_status = 2. This is so that I only list out the unconfirmed orders.
Last edited by chrihb07 on Thu Apr 29, 2010 7:10 am, edited 2 times in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Use query to list data from db and update value on the f

Post by social_experiment »

You should use '

Code: Select all

'' tags for your code, it makes reading the code much easier.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
chrihb07
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 6:10 am

Re: Use query to list data from db and update value on the f

Post by chrihb07 »

True that :)
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Use query to list data from db and update value on the f

Post by social_experiment »

Thanks for the '[syntax]' tags. Could you paste your table's structure and one or two rows of sample data from your table?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Use query to list data from db and update value on the f

Post by mikosiko »

in this piece of code... check which value the $per resultset contain....

Code: Select all

$per = mysql_fetch_array($result);{
$query = mysql_query("UPDATE carts
       Set admin_status = 5
       WHERE 'cart_id' = .'$per'. 'cart_id'");
also, why are you trying to concatenate the value or $per after the = ??
chrihb07
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 6:10 am

Re: Use query to list data from db and update value on the f

Post by chrihb07 »

$Per contains all the orders in the db. It should contain only one order (oldest), since we now have added a "LIMIT 1" in the first query. This is probably why it updates every field in the table, instead of only the first one.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Use query to list data from db and update value on the f

Post by mikosiko »

I can comment

$Per contains all the orders in the db.
Not true... mysql_fetch_array is only extracting ONE row of data no all the data in your table

It should contain only one order (oldest), since we now have added a "LIMIT 1" in the first query
that is correct in theory, but because in your while loop you already accessed that first row, the second time that you call mysql_fetch_array the resource $per should be empty... remember that: "mysql_fetch_array Returns an array that corresponds to the fetched row and moves the internal data pointer ahead."

. This is probably why it updates every field in the table, instead of only the first one.

I'm still wondering why you are using concatenation here:

Code: Select all

       WHERE 'cart_id' = .'$per'. 'cart_id'");
I will write it in this way or similar:

Code: Select all

       WHERE `cart_id` = '$per[ \"cart_id\"]'");
chrihb07
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 6:10 am

Re: Use query to list data from db and update value on the f

Post by chrihb07 »

Thanks, but did not work. Returned this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

Cant use [ ]
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Use query to list data from db and update value on the f

Post by mikosiko »

I will write it in this way or similar:
what else did you tried?

Cant use [ ]

yes you can, here is one way

Code: Select all

            WHERE cart_id =  {$per['cart_id']}");
and this is another way

Code: Select all

$per = mysql_fetch_array($result);

$updsql = "UPDATE carts SET  admin_status = 5
                WHERE cart_id = " . $per["cart_id"];
$query = mysql_query($updsql);
chrihb07
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 6:10 am

Re: Use query to list data from db and update value on the f

Post by chrihb07 »

Thank you so much for the help, all of you! It was only the ' ' that solved the problem! hehe...and the "LIMIT 1" made generating of orders alot faster, so thank you guys. We really appreciate the help! :))

Code: Select all

 $sql =  "SELECT carts.cart_id ,carts.fname, carts.lname, carts.country, carts.city, carts.street, carts.postnum, 
          carts.epost, carts.comm, carts.delivery_name, carts.delivery_country, carts.admin_status, 
          carts.delivery_city, carts.delivery_address, carts.delivery_postnum, carts.payment_method, 
    carts.admin_status, cart_prods.cart_id, cart_prods.store_id, cart_prods.pname, cart_prods.pprice, 
     cart_prods.amount, cart_prods.props 
        FROM   carts 
  INNER JOIN cart_prods 
  ON carts.cart_id = cart_prods.cart_id 
   WHERE (status ='AUTHORIZED' AND admin_status = 1) 
  ORDER BY created DESC 
  LIMIT 1"; 
   
  $result = mysql_query($sql); 
   
   
if (!$result) { 
    echo "Could not successfully run query ($sql) from DB: " . mysql_error(); 
    exit; 
} 
if (mysql_num_rows($result) == 0) { 
    echo "Ingen ordre igjen:-) Godt jobbet!!!"; 
    exit; 
} 

$row = mysql_fetch_assoc($result); 
$totalt = $row["pprice"] * $row["amount"]; 
    echo "<br>  Bestilte Produkter: <br> " ; 
echo "<br> Store Id: "; 
echo $row["store_id"]; 
echo "<br> Navn: "; 
echo $row["pname"]; 
echo "<br> Pris: "; 
echo $row["pprice"]; 
echo "<br> Antall: "; 
// etc 

mysql_query("UPDATE carts 
            SET admin_status = 1 
             WHERE cart_id = {$row[cart_id]} LIMIT 1")or die(mysql_error());
    
     

mysql_free_result($result); 
Post Reply