Page 1 of 1

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

Posted: Thu Apr 29, 2010 6:13 am
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.

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

Posted: Thu Apr 29, 2010 6:50 am
by social_experiment
You should use '

Code: Select all

'' tags for your code, it makes reading the code much easier.

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

Posted: Thu Apr 29, 2010 7:06 am
by chrihb07
True that :)

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

Posted: Thu Apr 29, 2010 7:38 am
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?

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

Posted: Thu Apr 29, 2010 8:36 am
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 = ??

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

Posted: Thu Apr 29, 2010 8:44 am
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.

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

Posted: Thu Apr 29, 2010 10:47 am
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\"]'");

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

Posted: Thu Apr 29, 2010 12:17 pm
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 [ ]

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

Posted: Thu Apr 29, 2010 12:25 pm
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);

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

Posted: Thu Apr 29, 2010 1:13 pm
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);