mysql query error(need help)

Question

PinoyDev -3 Posting Whiz in Training

15 Years Ago

$_SESSION['MM_paymentstatus']="to be verified";
$modeofpay="Credit Card";
mysql_select_db($database_enamysqldb, $enamysqldb);
$query_recunvesummary=sprintf("SELECT * FROM paymentsummary WHERE username=%s AND paymentmode=%s AND foryear=%s AND initialstatus=%s", GetSQLValueString($_SESSION['MM_Username'], "text"), GetSQLValueString($modeofpay, "text"),  GetSQLValueString($_SESSION['MM_yearlynow'], "text"), GetSQLValueString($_SESSION['MM_paymentstatus'], "text"));

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

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.

php

3 Contributors
9 Replies
152 Views
23 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by PinoyDev

All 9 Replies

cwarn23 387 Occupation: Genius

15 Years Ago

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.

Fest3er 39 Posting Whiz in Training

15 Years Ago

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.

Fest3er 39 Posting Whiz in Training

15 Years Ago

.
  .
  .
$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 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.

PinoyDev commented: has the expertise +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

PinoyDev -3 Posting Whiz in Training · Answer 1 · 2009-03-24T06:13:28+00:00

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.

Fest3er 39 Posting Whiz in Training · Answer 2 · 2009-03-24T07:18:48+00:00

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 many years 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.

PinoyDev -3 Posting Whiz in Training · Answer 3 · 2009-03-24T10:32:17+00:00

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 many years 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.

PinoyDev -3 Posting Whiz in Training · Answer 4 · 2009-03-24T11:09:41+00:00

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.

Fest3er 39 Posting Whiz in Training · Answer 5 · 2009-03-24T11:23:33+00:00

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!

PinoyDev -3 Posting Whiz in Training · Answer 6 · 2009-03-24T11:29:41+00:00

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.

mysql query error(need help)

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers