mysql query error(need help)

Posting Whiz in Training

232 posts since Jan 2009

Reputation Points: 7

Solved Threads: 1

Try and simplify the mysql query with the following on line 4:

$query_recunvesummary="SELECT * FROM paymentsummary WHERE username='".mysql_real_escape_string($_SESSION['MM_Username'])."' AND paymentmode='".mysql_real_escape_string($modeofpay)."' AND foryear='".mysql_real_escape_string($_SESSION['MM_yearlynow'])."' AND initialstatus='".mysql_real_escape_string($_SESSION['MM_paymentstatus'])."'");

And also make sure the variables/arrays have valid values.

cwarn23

Occupation: Genius

Team Colleague

3,033 posts since Sep 2007

Reputation Points: 413

Solved Threads: 259

In short, "WHERE field=value", you should have "value" inside single quotes ('). I stubbed my toes on this many times until I learned to put the value in quotes. Unless it was a function, of course.

Posting Whiz in Training

242 posts since Aug 2007

Reputation Points: 51

Solved Threads: 35

Try and simplify the mysql query with the following on line 4:

$query_recunvesummary="SELECT * FROM paymentsummary WHERE username='".mysql_real_escape_string($_SESSION['MM_Username'])."' AND paymentmode='".mysql_real_escape_string($modeofpay)."' AND foryear='".mysql_real_escape_string($_SESSION['MM_yearlynow'])."' AND initialstatus='".mysql_real_escape_string($_SESSION['MM_paymentstatus'])."'");

And also make sure the variables/arrays have valid values.

Ive modified the code a little bit but still it returns $totalunvepayment to zero:Whats wrong with this code.?

$query_recunvesummary="SELECT * FROM paymentsummary WHERE username='".mysql_real_escape_string($_SESSION['MM_Username'])."' AND paymentmode='".mysql_real_escape_string($modeofpay)."' AND foryear='".mysql_real_escape_string($_SESSION['MM_yearlynow'])."' AND initialstatus='".mysql_real_escape_string($_SESSION['MM_paymentstatus'])."'";

$recunvesummary = mysql_query($query_recunvesummary, $enamysqldb) or die(mysql_error());
$row_recunvesummary = mysql_fetch_assoc($recunvesummary);
$totalRows_recunvesummary = mysql_num_rows($recunvesummary);

$totalunvepayment=0;
while($row = mysql_fetch_array($recunvesummary)){
	$totalunvepayment = $totalunvepayment + $row['amountpaid'];
}
$_SESSION['MM_totalunvepayment']=$totalunvepayment;

When i echo the sql statement its echos (Resource ID #9) what does this mean.?

Pls help.

God bless on this mother earth.

Posting Whiz in Training

232 posts since Jan 2009

Reputation Points: 7

Solved Threads: 1

Ive modified the code a little bit but still it returns $totalunvepayment to zero:Whats wrong with this code.?

$query_recunvesummary="SELECT * FROM paymentsummary WHERE username='".mysql_real_escape_string($_SESSION['MM_Username'])."' AND paymentmode='".mysql_real_escape_string($modeofpay)."' AND foryear='".mysql_real_escape_string($_SESSION['MM_yearlynow'])."' AND initialstatus='".mysql_real_escape_string($_SESSION['MM_paymentstatus'])."'";

$recunvesummary = mysql_query($query_recunvesummary, $enamysqldb) or die(mysql_error());
$row_recunvesummary = mysql_fetch_assoc($recunvesummary);
$totalRows_recunvesummary = mysql_num_rows($recunvesummary);

$totalunvepayment=0;
while($row = mysql_fetch_array($recunvesummary)){
	$totalunvepayment = $totalunvepayment + $row['amountpaid'];
}
$_SESSION['MM_totalunvepayment']=$totalunvepayment;

When i echo the sql statement its echos (Resource ID #9) what does this mean.?

Pls help.

God bless on this mother earth.

Are you missing the connect statement, like:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

And why are you fetching data into an associative array via mysql_fetch_assoc() and then using mysql_fetch_array()? I think you can only fetch the data once....

Here's what I do with great success:

// function do_sql executes the specified statements, handles error,
//   and returns the resource by reference.

function do_sql($db, &$resource, $err_msg, $qstring) {
    //print "<p>$qstring</p>\n";
    $resource = mysql_query($qstring, $db)
      or die ($err_msg.": ".mysql_error());
}

// Database preparation
$datasetobj = mysql_connect("localhost", "username", "password")
      or die ("Unable to connect to database");
mysql_select_db("db_name", $dataestobj)
      or die ("Unable to select database");

// Query operation
do_sql($datasetobj,  $result, "Couldn't fetch data",
      "SELECT * FROM my_table
        WHERE my_column='5'");
$my_row_count = mysql_num_rows($result);
//print("<pre>Rows returned: $my_row_count</pre>\n");

// Result handling
if ($my_row_count>0)
{
  while ($my_row = mysql_fetch_array($result))
  {
    //print("<pre>"); print_r($my_row); print("</pre>\n");
    .
    .
    .
  }
}

You can uncomment the print statement in do_sql() to see the query that's being processed. And you can uncomment the prints in the fetch loop to see the data that's returned. These debug prints may show up in weird places, but you'll at least be able to see what's going on. And you can comment them out or delete them for 'production'.

Don't ask how many months it took me to get php/mysql to work reliably. And don't ask how manyyears it took before I realized I should write do_sql(), and to let PHP put the result into an associate array (with both numeric indices and column-name indices). :) :)

With that little bit of code above, I can whip up mysql-based web pages lickety-split. Each query gets a unique error message so I know where the failure is.

Posting Whiz in Training

242 posts since Aug 2007

Reputation Points: 51

Solved Threads: 35

Are you missing the connect statement, like:
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
And why are you fetching data into an associative array via mysql_fetch_assoc() and then using mysql_fetch_array()? I think you can only fetch the data once....

Here's what I do with great success:
// function do_sql executes the specified statements, handles error,
//   and returns the resource by reference.

function do_sql($db, &$resource, $err_msg, $qstring) {
    //print "<p>$qstring</p>\n";
    $resource = mysql_query($qstring, $db)
      or die ($err_msg.": ".mysql_error());
}

// Database preparation
$datasetobj = mysql_connect("localhost", "username", "password")
      or die ("Unable to connect to database");
mysql_select_db("db_name", $dataestobj)
      or die ("Unable to select database");

// Query operation
do_sql($datasetobj,  $result, "Couldn't fetch data",
      "SELECT * FROM my_table
        WHERE my_column='5'");
$my_row_count = mysql_num_rows($result);
//print("<pre>Rows returned: $my_row_count</pre>\n");

// Result handling
if ($my_row_count>0)
{
  while ($my_row = mysql_fetch_array($result))
  {
    //print("<pre>"); print_r($my_row); print("</pre>\n");
    .
    .
    .
  }
}
You can uncomment the print statement in do_sql() to see the query that's being processed. And you can uncomment the prints in the fetch loop to see the data that's returned. These debug prints may show up in weird places, but you'll at least be able to see what's going on. And you can comment them out or delete them for 'production'.

Don't ask how many months it took me to get php/mysql to work reliably. And don't ask how manyyears it took before I realized I should write do_sql(), and to let PHP put the result into an associate array (with both numeric indices and column-name indices). :) :)

With that little bit of code above, I can whip up mysql-based web pages lickety-split. Each query gets a unique error message so I know where the failure is.

thank you for this reply. Ive appreciate the function defination and the function call. But i cant just imagine why the while loop doesnt execute the loop even if there is va value:

while($row = mysql_fetch_array($recunvesummary)){
	$totalunvepayment = $totalunvepayment + $row['amountpaid'];
}

I cant just understand whats wrong with this.! If someone can help find the error, Im so thank you to you and to everyone.

Posting Whiz in Training

232 posts since Jan 2009

Reputation Points: 7

Solved Threads: 1

Show Comments

.
  .
  .
$row_recunvesummary = mysql_fetch_assoc($recunvesummary);
$totalRows_recunvesummary = mysql_num_rows($recunvesummary);

$totalunvepayment=0;
while($row = mysql_fetch_array($recunvesummary)){
	$totalunvepayment = $totalunvepayment + $row['amountpaid'];
}
  .
  .
  .
i dont know if theres something wrong on the query because the debugger does not detect an error.But it retuns $totalunvepayment to zero(0) even if there is a value in the mysqldatabase.!

Whats wrong with this.

Pls help God bless on this mother Earth.

Let me try again. If there is only one matching row in the database, the mysql_fetch_assoc() function grabs it, leaving nothing for your while loop to do. If there is more than one matching row, your sum will not include the first row.

Print out the actual query string you are sending to mysql, so you can see that that string is actually correct.

Print out the result of each fetch so youknow what is being fetched from the database, if anything.

Other than your seemingly extraneous mysql_fetch_assoc() call, you code looks like it should work.

Posting Whiz in Training

242 posts since Aug 2007

Reputation Points: 51

Solved Threads: 35

Let me try again. If there is only one matching row in the database, the mysql_fetch_assoc() function grabs it, leaving nothing for your while loop to do. If there is more than one matching row, your sum will not include the first row.

Print out the actual query string you are sending to mysql, so you can see that that string is actually correct.

Print out the result of each fetch so you know what is being fetched from the database, if anything.

Other than your seemingly extraneous mysql_fetch_assoc() call, you code looks like it should work.

yes ive echo all the data on my sql.but when there is two matching row only one row will be display. But if there is only one matching row. it returns to zero. What should i do to this.? Something wrong with the loop.? or can i just use for loop.? how can it be done.?

Pls help.Thank you very many for giving time to this.

Posting Whiz in Training

232 posts since Jan 2009

Reputation Points: 7

Solved Threads: 1

yes ive echo all the data on my sql.but when there is two matching row only one row will be display. But if there is only one matching row. it returns to zero. What should i do to this.? Something wrong with the loop.? or can i just use for loop.? how can it be done.?

Pls help.Thank you very many for giving time to this.

Will it help if I shout?

DELETE THE LINE WHERE YOU CALL MYSQL_FETCH_ASSOC()! IT IS EATING THE FIRST ROW RETURNED. IT IS THE LINE RIGHT ABOVE THE LINE WHERE YOU CALL MYSQL_NUM_ROWS()! DELETE THE MYSQL_FETCH_ASSOC() LINE AND YOUR PROGRAM WILL DO WHAT YOU EXPECT!

You now have the answer three different ways. I can't do any more!

Posting Whiz in Training

242 posts since Aug 2007

Reputation Points: 51

Solved Threads: 35

Will it help if I shout?

DELETE THE LINE WHERE YOU CALL MYSQL_FETCH_ASSOC()! IT IS EATING THE FIRST ROW RETURNED. IT IS THE LINE RIGHT ABOVE THE LINE WHERE YOU CALL MYSQL_NUM_ROWS()! DELETE THE MYSQL_FETCH_ASSOC() LINE AND YOUR PROGRAM WILL DO WHAT YOU EXPECT!

You now have the answer three different ways. I can't do any more!

thank youoooooooooooooooooooooooooooo.....very many much.............................Ive seen it now.. I understand what you mean just a minute. If i own a company i will hire you sir.hehe Thank you very much and to all who supported my thread...thanks also to this forum. This problem was solved.