Database Items are not Showing up on Webpage

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 reholder  Icon User is offline

  • New D.I.C Head

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

Database Items are not Showing up on Webpage

Posted 12 September 2013 - 09:12 AM

I'm working with a php script and connecting via PDO to a database. the database contains 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

Here's the simple code I'm working with.

<?php
    	$pageName = "/donations/index.php";
    	$databaseHost = "localhost";
    	$databaseName = "donations";
    	$databaseUser = "****";
    	$databasePassword = "****";
    	
    	//TODO Check, validate, sanitize your input...
    	$name = $_POST['name'];
    	$email = $_POST['email'];
    	$donation_amount = $_POST['amount'];
    	$item_id = $_POST['radioButtons'];
    
    	try {
    		$db = new PDO('mysql:host=' . $databaseHost . ';dbname=' . $databaseName . ';charset=utf8', $databaseUser, $databasePassword);
    		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    		$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    	} catch (PDOException $e) {
    		echo "Exception: " . $e->getMessage(); //TODO better error handling
    	}
    	
    	// Check to see if someone wants to donate something
    	if(!empty($_REQUEST['donate']))
    	{
    		try {
    			//Construct your query with placeholders
    			$sql = "INSERT INTO donations (name, email, donation_amount, item_id) VALUES (?, ?, ?, ?)";
    			//Prepare your query
    			$query = $db->prepare($sql);
    		
    			//Execute it passing parameters
    			$query->execute(array($name, $email, $donation_amount, $item_id));
    			
    			echo("Thank you for donating!\n<br>\n<br>");
    	} catch (PDOException $e) {
        	echo "Exception: " . $e->getMessage(); //TODO better error handling
    	}
    	}
    	
    	$request = "SELECT 
    				required_items.id,
    				required_items.name, 
    				required_items.required_amount - donations.donation_amount AS Amount_Left,
    				donations.item_id
    				FROM required_items JOIN donations ON donations.item_id=required_items.id";
    					
    	$stmt = $db->query($request);
    	$item_info = $stmt->fetch();
    	
    	// Round negative amounts to zero
    	if($item_info['Amount_Left'] < 0){
    		$item_info['Amount_Left'] = 0;
    		}
    
    ?>
    	<!--Print out the table tag and header-->
    	<form name="donationForm" action="<?php $pageName ?>" method="POST">
    	<fieldset>
    	<table>
    	<tr>
    		<th>Item Name</th><th>Amount</th>
        </tr>
        <tr>
           	<td><?php $item_info['name'] ?></td>
            <td><?php $item_info['Amount_Left'] ?></td>
            <td><input type="radio" name="radioButtons" value="<?php $item_info['item_id'] ?>"></input></td>
        </tr>
        </table>
        <div><label>Amount</label><input type="number" name="amount"></div>
        <div><label>Email</label><input type="email" name="email"></div>
        <div><label>Name</label><input type="text" name="name"></div>
        <div><input type="submit" name="donate" value="Donate"></div>
        </fieldset>
        </form>


Can anyone help me out? I've included the whole script because I'm not sure what exactly is going wrong. I've tried echoing out the information in the table but it did not work either. Essentially nothing shows up in the table fields except a radio button.

This post has been edited by Dormilich: 12 September 2013 - 10:13 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Database Items are not Showing up on Webpage

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 10:20 AM

you can remove lines #18-#20, #25 an move #35-#37 after #48. this way you donít get an "uncaught exception" error should happen something to the second request.

if nothing shows up (and there is no error) it could be that you just have no match in the query.

simplem test query: SELECT COUNT() FROM required_items JOIN donations ON donations.item_id=required_items.id
Was This Post Helpful? 1
  • +
  • -

#3 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 12 September 2013 - 10:36 AM

Thanks for your help.

I just tried that test query and I got an error. I think it's because there are no donations yet, as nothing has been "donated" because the table doesn't show what is required for donation. required_items has 5 rows in it.
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 10:50 AM

if you want the query to run for all items even if there is no donation for that available use a LEFT JOIN and coalesce the null values to 0.

This post has been edited by Dormilich: 12 September 2013 - 10:51 AM

Was This Post Helpful? 1
  • +
  • -

#5 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 12 September 2013 - 11:31 AM

So, can I ask, why a LEFT JOIN? I'm looking of the table to display every required item and amount needed for donations, no matter what. The only thing that changes is the number of each item needed, which changes based upon the entries in the donations table.

Will the COALESCE return anything if there are no donations?
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 11:36 AM

LEFT JOIN (with a really nice example)

COALESCE()

Quote

The only thing that changes is the number of each item needed, which changes based upon the entries in the donations table.

if you only want to show items that are also in the donation table, use the JOIN. but you will have to live with the fact that you donít get a result if no donations are made at all.
Was This Post Helpful? 1
  • +
  • -

#7 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 12 September 2013 - 12:09 PM

Ok the left join makes total sense, as I always want the required_items to be on the page. Coalesce is really not making sense to me though. I think the script may be getting confused because NULL in the donations_amount field does not equal 0, therefore it is not showing a number next to the item name in the table.

By the way, is there an easier way for me to show you how it's looking?
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 12:29 PM

Quote

I think the script may be getting confused because NULL in the donations_amount field does not equal 0

thatís why I said to use COALESCE(). itís a way to convert NULL to something other.

I mean, the documentation is pretty clear about that, isnít it?
Was This Post Helpful? 0
  • +
  • -

#9 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 12 September 2013 - 01:07 PM

I'm sorry but it is not clear to me. All I've read is it returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

In my list there are no non-NULL values, therefore it returns NULL. Am I wrong? How is that supposed to work?
Was This Post Helpful? 0
  • +
  • -

#10 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 01:14 PM

Quote

In my list there are no non-NULL values, therefore it returns NULL. Am I wrong? How is that supposed to work?

since I do not know how you used the COALESCE(), how should I answer that?

besides that, both cases are explained by example in the link provided.

This post has been edited by Dormilich: 12 September 2013 - 01:16 PM

Was This Post Helpful? 0
  • +
  • -

#11 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 12 September 2013 - 01:35 PM

Ok, well I'm not sure where I should do it, but I've tried this:

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


The only thing returned in the table is the name of the first item.
Was This Post Helpful? 0
  • +
  • -

#12 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 12 September 2013 - 01:42 PM

it should also output the required item’s ID. (the ID is in the query, but not used in the output)

donation_amount (despite not assigned to a table) should be NULL and COALESCE(NULL) is NULL (as per definition, the list contains only NULL values (one NULL value to be exact) and hence returns NULL).

the subtraction also yields NULL (number - NULL = NULL, as per SQL rules) ... guess what the COALESCE() was meant for ...


in summary, one name in the table is the expected result of your code.

This post has been edited by Dormilich: 12 September 2013 - 01:45 PM

Was This Post Helpful? 0
  • +
  • -

#13 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:13 PM

Ok I've figured out how to use Coalesce. Now my only question is this...

How can I get the php to echo every row of the table?

$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();
	
	} 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;
		}

?>
	<!--Print out the table tag and header-->
	<form name="donationForm" action="<?php $pageName ?>" method="POST">
	<fieldset>
	<table>
        <tbody>
            <tr>
                <td width="200">Item Name</th><td width="100">Amount</th><td width="0"></th>
            </tr>
            <tr>
            <?php
				while (){
					echo("<td>" . $item_info['name'] . "</td>");
					echo("<td>" . $item_info['Amount_Left'] . "</td>");
					echo("<td><input type=\"radio\" name=\"radioButtons\" value=\"". $item_info['id'] ."\"></input></td></tr><tr>");
				};
			?>
            </tr>
        <tbody>
    </table>


Everything I've tried in the while loop has echoed the first item an infinite amount of times.

This post has been edited by reholder: 16 September 2013 - 12:14 PM

Was This Post Helpful? 0
  • +
  • -

#14 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3511
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 12:24 PM

Quote

Everything I've tried in the while loop has echoed the first item an infinite amount of times.

of course. thatís 1) not how one writes a loop 2) you only fetch one row.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3317
  • View blog
  • Posts: 11,212
  • Joined: 12-December 12

Re: Database Items are not Showing up on Webpage

Posted 16 September 2013 - 12:27 PM

View Postreholder, on 16 September 2013 - 07:13 PM, said:

Everything I've tried in the while loop has echoed the first item an infinite amount of times.

It will do because fetch only returns a single row and you don't have a condition in the while that would terminate the loop.

If you read the documentation for fetch there are examples that demonstrate looping through the records.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2