5 Replies - 286 Views - Last Post: 11 September 2013 - 10:08 AM Rate Topic: -----

#1 reholder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 06-December 12

PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 06:49 AM

Hey there.
I've got a php code that updates a database with user input however it's supposed to update the webpage with the new database values. There are two items on the page that should update, however only one of the works. The other just stays the same. It's a donation script where there are two tables - one for required items and one for donated items. The script takes the amount being donated and subtracts it from the required , showing the user how many more donations are needed.

$results = $db->query("SELECT * FROM required_items;");
    $donations = $db->query("SELECT * FROM donations;");

    // Print out the table tag and header
    echo("<form name=\"donationForm\" action=\"" . $pageName ."\" method=\"POST\">\n<fieldset>");
    echo("<table>\n");
    echo("<tr>\n<th>Item Name</th><th>Amount</th>\n</tr>\n");

    try{    
        // For every row in the result of a query
        foreach($results as $row) {
            // Loop through every donation
            foreach($donations as $donation){
                // If the donated item matches the current row item
                if($donation['item_id'] == $row['id']){
                    // Subtract donated amount from the required amount
                    $row['required_amount'] = $row['required_amount'] - $donation['donation_amount'];
                    // Round negative amounts to zero
                    if($row['required_amount'] < 0){
                        $row['required_amount'] = 0;
                    }
                }
            }

            // Create a new table row
            echo("<tr>");
            // Create a new table cell for the name and required_amount fields
            echo("<td>" . $row['name'] . "</td>");
            echo("<td>" . $row['required_amount'] . "</td>");
            echo("<td><input type=\"radio\" name=\"radioButtons\" value=\"". $row['id'] ."\"></input></td>");
            echo("</tr>\n");
        }
    } catch(PDOException $e) {
        // Catch all errors and print them out
        echo("Error: ");
        echo($e->getMessage());
    }



As I said before, there are two items in the database but only one amount is being shown as updated. Any idea why the second one does not update?

Is This A Good Question/Topic? 0
  • +

Replies To: PHP code updating database, but not reflecting on web page

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3515
  • View blog
  • Posts: 10,143
  • Joined: 08-June 10

Re: PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 07:07 AM

might have something to do with intercrossing queries.

nevertheless, you have a much bigger problem: I heavily doubt that a nested loop like that is either intended or sensible. currently you fetch 1 required item and then run all donations under that, then the next item and then there are no more donations to loop over, etc.

EDIT: btw. if you finish the loop before your output, you can only get the last defined value (which does not need to contain data).

what you want (but not have coded) is to fetch 1 item and show the appropriate donation sum. for that you need to organise your data at the SQL level with a JOIN. since you left us no clue (thanks to the SQL wildcard *) what fields are in the DB there is some wild guessing now:
SELECT
  r.name AS Name,
  r.required_amount - d.donation_amount AS Amount_Left
FROM required_items AS r
JOIN donations AS d
  ON d.item_id = r.id
;

Now you can use a single query where all results are neatly ordered and already calculated.

note, if required_items.required_amount is an unsigned integer, it can’t be below 0.

This post has been edited by Dormilich: 11 September 2013 - 07:10 AM

Was This Post Helpful? 0
  • +
  • -

#3 reholder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 06-December 12

Re: PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 07:29 AM

Alright, well if you couldn't tell I'm still learning php and mysql. Just to clarify, you're saying is it'd be better off if the number of required items was calculated in the database and then picked up by the PHP as opposed to calculating it in the PHP?

To clarify on my end, the database includes two tables, one for required_items and one for donations.

required_items has: id, name, required_amount

donations has: id, name, email, donation_amount, item_id

Does that help clarify my information?
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3515
  • View blog
  • Posts: 10,143
  • Joined: 08-June 10

Re: PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 07:31 AM

Quote

Just to clarify, you're saying is it'd be better off if the number of required items was calculated in the database and then picked up by the PHP as opposed to calculating it in the PHP?

I do.
Was This Post Helpful? 0
  • +
  • -

#5 reholder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 06-December 12

Re: PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 07:49 AM

View PostDormilich, on 11 September 2013 - 08:07 AM, said:

SELECT
  r.name AS Name,
  r.required_amount - d.donation_amount AS Amount_Left



With my database information, this would be:

SELECT
  required_items.name AS Name,
  required_items.required_amount - donations.donation_amount AS Amount_Left
FROM required_items AS r
JOIN donations AS d
  ON d.item_id=r.id;



Correct? This should just replace my code in the PHP file I posted...
Was This Post Helpful? 0
  • +
  • -

#6 reholder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 06-December 12

Re: PHP code updating database, but not reflecting on web page

Posted 11 September 2013 - 10:08 AM

OK so I went ahead and did what I think is correct, but I'm getting an error now. mysql error 1054: Unknown column 'required_items.name', but that is in fact the column name...Here's what it looks like.

$amount_left = $db->query("SELECT required_items.name AS Name, required_items.required_amount - donations.donation_amount AS Amount_Left 
								FROM required_items AS r JOIN donations AS d ON d.item_id=r.id");

	// Print out the table tag and header
	echo("<form name=\"donationForm\" action=\"" . $pageName ."\" method=\"POST\">\n<fieldset>");
	echo("<table>\n");
	echo("<tr>\n<th>Item Name</th><th>Amount</th>\n</tr>\n");

	try{	
		// Round negative amounts to zero
		if($amount_left['Amount_Left'] < 0){
			$amount_left['Amount_Left'] = 0;
			}

			// Create a new table row
			echo("<tr>");
			// Create a new table cell for the name and required_amount fields
			echo("<td>" . $amount_left['Name'] . "</td>");
			echo("<td>" . $amount_left['Amount_Left'] . "</td>");
			echo("<td><input type=\"radio\" name=\"radioButtons\" value=\"". $row['id'] ."\"></input></td>");
		    echo("</tr>\n");
			
	} catch(PDOException $e) {
		// Catch all errors and print them out
		echo("Error: ");
    	echo($e->getMessage());
	}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1