[RESOLVED] Use query to list data from db and update value on the first row from query

[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:

$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);

Thank you!

[anakadote]

What's not happening as you expect it to? Are you getting any errors?

If you're trying to display just one db entry, instead of getting them all and then using ... && ($i<1)){ in your loop, just use "LIMIT 1" at the end of your query. And use the cart_id from that same query to update its confirmed status, so you can get rid of this stuff, which looks like an error to me:

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

[chrihb07]

Thanks for the reply anakadote!

The code works. I've changed some of the code, but the problem is that all orders change value, instead of the one that is listed on my screen.

 $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 = 5)
		ORDER BY created DESC
		LIMIT 1";


	$result = mysql_query($sql);


while (($row = mysql_fetch_array($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: ";
	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> ";



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

mysql_free_result($result); 

[anakadote]

Right, because you're looping through them. You only want to update the row with the same "cart_id" (I believe):

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

$totalt = $row["pprice"] * $row["amount"];
echo "<br> Bestilte Produkter: <br> " ;
echo "<br> Store Id: ";
echo $row["store_id"];
// etc.

// Now update the same row
mysql_query("UPDATE carts SET admin_status = 5 WHERE 'cart_id' = {$row['cart_id']} LIMIT 1");

[chrihb07]

Nothing happens. The code is executable but it won't update : /

 $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 = 5
       WHERE 'cart_id' = {$row['cart_id']}LIMIT 1");


mysql_free_result($result);
 

[anakadote]

You need a space before LIMIT

mysql_query("UPDATE carts
       Set admin_status = 5
       WHERE 'cart_id' = {$row['cart_id']}LIMIT 1"); 

If that doesn't work, try, verbatim:

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

[bradgrafelman]

Another problem is that cart_id shouldn't be surrounded with quotes in the query, assuming you meant to refer to the column named cart_id. As it is, you're comparing a string to what appears to be a number, thus your WHERE condition will always be false.

[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! 🙂)

 $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);