Simple coding problem, trying to loop through rows

[woodsonoversoul]

I'm trying to query a database to compile all the values for a certain row (row[4]). I keep bouncing around to different tutorials and the all sugest different way to do it, and I can't get any of them to work. Here is the code I'm currently working with, I've already succesfully queried the database and used it elsewhere on the page. This code gives no errors. It just outputs "You've spent 0". Any help is GREATLY appreciated, thanks in advance.

 

 

 

 

    $totSpent = 0;

    //Figure total spent /Start by querying totals

    while($row = $result->fetch_array())

    {

        $totSpent = totSpent + $row[4];

    }

   

    echo "You've spent $totSpent"  ;

    // free result set memory

    $result->close();

[teng84]

are you using the class???

for this $result->fetch_array()

 

where is the parameter for that fetch array is there any?

[trq]

Your much better off doing this in your query rather than having php loop through results. eg;

 

SELECT SUM(fldname) AS total FROM tbl;

[woodsonoversoul]

I'm not using any class as far as I know. I'm also not using any parameters. Could I possibly query only for that specific row? (I'm sure I could, but haven't been able to work that out either...) Total newb here, trying to follow some quides to achive what I want/need. What would you suggest?

 

 

edit: I just saw what Thope posted. Thanks, I'll try that

[teng84]

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM person");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

i just got it some where try?

hmm

[woodsonoversoul]

I tried Thorpe's process, and replaced my original code with:

 

 

$totSpent = mysql_query("SELECT SUM(price) AS total FROM purchase");   

    echo "You've spent $totSpent"  ;

 

And now it's saying I can't access the database:

 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'www-data'@'localhost' (using password: NO) in /var/www/spindex/display.php on line 69

 

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /var/www/spindex/display.php on line 69

 

even though I'm already connected. Do I have to reconnect to the db for every query?

[teng84]

its either you are not connected or you dont specify the DB to use

[woodsonoversoul]

In the code I just posted, where should I specify the database? I already selected it when I setup the connection. (I used mysqli in some of my previous queries/commands, don't know if that affects this)..

[trq]

Your not connected properly. Besides that... mysql_query returns a result resource, you can't simply echo it. You'll need something like...

 

<?php

 // connect to db.
 
 $sql = "SELECT SUM(price) AS total FROM purchase";
 if ($result = mysql_query($sql)) {
   if (mysql_num_rows($result)) {
     $row = mysql_fetch_assoc($result);
     echo $row['total'];
   }
 } else {
   echo mysql_error();
 }

?>

 

I know it might look long winded, but its best to allways check your queries succeed before attempting to use any result they produce.

[trq]

I used mysqli in some of my previous queries/commands, don't know if that affects this

 

If your using mysqli your code would look like....

 

<?php

  // connect and create mysqli object

  $sql = "SELECT SUM(price) AS total FROM purchase";
  if ($result = $mysqli->query($sql)) {
    if ($mysqli->num_rows()) {
      $row = $result->fetch_assoc();
      echo $row['total'];
    }
  } else {
    echo $mysqli->error;
  }

?>

[woodsonoversoul]

Thanks. I'm still getting an error:

 

Fatal error: Call to undefined method mysqli::num_rows()

 

But I've got to run. If I can't get it from here on my own, I'll continue this thread. Thanks again Thorpe, and thanks to everyone who helped.

 

 

[woodsonoversoul]

PS. That's from using the mysqli method

Thanks again

[deadimp]

Just a quick thing.

On this line:

$totSpent = totSpent + $row[4];

It's valid (except for the missing sigil $), but you can just use the += operator to make thing simpler:

$totSpent+=$row[4];

And of course, you have other operators like this: -= *= /= .= %= &= |= ^=

[jd2007]

please post the full code. thanks.

[woodsonoversoul]

As I said, I changed my code to the mysqli code suggested by Thorpe.

Now I get:

Fatal error: Call to undefined method mysqli::num_rows()

 

Any help is much appreciated. This is a page I'm working on to try to become better versed in php and mysql, so this is definatly a learning experience, and this is all stuff I have strung together from following numerous quides, I'm actually suprised any of it works. Once I solve this problem and why the delete function doesn't work, I'm going to re-write the page, so hopefully I'll better understand. Thanks again for everyone's help and advice.

 

FULL CODE (minus the private login information) IS:

// create mysqli object

// open connection

$mysqli = new mysqli($host, $user, "", $db);

 

// check for connection errors

if (mysqli_connect_errno()) {

    die("Unable to connect!");

}

 

// if id provided, then delete that record

if (isset($_GET['id'])) {

// create query to delete record

    $query = "DELETE FROM purchase WHERE id = ".$_GET['sale_id'];

   

// execute query

    if ($mysqli->query($query)) {

    // print number of affected rows

    echo $mysqli->affected_rows." row(s) affected";

    }

    else {

    // print error message

    echo "Error in query: $query. ".$mysqli->error;

    }

}

// query to get records

$query = "SELECT * FROM purchase";

 

 

 

// execute query

if ($result = $mysqli->query($query)) {

    // see if any rows were returned

    if ($result->num_rows > 0) {

        // yes

        // print them one after another

echo $row[0];

echo "<h1>category  location  price</h1>";

        echo "<table cellpadding=10 border=1>";

        while($row = $result->fetch_array()) {

            echo "<div id=\"navcontainer\">";

    echo "<ul>";

            echo "<li><a href=#>".$row[2]."</li>";

    echo "<li><a href=#>".$row[3]."</li>";

    echo "<li><a href=#>$".$row[4]."</li>";

            echo "<li><a href=".$_SERVER['PHP_SELF']."?id=".$row[0].">x</a></li>";

            echo "</ul>";

echo "</div>";

        }

    }

// display total

  $sql = "SELECT SUM(price) AS total FROM purchase";

  if ($result = $mysqli->query($sql)) {

    if ($mysqli->num_rows()) {

      $row = $result->fetch_assoc();

      echo $row['total'];

    }

  } else {

    echo $mysqli->error;

  }

    // free result set memory

    $result->close();

 

}

else {

    // print error message

    echo "Error in query: $query. ".$mysqli->error;

}

 

// close connection

$mysqli->close();

 

?>