Database Items are not Showing up on Webpage

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 695 Views - Last Post: 18 September 2013 - 10:33 PM Rate Topic: -----

#16 reholder  Icon User is offline

  • New D.I.C Head

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

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 12:52 PM

I guess for whatever reason I thought having more than one large PDO statement was to be avoided. All the variables seem hard to keep track of. Do they have to be unique every time you write a new PDO statement?
Was This Post Helpful? 0
  • +
  • -

#17 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 12:59 PM

Quote

I guess for whatever reason I thought having more than one large PDO statement was to be avoided.

what is a large PDO statement?

Quote

All the variables seem hard to keep track of.

I count 5 variables. Iíd say thatís a manageable amount.

Quote

Do they have to be unique every time you write a new PDO statement?

they better do. overwriting variables with values from different sources often leads to confusion.

anyways, you problem is that you (seemingly) donít understand how you fetch data from a DB in general. maybe Iím wrong and there would have been no problem with the old mysql extension, but you can use PDO code design-wise in the exactly same way (though there are more comfortable ways as well).
Was This Post Helpful? 0
  • +
  • -

#18 reholder  Icon User is offline

  • New D.I.C Head

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

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 01:16 PM

I'm trying to learn more about PHP and SQL through this small project of mine. I have never worked with databases before so most of this syntax is very new to me. I'm sorry for all the questions but I have no one to ask but online. What I was trying to ask in my last post was, these PDO statements seem to involve a lot of variables. If I do another PDO statement it just seems like the variables will start adding up.

Also, because I'm unsure of the formatting, I don't know basic information such as whether or not another PDO statement would be a good idea. I already have this one:

$request = "SELECT 
				required_items.name, 
				required_items.required_amount - COALESCE(donations.donation_amount, 0) AS Amount_Left,
				required_items.id
				FROM required_items LEFT JOIN donations ON donations.item_id=required_items.id";
					
	$stmt = $db->query($request);
	$item_info = $stmt->fetch();


Which as of right now gives me the "Amount_Left". Do I need to make another large statement to echo out each item in the table, or should I/can I modify this one to do that as well?

In the example posted on the manual, the statement is located inside a function.
function readDataForwards($dbh) {
  $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY BET';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
      $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
      print $data;
    }
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}

print "Reading forwards:\n";
readDataForwards($conn);



Would this be a better way to do it? If so, where does $conn come from?

This post has been edited by reholder: 16 September 2013 - 01:17 PM

Was This Post Helpful? 0
  • +
  • -

#19 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 03:42 PM

Quote

Which as of right now gives me the "Amount_Left".

and the name and the ID.

I think there is no need to use a cursor here. just a standard foreach() should do.
Was This Post Helpful? 0
  • +
  • -

#20 reholder  Icon User is offline

  • New D.I.C Head

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

Re: Database Items are not Showing up on Webpage

Posted 17 September 2013 - 07:20 AM

OK! Thanks to all your help, I've got it listing all the rows in the database. One (hopefully) quick question though, seems like something simple I'm over looking.

I've got the form working, so I go to "donate" an item. It subtracts as expected and displays the correct number on the page. When I go to "donate" the same item again, it creates a new table row and displays the new amount left. How can I make sure it does not create a new row?

$request = "SELECT 
				required_items.name, 
				required_items.required_amount - COALESCE(donations.donation_amount, 0) AS Amount_Left,
				required_items.id
				FROM required_items LEFT JOIN donations ON donations.item_id=required_items.id";
					
	$stmt = $db->query($request);
	$item_info = $stmt->fetchAll();
	
	} catch (PDOException $e) {
    	echo "Exception: " . $e->getMessage(); //TODO better error handling
	}
	
	// Round negative amounts to zero
	if($item_info['Amount_Left'] < 0){
		$item_info['Amount_Left'] = 0;
		}
?>
	<form name="donationForm" action="<?php $pageName ?>" method="POST">
	<fieldset>
	<table border="1">
        <tbody>
            <tr>
                <td width="200">Item Name</th><td width="100">Amount</th><td width="0"></th>
            </tr>
            <tr>
            <?php
				foreach ($item_info as $row):{
					echo("<td>" . $row['name'] . "</td>");
					echo("<td>" . $row['Amount_Left'] . "</td>");
					echo("<td><input type=\"radio\" name=\"radioButtons\" value=\"". $row['id'] ."\"></input></td></tr>\n<tr>");
				} endforeach;
			?>
            </tr>
        <tbody>
    </table>

This post has been edited by reholder: 17 September 2013 - 07:31 AM

Was This Post Helpful? 0
  • +
  • -

#21 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 17 September 2013 - 09:27 AM

Quote

How can I make sure it does not create a new row?

Iíd say INSERT Ö ON DUPLICATE KEY UPDATE
Was This Post Helpful? 0
  • +
  • -

#22 reholder  Icon User is offline

  • New D.I.C Head

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

Re: Database Items are not Showing up on Webpage

Posted 18 September 2013 - 01:42 PM

I believe the INSERT here is not a problem. I'm inserting into the "donations" table. When updating the "donations" table with donations, I want separate rows for each donation so I can keep track of who has donated what. The problem is the output of the SELECT statement.

I don't want the SELECT to display rows with the same donations.id, however I want the Amount_Left to be updated based on the amount that is already in the donation_amount column for that item_id.

If I use the INSERT ... ON DUPLICATE KEY UPDATE, how can I affect the temporary table that is displayed on the page? From what I've read, INSERT ... ON DUPLICATE KEY UPDATE would be used if I wanted to update something in the "donations" table.
Was This Post Helpful? 0
  • +
  • -

#23 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 18 September 2013 - 10:33 PM

View Postreholder, on 18 September 2013 - 10:42 PM, said:

I don't want the SELECT to display rows with the same donations.id, however I want the Amount_Left to be updated based on the amount that is already in the donation_amount column for that item_id.

then you need aggregation through GROUP BY.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2